Category Archives: index-fragmentation

Querying fragmentation levels and how to fix them

Querying CommandLog

Backups, index-fragmentation, misc

If you have implemented Ola Hallenger scripts, http://ola.hallengren.com/, you will have a CommandLog table where you keep track of execution times.

The following query will help you get information from it, very useful for tracking backup failures: 

USE DBA
SELECT
DatabaseName,
StartTime,
DATEDIFF(MINUTE,StartTime,EndTime) 'Duration in Min',
EndTime,
ErrorNumber,
ErrorMessage,
Command,
replace(
cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(1000))
,'errorlog','')
FROM DBO.COMMANDLOG
WHERE 1=1
--and StartTime > dateadd(dd,-1,getdate())
--and CommandType in ('BACKUP_DATABASE','BACKUP_LOG','RESTORE_VERIFYONLY')
--and CommandType in ('xp_create_subdir','xp_delete_file')
--and CommandType in ('DBCC_CHECKDB','ALTER_INDEX')

and ErrorNumber <> 0

order by ID desc

Below, we take advantage of window functions and we can see the last time a database had a DBCC checkdb done, along with its duration

  

    USE DBA
    
	;with Last_DBBC as
	(
	SELECT
    DatabaseName,
    StartTime,
    EndTime,
	ROW_NUMBER() over (PARTITION by databasename order by StartTime desc) 'rownumber'
    FROM DBO.COMMANDLOG
    WHERE 1=1
	and CommandType in ('DBCC_CHECKDB')
   	)
	select
	DatabaseName,
	StartTime, EndTime,
	DATEDIFF(MI,StartTime,EndTime) 'Duration in Min',
	DATEDIFF(DD,StartTime,GETDATE()) 'Age of DBCC in days'
	from Last_DBBC
	where 1=1
	and rownumber = 1

Statistics last Updated

index-fragmentation, Performance-Scripts 

/*

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry
-- June 2012
-- Last Modified: June 19, 2012
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry

DESCRIPTION: Statistics last udpate

 


 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

 

 

-- When were Statistics last updated on all indexes?  (Query 48)
SELECT o.name, i.name AS [Index Name],  
      STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
      s.auto_created, s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);

Check Fragmentation Levels

index-fragmentation, Performance-Scripts

Fragmentation levels.  Look for the pages count over 1,000 clause. 


/*
DESCRIPTION:
Check fragmentation levels
 
CONFIGURATION
None

Author: Miguel Quintana

Compatibility list:
MSSQL2005
MSSQL2008

Does not work
MSSQL2000
 
*/

 USE yourdatabase

SELECT t.name as TableName, b.name as IndexName, b.index_id, ps.avg_fragmentation_in_percent, PAGE_COUNT AS PageCounts
, STATS_DATE( b.object_id , b.index_id ) AS LastUpdatedDate
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.tables AS t on ps.OBJECT_ID = t.OBJECT_ID
AND ps.index_id = b.index_id
WHERE 1=1
and ps.database_id = DB_ID()
and page_count > 1000        -- ignore small tables
and b.index_id > 0            -- ignore heaps
and ps.avg_fragmentation_in_percent > 10  -- ignore small fragmentation
ORDER BY t.name, b.name

If receives error: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near (. you will have to fill in with the actual Database id and plug in that value. See below 


USE [yourdatabase]
SELECT DB_ID() AS [Database ID];
GO

Rebuild – Reorganize All Indexes

index-fragmentation, Script Generator

This script gets the fragmentation level of your indexes with over 1,000 pages (that way, it skips small tables)

If the fragmentation level is less than 40%, it will reorganize the index.

If the fragmentation level is more than 40%, it will rebuild the index.

The script also presents an option to do a "dry-run", to only print the statements. 


/*
DESCRIPTION:
Reindex or Reorg all indexes based on fragmentation threshold
 
CONFIGURATION

Top: enter name of database



Compatibility list:
MSSQL2005
MSSQL2008

Does not work
MSSQL2000
 */
 
USE <database name>
GO

Declare @TestRun int;

Set @TestRun = 1   --- Set @TestRun = 1 to execute the rebuild  Set @TestRun = 0 to only print the statements.


 
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);  
DECLARE @dbid smallint;  
 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function  
-- and convert object and index IDs to names.  
 
SET @dbid = DB_ID();  
 
SELECT  
    [object_id] AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag, page_count  
 
INTO #work_to_do  
 
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')  
 
WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation  
AND index_id > 0 -- Ignore heaps  
AND page_count > 1000; -- Ignore small tables  
 
-- Declare the cursor for the list of partitions to be processed.  
 
DECLARE partitions CURSOR FOR

SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;  
 
-- Open the cursor.  
 
OPEN partitions;  
 
-- Loop through the partitions.  
 
WHILE (1=1)  
BEGIN  
FETCH NEXT  
FROM partitions  
INTO @objectid, @indexid, @partitionnum, @frag;  
 
IF @@FETCH_STATUS < 0 BREAK;  
 
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
FROM sys.objects AS o  
JOIN sys.schemas as s ON s.schema_id = o.schema_id  
WHERE o.object_id = @objectid;  
 
SELECT @indexname = QUOTENAME(name)  
FROM sys.indexes  
WHERE object_id = @objectid AND index_id = @indexid;  
 
SELECT @partitioncount = count (*)  
FROM sys.partitions  
WHERE object_id = @objectid AND index_id = @indexid;  
 
-- 40 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
 
IF @frag < 40.0  
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
 
IF @frag >= 40.0  
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE=ON)';  
 
---IF @partitioncount > 1  
---SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  

IF @TestRun = 1
EXEC (@command);  

PRINT N'Executed: ' + @command;  
END  
 
-- Close and deallocate the cursor.  
 
CLOSE partitions;  
DEALLOCATE partitions;  
 
-- Drop the temporary table.  
 
DROP TABLE #work_to_do;  
GO