Dear-SQL-DBA-Episode-43-Plan-Guides-Removing-Query-Hints.ps1

<#
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
#>


function LoopParameterizedQuery{
	param($Limit, $Connection, $FakeAppQuery, $Parameters=@{})

	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandType = [System.Data.CommandType]::Text
    $SqlCmd.Connection = $Connection
	$SqlCmd.CommandText = $FakeAppQuery
	foreach($parameter in $Parameters.Keys){
 		[Void] $SqlCmd.Parameters.AddWithValue("@$parameter",$Parameters[$parameter])
 	}

	$Iterations=0
	while($Iterations -ne $Limit) {
		$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmd)
		$DataSet = New-Object System.Data.DataSet
		[Void] $SqlAdapter.Fill($DataSet)
	    $Iterations++
	}

	$SqlConnection.Close()
	return "All done!"
}

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=.;Database=BabbyNames;Integrated Security=True"

$Duration = Measure-Command { `
    LoopParameterizedQuery `
        -Limit 3 `
        -Connection $SqlConnection `
        -FakeAppQuery "    with RunningTotal AS (
	    SELECT
		    fnby.FirstNameId,
            fnby.StateCode,
            fnby.Gender,
		    ReportYear,
		    SUM(NameCount) OVER (PARTITION BY fnby.FirstNameId, StateCode, Gender ORDER BY fnby.ReportYear) as TotalNamed
	    FROM agg.FirstNameByYearState as fnby
    ),
    RunningTotalPlusLag AS (
	    SELECT
		    FirstNameId,
            StateCode,
            Gender,
		    ReportYear,
		    TotalNamed,
		    LAG(TotalNamed, 1, 0) OVER (PARTITION BY FirstNameId, StateCode, Gender ORDER BY ReportYear) AS TotalNamedPriorYear
	    FROM RunningTotal
    )
    SELECT
	    fn.FirstName,
        RunningTotalPlusLag.StateCode,
        RunningTotalPlusLag.Gender,
	    RunningTotalPlusLag.ReportYear,
	    RunningTotalPlusLag.TotalNamed,
	    RunningTotalPlusLag.TotalNamedPriorYear
    FROM RunningTotalPlusLag
    JOIN ref.FirstName as fn on
        RunningTotalPlusLag.FirstNameId=fn.FirstNameId
    WHERE 
        (@Threshold is NULL
         and TotalNamed >= 100 
	     and (TotalNamedPriorYear < 100  OR TotalNamedPriorYear IS NULL)
        )
        OR
        (TotalNamed >= @Threshold 
	    and (TotalNamedPriorYear < @Threshold  OR TotalNamedPriorYear IS NULL)
        )
    ORDER BY ReportYear DESC, StateCode
	OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);" `
        -Parameters @{Threshold=100000}`
}

Write-Output "Duration = $($Duration.TotalSeconds) seconds"

Dear-SQL-DBA-Episode-43-Plan-Guides-Removing-Query-Hints.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
*****************************************************************************/

RAISERROR(N'Did you mean to run the whole thing?',20,1) WITH LOG;
GO

/*******************************************************************/
/*                        PROBLEM                                  */
/*******************************************************************/

/* Restore the database.
Download BabbyNames.bak.zip
from here: https://github.com/LitKnd/BabbyNames/releases/tag/v1.1 
*/
use master;
GO
IF DB_ID('BabbyNames') IS NOT NULL
BEGIN
    ALTER DATABASE BabbyNames SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO
RESTORE DATABASE BabbyNames FROM 
    DISK = N'S:\MSSQL\Backup\BabbyNames.bak'
	WITH REPLACE,
	MOVE N'BabbyNames' TO N'S:\MSSQL\DATA\BabbyNames.mdf',  
	MOVE N'BabbyNames_log' TO N'S:\MSSQL\DATA\BabbyNames_log.ldf',
	STATS = 5;
GO

USE BabbyNames;
GO

/* We are using the "impossible filtered columnstore index" hack to enable 
batch mode on our queries. This hack was written about first by Itzik Ben-Gan.
For more info on this type of hack, see 
https://sqlworkbooks.com/2017/05/batch-mode-hacks-for-rowstore-queries-in-sql-server/
*/
CREATE NONCLUSTERED COLUMNSTORE INDEX nccx_agg_FirstNameByYearState 
ON agg.FirstNameByYearState
    (FirstNameId)
WHERE FirstNameId = -1 and FirstNameId = -2;
GO


ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

/* We've got an app where we can't change the code. And they added hints to the queries
that made them SLOWER!!!
Can we remove a hint with a plan guide? */

/* Run script: 006-Plan-Guides-Removing-Hints.ps1 */

/* Can we find the slow query in the cache?*/
/* sys.dm_exec_query_stats reports time in microseconds, we are converting to seconds */
SELECT
    (SELECT cast(st.text as nvarchar(max)) FOR XML PATH(''),TYPE) AS [TSQL],
    qs.execution_count AS [#],
    CAST(qs.total_worker_time/1000./1000. AS numeric(30,1)) AS [cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,1))
		END AS [avg cpu sec],
    CAST(qs.total_elapsed_time/1000./1000. AS numeric(30,1)) AS [elapsed sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_elapsed_time / execution_count / 1000. / 1000. AS numeric(30,1))
		END AS [avg elapsed sec],
    qs.total_logical_reads as [logical reads],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,1))
	END AS [avg logical reads],
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE st.text like '%RunningTotal%'
    OPTION (RECOMPILE);
GO

/* See the query hint? We want to get rid of THAT!*/

/* Tips:
1) Paste in the statement with the EXACT SPACING
2) If there is a parameter declaration at the beginning, I cut that out and move it to @params
	Discard the (round parenthesis)
	Keep captialization the same! (It matters even in the parameter declaration)
3) Look for things you need to change like quote marks or >= <
4) To remove a hint, you can use @hints = NULL, or replace it with a different hint
*/

/* Fill this in */
EXEC sp_create_plan_guide
    @name = N'removing hint',
    @stmt = N'    with RunningTotal AS (
	    SELECT
		    fnby.FirstNameId,
            fnby.StateCode,
            fnby.Gender,
		    ReportYear,
		    SUM(NameCount) OVER (PARTITION BY fnby.FirstNameId, StateCode, Gender ORDER BY fnby.ReportYear) as TotalNamed
	    FROM agg.FirstNameByYearState as fnby
    ),
    RunningTotalPlusLag AS (
	    SELECT
		    FirstNameId,
            StateCode,
            Gender,
		    ReportYear,
		    TotalNamed,
		    LAG(TotalNamed, 1, 0) OVER (PARTITION BY FirstNameId, StateCode, Gender ORDER BY ReportYear) AS TotalNamedPriorYear
	    FROM RunningTotal
    )
    SELECT
	    fn.FirstName,
        RunningTotalPlusLag.StateCode,
        RunningTotalPlusLag.Gender,
	    RunningTotalPlusLag.ReportYear,
	    RunningTotalPlusLag.TotalNamed,
	    RunningTotalPlusLag.TotalNamedPriorYear
    FROM RunningTotalPlusLag
    JOIN ref.FirstName as fn on
        RunningTotalPlusLag.FirstNameId=fn.FirstNameId
    WHERE 
        (@Threshold is NULL
         and TotalNamed >= 100 
	     and (TotalNamedPriorYear < 100  OR TotalNamedPriorYear IS NULL)
        )
        OR
        (TotalNamed >= @Threshold 
	    and (TotalNamedPriorYear < @Threshold  OR TotalNamedPriorYear IS NULL)
        )
    ORDER BY ReportYear DESC, StateCode
	OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@Threshold int',
    @hints = NULL;
GO

/* A previously perfected version of the plan guide... */
EXEC sp_create_plan_guide
    @name = N'removing hint',
    @stmt = N'    with RunningTotal AS (
	    SELECT
		    fnby.FirstNameId,
            fnby.StateCode,
            fnby.Gender,
		    ReportYear,
		    SUM(NameCount) OVER (PARTITION BY fnby.FirstNameId, StateCode, Gender ORDER BY fnby.ReportYear) as TotalNamed
	    FROM agg.FirstNameByYearState as fnby
    ),
    RunningTotalPlusLag AS (
	    SELECT
		    FirstNameId,
            StateCode,
            Gender,
		    ReportYear,
		    TotalNamed,
		    LAG(TotalNamed, 1, 0) OVER (PARTITION BY FirstNameId, StateCode, Gender ORDER BY ReportYear) AS TotalNamedPriorYear
	    FROM RunningTotal
    )
    SELECT
	    fn.FirstName,
        RunningTotalPlusLag.StateCode,
        RunningTotalPlusLag.Gender,
	    RunningTotalPlusLag.ReportYear,
	    RunningTotalPlusLag.TotalNamed,
	    RunningTotalPlusLag.TotalNamedPriorYear
    FROM RunningTotalPlusLag
    JOIN ref.FirstName as fn on
        RunningTotalPlusLag.FirstNameId=fn.FirstNameId
    WHERE 
        (@Threshold is NULL
         and TotalNamed >= 100 
	     and (TotalNamedPriorYear < 100  OR TotalNamedPriorYear IS NULL)
        )
        OR
        (TotalNamed >= @Threshold 
	    and (TotalNamedPriorYear < @Threshold  OR TotalNamedPriorYear IS NULL)
        )
    ORDER BY ReportYear DESC, StateCode
	OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@Threshold int',
    @hints = NULL;
GO


/* Validate with sys.fn_validate_plan_guide */
SELECT pg.name, val.*
FROM sys.plan_guides AS pg
OUTER APPLY sys.fn_validate_plan_guide (pg.plan_guide_id) as val;
GO


/* Run script: 006-Plan-Guides-Removing-Hints.ps1 */


/* Can we find the slow query in the cache NOW?*/
SELECT
    (SELECT cast(st.text as nvarchar(max)) FOR XML PATH(''),TYPE) AS [TSQL],
    qs.execution_count AS [#],
    CAST(qs.total_worker_time/1000./1000. AS numeric(30,1)) AS [cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,1))
		END AS [avg cpu sec],
    CAST(qs.total_elapsed_time/1000./1000. AS numeric(30,1)) AS [elapsed sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_elapsed_time / execution_count / 1000. / 1000. AS numeric(30,1))
		END AS [avg elapsed sec],
    qs.total_logical_reads as [logical reads],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,1))
	END AS [avg logical reads],
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE st.text like '%RunningTotal%'
    OPTION (RECOMPILE);
GO

/* Look at the execution plan. Can you tell a plan guide was used? */

EXEC sp_control_plan_guide @operation='DROP', @name=N'removing hint';
GO