This version of the SQL MP includes the addition of the SQL 2008 MP. This is not an in-depth tuning guide, but rather some at-a-glance suggestions of items you’ll want to take a look into, as they stand out. Download the MP Report and have a look for yourself, as you’re like to see a couple of things here that you have not noticed before. Unless otherwise noted, these observations are applicable to at least the SQL 2005 and 2008 versions.

Comments are enabled, so feel free to add comments on any of your experiences with this MP here.

Related Posts

• Download the MP Report (Excel format) to follow along HERE.
• Overriding SQL Components on a per-object basis

Run As Accounts and Profiles

This MP creates two Run As Profiles by default: SQL Server Discovery account and SQL Server Monitoring account. You’ll need to read the MP guide and ensure the accounts associated with these have appropriate permissions

Monitoring Cluster Resources

You’ll need to make sure of at least three items for successful cluster discovery and monitoring:

1. Run As Accounts must have admin permissions for the cluster
2. Install an agent on each physical node
3. Enable Agent Proxy on each of these agents.

NOTE: The MP Guide has detailed info on cluster monitoring, so be sure read the guide.

Agentless Monitoring - No longer supported since the October 2008 release of the MP.

Disabled Object Discoveries - The following discoveries are disabled by default. You may want to enable one of more of the following based on SQL features in use in your environment.

• Discover File Groups and Files - should be enabled for specific databases or DB Engine instances where per file deeper group and file monitoring is required.
• Discover Replication Components – Of interest if SQL replication is configured in your environment.
• SQL 2005 Replication Publications and Subscriptions Discovery Provider - Ditto
• Discover SQL 2005 Agent Jobs – If not enabled, you cannot override or disable the Job Duration monitor on a per-job basis.

Disabled Rules - The following rules are disabled by default. Since these are simply performance collection rules, you may enable these based on their reporting value in the eyes of your DBAs.

• Collect Database Free Space (MB)
• Collect Database Free Space (%)
• Collect Database Size (MB)
• Collect Transaction Log Free Space (MB)
• Collect Transaction Log Free Space (%)
• Collect Transaction Log Size (MB)

Disabled Monitors - The following monitors are disabled by default. The User Connections monitor is a self-tuning threshold, so if enabled, it may take some tuning to iron out the noise for some databases.

• DB Space Free (MB)
• Transaction Log Space Free (MB)
• SQL User Connections Performance
• Torn Page Detection

Unit Monitors with “Generates Alert” Parameter Disabled

The following unit monitors are enabled, but their Generates Alert parameter is set to False. This means you’ll see a change in health state for a monitor, but you’ll never get an alert unless you override this parameter. This parameter is overridable on all monitors. The appropriate thresholds for these monitors are likely to vary widely per environment, so check these with your database administrators before you enable alerting.

• Job Duration
• Last Run Status
• Blocking SPIDs

Noisy Rules and Monitors

The following rules and monitors often generate some noise in most environments.

• Percentage Change in DB % Used Space – Since TempDB and ReportServerTempDB will change in size substantially due to their purpose, you may find it necessary to bump the thresholds for these databases in this monitor, which are WARNING - 25% and ERROR – 45% growth over 5 sample periods.
• Auto Shrink Flag - This rule will alert for all databases that have the Auto Shrink flag enabled. This setting is often enabled for 3^rd party applications that use a utility database in SQL Express by default, such as some backup and anti-virus applications (Backup Exec comes to mind here). You may have to create a disable override for those databases. However, this should generally not be enabled for most databases. Check with the application vendor for best practice for their application databases.

As always, be sure to read the Management Pack Guide that comes in the MP download.

This entry was posted on Sunday, November 2nd, 2008 at 9:20 am and is filed under Essentials 2007, Management Packs, News, Operations Manager 2007, Tips -n- Tricks. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.