{"name":"Performance-related database settings","description":"Checking database settings that are often performance related.","tsql":"DECLARE @Result INT;\r\nDECLARE @High INT; -- For settings you feel need to be right\r\nDECLARE @Med INT; -- For settings you want to know about but arent critical\r\nDECLARE @Low INT; -- For settings that you want flagged but are low importance\r\n\r\nSELECT @High = 70,\r\n    @Med = 40,\r\n    @Low = 10;\r\n\r\nSELECT @Result = SUM(CASE WHEN [d].[compatibility_level] != [d2].[compatibility_level]\r\n                          THEN @Med\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[collation_name] != [d2].[collation_name]\r\n                          THEN @Med\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[user_access] != 0\r\n                          THEN @Low\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[is_auto_close_on] = 1\r\n                          THEN @High\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[is_auto_shrink_on] = 1\r\n                          THEN @High\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[state] != 0\r\n                          THEN @Low\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[is_auto_create_stats_on] != 1\r\n                          THEN @Med\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[is_auto_update_stats_on] != 1\r\n                          THEN @Med\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[is_ansi_nulls_on] = 1\r\n                          THEN @High\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[is_ansi_padding_on] = 1\r\n                          THEN @High\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[is_ansi_warnings_on] = 1\r\n                          THEN @High\r\n                          ELSE 0\r\n                     END\r\n                   + CASE WHEN [d].[is_arithabort_on] = 1\r\n                          THEN @High\r\n                          ELSE 0\r\n                     END)\r\n  FROM [sys].[databases] AS d \r\n  CROSS JOIN [sys].[databases] AS d2\r\n  WHERE [d2].[name] = 'master'\r\n    AND ([d].[database_id] = DB_ID()\r\n    AND [d].[state_desc] = 'Online');\r\n\r\nSELECT @Result;\r\n","instances":false,"frequency":3600,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Database settings alert","alertdescription":"Database settings that could affect performance","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":100},"_thresholds_medium":{"selected":true,"value":80},"_thresholds_low":{"selected":true,"value":50},"thresholds_comment":"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. \r\nThe 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.","targetentitytype":0}