some sp, dmv, notes from course SQL Server: Optimizing Ad Hoc Statement Performance Kimberly L. Tripp https://app.pluralsight.com/library/courses/sqlserver-optimizing-adhoc-statement-performance/table-of-contents

notes_sql_statments.md

Variable is strongly typed. If the statement is "safe" SQL Server will only have ONE plan to use/re-use.

DECLARE @ExecStrNVARCHAR (4000),
		@MemberNo	INT = 1567;

SELECT @ExecStr =
	N'SELECT [m].* 
	FROM [dbo].[member] AS [m] 
	WHERE [m].[member_no] = CONVERT (INT, ' 
		+ CONVERT (NVARCHAR (10), @MemberNo) + N')';
SELECT @ExecStr;
EXEC (@ExecStr);
GO

NOTES on strings

Using REPLACE instead of QUOTENAME. QUOTENAME is limited to NVARCHAR (128) for longer strings - you must use REPLACE:

SELECT @ExecStr = N'SELECT [m].* 
FROM [dbo].[member] AS [m] 
	WHERE [m].[lastname] LIKE CONVERT (VARCHAR (15), ''' 
       + REPLACE (@lastname, '''', '''''') 
       + N''')';

---

DEFAULT: 500 + 20% of the table (member = 10K rows) Member's stats will get INVALIDATED at 2,500 rows 20% = 2000 + 500

Or, if trace flag 2371 is turned on, then updates are dynamic (tied to table size). Read this blog post: http://bit.ly/qOAIqs During compilation the variable was deemed "UNKNOWN" (which makes sense, the assignment doesn't happen until runtime/execution)

DECLARE @LastNameVar VARCHAR (15) = 'Tripp';
		-- this is the EXACT same data type as the column
SELECT [m].* 
FROM [dbo].[member] AS [m]
WHERE [m].[lastname] = @LastNameVar;
GO

---

If a value is unknown - it cannot be "sniffed" If it cannot be sniffed then SQL Server has to use the "average" number of rows. This comes from the density vector.

---

No statistics therefore HEURISTICS. Heuristics = internal rules main point - statistics are generally better than heuristics.

---

Estimates come from:

Statistics – if they exist or if they can be (auto) created, using:

• The histogram: when the value can be “sniffed” (parameters)
• The density vector: when the value cannot be “sniffed” (variables)

Heuristics – if there are no statistics available and SQL Server cannot auto create them

• These are internal 'magic' numbers (cannot be changed)

• They often result in very poor plans (LEAVE AUTO_CREATE_STATISTICS ON)

• Sometimes this is the only option when better estimations cannot occur (comparison between columns (e.g. col1 > col2))

Statistics have to be reasonably small to be fast/useful They’re just estimates They’re not always guaranteed to be accurate They’re just meant to get us closer to the right value

show statistincs information

EXEC sp_autostats 'dbo.member'

---

Data analyzed to build the statistics

• Rows – number of rows in the table at the time the statistics were built
• Rows Sampled – the number of rows that were analyzed to generate the statistic
  • Sampling
    • Does not directly indicate a problem with statistics
    • Could be a problem if your data is heavily skewed
    • Is it a problem?
    • Using showplantooltip –estimate vs. actual rows
• If query performance is poor AND the actual is significantly OFF from the estimate then you might want to verify the statistics creation (rows v. rows sampled)
• If statistics were based on a sampling and performance is improved after statistics have been updated, then you might want to turn off auto update for this index (using STATISTICS_NORECOMPUTE) and schedule an UPDATE STATISTICS WITH FULLSCAN

---

When and How Does SQL Server Use Statistics?

• Estimation comes from “sniffing” the value
  • Result:estimate comes from HISTOGRAM
    • Pro: estimate is usually more accurate
    • Pro: that execution gets a plan designed for that value
    • Con: If/when this plan is saved –subsequentexecutions are prone to “parameter sniffing problems” (PSP)
• Value cannot be “sniffed”
  • Result: estimate comes from the DENSITY_VECTOR
  • Depends: estimate is an “average”
  • Depends: the plan generated is designed for the “average” value –not that value
  • Pro:If/when this plan is saved –subsequent executions are NOT prone to PSP
  • Con:When your data is NOT [relatively] evenly distributed, this plan might not be good for anyone

---

Summary: Estimates and Selectivity

• Method: ad hoc statement
  • Can have literals
  • Can be “sniffed” and estimated using the HISTOGRAM
  • Can generate an optimal plan
  • Can have variables
  • Cannot be "sniffed" (they are unknown during optimization/compilation)
  • Optimizes based on the average distribution of data (using the DENSITY_VECTOR)
  • Can be parameterized and cached but it’s extremely unlikely (only when safe)
  • Requires CPU/compilation on every execution
• Method: sp_executesql
  • Can generate an optimal plan for the firstexecution
  • Saves CPU/compilations costs for subsequent executions
  • Can be prone to parameter sniffing problems (PSP)
  • When the optimal plan varies (based on the parameters passed) then subsequent executions may suffer by using the plan chosen by the first execution’s parameters
• Method: Dynamic String Execution through EXEC (@string)
  • Turns the statement into an ad hoc statement
  • It behaves exactly like an ad hoc statement

---

Statements are unsafe when the statement

1. Uses an IN clause
2. Has more than one table in the FROM clause
3. Uses expressions joined by ORin a WHERE clause
4. When a SELECT query contains a sub-query

---

SIMPLE isn't good for everthing, some statements should be cached FORCED isn't good for everything some statements shouldn't be cached. Can use a hybrid approach Ad hoc when it varies sp_executesql when it doesn't The end result - it's often better to keep the database SIMPLE and force only those statements that are stable

---

Statement Caching

• Ad hoc statements
  • Simple parameterization –almost all statements will be compiled just for that execution; they will not be parameterized/saved (see rules for parameterization in whitepaper)
  • Forced parameterization –most statements will be parameterized/saved (you’ll see this in decreased CPU/compilations and potential for parameter-sniffing problems)
• sp_executesql (or, prepared statements)
  • A fantastic way to reduce the CPU/cache overhead that ad hoc has, but should only be used when a plan is stable and consistent
  • This is a better way to force a statement into cache as opposed to using forced parameterization database-wide
• Stored procedures
  • Can be “sniffed” but there are exceptions to what SQL Server will store in their plans

---

---

Memory (GB)	Plan Cache (GB)
4	3
8	3.5
16	4.2
32	5.8
64	9
128	12.2
256	30

---

Server setting: optimize for ad hoc workloads

On first execution, only the query_hash will go into cache, On second execution (if), the plan will be placed in cache. Create a single and more consistent plan with covering indexes – might make the plans more stable! SQL Server will pick up SOME stable statements IF and ONLY IF they’re SAFE Note: this only reduces compilation costs (e.g. CPU) but it does not reduce plan cache pollution because every ad hoc statement still goes into the ad hoc cache If you create a bunch of stable plans that SQL doesn’t see as SAFE but they essentially are (one query_plan_hashfor each query_hash) then you can consider the database setting: forced parameterization If you’re finding A LOT of single-use statements that have the same query_hashand are executed frequently but with only one query_plan_hashthen this is ideal! But, remember, ad hoc statements are always placed in the ad hoc plan cache so you still need optimize for ad hoc workloads…set that FIRST!

---

Statement Execution Solutions

• If SQL Server defines the statement as "safe":
  • Nothing do to here as SQL Server parameterizes it
  • Better to use sp_executesql to directly reduce ad hoc plan cache pollution
  • If you stabilize plans with better indexes, SQL Server might pick up more (of the simple) statements as "safe"
  • For ad hoc statements, nothing to do** unless the database is set to FORCED**, if so:
  • Change the code – do not use an ad hoc statement, use sp_executesql and OPTION (RECOMPILE)
  • If you can’t change the code – use a plan guide template to recompile the plan (PARAMETERIZATION SIMPLE)
  • NOTE: If you turn on the server setting: optimize for ad hoc workloads as well as the database option for parameterization: FORCED and you don’t have a lot of executions you can create more plan cache pollution by placing the forced plan at first execution as well as the compiled plan stub
• If SQL Server defines the statement as "unsafe", but the parameters do not require a plan change (i.e. a stable plan):
  • Covering indexes often lead to better plan stability…
  • If you can change the code –use sp_executesql
  • If you can’t change the code –consider a plan guide template to force the plan (PARAMETERIZATION FORCED)
  • NOTE: Not all statements can be used in a plan guide template (for example, LIKE) even if the parameters are consistent enough to generate the same plan.
  • Generally, avoid changing the database setting for PARAMETERIZATION unless the large majority of statements over your business cycle are stable plans with large numbers of executions (test, test, test!)
  • If SQL Server defines the statement as "unsafe" and the parameters supplied require plan changes (i.e. unstable plan):
  • Do not use sp_executesql, leave it as ad hoc
  • Or, if using sp_executesql–consider adding OPTION (RECOMPILE)
  • NOTE: There are other methods for dealing with various PSP patterns but they’re a bit beyond the scope of this course.

sp_SQLskills_CheckPlanCache.sql

-- Periodic clearing
USE [master];
GO

IF OBJECTPROPERTY (OBJECT_ID ('sp_SQLskills_CheckPlanCache'),
		'IsProcedure') = 1
	DROP PROCEDURE [sp_SQLskills_CheckPlanCache];
GO

CREATE PROCEDURE [dbo].[sp_SQLskills_CheckPlanCache]
	(@Percent	DECIMAL (6,3) OUTPUT,
	 @WastedMB	DECIMAL (19,3) OUTPUT)
AS
SET NOCOUNT ON;

DECLARE @ConfiguredMemory	DECIMAL (19,3)
	, @PhysicalMemory		DECIMAL (19,3)
	, @MemoryInUse			DECIMAL (19,3)
	, @SingleUsePlanCount	BIGINT;



CREATE TABLE #ConfigurationOptions
(
	[name]				NVARCHAR (35)
	, [minimum]			INT
	, [maximum]			INT
	, [config_value]	INT				-- in bytes
	, [run_value]		INT				-- in bytes
);
INSERT #ConfigurationOptions 
	EXEC ('sp_configure ''max server memory''');

SELECT @ConfiguredMemory 
		= [c].[run_value]/1024/1024 
FROM #ConfigurationOptions AS [c]
WHERE [c].[name] = 'max server memory (MB)'

SELECT @PhysicalMemory 
		= [omem].[total_physical_memory_kb] / 1024 
FROM [sys].[dm_os_sys_memory] AS [omem]

SELECT @MemoryInUse 
	= [pmem].[physical_memory_in_use_kb] / 1024 
FROM [sys].[dm_os_process_memory] AS [pmem]

SELECT @WastedMB 
			= SUM (CAST ((CASE 
				WHEN [cp].[usecounts] = 1 
					AND [cp].[objtype] 
						IN ('Adhoc', 'Prepared') 
				THEN [cp].[size_in_bytes] ELSE 0 END) 
					AS DECIMAL (18,2))) / 1024 / 1024 
	, @SingleUsePlanCount 
			= SUM (CASE 
				WHEN [cp].[usecounts] = 1 
					AND [cp].[objtype] 
						IN ('Adhoc', 'Prepared') 
				THEN 1 ELSE 0 END)
	, @Percent = @WastedMB/@MemoryInUse * 100
FROM [sys].[dm_exec_cached_plans] AS [cp]

SELECT	[TotalPhysicalMemory (MB)] = @PhysicalMemory
	, [TotalConfiguredMemory (MB)] = @ConfiguredMemory
	, [MaxMemoryAvailableToSQLServer (%)] =
		@ConfiguredMemory / @PhysicalMemory * 100
	, [MemoryInUseBySQLServer (MB)] = @MemoryInUse
	, [TotalSingleUsePlanCache (MB)] = @WastedMB
	, [TotalNumberOfSingleUsePlans] = @SingleUsePlanCount
	, [PercentOfConfiguredCacheWastedForSingleUsePlans (%)] 
		= @Percent
GO

EXEC [sys].[sp_MS_marksystemobject] 
	'sp_SQLskills_CheckPlanCache';
GO

-----------------------------------------------------------------
-- Next put this logic in an Agent job, set the values
-- to define when cache should be cleared, and then
-- automate it to run every so often (based on how fast 
-- your cache fills with useless single-use plans). 
-- It's an inexpensive check so checking every few hours
-- is reasonable.
-----------------------------------------------------------------

DECLARE @Percent		DECIMAL (6, 3)
		, @WastedMB		DECIMAL (19,3)
		, @StrMB		NVARCHAR (20)
		, @StrPercent	NVARCHAR (20);

EXEC [sp_SQLskills_CheckPlanCache] 
	  @Percent OUTPUT
	, @WastedMB OUTPUT;

SELECT @StrMB = CONVERT (NVARCHAR (20), @WastedMB)
		, @StrPercent = CONVERT (NVARCHAR (20), @Percent);

IF @Percent > 10 OR @WastedMB > 2000 -- 2GB
	BEGIN
		DBCC FREESYSTEMCACHE('SQL Plans') 
		RAISERROR ('%s MB (%s percent) was allocated to single-use plan cache. Single-use plans have been cleared.'
			, 10, 1, @StrMB, @StrPercent)
	END
ELSE
	BEGIN
		RAISERROR ('Only %s MB (%s percent) is allocated to single-use plan cache - no need to clear cache now.'
			, 10, 1, @StrMB, @StrPercent)
			-- Note: this is only a warning message and not an actual error.
	END;
GO

StatementCachingAnaliz.sql

-----------------------------------------------------------------
-- Course: SQL Server: Optimizing Ad Hoc Statement Performance
-- Module: Statement Caching
--   Demo: Analyzing query_hash and query_plan_hash
-----------------------------------------------------------------

SELECT TOP(10) [type] AS [Memory Clerk Type], 
       SUM(pages_kb)/1024 AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

select count(*), query_plan_hash 
from sys.dm_exec_query_stats 
group by query_plan_hash 
order by count(*) desc

----------------------------------
-- MUST BE RUN AFTER THE PRIOR DEMO
--   Demo: Ad Hoc Statements and the Plan Cache
----------------------------------

SELECT qs.query_hash,
       COUNT(DISTINCT qs.query_plan_hash) [Distinct plan count],
       SUM(qs.execution_count) [Execution total],
       SUM(cp.size_in_bytes) / 1024.0 / 1024.0 [size in mb]
FROM   sys.dm_exec_query_stats        AS qs
       JOIN sys.dm_exec_cached_plans  AS cp
            ON  cp.plan_handle = qs.plan_handle
GROUP BY
       qs.query_hash
ORDER BY
       [Execution total] DESC;

-- What's in cache for queries
SELECT [st].[text],
       [qs].*
FROM   [sys].[dm_exec_query_stats] AS [qs]
       CROSS APPLY [sys].[dm_exec_sql_text]
([qs].[sql_handle]) AS [st]
WHERE  [st].[text] LIKE N'%m_unsafe%';

-- Notice that these two new queries have the same query_hash 
-- but NOT the same query_plan_hash

-- Also, add in the database ID for the entity location
-- and/or to restrict this to only one database
-- Use: sys.dm_exec_plan_attributes

SELECT DB_NAME(CONVERT(INT, [pa].[value])) AS [Database Name],
       [st].[text],
       [qs].[query_hash],
       [qs].[query_plan_hash],
       [qs].[execution_count],
       [qs].[plan_handle],
       [qs].[statement_start_offset],
       [qs].*,
       [qp].*
FROM   [sys].[dm_exec_query_stats] AS [qs]
       CROSS APPLY [sys].[dm_exec_sql_text]
([qs].[sql_handle]) AS [st]
CROSS APPLY [sys].[dm_exec_query_plan]
([qs].[plan_handle]) AS [qp]
CROSS APPLY [sys].[dm_exec_plan_attributes]
([qs].[plan_handle]) AS [pa]
WHERE  [st].[text] LIKE N'%member%'
       AND [st].[text] NOT LIKE N'%syscacheobjects%'
       AND [pa].[attribute] = N'dbid' 
           -- AND [pa].[value] = DB_ID ()
           -- This last expression should be uncommented if you
           -- only want to see the current database's queries
ORDER BY
       2,
       [qs].[execution_count] DESC;
GO

-- Let's get an overall picture of how many 
-- plans EACH query_hash has?
SELECT [qs].[query_hash],
       [Distinct Plan Count]     = COUNT(DISTINCT [qs].[query_plan_hash]),
       [Execution Total]         = SUM([qs].[execution_count])
FROM   [sys].[dm_exec_query_stats] AS [qs]
GROUP BY
       [qs].[query_hash]
ORDER BY
       [Execution Total] DESC;
GO

-- When the "Distinct Plan Count" is mostly 1 for your queries
-- then you MIGHT consider using forced parameterization.

-- However, before you turn this on - you might want to get
-- more details about the queries that have MULTIPLE plans

-- Review a sampling of the queries (grouping by the query_hash)
-- and see which have the highest *Avg* CPU Time:
SELECT [Query Hash] = [qs2].[query_hash],
       [Query Plan Hash]            = [qs2].[query_plan_hash],
       [Avg CPU Time]               = SUM([qs2].[total_worker_time]) /
       SUM([qs2].[execution_count]),
       [Example Statement Text]     = MIN([qs2].[statement_text])
FROM   (
           SELECT [qs].*,
                  [statement_text] = SUBSTRING(
                      [st].[text],
                      ([qs].[statement_start_offset] / 2) + 1,
                      (
                          (
                              CASE [statement_end_offset]
                                   WHEN - 1 THEN DATALENGTH([st].[text])
                                   ELSE [qs].[statement_end_offset]
                              END 
                              - [qs].[statement_start_offset]
                          ) / 2
                      ) + 1
                  )
           FROM   [sys].[dm_exec_query_stats] AS [qs]
                  CROSS APPLY [sys].[dm_exec_sql_text]
           ([qs].[sql_handle]) AS [st]
       ) AS [qs2]
GROUP BY
       [qs2].[query_hash],
       [qs2].[query_plan_hash]
ORDER BY
       [Avg CPU Time] DESC;
GO

-- Review a sampling of the queries (grouping by the query_hash)
-- and see which have the highest cumulative effect by CPU Time:
SELECT [qs2].[query_hash]              AS [Query Hash],
       SUM([qs2].[total_worker_time])  AS [Total CPU Time - Cumulative Effect],
       COUNT(DISTINCT [qs2].[query_plan_hash]) AS [Number of plans],
       SUM([qs2].[execution_count])    AS [Number of executions],
       MIN([qs2].[statement_text])     AS [Example Statement Text]
FROM   (
           SELECT [qs].*,
                  [statement_text] = SUBSTRING(
                      [st].[text],
                      ([qs].[statement_start_offset] / 2) + 1,
                      (
                          (
                              CASE [statement_end_offset]
                                   WHEN - 1 THEN DATALENGTH([st].[text])
                                   ELSE [qs].[statement_end_offset]
                              END 
                              - [qs].[statement_start_offset]
                          ) / 2
                      ) + 1
                  )
           FROM   [sys].[dm_exec_query_stats] AS [qs]
                  CROSS APPLY [sys].[dm_exec_sql_text]
           ([qs].[sql_handle]) AS [st]
       )                               AS [qs2]
GROUP BY
       [qs2].[query_hash]
ORDER BY
       [Total CPU Time - Cumulative Effect] DESC;
GO


WITH basedata AS (
         SELECT qs.statement_start_offset / 2 AS stmt_start,
                qs.statement_end_offset / 2 AS stmt_end,
                est.encrypted  AS isencrypted,
                est.text       AS sqltext,
                epa.value      AS set_options,
                qp.query_plan,
                CHARINDEX('<ParameterList>', qp.query_plan) + LEN('<ParameterList>') AS paramstart,
                CHARINDEX('</ParameterList>', qp.query_plan) AS paramend
         FROM   sys.dm_exec_query_stats qs
                CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est
         CROSS APPLY sys.dm_exec_text_query_plan(
             qs.plan_handle,
             qs.statement_start_offset,
             qs.statement_end_offset
         ) qp
         CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
         WHERE  --est.objectid = OBJECT_ID(@procname)
                --AND est.dbid = DB_ID(@dbname) AND 
                epa.attribute = 'set_options'
     ), next_level AS (
         SELECT stmt_start,
                set_options,
                query_plan,
                CASE 
                     WHEN isencrypted = 1 THEN '-- ENCRYPTED'
                     WHEN stmt_start >= 0 THEN SUBSTRING(
                              sqltext,
                              stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN DATALENGTH(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END
                          )
                END  AS Statement,
                CASE 
                     WHEN paramend > paramstart THEN CAST(
                              SUBSTRING(query_plan, paramstart, paramend - paramstart) 
                              AS XML
                          )
                END  AS params
         FROM   basedata
     )
  
SELECT set_options              AS [SET],
       n.stmt_start             AS Pos,
       n.Statement,
       CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
       CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
       CAST(query_plan AS XML)  AS [Query plan]
FROM   next_level n
       CROSS APPLY n.params.nodes('ColumnReference') AS CR(c)
ORDER BY
       n.set_options,
       n.stmt_start,
       Parameter
       
CREATE PROCEDURE [QuickCheckOnCacheWSize]
@StringToFind NVARCHAR(4000)
AS
	SELECT [cp].[objtype],
	       [cp].[cacheobjtype],
	       [cp].[size_in_bytes],
	       [cp].[refcounts],
	       [cp].[usecounts],
	       [st].[text]
	FROM   sys.dm_exec_cached_plans AS [cp]
	       CROSS APPLY sys.dm_exec_sql_text ([cp].[plan_handle]) AS [st]
	WHERE  [cp].[objtype] IN (N'Adhoc', N'Prepared')
	       AND [st].[text] LIKE @StringToFind
	       AND (
	               [st].[text] NOT LIKE '%syscacheobjects%'
	               OR [st].[text] NOT LIKE '%SELECT%cp.objecttype%'
	           )
	ORDER BY
	       [cp].[objtype],
	       [cp].[size_in_bytes];
GO 

-- Essentially the same thing, but add the query
-- plan to the output
CREATE PROCEDURE [QuickCheckOnCacheWSizeAndPlan]
(
    @StringToFind   NVARCHAR (4000)
)
AS
SET NOCOUNT ON;

SELECT [st].[text]
	, [qs].[execution_count]
	, [qs].[plan_handle]
	, [qs].[statement_start_offset]
	, [qp].[query_plan]
FROM [sys].[dm_exec_query_stats] AS [qs] 
	CROSS APPLY [sys].[dm_exec_sql_text]
		 ([qs].[sql_handle]) AS [st]
	CROSS APPLY [sys].[dm_exec_query_plan]
		 ([qs].[plan_handle]) AS [qp]
WHERE [st].[text] LIKE @StringToFind 
	AND ([st].[text] NOT LIKE N'%syscacheobjects%'
		OR [st].[text] NOT LIKE N'%SELECT%cp.objecttype%')
ORDER BY 1, [qs].[execution_count] DESC;
GO

-- 2008 adds query_hash and query_plan_hash
CREATE PROCEDURE [QuickCheckOnCacheWHashSizeAndPlan]
@StringToFind NVARCHAR(4000)
AS
	SELECT [st].[text],
	       [qs].[query_hash],
	       [qs].[query_plan_hash],
	       [qs].[execution_count],
	       [qs].[plan_handle],
	       [qs].[statement_start_offset],
	       [qs].*,
	       [qp].*
	FROM   sys.dm_exec_query_stats AS [qs]
	       CROSS APPLY sys.dm_exec_sql_text ([qs].[sql_handle]) AS [st]
	CROSS APPLY sys.dm_exec_query_plan ([qs].[plan_handle]) AS [qp]
	WHERE  [st].[text] LIKE @StringToFind
	       AND (
	               [st].[text] NOT LIKE '%syscacheobjects%'
	               OR [st].[text] NOT LIKE '%SELECT%cp.objecttype%'
	           )
	ORDER BY
	       1,
	       [qs].[execution_count] DESC;
GO       
       

CREATE PROCEDURE [dbo].[CheckForPlanCachePollution]
AS
	SELECT [Cache Type] = [cp].[objtype],
	       [Total Plans]                   = COUNT_BIG(*),
	       [Total MBs]                     = SUM(CAST([cp].[size_in_bytes] AS DECIMAL(18, 2))) / 1024.0 / 1024.0,
	       [Avg Use Count]                 = AVG([cp].[usecounts]),
	       [Total MBs - USE Count 1]       = SUM(
	           CAST(
	               (
	                   CASE 
	                        WHEN [cp].[usecounts] = 1 THEN [cp].[size_in_bytes]
	                        ELSE 0
	                   END
	               ) 
	               AS DECIMAL(18, 2)
	           )
	       ) / 1024.0 / 1024.0,
	       [Total Plans - USE Count 1]     = SUM(CASE WHEN [cp].[usecounts] = 1 THEN 1 ELSE 0 END),
	       [Percent Wasted]                = (
	           SUM(
	               CAST(
	                   (
	                       CASE 
	                            WHEN [cp].[usecounts] = 1 THEN [cp].[size_in_bytes]
	                            ELSE 0
	                       END
	                   ) 
	                   AS DECIMAL(18, 2)
	               )
	           ) 
	           / SUM([cp].[size_in_bytes])
	       ) * 100
	FROM   [sys].[dm_exec_cached_plans] AS [cp]
	GROUP BY
	       [cp].[objtype]
	ORDER BY
	       [Total MBs - USE Count 1] DESC;
GO

CREATE PROCEDURE [dbo].[GetDemoQueryTimes]
AS
	SELECT [qh].*,
	       [qp].query_plan
	FROM   (
	           SELECT [cp].[objtype],
	                  [Query Hash]          = [qs2].[query_hash],
	                  [Query Plan Hash]     = [qs2].[query_plan_hash],
	                  [Total MB]            = SUM([cp].[size_in_bytes]) /
	                  1024.00 / 1024.00,
	                  [Avg CPU Time]        = SUM([qs2].[total_worker_time]) /
	                  SUM([qs2].[execution_count]),
	                  [Execution Total]     = SUM([qs2].[execution_count]),
	                  [Total Cost]          = SUM([qs2].[total_worker_time]),
	                  [Example Statement Text] = MIN([qs2].[statement_text]),
	                  [plan_handle]         = MIN([qs2].[plan_handle]),
	                  [statement_start_offset] = MIN([qs2].[statement_start_offset])
	           FROM   (
	                      SELECT [qs].*,
	                             SUBSTRING(
	                                 [st].[text],
	                                 ([qs].[statement_start_offset] / 2) + 1,
	                                 (
	                                     (
	                                         CASE [statement_end_offset]
	                                              WHEN -1 THEN DATALENGTH([st].[text])
	                                              ELSE [qs].[statement_end_offset]
	                                         END - 
	                                         [qs].[statement_start_offset]
	                                     ) / 2
	                                 ) + 1
	                             ) AS [statement_text]
	                      FROM   [sys].[dm_exec_query_stats] AS [qs]
	                             CROSS APPLY [sys].[dm_exec_sql_text] 
	                      ([qs].[sql_handle]) AS [st]
	                      WHERE  --[st].[text] LIKE '%member%' AND 
	                             [st].[text] NOT LIKE '%dm_exec%'
	                  ) AS [qs2]
	                  INNER JOIN [sys].[dm_exec_cached_plans] AS [cp]
	                       ON  [qs2].[plan_handle] = [cp].[plan_handle]
	           GROUP BY
	                  [cp].[objtype],
	                  [qs2].[query_hash],
	                  [qs2].[query_plan_hash]
	       ) AS [qh]
	       CROSS APPLY [sys].[dm_exec_query_plan] 
	([qh].[plan_handle]) AS [qp]
	-- For the demo, use the ORDER BY [Example Statement Text]
	ORDER BY
	       [Example Statement Text]
	-- For the real-world, use the following order by:
	-- ORDER BY [qh].[Total Cost] DESC
	;

Mostly from https://app.pluralsight.com/library/courses/sqlserver-optimizing-adhoc-statement-performance/table-of-contents