Our discussion of proactive tasks that keep your database healthy continues in this post as we tackle server and database options. You might already be thinking this will be a quick post – who changes server or database options that often? You would be surprised, especially if you have many people who have access to the SQL Server. Server and database options should change infrequently – for the most part these are set at installation and left alone. But every so often there is good reason to make a change – be it performance-related, due to a change in application code, or perhaps because something was set incorrectly the first time around. Test these changes first, and capture appropriate metrics before and after the change. It seems pretty straightforward and obvious, right? You might think so, but if you don’t have a change management process in place that is strictly followed, it’s not.
In a majority of environments, more than one person has access to SQL Server, and more than one person has the privileges necessary to change server or database options. If the wrong setting is changed, the performance impact can be significant. (Have you ever inadvertently set the max memory setting to a value in GB instead of MB? In case you’re wondering, 128MB is not enough memory needed for a SQL Server instance to start. Check out Ted Krueger’s post on how to fix this, should you ever make that mistake.) Other changes can create smaller problems that are still troublesome and sometimes hard to track down (disabling Auto Create Statistics is a good example). You may think that these changes would be well-communicated (sometimes you’re so busy putting out fires you forget) or easy to notice (not always). To avoid this, we track the settings and then, when running our regular checks (or when troubleshooting an issue), we verify that nothing has changed.
Capturing the Data
Unlike the previous post on maintenance tasks, where we relied on msdb to hold on to the data we cared about, we have to set up data capture for instance and database settings. We’ll snapshot sys.configurations and sys.database_info daily to tables in our Baselines database, then use queries to see if anything changed, and when.
USE [Baselines];
GO
IF OBJECT_ID(N'dbo.SQLskills_ConfigData', N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[SQLskills_ConfigData]
(
[ConfigurationID] [int] NOT NULL ,
[Name] [nvarchar](35) NOT NULL ,
[Value] [sql_variant] NULL ,
[ValueInUse] [sql_variant] NULL ,
[CaptureDate] [datetime] NOT NULL DEFAULT SYSDATETIME()
) ON [PRIMARY];
GO
CREATE CLUSTERED INDEX [CI_SQLskills_ConfigData]
ON [dbo].[SQLskills_ConfigData] ([CaptureDate],[ConfigurationID]);
GO
IF OBJECT_ID(N'dbo.SQLskills_DBData', N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[SQLskills_DBData]
(
[name] [sysname] NOT NULL,
[database_id] [int] NOT NULL,
[source_database_id] [int] NULL,
[owner_sid] [varbinary](85) NULL,
[create_date] [datetime] NOT NULL,
[compatibility_level] [tinyint] NOT NULL,
[collation_name] [sysname] NULL,
[user_access] [tinyint] NULL,
[user_access_desc] [nvarchar](60) NULL,
[is_read_only] [bit] NULL,
[is_auto_close_on] [bit] NOT NULL,
[is_auto_shrink_on] [bit] NULL,
[state] [tinyint] NULL,
[state_desc] [nvarchar](60) NULL,
[is_in_standby] [bit] NULL,
[is_cleanly_shutdown] [bit] NULL,
[is_supplemental_logging_enabled] [bit] NULL,
[snapshot_isolation_state] [tinyint] NULL,
[snapshot_isolation_state_desc] [nvarchar](60) NULL,
[is_read_committed_snapshot_on] [bit] NULL,
[recovery_model] [tinyint] NULL,
[recovery_model_desc] [nvarchar](60) NULL,
[page_verify_option] [tinyint] NULL,
[page_verify_option_desc] [nvarchar](60) NULL,
[is_auto_create_stats_on] [bit] NULL,
[is_auto_update_stats_on] [bit] NULL,
[is_auto_update_stats_async_on] [bit] NULL,
[is_ansi_null_default_on] [bit] NULL,
[is_ansi_nulls_on] [bit] NULL,
[is_ansi_padding_on] [bit] NULL,
[is_ansi_warnings_on] [bit] NULL,
[is_arithabort_on] [bit] NULL,
[is_concat_null_yields_null_on] [bit] NULL,
[is_numeric_roundabort_on] [bit] NULL,
[is_quoted_identifier_on] [bit] NULL,
[is_recursive_triggers_on] [bit] NULL,
[is_cursor_close_on_commit_on] [bit] NULL,
[is_local_cursor_default] [bit] NULL,
[is_fulltext_enabled] [bit] NULL,
[is_trustworthy_on] [bit] NULL,
[is_db_chaining_on] [bit] NULL,
[is_parameterization_forced] [bit] NULL,
[is_master_key_encrypted_by_server] [bit] NOT NULL,
[is_published] [bit] NOT NULL,
[is_subscribed] [bit] NOT NULL,
[is_merge_published] [bit] NOT NULL,
[is_distributor] [bit] NOT NULL,
[is_sync_with_backup] [bit] NOT NULL,
[service_broker_guid] [uniqueidentifier] NOT NULL,
[is_broker_enabled] [bit] NOT NULL,
[log_reuse_wait] [tinyint] NULL,
[log_reuse_wait_desc] [nvarchar](60) NULL,
[is_date_correlation_on] [bit] NOT NULL,
[is_cdc_enabled] [bit] NOT NULL,
[is_encrypted] [bit] NULL,
[is_honor_broker_priority_on] [bit] NULL,
[replica_id] [uniqueidentifier] NULL,
[group_database_id] [uniqueidentifier] NULL,
[default_language_lcid] [smallint] NULL,
[default_language_name] [nvarchar](128) NULL,
[default_fulltext_language_lcid] [int] NULL,
[default_fulltext_language_name] [nvarchar](128) NULL,
[is_nested_triggers_on] [bit] NULL,
[is_transform_noise_words_on] [bit] NULL,
[two_digit_year_cutoff] [smallint] NULL,
[containment] [tinyint] NULL,
[containment_desc] [nvarchar](60) NULL,
[target_recovery_time_in_seconds] [int] NULL,
[CaptureDate] [datetime] NOT NULL DEFAULT SYSDATETIME()
) ON [PRIMARY];
GO
CREATE CLUSTERED INDEX [CI_SQLskills_DBData]
ON [dbo].[SQLskills_DBData] ([CaptureDate],[database_id]);
GO
The script to create the SQLskills_DBData table is compatible with SQL Server 2014. For earlier versions, you may need to modify base table and snapshot query (see next set of code).
Once you have the tables created, create a job that will execute the following two queries daily. Again, we wouldn’t expect that these options would change more than once a day, and while we hope no one would change a setting, then change it back (therefore it wouldn’t show up in a capture), that’s always a possibility. If you find that this data capture does not suite your needs, because settings change frequently or temporarily, you may want to implement a trigger or use auditing.
To edit server options via (sp_configure), a login needs the ALTER SETTINGS server-level permission, which is included if you are a member of the sysadmin or serveradmin roles. To edit most database settings (ALTER DATABASE SET), you need the ALTER permission in the database, though some options require additional rights, such as CONTROL SERVER or the server-level option ALTER ANY DATABASE.
/* Statements to use in scheduled job */
INSERT INTO [dbo].[SQLskills_ConfigData]
(
[ConfigurationID] ,
[Name] ,
[Value] ,
[ValueInUse]
)
SELECT
[configuration_id] ,
[name] ,
[value] ,
[value_in_use]
FROM [sys].[configurations];
GO
INSERT INTO [dbo].[SQLskills_DBData]
(
[name],
[database_id],
[source_database_id],
[owner_sid],
[create_date],
[compatibility_level],
[collation_name],
[user_access],
[user_access_desc],
[is_read_only],
[is_auto_close_on],
[is_auto_shrink_on],
[state],
[state_desc],
[is_in_standby],
[is_cleanly_shutdown],
[is_supplemental_logging_enabled],
[snapshot_isolation_state],
[snapshot_isolation_state_desc],
[is_read_committed_snapshot_on],
[recovery_model],
[recovery_model_desc],
[page_verify_option],
[page_verify_option_desc],
[is_auto_create_stats_on],
[is_auto_update_stats_on],
[is_auto_update_stats_async_on],
[is_ansi_null_default_on],
[is_ansi_nulls_on],
[is_ansi_padding_on],
[is_ansi_warnings_on],
[is_arithabort_on],
[is_concat_null_yields_null_on],
[is_numeric_roundabort_on],
[is_quoted_identifier_on],
[is_recursive_triggers_on],
[is_cursor_close_on_commit_on],
[is_local_cursor_default],
[is_fulltext_enabled],
[is_trustworthy_on],
[is_db_chaining_on],
[is_parameterization_forced],
[is_master_key_encrypted_by_server],
[is_published],
[is_subscribed],
[is_merge_published],
[is_distributor],
[is_sync_with_backup],
[service_broker_guid],
[is_broker_enabled],
[log_reuse_wait],
[log_reuse_wait_desc],
[is_date_correlation_on],
[is_cdc_enabled],
[is_encrypted],
[is_honor_broker_priority_on],
[replica_id],
[group_database_id],
[default_language_lcid],
[default_language_name],
[default_fulltext_language_lcid],
[default_fulltext_language_name],
[is_nested_triggers_on],
[is_transform_noise_words_on],
[two_digit_year_cutoff],
[containment],
[containment_desc],
[target_recovery_time_in_seconds]
)
SELECT
[name],
[database_id],
[source_database_id],
[owner_sid],
[create_date],
[compatibility_level],
[collation_name],
[user_access],
[user_access_desc],
[is_read_only],
[is_auto_close_on],
[is_auto_shrink_on],
[state],
[state_desc],
[is_in_standby],
[is_cleanly_shutdown],
[is_supplemental_logging_enabled],
[snapshot_isolation_state],
[snapshot_isolation_state_desc],
[is_read_committed_snapshot_on],
[recovery_model],
[recovery_model_desc],
[page_verify_option],
[page_verify_option_desc],
[is_auto_create_stats_on],
[is_auto_update_stats_on],
[is_auto_update_stats_async_on],
[is_ansi_null_default_on],
[is_ansi_nulls_on],
[is_ansi_padding_on],
[is_ansi_warnings_on],
[is_arithabort_on],
[is_concat_null_yields_null_on],
[is_numeric_roundabort_on],
[is_quoted_identifier_on],
[is_recursive_triggers_on],
[is_cursor_close_on_commit_on],
[is_local_cursor_default],
[is_fulltext_enabled],
[is_trustworthy_on],
[is_db_chaining_on],
[is_parameterization_forced],
[is_master_key_encrypted_by_server],
[is_published],
[is_subscribed],
[is_merge_published],
[is_distributor],
[is_sync_with_backup],
[service_broker_guid],
[is_broker_enabled],
[log_reuse_wait],
[log_reuse_wait_desc],
[is_date_correlation_on],
[is_cdc_enabled],
[is_encrypted],
[is_honor_broker_priority_on],
[replica_id],
[group_database_id],
[default_language_lcid],
[default_language_name],
[default_fulltext_language_lcid],
[default_fulltext_language_name],
[is_nested_triggers_on],
[is_transform_noise_words_on],
[two_digit_year_cutoff],
[containment],
[containment_desc],
[target_recovery_time_in_seconds]
FROM [sys].[databases];
GO
Checking for Changes
Now that we’re capturing this information, how do we find changes? Knowing that there could be multiple settings changed, and on different dates, we need a method that looks at each row. This isn’t hard to do, but it doesn’t generate the prettiest code. For server options, it’s not too bad:
;WITH [f] AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY [ConfigurationID] ORDER BY [CaptureDate] ASC) AS [RowNumber],
[ConfigurationID] AS [ConfigurationID],
[Name] AS [Name],
[Value] AS [Value],
[ValueInUse] AS [ValueInUse],
[CaptureDate] AS [CaptureDate]
FROM [Baselines].[dbo].[ConfigData]
)
SELECT
[f].[Name] AS [Setting],
[f].[CaptureDate] AS [Date],
[f].[Value] AS [Previous Value],
[f].[ValueInUse] AS [Previous Value In Use],
[n].[CaptureDate] AS [Date Changed],
[n].[Value] AS [New Value],
[n].[ValueInUse] AS [New Value In Use]
FROM [f]
LEFT OUTER JOIN [f] AS [n]
ON [f].[ConfigurationID] = [n].[ConfigurationID]
AND [f].[RowNumber] + 1 = [n].[RowNumber]
WHERE ([f].[Value] <> [n].[Value] OR [f].[ValueInUse] <> [n].[ValueInUse]);
GO
Changed Instance Settings
For database options, the query is in a stored procedure (because it was so unwieldy), which you can download here. To run the stored procedure:
EXEC dbo.usp_FindDBSettingChanges
The output will list the database and the setting that changed, as well as the date:
Changed Database Settings
You can run these queries when performance issues come up, to quickly check if any settings have changed, or you could be a bit more proactive and run them regularly in a scheduled job that notifies you if anything has changed. I didn’t include the T-SQL code to send an email using database mail if there’s a change, but that won’t be hard to do based on the code provided here.
Using Performance Advisor
SQL Sentry Performance Advisor does not track this information by default, but you can still capture the information in a database, then have PA check to see if any settings have changed, and notify you if they have. To set this up, create the SQLskills_ConfigData and SQLskillsDBData tables and set up the scheduled job to insert into those tables on a regular basis. Within the SQL Sentry client, set up a Custom Condition, as we did in an earlier post in this series, Proactive SQL Server Health Checks, Part 1: Disk Space post.
Within the Custom Condition, you have two options. First, you could just execute the code provided which checks historical data to see if anything has changed (and then send a notification if so). Checking historical data for change is something that you would run daily, as you would with an Agent Job. Alternatively, you could be more proactive and compare current, running values against the most recent data on a more frequent basis, e.g. once an hour, to look for changes. Example code to check the current settings for the instance against the most recent capture:
;WITH [lc] AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY [ConfigurationID] ORDER BY [CaptureDate] ASC) AS [RowNumber],
[ConfigurationID] AS [ConfigurationID],
[Name] AS [Name],
[Value] AS [Value],
[ValueInUse] AS [ValueInUse],
[CaptureDate] AS [CaptureDate]
FROM [Baselines].[ConfigData]
WHERE [CaptureDate] = (SELECT MAX([CaptureDate]) FROM [Baselines].[ConfigData])
)
SELECT
[lc].[Name] AS [Setting],
[lc].[CaptureDate] AS [Date],
[lc].[Value] AS [Last Captured Value],
[lc].[ValueInUse] AS [Last Captured Value In Use],
CURRENT_TIMESTAMP AS [Current Time],
[c].[Value] AS [Current Value],
[c].[value_in_use] AS [Current Value In Use]
FROM [sys].[configurations] AS [c]
LEFT OUTER JOIN [lc]
ON [lc].[ConfigurationID] = [c].[configuration_id]
WHERE ([lc].[Value] <> [c].[Value] OR [lc].[ValueInUse] <> [c].[value_in_use]);
GO
Summary
Checking instance and database options is straightforward and obvious, and in some situations this historical information can save you significant time when troubleshooting. If you’re not capturing this information anywhere, I encourage you to start; it’s always better to proactively look for issues than to react when you’re fire-fighting and potentially stressed, unsure of what’s causing a problem in your production environment.
Excellent tips