Performance-related database settings

| 1 Comment | 7,247 views
Categories: ,

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)

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

Metric Name:
Performance-related database settings
Description:
Checking database settings that are often performance related.
Enter the T-SQL query that will collect data:
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;
Select 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.
Choose databases to collect from:
All user databases
Set collection frequency:
Collect data every hour
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
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 thresholds
Default threshold values:
Set the thresholds as follows:
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:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)
  • James Dionne

    To get a quick list of what is not configured exactly as the Master database run the following code:

    Select CASE WHEN [d].[compatibility_level] != [d2].[compatibility_level]

    THEN [d].[compatibility_level]

    ELSE 0

    END Compatibility_level

    , CASE WHEN [d].[collation_name] != [d2].[collation_name]

    THEN [d].[collation_name]

    ELSE 0

    END collation_name

    , CASE WHEN [d].[user_access] != 0

    THEN [d].[user_access]

    ELSE 0

    END

    , CASE WHEN [d].[is_auto_close_on] = 1

    THEN [d].[is_auto_close_on]

    ELSE 0

    END is_auto_close_on

    , CASE WHEN [d].[is_auto_shrink_on] = 1

    THEN [d].[is_auto_shrink_on]

    ELSE 0

    END is_auto_shrink_on

    , CASE WHEN [d].[state] != 0

    THEN [d].[state]

    ELSE 0

    END state

    , CASE WHEN [d].[is_auto_create_stats_on] != 1

    THEN [d].[is_auto_create_stats_on]

    ELSE 0

    END is_auto_create_stats_on

    , CASE WHEN [d].[is_auto_update_stats_on] != 1

    THEN [d].[is_auto_update_stats_on]

    ELSE 0

    END is_auto_update_stats_on

    , CASE WHEN [d].[is_ansi_nulls_on] = 1

    THEN [d].[is_ansi_nulls_on]

    ELSE 0

    END is_ansi_nulls_on

    , CASE WHEN [d].[is_ansi_padding_on] = 1

    THEN [d].[is_ansi_padding_on]

    ELSE 0

    END is_ansi_padding_on

    , CASE WHEN [d].[is_ansi_warnings_on] = 1

    THEN [d].[is_ansi_warnings_on]

    ELSE 0

    END is_ansi_warnings_on

    , CASE WHEN [d].[is_arithabort_on] = 1

    THEN [d].[is_arithabort_on]

    ELSE 0

    END is_arithabort_on

    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’);

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)