Day 9 - Checking SQL Query Performance with MOM 2005

Getting ready to hit the road for holiday break. Wanted to get an update out before everyone is home for the holiday. We've 3 days left, so keep an eye out over the weekend...   So for Friday, the 9th day of Christmas, we're going to address monitoring of SQL query performance. I'm not talking about jobs running for 2 or 3 minutes, I am talking about monitoring SQL queries where performance issues can be measured in seconds. In N-tier e-commerce environments, I've seen some huge SQL databases where most of the activity boils down to a small number (less than 5-10) of high traffic tables. So it's really all about measuring performance in high traffic areas to ensure contention isn't impacting customer activity.   And it's not always MS SQL on the back end, so it would be nice to query other RDBMS as well. Here's a script we use to measure and alert on query latency for MS SQL databases, and by changing the query string, on other database platforms (we currently use a modified version of this for measuring query performance on a Progress database hosted on Unix - another fine example of MOM extensibility).   DESCRIPTION: This script will execute 1) query against a remote database server, 2) raise an alert when query response exceeds a user 3) charts query performance in a MOM performance object PARAMETERS: Conn - Connection string passed by MOM. This string can be provided by an UDL file.  Threshold - time limit for the SQL transaction. If execution time exceeds this limit, it generate an alert. Command - SQL Statement Column - What column to retrieve data from CompareString - What string to compare the results to. If this fails, the script will generate an event. LogSucessful - This is for troubleshooting purposes.  If this is enabled, then you will recieve alerts and events on successful execution of the script. Server - The server that will serve as an proxy for the script. Strictly for alerting purposes. ** The column name and Compare fields are primarily used for string matching of    SQL results. If you do not want that, then at the minimum Compare parameter needs to    be blank and ColumnName parameter can be as well. If you want string match testing,    then both fields need to be populated.       This came from one of the engineers on my managed services team. so give my brother Neale Brown some props for his effort on this one. Neale is coming up on MOM 2005, and will be neck deep in testing with us on the SCOM beta. Expect good things :)   WHERE TO GET IT: Download the SQL Query Performance Check script at the URL below: http://www.momresources.org/momscripts/sqlperfcheck.txt