What SQL maintenance should I perform on my OpsMgr databases?

OpsMgr 2007: Troubleshooting Console Performance in large environments

SMS&MOM — Thu, 17 Jul 2008 15:50:12 GMT

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

Required SQL Maintenance for OpsMgr

Steve Rachui's Manageability blog - ConfigMgr/OpsMgr — Tue, 12 Aug 2008 09:33:40 GMT

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

re: What SQL maintenance should I perform on my OpsMgr databases?

kobile — Tue, 02 Sep 2008 15:20:25 GMT

Hi,

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

any idea ?

re: What SQL maintenance should I perform on my OpsMgr databases?

kevinhol — Tue, 02 Sep 2008 15:46:38 GMT

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.

re: What SQL maintenance should I perform on my OpsMgr databases?

kobile — Tue, 02 Sep 2008 17:57:17 GMT

Hi Kevin,

Thanks for the quick reply. this is very helpful.

re: What SQL maintenance should I perform on my OpsMgr databases?

Derek — Mon, 08 Dec 2008 16:16:50 GMT

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?

re: multiple management groups

kevinhol — Wed, 10 Dec 2008 17:51:26 GMT

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

re: What SQL maintenance should I perform on my OpsMgr databases?

Michael — Thu, 08 Jan 2009 13:09:09 GMT

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,