~ Chris Butcher | Senior Support Escalation Engineer
Hi folks, it’s Chris Butcher again with part 2 in my series of posts on DPMDB maintenance. This installment covers identifying and dealing with fragmentation in your DPMDB, and if you happened to miss Part 1 you can find it here:
DPMDB Maintenance: Database consistency check and your DPMDB
I often hear questions around maintenance of the DPM database and there are two areas where these questions come in. First is how to manage the size of the database, although I won’t be going into detail on that here as that is covered in my next blog on this series. The other question concerns DPMDB optimization. The reality of things is that many DPM admins are not also SQL admins but they’re still stuck with hoping their DPM database is optimized. Well, depending on how long your DPM server has been running, this may not be the case and there are some things you can do to try to help with that.
This article breaks this down into four phases:
- Determining current fragmentation levels
- One time job to reorganize the indexes
- One time job to rebuild the indexes
- Scheduling ongoing reorganization job
SQL databases, similar to the file system on a disk, will fragment over time. As tables grow and shrink, this fragmentation can ultimately play a role in how well SQL runs. When fragmentation happens, SQL has two ways it can address the issue: Reorganizing , which is a light weight job that will defragment the leaf level of clustered and non-clustered indexes on tables, and Rebuilding , which will drop and re-create the indexes.
Both methods have a small side effect of reclaiming some disk space by compacting. More information on this topic from a SQL standpoint can be found here:
SQL Server - Reorganize and Rebuild Indexes : https://technet.microsoft.com/en-us/library/ms189858.aspx
Database maintenance in relation to reorganizing or rebuilding indexes in the DPMDB will vary by workload. The following script can be run against your DPMDB to determine the amount of fragmentation. Specifically, this will show how many indexes have >30% fragmentation and how many tables there are at that level.
NOTE These scripts are written using DPMDB for the name. Most servers will likely have a different DPMDB name (usually DPMDB_Comuptername). Be sure to change this within the script to reflect your DPMDB name.
Determining current fragmentation levels
USE [DPMDB]
SELECT OBJECT_NAME(object_id),index_id, avg_fragmentation_in_percent, fragment_count, page_count, avg_fragment_size_in_pages, index_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), Null,Null,Null,Null)
WHERE avg_fragmentation_in_percent > 30
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
order by avg_fragmentation_in_percent desc
To run the query, open SQL Management Studio (you may have to “Run as Administrator”).
1. Expand Databases to show the name of the database you will need to run this against.
2. Click New Query to open the query window.
3. Copy the query above into the query window. On the top line, change the USE DPMDB command to reflect the correct name of the database in your environment.
4. Select Execute from the top menu bar.
5. Review the output in the bottom pane. Look for avg_fragmentation_in_percent to determine the amount of fragmentation in your given database.
Using these steps, you can identify how many tables have high fragmentation and determine if you should rebuild or simply reorganize. I have some general suggestions below but please note that these values are guidelines, not hard and fast rules.
- Index should be rebuild when index fragmentation is greater than 30% or 40%.
- Index should be reorganized when index fragmentation is between 10% to 40%.
- Index rebuilding process uses more CPU and it locks the database resources and requires DPM to be turned off while it runs.
A caveat to this is that if you have fragmentation percentages higher than the suggested numbers but the fragmentation count is low (which is possible after rebuilding). In those cases, generally speaking you shouldn’t need to worry about it.
Based on your findings, you have two options as mentioned above. Option one is to reorganize the indexes when fragmentation is between 10 and 40 percent. This can be done while the DPM server is online and operating normally. It can have a slight impact on performance but shouldn’t have any noticeable negative impact.
IMPORTANT As with anything else you do with the DPMDB, it is important to back up the database before running any scripts that modify it in any way. This allows you to go back to a point in time in case of a failure. You can reference this blog post and scroll down to the sections titled Using DPMBACKUP to back up the DPMDB or Use Native SQL Server backup and not use DPM for backup at all for the easiest ways to get a backup of your DPM database.
One time job to reorganize the indexes
Using steps similar to the ones above, you can run a script to manually re-index all of the tables. Just substitute the lines below and paste them in step 3 above. Be sure to change the DPMDB name to match yours before executing.
USE [DPMDB]
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REORGANIZE"
One time job to rebuild the indexes
If you find a very high amount of fragmentation, it may be more valuable to have SQL rebuild the indexes. In order for this to happen you will have to stop the DPM services so it should be done during a slow time in the DPM cycle.
Before running this command, open Services Manager on the DPM server, stop DPM and set it to disabled.
Once the service is stopped, go back to SQL Management Studio and execute the following command to have SQL rebuild the fragmented indexes. As usual, be sure to change the DB name on the first line to match with your system.
USE [DPMDB]
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"
Now that we have things running a bit more optimized, the question arises, “How do we keep it that way?” Great question and there are several possible answers.
The article below talks about the options. I find the wizard to be the easiest to follow and of course covers what we want from a DPM perspective, so that is the one I will highlight here:
SQL Server - Create a Maintenance Plan : https://technet.microsoft.com/en-us/library/ms189953.aspx
Using the Maintenance Plan Wizard, we will walk through creation of a new plan to automatically run a reorganization of the DPMDB indexes on a scheduled interval. The schedule for this should be done to best meet your usage and needs, but for a heavily used DPMDB a general guideline for this would be to reorganize once a week and then rebuild as needed. Since that is a manual process, it can be checked as often as monthly (using the first script mentioned) but at least once per quarter. Of course if the script shows a high fragmentation again, a rebuild should be run.
Scheduling ongoing reorganization job
1. In SQL Management Studio, expand Management , right-click Maintenance Plans and select Maintenance Plan Wizard .
2. In the Wizard, give your plan a name to help recognize it, then click on Change to create a schedule for this job.
3. Choose to make the job recurring and set the schedule to something that meets your needs. As stated above, for reorganizing the indexes, once a week is the most frequent schedule you will likely need. Since this will be done while DPM is still running, try to schedule it at an hour when the fewest backups will be running to minimize the impact.
4. When selecting the maintenance tasks, check Reorganize Index only.
5. Click Next on the Task Order screen.
6. For the Define Reorganize Index Task screen, change Databases to Specific databases and select your DPMDB by name.
7. Click Next on Report Options unless you want to specify something different to receive reports.
8. Select Finish to complete the process and allow SQL to configure the schedule to run automatically.
You can continue on to Part 3 here .
Chris Butcher | Senior Support Escalation Engineer | Microsoft GBS Management and Security Division
Get the latest System Center news on Facebook and Twitter :
System Center All Up: http://blogs.technet.com/b/systemcenter/
Configuration Manager Support Team blog:
http://blogs.technet.com/configurationmgr/
Data Protection Manager Team blog:
http://blogs.technet.com/dpm/
Orchestrator Support Team blog:
http://blogs.technet.com/b/orchestrator/
Operations Manager Team blog:
http://blogs.technet.com/momteam/
Service Manager Team blog:
http://blogs.technet.com/b/servicemanager
Virtual Machine Manager Team blog:
http://blogs.technet.com/scvmm
WSUS Support Team blog: http://blogs.technet.com/sus/
The RMS blog: http://blogs.technet.com/b/rms/
App-V Team blog: http://blogs.technet.com/appv/
MED-V Team blog: http://blogs.technet.com/medv/
Server App-V Team blog: http://blogs.technet.com/b/serverappv
The Surface Team blog: http://blogs.technet.com/b/surface/
The Application Proxy blog: http://blogs.technet.com/b/applicationproxyblog/
The Forefront Endpoint Protection blog :
http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog :
http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog:
http://blogs.technet.com/b/isablog/
The Forefront UAG blog:
http://blogs.technet.com/b/edgeaccessblog/
System Center 2012 Data Protection Manager System Center 2012 R2 Data Protection Manager DPM 2012 R2