02 July 2012

Performance-related database settings

by Jonathan Allen

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 Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate 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


		
			
			
Transact-SQL
			
			DECLARE @Result INT;
DECLARE @High INT; -- For settings you feel need to be right
DECLARE @Med INT; -- For settings you want to know about but arent critical
DECLARE @Low INT; -- For settings that you want flagged but are low importance

SELECT @High = 70,
    @Med = 40,
    @Low = 10;

SELECT @Result = SUM(CASE WHEN [d].[compatibility_level] != [d2].[compatibility_level]
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[collation_name] != [d2].[collation_name]
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[user_access] != 0
                          THEN @Low
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_close_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_shrink_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[state] != 0
                          THEN @Low
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_create_stats_on] != 1
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_update_stats_on] != 1
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_nulls_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_padding_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_warnings_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_arithabort_on] = 1
                          THEN @High
                          ELSE 0
                     END)
  FROM [sys].[databases] AS d 
  CROSS JOIN [sys].[databases] AS d2
  WHERE [d2].[name] = 'master'
    AND ([d].[database_id] = DB_ID()
    AND [d].[state_desc] = 'Online');

SELECT @Result;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
				
						DECLARE @Result INT;
DECLARE @High INT; -- For settings you feel need to be right
DECLARE @Med INT; -- For settings you want to know about but arent critical
DECLARE @Low INT; -- For settings that you want flagged but are low importance
 
SELECT @High = 70,
    @Med = 40,
    @Low = 10;
 
SELECT @Result = SUM(CASE WHEN [d].[compatibility_level] != [d2].[compatibility_level]
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[collation_name] != [d2].[collation_name]
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[user_access] != 0
                          THEN @Low
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_close_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_shrink_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[state] != 0
                          THEN @Low
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_create_stats_on] != 1
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_update_stats_on] != 1
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_nulls_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_padding_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_warnings_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_arithabort_on] = 1
                          THEN @High
                          ELSE 0
                     END)
  FROM [sys].[databases] AS d 
  CROSS JOIN [sys].[databases] AS d2
  WHERE [d2].[name] = 'master'
    AND ([d].[database_id] = DB_ID()
    AND [d].[state_desc] = 'Online');
 
SELECT @Result;

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

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

11,540 Rate

Click to rate this post!

[Total: 0 Average: 0]

Performance-related database settings

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics