Tuesday, August 23, 2011

Alternate & Faster way to use DBCC CHECKDB!!

/*T-sql which  identifies the most common IO errors significantly faster than DBCC CHECKDB.*/


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Ensure buffer pool is empty.
DBCC DROPCLEANBUFFERS
-- Get details of all heaps, clustered indexes and non-clustered indexes to check.
SELECT ss.name AS SchemaName, st.name AS TableName, s.name AS IndexName , s.rowcnt AS 'Row Count' , s.indid INTO #IndexDetails
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100 -- Only user tables
AND s.rowcnt >= 1 -- Ignore stats rows
ORDER BY s.indid, [Row Count] DESC -- Do heaps and clustered first

DECLARE @CheckIndexesSQL NVARCHAR(MAX)


SET @CheckIndexesSQL = ''


-- Build SQL to read each page in each index (including clustered index).
SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10)+ 'SELECT COUNT_BIG(*) AS [TableName: ' + SchemaName + '.'
+ TableName + '. IndexName: ' + ISNULL(IndexName, 'HEAP') + '. IndexId: ' + CAST(indid AS VARCHAR(3)) + '] FROM '
+ QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'
FROM #IndexDetails

-- Debug.
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000

WHILE (@StartOffset < LEN(@CheckIndexesSQL))
BEGIN
PRINT SUBSTRING(@CheckIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length

END
PRINT SUBSTRING(@CheckIndexesSQL, @StartOffset, @Length)



-- Do work.
EXECUTE sp_executesql @CheckIndexesSQL
-

No comments:

Post a Comment