Database Reference
In-Depth Information
The fragmentation of the
Purchasing.PurchaseOrderHeader
table is extremely light: 28 percent. Meanwhile, the
avg_page_space_used_in_percent
is greater than 90 percent for many of the indexes. When you take into account the
number of pages for the indexes on the table (42 or less), you're very unlikely to get an improvement in performance
by defragging the index (assuming you can), as detailed in Chapter 13.
The same can be said of
Purchasing.PurchaseOrderDetail
, which has very low fragmentation and a low page
count.
Production.Product
has slightly higher degrees of fragmentation; but again, the page count is very low, so
defragging the index is not likely to help much.
Person.Employee
has one index with
66
percent fragmentation; once
again, however, it's only on three pages. Finally,
Person.Person
has almost no fragmentation to speak of.
Here's an experiment to try as part of the iterative performance-tuning process. Run the index defragmentation
script supplied in Chapter 13 (and repeated here).
DECLARE @DBName NVARCHAR(255),
@TableName NVARCHAR(255),
@SchemaName NVARCHAR(255),
@IndexName NVARCHAR(255),
@PctFrag DECIMAL,
@Defrag NVARCHAR(MAX)
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'#Frag') )
DROP TABLE #Frag;
CREATE TABLE #Frag
(
DBName NVARCHAR(255),
TableName NVARCHAR(255),
SchemaName NVARCHAR(255),
IndexName NVARCHAR(255),
AvgFragment DECIMAL
)
EXEC sys.sp_MSforeachdb 'INSERT INTO #Frag ( DBName, TableName, SchemaName, IndexName, AvgFragment )
SELECT ''?'' AS DBName ,t.Name AS TableName ,sc.Name AS SchemaName ,i.name AS IndexName ,s.avg_
fragmentation_in_percent FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL,
NULL, ''Sampled'') AS s JOIN ?.sys.indexes i ON s.Object_Id = i.Object_id
AND s.Index_id = i.Index_id JOIN ?.sys.tables t ON i.Object_id = t.Object_Id JOIN ?.sys.schemas sc
ON t.schema_id = sc.SCHEMA_ID
WHERE s.avg_fragmentation_in_percent > 20
AND t.TYPE = ''U''
AND s.page_count > 8
ORDER BY TableName,IndexName';
DECLARE cList CURSOR
FOR
SELECT *
FROM #Frag
