Share via

Find the percentage of fragmentation in a table for a database (What's the permission level needed?)

  • Article

I can find out the percentage of fragmentation for a particular table using the below query:

 USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName], 
 SI.name AS [IndexName], 
 IPS.Index_type_desc, 
 IPS.avg_fragmentation_in_percent, 
 IPS.avg_fragment_size_in_pages, 
 IPS.avg_page_space_used_in_percent, 
 IPS.record_count, 
 IPS.ghost_record_count,
 IPS.fragment_count, 
 IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'master'), NULL, NULL, NULL , 'DETAILED') IPS
 JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
 JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
AND IPS.avg_fragmentation_in_percent>=20
ORDER BY 1,5
GO

If you don't want to monitor the percentage of fragmentation yourself and you want to give someone the permissions to view this information, execute the following:

 USE AdventureWorks2012;
GO
GRANT VIEW DATABASE STATE TO [domain\user_account];
GO

DO NOT give db_owner permission :-)

This blog is just an example of what level of permission need to be provided to the application users so that we DBA's can avoid having to provide such reports ourselves and educate your application developers to do the right thing.

-KKB

 

Note: Fragmentation is not a problem, what's causing too much fragmentation is the problem. If you're successful in identifying a stored procedure or a statement which caused your box to go 100% CPU, check the query plan of that articular query during the issue and during when the CPU is 40-80%. Understand why, when we have a bad performance, the SQL Server took the bad plan and see if you can improve the performance.