This script will display fragmentation info on every index in a specified table.
-- ShowIndexFragmentation.sql
SET NOCOUNT ON
-- Set variables.
DECLARE @sDatabase sysname
SET @sDatabase = 'YourDB' -- Change this to your database!
DECLARE @sTableName sysname
SET @sTableName = 'YourTable' -- Change this to your table!
DECLARE @sCmd NVARCHAR(4000)
SET @sCmd = ''
-- Create temp table.
IF OBJECT_ID('tempdb..#ShowContigRaw') IS NOT NULL DROP TABLE #ShowContigRaw
CREATE TABLE #ShowContigRaw
(
TableName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity DECIMAL(38,14),
ScanDensity INT,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL(38,14),
ExtentFrag DECIMAL(38,14)
)
-- Call DBCC SHOWCONTIG on all indexes on the table to populate the temp table.
SET @sCmd = 'USE ' + @sDatabase +
' DBCC SHOWCONTIG (''' + @sTableName +
''') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'
RAISERROR(@sCmd, 10, 1) WITH NOWAIT
INSERT INTO #ShowContigRaw EXEC(@sCmd)
-- Create temp table.
IF OBJECT_ID('tempdb..#ShowContigCooked') IS NOT NULL DROP TABLE #ShowContigCooked
CREATE TABLE #ShowContigCooked
(
TableName NVARCHAR(255), -- Table Name
ObjectId NVARCHAR(255), -- Object ID
IndexName NVARCHAR(255), -- Index Name
IndexId NVARCHAR(255), -- Index ID
Lvl NVARCHAR(255), -- Level
CountPages NVARCHAR(255), -- Page Count
CountRows NVARCHAR(255), -- Row Count
MinRecSize NVARCHAR(255), -- Min Row Size
MaxRecSize NVARCHAR(255), -- Max Row Size
AvgRecSize NVARCHAR(255), -- Avg Row Size
ForRecCount NVARCHAR(255), -- Forwarded Record Count
Extents NVARCHAR(255), -- Extent Count
ExtentSwitches NVARCHAR(255), -- Extent Switches
AvgFreeBytes NVARCHAR(255), -- AvgFreeBytes
AvgPageDensity NVARCHAR(255), -- Avg Page Density
ScanDensity NVARCHAR(255), -- Extent Contiguousness aka Scan Density
BestCount NVARCHAR(255), -- Extent Changes Best Count
ActualCount NVARCHAR(255), -- Extent Changes Actual Count
LogicalFrag NVARCHAR(255), -- Logical Fragmentation
ExtentFrag NVARCHAR(255) -- Extent Fragmentation
)
INSERT INTO #ShowContigCooked
SELECT * FROM #ShowContigRaw
-- Massage values.
UPDATE #ShowContigCooked
SET TableName = RTRIM(TableName)
UPDATE #ShowContigCooked
SET IndexName = 'N/A'
WHERE IndexName = ''
UPDATE #ShowContigCooked
SET IndexName = RTRIM(IndexName)
UPDATE #ShowContigCooked
SET CountPages = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, CountPages), 1), '.00', '')
UPDATE #ShowContigCooked
SET CountRows = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, CountRows), 1), '.00', '')
UPDATE #ShowContigCooked
SET MinRecSize = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, MinRecSize), 1), '.00', '')
UPDATE #ShowContigCooked
SET MaxRecSize = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, MaxRecSize), 1), '.00', '')
UPDATE #ShowContigCooked
SET AvgRecSize = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, AvgRecSize), 1), '.00', '')
UPDATE #ShowContigCooked
SET ForRecCount = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, ForRecCount), 1), '.00', '')
UPDATE #ShowContigCooked
SET Extents = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, Extents), 1), '.00', '')
UPDATE #ShowContigCooked
SET ExtentSwitches = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, ExtentSwitches), 1), '.00', '')
UPDATE #ShowContigCooked
SET AvgPageDensity = CONVERT(NVARCHAR,
CONVERT(MONEY, AvgPageDensity), 1) + '%'
UPDATE #ShowContigCooked
SET BestCount = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, BestCount), 1), '.00', '')
UPDATE #ShowContigCooked
SET ActualCount = REPLACE(CONVERT(NVARCHAR,
CONVERT(MONEY, ActualCount), 1), '.00', '')
UPDATE #ShowContigCooked
SET ScanDensity = CONVERT(NVARCHAR,
CONVERT(MONEY, ScanDensity), 1) + '%'
UPDATE #ShowContigCooked
SET LogicalFrag = CONVERT(NVARCHAR,
CONVERT(MONEY, LogicalFrag), 1) + '%'
UPDATE #ShowContigCooked
SET ExtentFrag = CONVERT(NVARCHAR,
CONVERT(MONEY, ExtentFrag), 1) + '%'
-- Done. Display results.
SELECT
'Name of the table or view.'
AS 'Table Name',
'OBJECT_ID of the table or view.'
AS 'Object ID',
'Name of the index.'
AS 'Index Name',
'ID of the index.'
AS 'Index ID',
'Level of the index. Level 0 is the leaf (or data) level of the index.
The level number increases moving up the tree toward the index root.
Level is 0 for a heap.'
AS 'Level',
'Number of pages comprising that level of the index or entire heap.'
AS 'Page Count',
'Number of data or index records at that level of the index.
For a heap, this is the number of data records in the entire heap.'
AS 'Row Count',
'Minimum record size in that level of the index or entire heap.'
AS 'Min Row Size',
'Maximum record size in that level of the index or entire heap.'
AS 'Max Row Size',
'Average record size in that level of the index or entire heap.'
AS 'Avg Row Size',
'On UPDATES, if there is insufficient space on the page to fit the new record,
the record is moved to a new page, and a forwarding record is left in the
original location to point to the new location.'
AS 'Forwarded Record Count',
'Number of extents in that level of the index or entire heap.'
AS 'Extent Count',
'Number of jumps from one extent to another while traversing all the pages.'
AS 'Extent Switches',
-- 'Average number of free bytes on the pages scanned. The higher the number,
-- the less full the pages are. Lower numbers are better. This number is also
-- affected by row size; a large row size can result in a higher number.'
-- AS 'AvgFreeBytes',
'Measure of how full the pages are. The higher the percentage, the better.'
AS 'Avg Page Density',
'"BestCount" / "Actual count" Is 100% if all extents are contiguous; else,
some fragmentation exists.'
AS 'Extent Changes Best Count',
'The ideal number of extent changes if the extents are perfectly contiguous.'
AS 'Extent Changes Actual Count',
'Actual number of extent changes while traversing.'
AS 'Extent Contiguousness aka Scan Density',
'Percentage of out-of-order pages returned from scanning the leaf pages of
an index. This number is not relevant to heaps and text indexes.'
AS 'Logical Fragmentation',
'Percentage of out-of-order extents in scanning the leaf pages of an index.
This number is not relevant to heaps.'
AS 'Extent Fragmentation'
UNION
SELECT
TableName AS 'Table Name', -- YourTable
ObjectId AS 'Object ID', -- 613577224
IndexName AS 'Index Name', -- tank_data_index
IndexId AS 'Index ID', -- 2
Lvl AS 'Level', -- 0
CountPages AS 'Page Count', -- 48,139
CountRows AS 'Row Count', -- 7,271,509
MinRecSize AS 'Min Row Size', -- 51
MaxRecSize AS 'Max Row Size', -- 61
AvgRecSize AS 'Avg Row Size', -- 51
ForRecCount AS 'Forwarded Record Count', -- 0
Extents AS 'Extent Count', -- 9,233
ExtentSwitches AS 'Extent Switches', -- 26,012
-- AvgFreeBytes AS 'AvgFreeBytes',
AvgPageDensity AS 'Avg Page Density', -- 98%
BestCount AS 'Extent Changes Best Count', -- 6,018
ActualCount AS 'Extent Changes Actual Count', -- 26,013
ScanDensity AS 'Scan Density', -- 23%
LogicalFrag AS 'Logical Fragmentation', -- 19%
ExtentFrag AS 'Extent Fragmentation' -- 70%
FROM #ShowContigCooked
WHERE TableName NOT LIKE 'dt%' AND
TableName NOT LIKE 'sys%'
ORDER BY 'Extent Fragmentation' DESC
DROP TABLE #ShowContigCooked
0 comments:
Post a Comment