Tuesday, October 1, 2013

Average Space Used in Pages

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

No comments:

Post a Comment