Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 377

5. In this recipe, we will obtain all the rows from the Products table by returning

total sales and discounts for each production plus total revenue calculation for
each product.

6. The required TSQL is shown as follows:

--The following examples return all rows from the Product table
based on the following condition:
--the first level query returns total sales and the discounts for
each product.
--in the second level query, the total revenue is calculated for
each product.
USE AdventureWorks2008R2;
GO
DECLARE @ProductName nvarchar(25)
SET @ProductName like'%Bike%'
SELECT distinct p.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID AND
p.Name = @ProductName
ORDER BY ProductName,Discounts ASC;
GO

7. The query returns 21 rows for the @ProductName = 'Bike Wash - Dissolver'

value.

8. Now, let us obtain the information on the compiled plan in the memory:

--Obtain the information about a compiled plan in the memory that
is reused based on the number of executions
SELECT refcounts,cacheobjtype, objtype, usecounts,size_in_bytes
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE 'DECLARE @Product%'

9. The result is as follows:

refcounts cacheobjtype objtype usecounts size_in_bytes
2 Compiled Plan Adhoc 1 81920
1 Compiled Plan Adhoc 1 32768

10. Let us add the RECOMPILE hint to the query used in step 7:
--Use the RECOMPILE hint
DECLARE @ProductName nvarchar(25)
SET @ProductName='Bike Wash - Dissolver'
SELECT distinct p.Name AS ProductName,