Configuration Enforcement

This post is a companion to the webcast today. I'll update it once the webcast airs and I have the recording up.

Here is the complete set of scripts from the webcast I was using.

-- turn on optimize for ad hoc workloads
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

-- turn off optimize for ad hoc workloads
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

/* from user Kin on dba.stackexchange.com
dba.stackexchange.com/.../why-would-i-not-use-the-sql-server-option-optimize-for-ad-hoc-workloads
*/

--- for 2008 and up .. Optimize ad-hoc for workload
IF EXISTS (
-- this is for 2008 and up
SELECT 1
FROM sys.configurations
WHERE NAME = 'optimize for ad hoc workloads'
)
BEGIN
DECLARE @AdHocSizeInMB DECIMAL(14, 2)
,@TotalSizeInMB DECIMAL(14, 2)
,@ObjType NVARCHAR(34)

SELECT @AdHocSizeInMB = SUM(CAST((
CASE
WHEN usecounts = 1
AND LOWER(objtype) = 'adhoc'
THEN size_in_bytes
ELSE 0
END
) AS DECIMAL(14, 2))) / 1048576
,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
FROM sys.dm_exec_cached_plans

SELECT 'SQL Server Configuration' AS GROUP_TYPE
,' Total cache plan size (MB): ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '. Percentage of total cache plan occupied by adhoc plans only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS COMMENTS
,' ' + CASE
WHEN @AdHocSizeInMB > 200
OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference. Ref: sqlserverperformance.idera.com/.../. www.sqlskills.com/.../procedure-cache-and-optimizing-for-adhoc-workloads.aspx'
ELSE 'Setting Optimize for ad hoc workloads will make little difference !!'
END + ' ' AS RECOMMENDATIONS
END


/* modified for our use in uptime to return just how many MB are used for adhoc query plan storage
200 MB or more is the recommended size for using optimize for ad hoc workloads */
IF EXISTS (
-- this is for 2008 and up
SELECT 1
FROM sys.configurations
WHERE NAME = 'optimize for ad hoc workloads'
)
BEGIN
DECLARE @AdHocSizeInMB DECIMAL(14, 2)
,@TotalSizeInMB DECIMAL(14, 2)
,@ObjType NVARCHAR(34)

SELECT @AdHocSizeInMB = SUM(CAST((
CASE
WHEN usecounts = 1
AND LOWER(objtype) = 'adhoc'
THEN size_in_bytes
ELSE 0
END
) AS DECIMAL(14, 2))) / 1048576
,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
FROM sys.dm_exec_cached_plans

SELECT cast(@AdHocSizeInMB AS VARCHAR(max))
END

-- reconfigure if we see the value set false

IF (SELECT value FROM sys.configurations WHERE NAME = 'optimize for ad hoc workloads') = 0
-- turn on optimize for ad hoc workloads
BEGIN
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
RECONFIGURE WITH OVERRIDE
END
ELSE
BEGIN
select 1;
END

  • Ok. Update time!

    In the demo I discussed three main trains of thought around configuration enforcement.

    1. Check conditions and report
    In this model I am measuring the conditions that would be present for me to be pushed to change a configuration value. In the example we use optimize for ad hoc workloads in SQL Server.

    2. Check configuration value and report
    Nuff said...

    3. Ensure policy is met
    In this model, I check to see if the value is set the way I want it to be and if not, change it while producing a warning message.

    We ran into some trouble around this third example...

    The "SQL Server (Basic Checks)" service monitor cannot be used in this case if we want decent messages in our alerts for starters. Because it merely tells you ok or not ok if the match field's text is matched in the output or not. If it isn't matched, it will not tell you why not / what text did appear. Secondly, it, along with "SQL Server Query Monitor", will NOT process "messages" that come from SQL server, only the result sets themselves. The sp_configure stored procedure we're calling here presents its output in message form, not as part of a result set. A quick review of the error message and code here:

    github.com/.../MonitorSQLServerQuery.java

    ... for our plugin, revealed just exactly what was going on. The good news is, there's a workaround, the bad part is that there is a bug. The bug prevents the service monitor from going into an alert state, though it does produce the desired end result of ensuring the configuration value is enforced.

    Assuming there wasn't a bug, to work around this limitation on the lack of message processing, start by using the "SQL Server Query Monitor" instead of the basic checks monitor. Then the code we use is thus (adds an extra select with our message)

    -- reconfigure if we see the value set false
    SET NOCOUNT ON;
    IF (SELECT value FROM sys.configurations WHERE NAME = 'optimize for ad hoc workloads') = 0
    -- turn on optimize for ad hoc workloads
    BEGIN
    EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
    RECONFIGURE WITH OVERRIDE
    SELECT 'Reconfigured optimize for ad hoc workloads - set value to true' AS action
    END
    ELSE
    BEGIN
    select 1 as configured;
    END

    We set to critical alert if we see anything besides 1 in the text result field in the service monitor. Thus, if the setting is set to true on the SQL server being monitored, we get a 1 back and the status remains ok. If it sees the setting as false, it will change the setting and output "Reconfigured optimize for ad hoc workloads - set value to true" instead of 1, which will go critical and show that text in the message.

    This all seems right, but because of the bug If I simply check the state of the configuration value and return 1 or the alert text, the state is represented correct as either OK or CRIT. Also NOCOUNT ON wasn't necessary... I would use a query like this now instead so that I get nice error messages and alert texts.

    IF (SELECT value FROM sys.configurations WHERE NAME = 'optimize for ad hoc workloads') = 0
    BEGIN
    SELECT 'optimize for ad hoc workloads is set to false' as configured
    END
    ELSE
    BEGIN
    SELECT 'optimize for ad hoc workloads is set to true' as configured;
    END

    The reality of it is, you can just get away with using "SQL Server (basic checks)" with a simpler script like

    SELECT value FROM sys.configurations WHERE NAME = 'optimize for ad hoc workloads'

    and match on 1 if you want it set to true or 0 if you want it set to false. If you want to fix it, I would do that with an action response if i want it done automatically or just send myself or my DBA team an alert email so we know what to go fix. Uptime also integrates with ServiceNow, so if you want to create an incident for tracking and resolution, we could do that just by checking the box in the alert profile as well.

    I'll post updates as the issue gets addressed and functionality is returned / improved and when I get the video of the session posted.

    TTFN