playing with simple parameterization.sql

/*****************************************************************************
Copyright (c) 2018 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
Setup:
    Download BabbyNames.bak.zip (43 MB database backup)
    https://github.com/LitKnd/BabbyNames/releases/tag/v1.2

This script is intendend for private test environments only
*****************************************************************************/


ALTER DATABASE BabbyNames2016 SET QUERY_STORE CLEAR;
GO
DBCC FREEPROCCACHE
GO

USE BabbyNames2016
GO

SELECT FirstName FROM ref.FirstName where FirstNameId=76682;
GO
SELECT FirstName FROM ref.FirstName where FirstNameId=86055;
GO

SELECT FirstNameId FROM ref.FirstName where FirstName='Grant';
GO
SELECT FirstNameId FROM ref.FirstName where FirstName='Kendra';
GO


SELECT 
	qsq.query_id,
	query_sql_text,
	query_parameterization_type,
	qsp.plan_id,
	cast(qsp.query_plan as XML) as qplan
FROM sys.query_store_query as qsq
JOIN sys.query_store_query_text as qt on 
	qsq.query_text_id = qt.query_text_id
JOIN sys.query_store_plan as qsp on qsq.query_id=qsp.query_id
WHERE qt.query_sql_text like '%FirstNameId%'
GO
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 '%FirstNameId%'
    OPTION (RECOMPILE);
GO