Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

What SQL maintenance should I perform on my OpsMgr databases?

What SQL maintenance should I perform on my OpsMgr databases?

  • Comments 28
  • Likes

This question comes up a lot.  The answer is really - not what maintenance you should be performing... but what maintenance you should be *excluding*.... or when.  Here is why:

Most SQL DBA's will set up some pretty basic default maintenance on all SQL DB's they support.  This often includes, but is not limited to:

DBCC CHECKDB  (to look for DB errors and report on them)

UPDATE STATISTICS  (to boost query performance)

DBCC DBREINDEX  (to rebuild the table indexes to boost performance)

BACKUP

SQL DBA's might schedule these to run via the SQL Agent to execute nightly, weekly, or some combination of the above depending on DB size and requirements.

On the other side of the coin.... in some companies, the MOM/OpsMgr team installs and owns the SQL server.... and they dont do ANY default maintenance to SQL.  Because of this - a focus in OpsMgr was to have the Ops DB and Datawarehouse DB to be fully self-maintaining.... providing a good level of SQL performance whether or not any default maintenance was being done.

Operational Database:

Reindexing is already taking place against the OperationsManager database for some of the tables.  This is built into the product.  What we need to ensure - is that any default DBA maintenance tasks are not redundant nor conflicting with our built-in maintenance, and our built-in schedules:

There is a rule in OpsMgr that is targeted at the Root Management Server:

image

The rule executes the "p_OptimizeIndexes" stored procedure, every day at 2:30AM:

image

image

This rule cannot be changed or modified.  Therefore - we need to ensure there is not other SQL maintenance (including backups) running at 2:30AM, or performance will be impacted.

If you want to view the built-in UPDATE STATISTICS and DBCC DBREINDEX jobs history - just run the following queries:

select *
from DomainTable dt
inner join DomainTableIndexOptimizationHistory dti
on dt.domaintablerowID = dti.domaintableindexrowID
ORDER BY optimizationdurationseconds DESC

select *
from DomainTable dt
inner join DomainTableStatisticsUpdateHistory dti
on dt.domaintablerowID = dti.domaintablerowID
ORDER BY UpdateDurationSeconds DESC

Take note of the update/optimization duration seconds column.  This will show you how long your maintenance is typically running.  In a healthy environment these should not take very long.

 

If you want to view the fragmentation levels of the current tables in the database, run:

DBCC SHOWCONTIG WITH FAST

Here is some sample output:

----------------------------------------------------------------------------------------------

DBCC SHOWCONTIG scanning 'Alert' table...
Table: 'Alert' (1771153355); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 936
- Extent Switches..............................: 427
- Scan Density [Best Count:Actual Count].......: 27.34% [117:428]
- Logical Scan Fragmentation ..................: 60.90%

----------------------------------------------------------------------------------------------

In general - we would like the "Scan density" to be high (Above 80%), and the "Logical Scan Fragmentation" to be low (below 30%).  What you might find... is that *some* of the tables are more fragmented than others, because our built-in maintenance does not reindex all tables.  Especially tables like the raw perf, event, and localizedtext tables.

That said - there is nothing wrong with running a DBA's default maintenance against the Operational database..... reindexing these tables in the database might also help console performance.  We just dont want to run any DBA maintenance during the same time that we run our own internal maintenance, so try not to conflict with this schedule.  Care should also be taken in any default DBA maintenance, that it does not run too long, or impact normal operations of OpsMgr.  Maintenance jobs should be monitored, and should not conflict with the backup schedules as well.

Here is a reindex job you can schedule with SQL agent.... for the OpsDB:

USE OperationsManager
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable "Print 'Reindexing '+'?' DBCC DBREINDEX ('?')"

 

Data Warehouse Database:

The data warehouse DB is also fully self maintaining.  This is called out by a rule "Standard Data Warehouse Data Set maintenance rule" which is targeted to the "Standard Data Set" object type.  This stored procedure is called on the data warehouse every 60 seconds.  It performs many, many tasks, of which Index optimization is but one.

image

This SP calls the StandardDatasetOptimize stored procedure, which handles any index operations.

To examine the index and statistics history - run the following query for the Alert, Event, Perf, and State tables:

 

select basetablename, optimizationstartdatetime, optimizationdurationseconds,
      beforeavgfragmentationinpercent, afteravgfragmentationinpercent,
      optimizationmethod, onlinerebuildlastperformeddatetime
