Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*****************************************************************************
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment