Check SQL index fragmentation on the ConfigMgr database
While working on a performance problem with a couple of very talented SQL gurus I was handed this script. It checks, among other things, the fragmentation of the indexes in the ConfigMgr database. This will help tell you if your rebuild indexes task is being run often enough, or if you need to target specific indexes more often with an additional SQL Task.
**************CODE BELOW HERE********************
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'ConfigMgr'), NULL, NULL, NULL , 'DETAILED')
order by 9 desc;
GO
*************CODE ABOVE HERE**********************
Be sure you change the "ConfigMgr" above to the name of your database!!
This is going to return quite a few indexes, and if you check the 9th column (avg_fragmentation_in_percent), you can see how badly they are torn up. Now, before you get too upset that most of them read 100%, keep in mind the Page_Count column. If an index only has 5 pages, and it shows 100% fragmentation, then that is not really that big of a deal. It just means that those 5 pages aren't in order. If, however, you see an index with 20,000 pages and it shows a high fragmentation percentage....well, then you can be sure that you aren't getting all of the performance you can from your SQL database.
If you need to find out what index has a high fragmentation - check out the 2nd column. Object_ID. Note the object_id and run this query:
*********CODE BELOW HERE***************
SELECT OBJECT_NAME(OBJECT_ID)
FROM master.sys.objects
*********CODE ABOVE HERE****************
Be sure you change the "OBJECT_ID" above to the appropriate ID you want to query!!
This will return the 'common' name for the index, and should give you a good idea what table it's attached to.
So, keep in mind that the 9th column - avg_fragmentation_in_percent - will show 100% for quite a few indexes....but the page count on those indexes should be low. If you find an index with a high number of pages, and high fragmentation percent, then consider running your Rebuild Indexes task more often, or target specific indexes with a SQL task.