First published on TECHNET on Mar 28, 2014
Authored by Santos Martinez
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
select distinct sch.name + '.' + OBJECT_NAME(stat.object_id), ind.name, convert(int,stat.avg_fragmentation_in_percent)
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') stat
join sys.indexes ind on stat.object_id=ind.object_id and stat.index_id=ind.index_id
join sys.objects obj on obj.object_id=stat.object_id
join sys.schemas sch on obj.schema_id=sch.schema_id
where ind.name is not null and stat.avg_fragmentation_in_percent > 10.0 and ind.type > 0
order by convert(int,stat.avg_fragmentation_in_percent) desc
Below are sample results from that query:
Once we confirm that we have Index fragmentation, we type the following command line to correct the issue:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
Note: This command may take some time, please allow the system to complete.
You can also schedule key maintenance tasks to occur periodically using a SQL Maintenance Plan to optimize database performance. Follow the steps below to create a maintenance plan.
In the SQL Management Studio Console, expand Management > Right Click Maintenance Plan and select Maintenance Plan Wizard.
Provide a name for your maintenance plan and click Next to choose the tasks we want to include in our maintenance plan.
In the Select Maintenance Tasks page, select the following tasks:
- Reorganize Index
- Rebuild Index
- Update Statistics
- Clean Up History
Click Next Twice, once you have all the proper tasks selected.
On the next page you will be asked to define the Reorganize Index Task. Click on Databases and select all databases if this SQL Server is used exclusively for ConfigMgr.
Click the schedule option to set the date and time this task should run. Ensure the scheduled frequency is different for each task. Avoid overlaps between tasks.
NOTE: Make there is no overlapping with other maintenance plans or site maintenance tasks, this will include backup or ConfigMgr rebuild index task.
At the Define Rebuild Index Task page, select the same databases as the first screen. It is very important that you match this on every task.
Ensure there is a unique schedule for the job.
Finish up by configuring the Update Statistics Task and ensure it runs after all other tasks have completed.
At the Define History Cleanup Task, select Ok and click Next.
Click Change to schedule the task and click Next
Ensure you clean up your task history and click Next to complete the task.
Maintenance Plan Completed, click close and proceed to the next steps.
Now that we have completed our SQL Maintenance Plan, let’s go ahead and configure the Rebuild Index site maintenance task in ConfigMgr to avoid any overlap between ConfigMgr and SQL.
Open ConfigMgr Console > go to the Administration Workspace, Expand Sites, select the site and right click Site Maintenance.
Select Rebuild Indexes and click edit.
We disabled the Rebuild Indexes from running on Saturday. The custom maintenance plan will run on that day.
Review the current schedule to ensure no overlap with your new rebuild index maintenance plan.
Final Validation
We need to confirm there is no more fragmentation in our index, you can go back to the first T-SQL command line or follow the next step to confirm your database is back to a healthy state.
Run the DBCC Showcontig again to validate the current fragmentation:
Notice there is no more fragmentation on the tables after the command line. If we test the ConfigMgr console you will notice it should be performing faster than before.
Here are some additional resources:
For System Center Configuration Manager 2007:
ConfigMgr Predefined Maintenance Tasks Overview - https://docs.microsoft.com/en-us/previous-versions/system-center/configuration-manager-2007/bb632595...
Rebuild Indexes Task - https://docs.microsoft.com/en-us/previous-versions/system-center/configuration-manager-2007/bb632800...
Troubleshooting ConfigMgr Performance - https://docs.microsoft.com/en-us/previous-versions/system-center/configuration-manager-2007/bb932206...
For System Center 2012 Configuration Manager:
Planning for Maintenance Tasks for Configuration Manager - https://docs.microsoft.com/en-us/previous-versions/system-center/system-center-2012-R2/gg712686(v=te...
sys.dm_db_index_physical_stats
Reorganizing and Rebuilding Indexes
Santos Martinez – Sr. PFE – ConfigMgr and Databases
Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of any included script samples are subject to the terms specified in the Terms of Use