-- This is the query we use, which we refer to as the usecount query:
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
-- For example, if the following three queries are executed in the Northwind2 database, the first
-- and third queries use the same plan, but the second one needs to generate a new plan:
SELECT * FROM Orders WHERE CustomerID = 'HANAR'
SELECT * FROM Orders WHERE CustomerID = 'CHOPS'
SELECT * FROM Orders WHERE CustomerID = 'HANAR'
-- Then run the usecount query referred to previously:
USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'CHOPS';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
-- If you run the code here, you see that none of the queries can reuse the same plan:
USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM orders WHERE customerID = 'HANAR'
GO
-- Try it again
SELECT * FROM orders WHERE customerID = 'HANAR'
GO
SELECT * FROM orders
WHERE customerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR'
GO
select * from orders where customerid = 'HANAR'
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
-- Enabling the Optimize for Ad Hoc Workloads option is very straightforward, as shown in
-- the following code:
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
-- I need to modify my usecounts query slightly, and instead of looking for rows that have a
-- cacheobjtype value of Compiled Plan, I look for cacheobjtype values that start with Compiled Plan:
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
GO
USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype LIKE 'Compiled Plan%'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype LIKE 'Compiled Plan%'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
-- If you are running my sample queries as you are reading, you might want to turn off the Optimize
-- for Ad Hoc Workloads option at this point:
EXEC sp_configure 'optimize for ad hoc workloads', 0;
RECONFIGURE;
GO
-- For example, these two queries run in the Northwind2 database can use the same plan:
SELECT FirstName, LastName, Title FROM Employees
WHERE EmployeeID = 6;
SELECT FirstName, LastName, Title FROM Employees
WHERE EmployeeID = 2;
-- You can observe this behavior by running the following code and observing the output of the
-- usecount query:
USE Northwind2
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 2;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
-- A database option called PARAMETERIZATION FORCED can be enabled with the following command:
ALTER DATABASE <database_name> SET PARAMETERIZATION FORCED;
-- As strange as it may seem, even if you switch the order of the queries and use the bigger value
-- first, you get two prepared queries with two different parameter datatypes:
USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 622;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
-- However, with PARAMETERIZATION FORCED, that’s not what we get, as you can see when you run
-- the following code:
USE Northwind2;
GO
ALTER DATABASE Northwind2 SET PARAMETERIZATION FORCED;
GO
SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
SELECT * FROM BigOrders WHERE CustomerID = 'CENTC'
GO
SELECT * FROM BigOrders WHERE CustomerID = 'SAVEA'
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
ALTER DATABASE Northwind2 SET PARAMETERIZATION SIMPLE;
GO
-- The same cached plan can be used for all the following queries:
EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 6;
EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 2;
EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 6;
-- If we take the same example used earlier when we set the database to PARAMETERIZATION FORCED,
-- we can see that using sp_executesql is just as inappropriate.
USE Northwind2;
GO
SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
EXEC sp_executesql N'SELECT * FROM BigOrders
WHERE CustomerID = @p', N'@p nvarchar(10)', 'CENTC';
GO
EXEC sp_executesql N'SELECT * FROM BigOrders
WHERE CustomerID = @p', N'@p nvarchar(10)', 'SAVEA';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
SET STATISTICS IO OFF;
GO
-- Here is an example in the Northwind2 database of forcing recompilation for a stored procedure:
USE Northwind2;
GO
CREATE PROCEDURE P_Customers
@cust nvarchar(10)
AS
SELECT RowNum, CustomerID, OrderDate, ShipCountry
FROM BigOrders
WHERE CustomerID = @cust;
GO
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO ON;
GO
EXEC P_Customers 'CENTC';
GO
EXEC P_Customers 'SAVEA';
GO
EXEC P_Customers 'SAVEA' WITH RECOMPILE;
-- We create it in the pubs sample database because the authors table contains a
-- Social Security number in the au_id column:
USE pubs;
GO
CREATE FUNCTION dbo.fnMaskSSN (@ssn char(11))
RETURNS char(11)
AS
BEGIN
SELECT @SSN = 'xxx-xx-' + right (@ssn,4)
RETURN @SSN
END;
GO
DBCC FREEPROCCACHE;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-45-6789';
SELECT @mask;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-66-1111';
SELECT @mask;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-66-1111' WITH RECOMPILE;
SELECT @mask;
GO
-- If a scalar function is used within an expression, as in the example here, there is no way to
-- request recompilation:
SELECT dbo.fnMaskSSN(au_id), au_lname, au_fname, au_id FROM authors;
-- Here are two functions that do the same thing:
USE Northwind2;
GO
CREATE FUNCTION Fnc_Inline_Customers (@cust nvarchar(10))
RETURNS TABLE
AS
RETURN
(SELECT RowNum, CustomerID, OrderDate, ShipCountry
FROM BigOrders
WHERE CustomerID = @cust);
GO
CREATE FUNCTION Fnc_Multi_Customers (@cust nvarchar(10))
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate datetime,
ShipCountry nvarchar(30))
AS
BEGIN
INSERT INTO @T
SELECT RowNum, CustomerID, OrderDate, ShipCountry
FROM BigOrders
WHERE CustomerID = @cust
RETURN
END;
GO
-- Here are the calls to the functions:
DBCC FREEPROCCACHE
GO
SELECT * FROM Fnc_Multi_Customers('CENTC')
GO
SELECT * FROM Fnc_Inline_Customers('CENTC')
GO
SELECT * FROM Fnc_Multi_Customers('SAVEA')
GO
SELECT * FROM Fnc_Inline_Customers('SAVEA')
GO
-- Table 9-1 shows the results of running this code:
SELECT * FROM sys.dm_exec_plan_attributes
(0x06001200CF0B831CB821AA05000000000000000000000000)
-- In this next query, we want to retrieve the set_options, the object_id, and the
-- sql_handle from the list of attributes:
SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan'
) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute
IN ("set_options", "object_id", "sql_handle")) AS pvt;
-- You can determine what values these two SET options had for your objects by selecting
-- from the OBJECTPROPERTY function, as shown:
SELECT OBJECTPROPERTY(object_id('<object name>'), 'ExecIsQuotedIdentOn');
SELECT OBJECTPROPERTY(object_id('<object name>'), 'ExecIsAnsiNullsOn');
-- The following code illustrates this:
USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SET ANSI_NULLS ON
GO
SELECT * FROM orders WHERE customerid = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'CENTC';
GO
SET ANSI_NULLS OFF
GO
SELECT * FROM orders WHERE customerid = 'HANAR';
GO
SET ANSI_NULLS ON
GO
-- Now examine the sys.dm_exec_query_stats view and notice two different rows for the
-- query searching for ‘HANAR’
SELECT execution_count, text, sql_handle, query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS TXT
CROSS APPLY sys.dm_exec_query_plan(plan_handle)AS PLN;
GO
-- The two rows containing ‘HANAR’should have the same value for sql_handle;
-- Copy that sql_handle value and paste into the command below:
DBCC FREEPROCCACHE(0x02000000CECDF507D9D4D70720F581172A42506136AA80BA);
GO
-- If you examine sys.dm_exec_query_stats again, you see the rows for this query
-- have been removed
SELECT execution_count, text, sql_handle, query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS TXT
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS PLN;
GO
-- You can query this view to retrieve the number of buckets for each of the plan cache stores
-- using the following query:
SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP',
'CACHESTORE_PHDR', 'CACHESTORE_XPROC');
-- The following query returns the size of all the cache stores holding plans, plus the size of
-- the SQL Manager, which stores the T-SQL text of all the adhoc and prepared queries.
SELECT type AS Store, SUM(pages_allocated_count) AS Pages_used
FROM sys.dm_os_memory_objects
WHERE type IN ('MEMOBJ_CACHESTOREOBJCP', 'MEMOBJ_CACHESTORESQLCP',
'MEMOBJ_CACHESTOREXPROC', 'MEMOBJ_SQLMGR')
GROUP BY type
-- Here is the same query we discussed earlier to return attribute information and pivot it
-- so that three of the attributes are returned in the same row as the plan_handle value:
SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan'
) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute
IN ("set_options", "object_id", "sql_handle")) AS pvt;
-- After executing both batches, we examine the sys.dm_exec_query_stats view:
USE Northwind2;
DBCC FREEPROCCACHE;
SET QUOTED_IDENTIFIER OFF;
GO
-- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SET QUOTED_IDENTIFIER ON;
GO
-- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SELECT st.text, qs. sql_handle, qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st;
GO
-- The results are returned in order of frequency, with the batch having the most use
-- showing up first:
SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes,
cp.cacheobjtype, cp.objtype
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY cp.usecounts DESC
-- It also calls the sys.dm_exec_sql_text function to return the associated T-SQL batch:
SELECT text, plan_handle, d.usecounts, d.cacheobjtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY
sys.dm_exec_cached_plan_dependent_objects(plan_handle) d;
-- This query returns the 10 longest-running queries currently executing:
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_elapsed_time DESC
-- The following query returns the top 10 queries by total CPU time, to help you identify
-- the most expensive queries on your machine running SQL Server:
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
ORDER BY total_elapsed_time/execution_count DESC;
-- The first query shown here is one we saw earlier, and it can be used to determine the number of
-- buckets in the hash tables for the Object Store and the SQL Store, and the second query returns
-- the number of entries in each of those stores:
SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP');
GO
SELECT type, count(*) total_entries
FROM sys.dm_os_memory_cache_entries
WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
GROUP BY type;
GO
-- To see the size of all the plans in a cache store, you need to examine sys.dm_exec_cached_plans,
-- as shown here:
SELECT objtype, count(*) AS 'number of plans',
SUM(size_in_bytes)/(1024.0 * 1024.0 * 1024.0)
AS size_in_gb_single_use_plans
FROM sys.dm_exec_cached_plans
GROUP BY objtype;
-- The sys.dm_os_memory_cache_entries DMV can show you the current and original cost of any
-- cache entry, as well as the components that make up that cost:
SELECT text, objtype, refcounts, usecounts, size_in_bytes,
disk_ios_count, context_switches_count,
pages_allocated_count, original_cost, current_cost
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_os_memory_cache_entries e
ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan'
AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
ORDER BY objtype desc, usecounts DESC;
-- For example, it would be relatively straightforward to do one more OUTER APPLY, to join this
-- view with the sys.dm_exec_query_plan function, to get the XML plan for each of the plans in cache.
USE master
GO
CREATE VIEW sp_cacheobjects
(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid,
refcounts, usecounts, pagesused, setopts, langid, dateformat,
status, lasttime, maxexectime, avgexectime, lastreads,
lastwrites, sqlbytes, sql)
AS
SELECT pvt.bucketid,
CONVERT(nvarchar(18), pvt.cacheobjtype) AS cacheobjtype,
pvt.objtype,
CONVERT(int, pvt.objectid) AS object_id,
CONVERT(smallint, pvt.dbid) AS dbid,
CONVERT(smallint, pvt.dbid_execute) AS execute_dbid,
CONVERT(smallint, pvt.user_id) AS user_id,
pvt.refcounts, pvt.usecounts,
pvt.size_in_bytes / 8192 AS size_in_bytes,
CONVERT(int, pvt.set_options) AS setopts,
CONVERT(smallint, pvt.language_id) AS langid,
CONVERT(smallint, pvt.date_format) AS date_format,
CONVERT(int, pvt.status) AS status,
CONVERT(bigint, 0),
CONVERT(bigint, 0),
CONVERT(bigint, 0),
CONVERT(bigint, 0),
CONVERT(bigint, 0),
CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2),
CONVERT(nvarchar(3900), fgs.text)
FROM (SELECT ecp.*, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans ecp
OUTER APPLY
sys.dm_exec_plan_attributes(ecp.plan_handle) epa) AS ecpa
PIVOT (MAX(ecpa.value) for ecpa.attribute IN
("set_options", "objectid", "dbid",
"dbid_execute", "user_id", "language_id",
"date_format", "status")) AS pvt
OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs
-- For example, suppose user sue issues the following SELECT statement:
SELECT * FROM Orders
-- However, the situation is different if sue issues this command:
SELECT * FROM dbo.Orders
-- The query here lists all the resources that your SQL Server service might have to wait for,
-- and it displays the resources with the longest waiting list:
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC
-- You can look at the view sys.dm_os_memory_cache_counters to see the amount of memory allocated
-- in the multipage units:
SELECT name, type, single_pages_kb, multi_pages_kb,
single_pages_in_use_kb, multi_pages_in_use_kb
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP';
-- If you notice a high value for RESOURCE_SEMAPHORE_QUERY_COMPILE waits, you can examine the entries
-- in the plan cache through the sys.dm_exec_cached_plans view, as shown here:
SELECT usecounts, cacheobjtype, objtype, bucketid, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
ORDER BY objtype;
-- Take a look at these two queries in the Northwind2 database:
SELECT * FROM Customers
WHERE CompanyName = 'Around the Horn';
GO
SELECT * FROM Customers
WHERE CompanyName = 'Rattlesnake Canyon Grocery';
GO
-- When the following batch is optimized, SQL Server doesn’t have a specific value for the variable:
USE Northwind2;
DECLARE @custID nchar(10);
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID;
-- The RECOMPILE hint can be very useful here because it tells the Optimizer to come up with a
-- new plan for the single SELECT statement right before that statement is executed, which is after
-- the SET statement has executed:
USE Northwind2;
DECLARE @custID nchar(10);
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID
OPTION (RECOMPILE);
-- Here is an example of a plan guide that tells SQL Server to use the OPTIMIZE FOR hint whenever
-- the specified statement is found in the Sales.GetOrdersByCountry procedure:
EXEC sp_create_plan_guide
@name = N'plan_US_Country',
@stmt =
N'SELECT SalesOrderID, OrderDate, h.CustomerID, h.TerritoryID
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.Customer AS c
ON h.CustomerID = c.CustomerID
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetOrdersByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';
-- Here is an example of a plan guide that tells SQL Server to use only one CPU (no parallelization)
-- when a particular query is executed as a stand-alone query:
EXEC sp_create_plan_guide
@name = N'plan_SalesOrderHeader_DOP1',
@stmt = N'SELECT TOP 10 *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';
-- To see an example of using a template guide and forcing parameterization, first clear your procedure cache,
-- and then execute these two queries in the AdventureWorks database:
DBCC FREEPROCCACHE;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;
-- In other words, anytime a query that parameterizes to the same form as the query here,
-- it uses the same plan already cached:
DECLARE @sample_statement nvarchar(max);
DECLARE @paramlist nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;',
@sample_statement OUTPUT,
@paramlist OUTPUT
SELECT @paramlist as parameters, @sample_statement as statement
EXEC sp_create_plan_guide @name = N'Template_Plan',
@stmt = @sample_statement,
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = @paramlist,
@hints = N'OPTION(PARAMETERIZATION FORCED)';
-- After creating the plan guide, run the same two statements as shown previously, and then
-- examine the plan cache:
DBCC FREEPROCCACHE;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;
GO
SELECT objtype, dbid, usecounts, sql
FROM sp_cacheobjects
WHERE cacheobjtype = 'Compiled Plan';
-- There are 12 different SpecialOfferID values, and most of them occur only a few hundred times
-- (out of the 121,317 rows in the Sales.SalesOrderDetail) at most, as the following script and
-- output illustrates:
USE AdventureWorks
GO
SELECT SpecialOfferID, COUNT(*) as Total
FROM Sales.SalesOrderDetail
GROUP BY SpecialOfferID;
-- As there are 1,238 pages in the table, for most of the values, a nonclustered index on
-- SpecialOfferID could be useful, so here is the code to build one:
CREATE INDEX Detail_SpecialOfferIndex ON Sales.SalesOrderDetail(SpecialOfferID);
-- So we create a template plan guide to autoparameterize queries of this form:
SELECT * FROM Sales.SalesOrderDetail WHERE SpecialOfferID = 4;
-- The hint forces SQL Server to assume a specific value of 4 every time the query needs
-- to be reoptimized:
USE AdventureWorks;
-- Get plan template and create plan Guide
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM Sales.SalesOrderDetail WHERE SpecialOfferID = 4',
@stmt OUTPUT,
@params OUTPUT
--SELECT @stmt as statement -- show the value when debugging
--SELECT @params as parameters -- show the value when debugging
EXEC sp_create_plan_guide N'Template_Plan_for SpecialOfferID',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION (PARAMETERIZATION FORCED)';
EXEC sp_create_plan_guide
@name = N'Force_Value_for_Prepared_Plan',
@stmt = @stmt,
@type = N'SQL',
@module_or_batch = NULL,
@params = @params,
@hints = N'OPTION (OPTIMIZE FOR (@0 = 4))';
GO
-- You can verify that the plan is being autoparameterized and optimized for a value that uses
-- a nonclustered index on SpecialOfferID by running a few tests as follows:
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 3;
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 4;
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 5;
GO
-- To validate all of the existing plan guides in a system, you can use the sys.fn_validate_plan_guide
-- function:
SELECT * FROM sys.plan_guides pg
CROSS APPLY
(SELECT * FROM sys.fn_validate_plan_guide(pg.plan_guide_id)) v;
-- So you can incorporate this into any schema changes in the system:
BEGIN TRANSACTION;
DROP INDEX t2.myindex;
IF EXISTS(
SELECT * FROM sys.plan_guides pg
CROSS APPLY
(SELECT * FROM sys.fn_validate_plan_guide(pg.plan_guide_id)) v
)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION;
-- Suppose that we have found that the plan just executed for the following query performs extremely well,
-- and we’d like to make sure that plan is the one used on subsequent executions:
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
-- I can find the corresponding plan in cache by searching for a text value that matches the query:
SELECT plan_handle
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'SELECT City,%';
-- Once I have that plan_handle, I can pass it as a parameter to the sp_create_plan_guide_from_handle
-- procedure as follows:
EXEC sp_create_plan_guide_from_handle
@name = N'Guide1_from_XML_showplan',
@plan_handle = 0x06000600F19B1E1FC0A14C0A000000000000000000000000
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
-- For example, if the following three queries are executed in the Northwind2 database, the first
-- and third queries use the same plan, but the second one needs to generate a new plan:
SELECT * FROM Orders WHERE CustomerID = 'HANAR'
SELECT * FROM Orders WHERE CustomerID = 'CHOPS'
SELECT * FROM Orders WHERE CustomerID = 'HANAR'
-- Then run the usecount query referred to previously:
USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'CHOPS';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
-- If you run the code here, you see that none of the queries can reuse the same plan:
USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM orders WHERE customerID = 'HANAR'
GO
-- Try it again
SELECT * FROM orders WHERE customerID = 'HANAR'
GO
SELECT * FROM orders
WHERE customerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR'
GO
select * from orders where customerid = 'HANAR'
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
-- Enabling the Optimize for Ad Hoc Workloads option is very straightforward, as shown in
-- the following code:
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
-- I need to modify my usecounts query slightly, and instead of looking for rows that have a
-- cacheobjtype value of Compiled Plan, I look for cacheobjtype values that start with Compiled Plan:
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
GO
USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype LIKE 'Compiled Plan%'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype LIKE 'Compiled Plan%'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
-- If you are running my sample queries as you are reading, you might want to turn off the Optimize
-- for Ad Hoc Workloads option at this point:
EXEC sp_configure 'optimize for ad hoc workloads', 0;
RECONFIGURE;
GO
-- For example, these two queries run in the Northwind2 database can use the same plan:
SELECT FirstName, LastName, Title FROM Employees
WHERE EmployeeID = 6;
SELECT FirstName, LastName, Title FROM Employees
WHERE EmployeeID = 2;
-- You can observe this behavior by running the following code and observing the output of the
-- usecount query:
USE Northwind2
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 2;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
-- A database option called PARAMETERIZATION FORCED can be enabled with the following command:
ALTER DATABASE <database_name> SET PARAMETERIZATION FORCED;
-- As strange as it may seem, even if you switch the order of the queries and use the bigger value
-- first, you get two prepared queries with two different parameter datatypes:
USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 622;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
-- However, with PARAMETERIZATION FORCED, that’s not what we get, as you can see when you run
-- the following code:
USE Northwind2;
GO
ALTER DATABASE Northwind2 SET PARAMETERIZATION FORCED;
GO
SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
SELECT * FROM BigOrders WHERE CustomerID = 'CENTC'
GO
SELECT * FROM BigOrders WHERE CustomerID = 'SAVEA'
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
ALTER DATABASE Northwind2 SET PARAMETERIZATION SIMPLE;
GO
-- The same cached plan can be used for all the following queries:
EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 6;
EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 2;
EXEC sp_executesql N'SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @p', N'@p tinyint', 6;
-- If we take the same example used earlier when we set the database to PARAMETERIZATION FORCED,
-- we can see that using sp_executesql is just as inappropriate.
USE Northwind2;
GO
SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
EXEC sp_executesql N'SELECT * FROM BigOrders
WHERE CustomerID = @p', N'@p nvarchar(10)', 'CENTC';
GO
EXEC sp_executesql N'SELECT * FROM BigOrders
WHERE CustomerID = @p', N'@p nvarchar(10)', 'SAVEA';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
SET STATISTICS IO OFF;
GO
-- Here is an example in the Northwind2 database of forcing recompilation for a stored procedure:
USE Northwind2;
GO
CREATE PROCEDURE P_Customers
@cust nvarchar(10)
AS
SELECT RowNum, CustomerID, OrderDate, ShipCountry
FROM BigOrders
WHERE CustomerID = @cust;
GO
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO ON;
GO
EXEC P_Customers 'CENTC';
GO
EXEC P_Customers 'SAVEA';
GO
EXEC P_Customers 'SAVEA' WITH RECOMPILE;
-- We create it in the pubs sample database because the authors table contains a
-- Social Security number in the au_id column:
USE pubs;
GO
CREATE FUNCTION dbo.fnMaskSSN (@ssn char(11))
RETURNS char(11)
AS
BEGIN
SELECT @SSN = 'xxx-xx-' + right (@ssn,4)
RETURN @SSN
END;
GO
DBCC FREEPROCCACHE;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-45-6789';
SELECT @mask;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-66-1111';
SELECT @mask;
GO
DECLARE @mask char(11);
EXEC @mask = dbo.fnMaskSSN '123-66-1111' WITH RECOMPILE;
SELECT @mask;
GO
-- If a scalar function is used within an expression, as in the example here, there is no way to
-- request recompilation:
SELECT dbo.fnMaskSSN(au_id), au_lname, au_fname, au_id FROM authors;
-- Here are two functions that do the same thing:
USE Northwind2;
GO
CREATE FUNCTION Fnc_Inline_Customers (@cust nvarchar(10))
RETURNS TABLE
AS
RETURN
(SELECT RowNum, CustomerID, OrderDate, ShipCountry
FROM BigOrders
WHERE CustomerID = @cust);
GO
CREATE FUNCTION Fnc_Multi_Customers (@cust nvarchar(10))
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate datetime,
ShipCountry nvarchar(30))
AS
BEGIN
INSERT INTO @T
SELECT RowNum, CustomerID, OrderDate, ShipCountry
FROM BigOrders
WHERE CustomerID = @cust
RETURN
END;
GO
-- Here are the calls to the functions:
DBCC FREEPROCCACHE
GO
SELECT * FROM Fnc_Multi_Customers('CENTC')
GO
SELECT * FROM Fnc_Inline_Customers('CENTC')
GO
SELECT * FROM Fnc_Multi_Customers('SAVEA')
GO
SELECT * FROM Fnc_Inline_Customers('SAVEA')
GO
-- Table 9-1 shows the results of running this code:
SELECT * FROM sys.dm_exec_plan_attributes
(0x06001200CF0B831CB821AA05000000000000000000000000)
-- In this next query, we want to retrieve the set_options, the object_id, and the
-- sql_handle from the list of attributes:
SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan'
) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute
IN ("set_options", "object_id", "sql_handle")) AS pvt;
-- You can determine what values these two SET options had for your objects by selecting
-- from the OBJECTPROPERTY function, as shown:
SELECT OBJECTPROPERTY(object_id('<object name>'), 'ExecIsQuotedIdentOn');
SELECT OBJECTPROPERTY(object_id('<object name>'), 'ExecIsAnsiNullsOn');
-- The following code illustrates this:
USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SET ANSI_NULLS ON
GO
SELECT * FROM orders WHERE customerid = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'CENTC';
GO
SET ANSI_NULLS OFF
GO
SELECT * FROM orders WHERE customerid = 'HANAR';
GO
SET ANSI_NULLS ON
GO
-- Now examine the sys.dm_exec_query_stats view and notice two different rows for the
-- query searching for ‘HANAR’
SELECT execution_count, text, sql_handle, query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS TXT
CROSS APPLY sys.dm_exec_query_plan(plan_handle)AS PLN;
GO
-- The two rows containing ‘HANAR’should have the same value for sql_handle;
-- Copy that sql_handle value and paste into the command below:
DBCC FREEPROCCACHE(0x02000000CECDF507D9D4D70720F581172A42506136AA80BA);
GO
-- If you examine sys.dm_exec_query_stats again, you see the rows for this query
-- have been removed
SELECT execution_count, text, sql_handle, query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS TXT
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS PLN;
GO
-- You can query this view to retrieve the number of buckets for each of the plan cache stores
-- using the following query:
SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP',
'CACHESTORE_PHDR', 'CACHESTORE_XPROC');
-- The following query returns the size of all the cache stores holding plans, plus the size of
-- the SQL Manager, which stores the T-SQL text of all the adhoc and prepared queries.
SELECT type AS Store, SUM(pages_allocated_count) AS Pages_used
FROM sys.dm_os_memory_objects
WHERE type IN ('MEMOBJ_CACHESTOREOBJCP', 'MEMOBJ_CACHESTORESQLCP',
'MEMOBJ_CACHESTOREXPROC', 'MEMOBJ_SQLMGR')
GROUP BY type
-- Here is the same query we discussed earlier to return attribute information and pivot it
-- so that three of the attributes are returned in the same row as the plan_handle value:
SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan'
) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute
IN ("set_options", "object_id", "sql_handle")) AS pvt;
-- After executing both batches, we examine the sys.dm_exec_query_stats view:
USE Northwind2;
DBCC FREEPROCCACHE;
SET QUOTED_IDENTIFIER OFF;
GO
-- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SET QUOTED_IDENTIFIER ON;
GO
-- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SELECT st.text, qs. sql_handle, qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st;
GO
-- The results are returned in order of frequency, with the batch having the most use
-- showing up first:
SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes,
cp.cacheobjtype, cp.objtype
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY cp.usecounts DESC
-- It also calls the sys.dm_exec_sql_text function to return the associated T-SQL batch:
SELECT text, plan_handle, d.usecounts, d.cacheobjtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY
sys.dm_exec_cached_plan_dependent_objects(plan_handle) d;
-- This query returns the 10 longest-running queries currently executing:
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_elapsed_time DESC
-- The following query returns the top 10 queries by total CPU time, to help you identify
-- the most expensive queries on your machine running SQL Server:
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
ORDER BY total_elapsed_time/execution_count DESC;
-- The first query shown here is one we saw earlier, and it can be used to determine the number of
-- buckets in the hash tables for the Object Store and the SQL Store, and the second query returns
-- the number of entries in each of those stores:
SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP');
GO
SELECT type, count(*) total_entries
FROM sys.dm_os_memory_cache_entries
WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
GROUP BY type;
GO
-- To see the size of all the plans in a cache store, you need to examine sys.dm_exec_cached_plans,
-- as shown here:
SELECT objtype, count(*) AS 'number of plans',
SUM(size_in_bytes)/(1024.0 * 1024.0 * 1024.0)
AS size_in_gb_single_use_plans
FROM sys.dm_exec_cached_plans
GROUP BY objtype;
-- The sys.dm_os_memory_cache_entries DMV can show you the current and original cost of any
-- cache entry, as well as the components that make up that cost:
SELECT text, objtype, refcounts, usecounts, size_in_bytes,
disk_ios_count, context_switches_count,
pages_allocated_count, original_cost, current_cost
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_os_memory_cache_entries e
ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan'
AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
ORDER BY objtype desc, usecounts DESC;
-- For example, it would be relatively straightforward to do one more OUTER APPLY, to join this
-- view with the sys.dm_exec_query_plan function, to get the XML plan for each of the plans in cache.
USE master
GO
CREATE VIEW sp_cacheobjects
(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid,
refcounts, usecounts, pagesused, setopts, langid, dateformat,
status, lasttime, maxexectime, avgexectime, lastreads,
lastwrites, sqlbytes, sql)
AS
SELECT pvt.bucketid,
CONVERT(nvarchar(18), pvt.cacheobjtype) AS cacheobjtype,
pvt.objtype,
CONVERT(int, pvt.objectid) AS object_id,
CONVERT(smallint, pvt.dbid) AS dbid,
CONVERT(smallint, pvt.dbid_execute) AS execute_dbid,
CONVERT(smallint, pvt.user_id) AS user_id,
pvt.refcounts, pvt.usecounts,
pvt.size_in_bytes / 8192 AS size_in_bytes,
CONVERT(int, pvt.set_options) AS setopts,
CONVERT(smallint, pvt.language_id) AS langid,
CONVERT(smallint, pvt.date_format) AS date_format,
CONVERT(int, pvt.status) AS status,
CONVERT(bigint, 0),
CONVERT(bigint, 0),
CONVERT(bigint, 0),
CONVERT(bigint, 0),
CONVERT(bigint, 0),
CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2),
CONVERT(nvarchar(3900), fgs.text)
FROM (SELECT ecp.*, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans ecp
OUTER APPLY
sys.dm_exec_plan_attributes(ecp.plan_handle) epa) AS ecpa
PIVOT (MAX(ecpa.value) for ecpa.attribute IN
("set_options", "objectid", "dbid",
"dbid_execute", "user_id", "language_id",
"date_format", "status")) AS pvt
OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs
-- For example, suppose user sue issues the following SELECT statement:
SELECT * FROM Orders
-- However, the situation is different if sue issues this command:
SELECT * FROM dbo.Orders
-- The query here lists all the resources that your SQL Server service might have to wait for,
-- and it displays the resources with the longest waiting list:
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC
-- You can look at the view sys.dm_os_memory_cache_counters to see the amount of memory allocated
-- in the multipage units:
SELECT name, type, single_pages_kb, multi_pages_kb,
single_pages_in_use_kb, multi_pages_in_use_kb
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP';
-- If you notice a high value for RESOURCE_SEMAPHORE_QUERY_COMPILE waits, you can examine the entries
-- in the plan cache through the sys.dm_exec_cached_plans view, as shown here:
SELECT usecounts, cacheobjtype, objtype, bucketid, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
ORDER BY objtype;
-- Take a look at these two queries in the Northwind2 database:
SELECT * FROM Customers
WHERE CompanyName = 'Around the Horn';
GO
SELECT * FROM Customers
WHERE CompanyName = 'Rattlesnake Canyon Grocery';
GO
-- When the following batch is optimized, SQL Server doesn’t have a specific value for the variable:
USE Northwind2;
DECLARE @custID nchar(10);
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID;
-- The RECOMPILE hint can be very useful here because it tells the Optimizer to come up with a
-- new plan for the single SELECT statement right before that statement is executed, which is after
-- the SET statement has executed:
USE Northwind2;
DECLARE @custID nchar(10);
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID
OPTION (RECOMPILE);
-- Here is an example of a plan guide that tells SQL Server to use the OPTIMIZE FOR hint whenever
-- the specified statement is found in the Sales.GetOrdersByCountry procedure:
EXEC sp_create_plan_guide
@name = N'plan_US_Country',
@stmt =
N'SELECT SalesOrderID, OrderDate, h.CustomerID, h.TerritoryID
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.Customer AS c
ON h.CustomerID = c.CustomerID
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetOrdersByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';
-- Here is an example of a plan guide that tells SQL Server to use only one CPU (no parallelization)
-- when a particular query is executed as a stand-alone query:
EXEC sp_create_plan_guide
@name = N'plan_SalesOrderHeader_DOP1',
@stmt = N'SELECT TOP 10 *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';
-- To see an example of using a template guide and forcing parameterization, first clear your procedure cache,
-- and then execute these two queries in the AdventureWorks database:
DBCC FREEPROCCACHE;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;
-- In other words, anytime a query that parameterizes to the same form as the query here,
-- it uses the same plan already cached:
DECLARE @sample_statement nvarchar(max);
DECLARE @paramlist nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;',
@sample_statement OUTPUT,
@paramlist OUTPUT
SELECT @paramlist as parameters, @sample_statement as statement
EXEC sp_create_plan_guide @name = N'Template_Plan',
@stmt = @sample_statement,
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = @paramlist,
@hints = N'OPTION(PARAMETERIZATION FORCED)';
-- After creating the plan guide, run the same two statements as shown previously, and then
-- examine the plan cache:
DBCC FREEPROCCACHE;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;
GO
SELECT objtype, dbid, usecounts, sql
FROM sp_cacheobjects
WHERE cacheobjtype = 'Compiled Plan';
-- There are 12 different SpecialOfferID values, and most of them occur only a few hundred times
-- (out of the 121,317 rows in the Sales.SalesOrderDetail) at most, as the following script and
-- output illustrates:
USE AdventureWorks
GO
SELECT SpecialOfferID, COUNT(*) as Total
FROM Sales.SalesOrderDetail
GROUP BY SpecialOfferID;
-- As there are 1,238 pages in the table, for most of the values, a nonclustered index on
-- SpecialOfferID could be useful, so here is the code to build one:
CREATE INDEX Detail_SpecialOfferIndex ON Sales.SalesOrderDetail(SpecialOfferID);
-- So we create a template plan guide to autoparameterize queries of this form:
SELECT * FROM Sales.SalesOrderDetail WHERE SpecialOfferID = 4;
-- The hint forces SQL Server to assume a specific value of 4 every time the query needs
-- to be reoptimized:
USE AdventureWorks;
-- Get plan template and create plan Guide
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM Sales.SalesOrderDetail WHERE SpecialOfferID = 4',
@stmt OUTPUT,
@params OUTPUT
--SELECT @stmt as statement -- show the value when debugging
--SELECT @params as parameters -- show the value when debugging
EXEC sp_create_plan_guide N'Template_Plan_for SpecialOfferID',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION (PARAMETERIZATION FORCED)';
EXEC sp_create_plan_guide
@name = N'Force_Value_for_Prepared_Plan',
@stmt = @stmt,
@type = N'SQL',
@module_or_batch = NULL,
@params = @params,
@hints = N'OPTION (OPTIMIZE FOR (@0 = 4))';
GO
-- You can verify that the plan is being autoparameterized and optimized for a value that uses
-- a nonclustered index on SpecialOfferID by running a few tests as follows:
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 3;
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 4;
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 5;
GO
-- To validate all of the existing plan guides in a system, you can use the sys.fn_validate_plan_guide
-- function:
SELECT * FROM sys.plan_guides pg
CROSS APPLY
(SELECT * FROM sys.fn_validate_plan_guide(pg.plan_guide_id)) v;
-- So you can incorporate this into any schema changes in the system:
BEGIN TRANSACTION;
DROP INDEX t2.myindex;
IF EXISTS(
SELECT * FROM sys.plan_guides pg
CROSS APPLY
(SELECT * FROM sys.fn_validate_plan_guide(pg.plan_guide_id)) v
)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION;
-- Suppose that we have found that the plan just executed for the following query performs extremely well,
-- and we’d like to make sure that plan is the one used on subsequent executions:
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
-- I can find the corresponding plan in cache by searching for a text value that matches the query:
SELECT plan_handle
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'SELECT City,%';
-- Once I have that plan_handle, I can pass it as a parameter to the sp_create_plan_guide_from_handle
-- procedure as follows:
EXEC sp_create_plan_guide_from_handle
@name = N'Guide1_from_XML_showplan',
@plan_handle = 0x06000600F19B1E1FC0A14C0A000000000000000000000000
