Talking Database in ConfigMgr


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.

 WIN13_Pamela_Asus_01

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:

image

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:

image

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.

image

image

Provide a name for your maintenance plan and click Next to choose the tasks we want to include in our maintenance plan.

image

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.

image

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.

image

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.

image

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.

image

Ensure there is a unique schedule for the job.

image

Finish up by configuring the Update Statistics Task and ensure it runs after all other tasks have completed.

image

At the Define History Cleanup Task, select Ok and click Next.

image

Click Change to schedule the task and click Next

Ensure you clean up your task history and click Next to complete the task.

image

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.

image

Select Rebuild Indexes and click edit.

image

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:

image

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 – http://technet.microsoft.com/en-us/library/bb632595.aspx

Rebuild Indexes Task – http://technet.microsoft.com/en-us/library/bb632800.aspx

Troubleshooting ConfigMgr Performance – http://technet.microsoft.com/en-us/library/bb932206.aspx

For System Center 2012 Configuration Manager:

Planning for Maintenance Tasks for Configuration Manager – http://technet.microsoft.com/en-us/library/e555d7e3-3681-440a-82d0-319d2b4bdd08#BKMK_PlanMaintenanceTasks

 
Non Product specific:

sys.dm_db_index_physical_stats

Reorganizing and Rebuilding Indexes

http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx

 

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

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

