OpsMgr Database Performance monitoring – Part 1: the SQL Part

As OpsMgr is one of the most demanding SQL applications I am aware of, monitoring the performance of the database is always a very important task. One could use the usual performance reports, but they have some disadvantages in my opinion:

• There is not a “one stop shop” report for all necessary information. You need multiple reports to gain a complete overview of the performance.
• Some important information are not available in standard reports like the table sizes or table fragmentation

So how can we get a better solution with all necessary information in one place? A lot of folks on the web (thanks to Kevin Holman and Steve Rachui and many others) have published SQL queries, which can help to analyze the health of the OperationsManager database.

So I tried some of the queries and was amazed, what wealth of information they provide.  But what always disturbs me was on the one hand the number of different scripts I had to run and on the other hand the lack of automation.

Ok, so let’s create a simple automated SQL Performance reporting solution:

Step 1:  Create a structured SQL script, with all necessary queries
Step 2:  Create a PowerShell wrapper for the command
Step 3: Run the wrapper as an automated task with a task scheduler

 

Step 1: the SQL Script

Please note, that I am not a DBA, in fact I am a complete SQL newbie, so if you can provide some optimizations to my script, please let me know.

The attached SQL script generates a text file with the following information:

• All Event counts per Day, incl. Sum for the whole DB
• TOP 50 Events, sorted by Creator
• Top 20 Computer with most Events in the DB
• Alerts per Day
• Top 50 Monitors with most State Change Events
• Top 20 Alerts, sorted by  Alert count
• Top 20 Alerts, sorted by  Repeat count
• Inserted Performance counter per day
• Top 100 Performance Counter
•  Database Size
• Top 10 Tables sorted by size
• Database Defragmentation (Scan Density should be > 80%, Logical Scan Fragmentation < 30%)

Of course you can extend this script indefinitely

What about the runtime? This script runs about 1-2 Minutes on a very slow SQL 2005 cluster with a 7 GB DB.