Hello, ConfigNinja here to write to you about optimizing the site database in System Center 2012 R2 Configuration Manager. One of the areas we cover with our customers is the ConfigMgr site database.
For the past few months I have reviewed data about the site database and ways to improve performance, especially since it’s being used constantly. One of the issues that have come up a few times is fragmented indexes. On this blog post, I will explain how to find out if you have fragmented indexes in the ConfigMgr site database and methods to detect this in your own ConfigMgr site database.
Let’s start by finding out if your site database is fragmented or not. We suggest that you rebuild those indexes if the site database is fragmented more than 10%.
You can follow these steps to determine the fragmentation of your site database.
Open SQL Management Studio > and connect to the ConfigMgr SQL Server.
Select New Query >
Select the CM_XXX (your site database)
Execute the following T-SQL command: DBCC Showcontig
Below is sample of the output this command produces:
You can also run the following T-SQL Command Line to identify those tables with more than 10% of Fragmentation:
This query is what ConfigMgr executes when running the Rebuild Index site maintenance task
Thanks for reading, leave a comment and tell me if your database was fragmented.
Special Thanks to Steven Hernandez, my long time friend Todd Feller and the PFE ConfigMgr Community for their contributions to this post.
Santos Martinez – Sr. PFE – ConfigMgr and Databases