This script will give the average space used in percentage for indexes and heaps. The lesser the space used is, the more overheard in reading them from the disks. SQL Server will read pages that are partially empty and thus more round trips would be needed to the disk sub system
By re-building the indexes with a higher fill factor, the I/O system reads can be reduced. This is especially true for tables whose data gets added sequentially as fragmentation will not be much of a concern in such tables
SELECT DB_NAME(database_id) AS database_name
, so.[name] AS table_name
, dmv.index_id
, si.[name]
, dmv.index_type_desc
, dmv.alloc_unit_type_desc
, dmv.index_depth
, dmv.index_level
, avg_fragmentation_in_percent
, avg_page_space_used_in_percent
, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(DB_NAME()), NULL, NULL, NULL, 'DETAILED') dmv
INNER JOIN sysobjects so
ON dmv.[object_id] = so.id
INNER JOIN sys.indexes si
ON
dmv.object_id = si.object_id
AND dmv.index_id = si.index_id
ORDER BY table_name, si.name
Look for the 'avg_page_space_used_in_percent' column in the above query's output
By re-building the indexes with a higher fill factor, the I/O system reads can be reduced. This is especially true for tables whose data gets added sequentially as fragmentation will not be much of a concern in such tables
SELECT DB_NAME(database_id) AS database_name
, so.[name] AS table_name
, dmv.index_id
, si.[name]
, dmv.index_type_desc
, dmv.alloc_unit_type_desc
, dmv.index_depth
, dmv.index_level
, avg_fragmentation_in_percent
, avg_page_space_used_in_percent
, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(DB_NAME()), NULL, NULL, NULL, 'DETAILED') dmv
INNER JOIN sysobjects so
ON dmv.[object_id] = so.id
INNER JOIN sys.indexes si
ON
dmv.object_id = si.object_id
AND dmv.index_id = si.index_id
ORDER BY table_name, si.name
Look for the 'avg_page_space_used_in_percent' column in the above query's output
No comments:
Post a Comment