Posts

Showing posts from February, 2021

Rebuild all indexes in a Database

Image
Manually Rebuild Indexing   Indexes can be rebuilt/reorganize in SQL Server Management Studio using the following steps; 1- In Object Explorer locate the table that holds the intended index and expand it. 2- Expand Indexes. 3- Right-click on the index and click on Rebuild or Reorganize. Rebuild All Table Indexing By Script Try the following script: Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD' GO Also I prefer(After a long search) to use the following script, it contains  @fillfactor  determines how much percentage of the space on each leaf-level page is filled with data. DECLARE @TableName VARCHAR ( 255 ) DECLARE @sql NVARCHAR( 500 ) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(name) AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @ @FETCH _STATUS = 0 BEGIN SET

How to Check Index Fragmentation on Indexes in a Database

  Issue SQL Queries taking longer than normal to complete. Cause when a database is frequently updated via INSERT, UPDATE, or DELETE statements, over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. If database indexes are fragmented, the SQL Server query optimizer may choose a non-optimal execution plan when using an index to resolve a query. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. Resolution *Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.* The following is a simple query that will list every index on every table in your database, ordered by the