from StandardDatasetOptimizationHistory sdoh
inner join StandardDatasetAggregationStorageIndex sdasi
on sdoh.StandardDatasetAggregationStorageIndexRowId = sdasi.StandardDatasetAggregationStorageIndexRowId
inner join StandardDatasetAggregationStorage sdas
on sdasi.StandardDatasetAggregationStorageRowId = sdas.StandardDatasetAggregationStorageRowId
ORDER BY optimizationdurationseconds DESC

 

Then examine the default domain tables optimization history.... run the same two queries as listed above for the OperationsDB.

In the data warehouse - we can see that all the necessary tables are being updated and reindexed as needed.  When a table is 10% fragmented - we reorganize.  When it is 30% or more, we rebuild the index.

Therefore - there is no need for a DBA to execute any UPDATE STATISTICS or DBCC DBREINDEX maintenance against this database.  Furthermore, since we run our maintenance every 60 seconds, and only execute maintenance when necessary, there is no "set window" where we will run our maintenance jobs.  This means that if a DBA team also sets up a UPDATE STATISTICS or DBCC DBREINDEX job - it can conflict with our jobs and execute concurrently.  This should not be performed. 

 

For the above reasons, I would recommend against any maintenance jobs on the Data Warehouse DB, beyond a CHECKDB (only if DBA's mandate it) and a good backup schedule. 

 

For the OpsDB: any standard maintenance is fine, as long as it does not conflict with the built-in maintenance, or impact production by taking too long, or having an impact on I/O.

 

Lastly - I'd like to discuss the recovery model of the SQL database.  We default to "simple" for all our DB's.  This should be left alone.... unless you have *very* specific reasons to change this.  Some SQL teams automatically assume all databases should be set to "full" recovery model.  This requires that they back up the transaction logs on a very regular basis, but give the added advantage of restoring up to the time of the last t-log backup.  For OpsMgr, this is of very little value, as the data changing on an hourly basis is of little value compared to the complexity added by moving from simple to full.  Also, changing to full will mean that your transaction logs will only checkpoint once a t-log backup is performed.  What I have seen, is that many companies aren't prepared for the amount of data written to these databases.... and their standard transaction log backups (often hourly) are not frequent enough to keep them from filling.  The only valid reason to change to FULL, in my opinion, is when you are using an advanced replication strategy, like log shipping, which requires full recovery model.  When in doubt - keep it simple.  :-)

 

 

P.S....  The Operations Database needs 50% free space at all times.  This is for growth, and for re-index operations to be successful.  This is a general supportability recommendation, but the OpsDB will alert when this falls below 40%. 

For the Data warehouse.... we do not require the same 50% free space.  This would be a temendous requireemnts if we had a multiple-terabyte database!

Think of the data warehouse to have 2 stages... a "growth" stage (while it is adding data and not yet grooming much (havent hit the default 400 days retention) and a "maturity stage" where agent count is steady, MP's are not changing, and the grooming is happening because we are at 400 days retention.  During "growth" we need to watch and maintain free space, and monitor for available disk space.  In "maturity" we only need enough free space to handle our index operations.  when you start talking 1 Terabyte of data.... that means 500GB of free space, which is expensive, and.  If you cannot allocate it.... then just allow auto-grow and monitor the database.... but always plan for it from a volume size perspective.

For transaction log sizing - we don't have any hard rules.  A good rule of thumb for the OpsDB is ~20% to 50% of the database size.... this all depends on your environment.  For the Data warehouse, it depends on how large the warehouse is - but you will probably find steady state to require somewhere around 10% to 20% of the warehouse size.  Any time we are doing any additional grooming of an alert/event/perf storm.... or changin grooming from 400 days to 300 days - this will require a LOT more transaction log space - so keep that in mind as your databases grow.

Comments
  • I've been getting a few questions lately about slow performance with the console so I thought a post

  • My collegue, Kevin Holman, has a blog post with great discussion about SQL maintenance for OpsMgr databases.

  • Hi,

    i installed ACS and i can't find any comments about maintenance plan for the ACS DB.

    any idea ?

  • ACS uses a SQL parttion (table) for each day's worth of new data.  This partition is closed and then indexed every night, table by table.  If everything works perfectly - there is no need to do anything to an ACS DB other than back it up.

    However, it is possible that the indexing can fail on an ACS DB - especially if the table is very large.  If this occurs, a reindex operation on the DB or table would be beneficial for reporting/query performance.

    That being said - this should only be done if the ACS DB insert rate is not very high.... because reindex operations greatly tax the SQL server CPU/Memory/Disk I/O, and will slow down the available insert rate for ACS.

    If you want to see how your partition look today, run a DBCC SHOWCONTIG WITH FAST as described above.

    In most scenarios - a backup is the only maintenance that should be performed on an ACS DB.

  • Hi Kevin,

    Thanks for the quick reply. this is very helpful.

  • Kevin,

    With multiple management groups reporting to a single data warehouse is there a chance that the Standard Data Warehouse Data Set maintenance rule will run on each management group and cause issues on the data warehouse?

  • Derek - that is an excellent question - I am trying to find out the recommendation there.

  • Hi, is there a reason to update the statistics before rebuilding the indexes, wouldnt it be a better idea to do it the other way around?

    Kind regards,

    Mc

  • You guys are really stupid to reinvent the wheel! This product is a nightmare with all these blocking and deadlock.When you do the "self maintenance". Let the DBAs do their work

  • Well Joe, that's certainly one person's opinion.  I bet you make a lot of friends with that approach.  :-)

    Let me give you some historical and technical perspective - that the typical DBA wont know out of the box:

    When our previous product shipped - MOM 2005 - one of the most common scenarios we would see is that many MOM 2005 customers deployed their own SQL servers in the monitoring teams.  There was no DBA involved, and the monitoring teams were not SQL experts.  What we found, is many performance issues caused by the fact that NO maintenance was being done.  There were no index jobs at all set up on a huge percentage of servers.  It made sense to include SQL maintenance rules in the product - to make it totally self-maintaining for that large percentage of customers who deployed the DB's on SQL, but the SQL servers were not managed by an experienced DBA team, familiar with index maintenance.

    Secondly - DBA's often have "standard maintenance" jobs that they run on all their servers/databases.  This doesn't make sense in 100% of cases, because the DBA will not understand all the nuances and schema of the OpsMgr databases.  Standardized DBA maintenance, especially on SQL STD edition (which does not support online indexing) will CAUSE the blocking to occur.  Our maintenance scripts for index operations have the knowledge of which tables should be indexed, which don't, and which will cause issues on inserts during maintenance operations.  It makes MORE sense for the developers of the product to add the maintenance jobs.

    Furthermore - it is VERY common for Data Warehouse products to include their own maintenance.  Applying a standard indexing job to a huge warehouse will result in performance issues. You have to understand the warehouse design, and how the application inserts data, in order to schedule and design maintenance.  The OpsMgr Warehouse have a very specialized maintenance procedure which runs on a constant schedule - keeping track of how fragmented the indexes, and specialized frequencies that keep track of how often (last run time) each job and index operation runs.  This keeps the warheouse performing perfectly with all the knowledge behind the design of the application.  This just makes sense.

    Why on earth, would any DBA - care?  Once they understand this - simply exclude this DB from their index maintenance, understanding that their defacto maintenance does not apply?  It is not like anyone is asking you to do anything "extra".  Just the opposite....

    I look forward to your (hopefully more tactful) response.  :-)

  • I will have to agree with Kevin.

    Our DBA team (we have one) is taking care of Enterprise DBs but 90% of the time their responses about issues are it is an Application problem which send back the Monitoring to the Monitoring Team and not the DBA team. Also they do as Kevin said "Standard" Maintenance tasks following their own constraints and don't want to hear any "specifics", they don't want to learn about Applications. So I need to have at least one mind which cover SQL and Application to review the conflicts, the issues, the problems and the constraints within this "mixed" environment.

    Thanks,

    Dom

  • I am not able to see any reports from Reporting, Went through the article " How to  troubleshoot blank reports"But no use.

    Are there any commands to check if the data is written to the DB or not.

    I am able to see the performance reports on the Monitoring console, Each time have to copy to clip board and paste it on to a pdf file.

  • Hi,

    I was looking at the PerformanceData_## tables and noticed the indices were fragemented to over 98%.  All the other tables were below 15%.  I don't see any error or warnings and we don't have any overrides to stop the maintenace jobs.   So either my timing must be bad every time I check the indices for these tables or something is bad with the maintenance job and I cannot find it.  The more I think about it, the performancedata_## tables probably do nothing but get insert statements, so maybe they don't need reindexing?   And if they don't need reindexing, why have them there.  

    Thoughts?

  • @Z -

    PerformanceData_## tables are where all the daily perf data is stored.  We dont reindex this data by design, it is transient in nature and most customers only keep from 2-4 days of this data.  The console views accessing this data on demand are fairly efficient.

  • Great Post, many thanks.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs