OPTIMIZED INDEXING IN SQL SERVER
Indexing is an important task to keep the database performance good. But there are many issues with indexing which we being a DBA have to pay attention.
Points to remember indexing:
1. Indexing regularly increases the i/o on disk. More disk activity is always dangerous for the CPU.
2. Choosing correct fill factor is mandatory for the performance.
3. Fragmentation percentage should be rightly considered for reorganizing or rebuilding indexes.
4. All the indexes cannot be rebuild with ONLINE operation. Blob indexes should come under offline indexes.
5. Online Indexing comes under Enterprise\Developer edition.
6. Page count of indexes is important to consider for large tables.
7. Logging of data is important to know which index has taken more time for getting rebuild or reorg.
If a table is very large then huge amount of fragmented pages also do not come under more than 20% fragmentation percentage.
Keeping above point in view, Below code is built in such a way
1. It will keep the logging of every index in msdb database.
2. Depending upon edition of SQL Server, code will be intelligent enough to run in any edition.
3. BLOB indexes will be reindex'd offline only.
---NEW INDEXING CODE (LIVE)----
------------------------------------------------DECLARE @C1 CURSOR
DECLARE @Tname NVARCHAR(MAX)DECLARE @Iname NVARCHAR(MAX)DECLARE @Frag INT
DECLARE @PCount INT
DECLARE @FCount INT
DECLARE @Obj_id bigintDECLARE @Ind_id INT
DECLARE @dt1 DATETIME
DECLARE @dt2 DATETIME
DECLARE @edition NVARCHAR(100)DECLARE @version NVARCHAR(100)DECLARE @SQL1 NVARCHAR(MAX)DECLARE @SQL2 NVARCHAR(MAX)DECLARE @SQL3 NVARCHAR(MAX)DECLARE @SQL4 NVARCHAR(MAX)DECLARE @dbname NVARCHAR(MAX)DECLARE @schname NVARCHAR(MAX)IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sys.objects WHERE name LIKE 'Index_Operation')BEGIN
CREATE TABLE msdb.dbo.Index_Operation (DB_NAME NVARCHAR(MAX),Obj_id bigint,Ind_id INT,Index_name NVARCHAR(MAX),Frag_Percent INT,Page_Count INT,Fragments_Count INT,Ind_Operation VARCHAR(20),Start_time DATETIME,End_time DATETIME,Time_Taken_Sec FLOAT,TYPE CHAR(1))END
SELECT * INTO #indices FROM (SELECT
DISTINCT OBJECT_NAME(a.OBJECT_ID) Tname,b.name Iname,SUM(a.avg_fragmentation_in_percent)/COUNT(index_level) Frag,DB_NAME(a.database_id) dbname,OBJECT_SCHEMA_NAME(a.OBJECT_ID) schname,a.OBJECT_ID,a.index_id,a.avg_fragment_size_in_pages,page_count,fragment_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,NULL, NULL,NULL) AS a JOIN sys.indexes AS b WITH(nolock) ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_id WHERE a.database_id = DB_ID() AND b.name IS NOT NULL
AND a.avg_fragmentation_in_percent <>0 AND a.page_count>1000 AND fragment_count>100 AND b.allow_page_locks=1 AND b.name NOT LIKE '%merge%' GROUP BY a.OBJECT_ID,b.name,a.database_id,a.index_id,a.avg_fragment_size_in_pages,page_count,fragment_count
) ind
SET @C1= CURSOR FOR SELECT Tname,Iname,Frag,dbname,schname,[object_id],index_id,page_count,fragment_count FROM #indices
SELECT * INTO #indcolumns FROM (SELECT OBJECT_ID(A.TABLE_NAME) [OBJECT_ID],A.TABLE_SCHEMA,A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS A WHERE DATA_TYPE IN (LOWER('FILESTREAM'),LOWER('XML'),LOWER('VARBINARY'),LOWER('TEXT'),LOWER('NTEXT'),LOWER('IMAGE'))
OR
(DATA_TYPE IN (LOWER('VARCHAR'),LOWER('NVARCHAR')) AND CHARACTER_MAXIMUM_LENGTH = -1)) A
SET @edition= (SELECT CONVERT(VARCHAR(100),SERVERPROPERTY('Edition')))SET @version= (SELECT CONVERT(NVARCHAR(100),SUBSTRING(@@version,1,CHARINDEX('-',@@version)-1)))OPEN @C1 FETCH next FROM @C1 INTO @Tname,@Iname,@Frag,@dbname,@schname,@Obj_id,@Ind_id,@PCount,@FCount
WHILE (@@FETCH_STATUS=0)
BEGIN
IF(@FCount>500)
BEGIN
IF ( @edition LIKE '%Enterprise%' AND @version LIKE '%2008%')
BEGIN
IF EXISTS (SELECT [OBJECT_ID] FROM #indcolumns WHERE [OBJECT_ID]=@Obj_id)
BEGIN
BEGIN TRY
SET @SQL1='ALTER INDEX ['+@Iname +'] on ['+@dbname+'].['+@schname+'].['+@Tname+'] REBUILD'
PRINT @SQL1
SET @dt1=GETDATE()
EXEC sp_executesql @SQL1
SET @dt2=GETDATE()
INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'REBUILD',@dt1,@dt2,CAST(DATEDIFF(MILLISECOND,@dt1,@dt2) AS FLOAT)/1000,'B')
END TRY
BEGIN CATCH
SET @dt1=GETDATE()
SET @dt2=GETDATE()
INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'FAILED',@dt1,@dt2,NULL,'B')
END CATCH
END
ELSE
BEGIN
BEGIN TRY
SET @SQL2='ALTER INDEX ['+@Iname +'] on ['+@dbname+'].['+@schname+'].['+@Tname+'] REBUILD with (FILLFACTOR= 80 , SORT_IN_TEMPDB = ON, ONLINE = ON) '
PRINT @SQL2
SET @dt1=GETDATE()
EXEC sp_executesql @SQL2
SET @dt2=GETDATE()
INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'REBUILD',@dt1,@dt2,CAST(DATEDIFF(MILLISECOND,@dt1,@dt2) AS FLOAT)/1000,'I')
END TRY
BEGIN CATCH
SET @dt1=GETDATE()
SET @dt2=GETDATE()
INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'FAILED',@dt1,@dt2,NULL,'I')
END CATCH
END
END
ELSE
BEGIN
BEGIN TRY
SET @SQL3='ALTER INDEX ['+@Iname +'] on ['+@dbname+'].['+@schname+'].['+@Tname+'] REBUILD with (FILLFACTOR= 80 , SORT_IN_TEMPDB = ON) '
PRINT @SQL3
SET @dt1=GETDATE()
EXEC sp_executesql @SQL3
SET @dt2=GETDATE()
INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'REBUILD',@dt1,@dt2,CAST(DATEDIFF(MILLISECOND,@dt1,@dt2) AS FLOAT)/1000,'I')
END TRY
BEGIN CATCH
SET @dt1=GETDATE()
SET @dt2=GETDATE()
INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'FAILED',@dt1,@dt2,NULL,'I')
END CATCH
END
END
ELSE
BEGIN
BEGIN TRY
SET @SQL4='ALTER INDEX ['+@Iname+'] on ['+@dbname+'].['+@schname+'].['+@Tname+'] REORGANIZE '
PRINT @SQL4
SET @dt1=GETDATE()
EXEC sp_executesql @SQL4
SET @dt2=GETDATE()
INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'REORGANIZE',@dt1,@dt2,CAST(DATEDIFF(MILLISECOND,@dt1,@dt2) AS FLOAT)/1000,'I')
END TRY
BEGIN CATCH
SET @dt1=GETDATE()
SET @dt2=GETDATE()
INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'FAILED',@dt1,@dt2,NULL,'I')
END CATCH
END
FETCH next FROM @C1 INTO @Tname,@Iname,@Frag,@dbname,@schname,@Obj_id,@Ind_id,@PCount,@FCount
END
CLOSE @C1DELETE FROM msdb.dbo.Index_Operation WHERE End_time<=GETDATE()-31DROP TABLE #indicesDROP TABLE #indcolumnsDEALLOCATE @C1