Comments (16)

  1. Anoop C Nair says:

    Hi Santos ! – Nice one ! Thank you for sharing. Do you think this method is effective than the method mentioned by Steve in the following post. http://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/

    Regards
    Anoop C Nair

  2. @Steve, Thanks for taking the time and write about it, I really appreciated the time and effort. Good work.

  3. Anoop C Nair says:

    Hi Santos ! – I’m doing well. Hope you’re also doing great and enjoying the travel ;). Thank you for answering the question.

    Regards
    Anoop

  4. Hi Anoop,

    Hope you are doing great and thanks for your question, I think Steve method is really good one for CM 2007 rebuild index and a monitor option to when the normal maintenance task did not ran.
    My blog post is more intended for 2012 and was made to explain the process troubleshoot and remediate a Index Fragmentation problem. So its really 2 different scenarios from my point of view.

    -Santos

  5. RonRatzlaff says:

    Great stuff Santos!! One question, how come your method here wasn’t covered at TechED 2014, or was it? The reason why I ask is because it looks like Stefan and Jorgen covered the "Steve Thompson" method that Anoop had mentioned, that basically involves
    creating a maintenance database and then running Ola Hallengren’s SQL script against it to create the necessary objects and then run the index optimizer script against the ConfigMgr 2012 database on a weekly basis. There seems to be some, dare I say disagreement,
    amongst some SCCM MVPs regarding the best way to go about doing this. Some of the MVPs believe that the Rebuild Indexes Site Maintenance Task in ConfigMgr 2012 still has some issues. Therefore, some of them are stating that the best method is to keep the Rebuild
    Indexes disabled and then use the Steve Thompson method. I just want a consensus and use a method that is going to work, but one that is not going to create additional overhead for me.

    Thanks Santos

  6. Steve Thompson says:

    Santos — thanks for posting this, since my name was mentioned several times, I needed to respond. Ratzinator – what Stefan and Jorgen covered, was not the "Steve Thompson" method, rather, the "Ola Hallengren" method of Index and Statistic optimization
    that I first presented at MMS 2012. There are advantages of Ola’s method, too much to explain in a comment. Look for a blog article.
    http://stevethompsonmvp.wordpress.com

  7. Anonymous says:

    Pingback from ConfigMgr 2012 Index Optimization | Steve Thompson [MVP]

  8. Anonymous says:

    Pingback from ConfigMgr 2012 Index Optimization | Steve Thompson [MVP]

  9. Anonymous says:

    Pingback from ConfigMgr 2012 Index Optimization | Steve Thompson [MVP]

  10. Anonymous says:

    Pingback from ConfigMgr 2012 Index Optimization | Steve Thompson [MVP]

  11. Anonymous says:

    Pingback from ConfigMgr 2012 Index Optimization | Steve Thompson [MVP]

  12. Anonymous says:

    Pingback from ConfigMgr 2012 Index Optimization | Steve Thompson [MVP]

  13. Anonymous says:

    Pingback from ConfigMgr 2012 Index Optimization | Steve Thompson [MVP]

  14. RonRatzlaff says:

    Steve, it’s "The_Ratzenator" dude, come on – LOL. Hey, I had to give you some credit so I thought the "Steve Thompson Method" had a nice ring to it, which you kinda gotta admit, it does. I call the method in this post here, "The Santos Martinez Method".
    I just do that to help me with my own personal notes and It’s not meant to be taken literally, unless of course you want to. I was actually more or less referring to what you had discussed regarding the creation of the CMMonitor database and then implementing
    Ola’s SQL script to create the necessary objects in that database to use against other databases. There have been other MVPs that have not mentioned the creation of a separate database (such as CMMonitor or a DBMaint etc.) with the exception of Jorgen and
    Stefan and I didn’t hear their presentation until after I began implementing Ola’s script during a test run. I am not an "SQL Guy" in the least. I can write some pretty nifty SQL Reports within the context of SCCM, but that does not deem one an SQL guru by
    any stretch. For me, it just seemed like I couldn’t find any solid consensus on how the index optimization process should be configured. So, I took a step back and decided to put in some more research time, which is where I am at now.

    HAHAHA WOW Steve, you used my comment in that post – now that is funny. Okay, it’s official, we have to call this "The_Ratzenator Method". It’s where some wise ass off of the TechNet Forums throws out names of MVPs that warrants the creation of a new post for
    the sake of clarification regarding the obscurity of a particular procedure, by an MVP. I swear that was NOT my intention, but it obviously seems to work well – LOL.

    Thanks Steve, that was a GREAT post!

    You guys are AWESOME man, seriously. You really have helped me out a TON and I truly appreciate it. I only wish I would have started posting to the TechNet Forums much much earlier. I had no idea I was going to receive so much support and help like this.

    Also, Ola, if you ever come across this, thank you very, very much as well.

    SCCM is such a BEAST of a product and having support like this is fantastic.

    Santos, we still love you man. Gotta have love for a fellow martial arts brother (BJJ).

    Take it easy guys and thanks again for your all your help.

  15. RonRatzlaff says:

    Steve, it’s "The_Ratzenator" dude, come on – LOL. Hey, I had to give you some credit so I thought the "Steve Thompson Method" had a nice ring to it, which you kinda gotta admit, it does. I call the method in this post here, "The Santos Martinez Method".
    I just do that to help me with my own personal notes and It’s not meant to be taken literally, unless of course you want to. I was actually more or less referring to what you had discussed regarding the creation of the CMMonitor database and then implementing
    Ola’s SQL script to create the necessary objects in that database to use against other databases. There have been other MVPs that have not mentioned the creation of a separate database (such as CMMonitor or a DBMaint etc.) with the exception of Jorgen and
    Stefan and I didn’t hear their presentation until after I began implementing Ola’s script during a test run. I am not an "SQL Guy" in the least. I can write some pretty nifty SQL Reports within the context of SCCM, but that does not deem one an SQL guru by
    any stretch. For me, it just seemed like I couldn’t find any solid consensus on how the index optimization process should be configured. So, I took a step back and decided to put in some more research time, which is where I am at now.

    HAHAHA WOW Steve, you used my comment in that post – now that is funny. Okay, it’s official, we have to call this "The_Ratzenator Method". It’s where some wise ass off of the TechNet Forums throws out names of MVPs that warrants the creation of a new post for
    the sake of clarification regarding the obscurity of a particular procedure, by an MVP. I swear that was NOT my intention, but it obviously seems to work well – LOL.

    Thanks Steve, that was a GREAT post!

    You guys are AWESOME man, seriously. You really have helped me out a TON and I truly appreciate it. I only wish I would have started posting to the TechNet Forums much much earlier. I had no idea I was going to receive so much support and help like this.

    Also, Ola, if you ever come across this, thank you very, very much as well.

    SCCM is such a BEAST of a product and having support like this is fantastic.

    Santos, we still love you man. Gotta have love for a fellow martial arts brother (BJJ).

    Take it easy guys and thanks again for your all your help.

  16. Daniel says:

    Hi, what is the point of having the rebuild index in configmgr in the first place, if it doesn’t actually rebuild the index? should we just remove the schedule all together from config manager and let sql handle the reindex part?

Skip to main content