Performance-related database settings

If you manage a server where you are not in complete control of the creation of databases, or you’re unfamiliar with what settings to change, you may miss things out or set them incorrectly.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

If you manage a server where you are not in complete control of the creation of databases, or you’re unfamiliar with what settings to change, you may miss things out or set them incorrectly. This metric could pick up on issues that affect performance in obscure ways, and saves you having to search for them when a system suddenly stops performing as you would expect.

The settings here are not a complete list and the values attributed to them are not necessarily how you may choose to have them. They are provided as examples of how to test for settings and how to attribute a severity to the setting. A higher value indicates a setting that you are more interested in, ensuring it meets your own best practice.

Metric definition

Name

Performance-related database settings

Description

Checking database settings that are often performance related.

The T-SQL query that will collect data

Instances to collect from

Choose the instances where this is important. Possibly this could be your development instances. Catching the issues early in development will prevent a lot of issues reaching your live systems. If you are concerned about your production databases getting settings altered then include them too.

Databases to collect from

1

Collection frequency

3600

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Database settings alert

Description

Database settings that could affect performance

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:100
Medium:80
Low:50

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance. The alert thresholds depend on how strict you want to be and how you set the values in the TSQL. High, medium and low variables should relate to your database settings. For example, if you have the @Low value set at 10, then 5 of these features will trigger this alert. If you set this too high then you may miss the more important settings.

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled