-
-
Save SQLKiwi/35db6bfba176a262b42bb51774ae2a1e to your computer and use it in GitHub Desktop.
Query Optimizer Deep Dive Scripts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
-- Test query | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM | |
Production.Product AS p, | |
Production.ProductInventory AS inv | |
WHERE | |
inv.ProductID = p.ProductID | |
AND p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name; | |
GO | |
-- Input tree (ISO-89) | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM | |
Production.Product AS p, | |
Production.ProductInventory AS inv | |
WHERE | |
inv.ProductID = p.ProductID | |
AND p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (RECOMPILE, QUERYTRACEON 8605); | |
GO | |
-- Input tree (ISO-92) | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (RECOMPILE, QUERYTRACEON 8605); | |
GO | |
-- Forced index hint | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p WITH (INDEX([AK_Product_Name])) | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (RECOMPILE); | |
GO | |
-- Hash join | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p WITH (INDEX([AK_Product_Name])) | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (HASH JOIN, RECOMPILE); | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
-- Directly using a constant | |
SELECT TOP (10) * | |
FROM Production.Product AS p | |
WHERE p.ProductID < 30; | |
GO | |
-- Constant folding an expression | |
SELECT p.Name | |
FROM Production.Product AS p | |
WHERE p.Name LIKE SUBSTRING(LEFT(CHAR(ASCII(CHAR(68))), 1) + '%', 1, 2) | |
GO | |
-- Remove redundancy | |
SELECT TOP (10) * FROM | |
( | |
SELECT * FROM | |
( | |
SELECT * FROM | |
( | |
SELECT * FROM Production.Product AS p | |
WHERE p.ProductID = 400 | |
) AS q1 | |
WHERE q1.ProductID = 400 | |
) AS q2 | |
WHERE q2.ProductID = 400) | |
AS q3 | |
WHERE q3.ProductID = 400; | |
GO | |
-- Simplify domains | |
SELECT TOP (10) * | |
FROM Production.Product AS p | |
WHERE p.ProductID BETWEEN 300 AND 400 | |
AND p.ProductID BETWEEN 200 AND 500 | |
AND p.ProductID BETWEEN 400 AND 600; | |
GO | |
-- Computed column matching | |
DECLARE @Example AS TABLE | |
( | |
col1 int NULL, | |
col2 int NULL, | |
col3 AS col1 * col2 UNIQUE CLUSTERED | |
); | |
SELECT * FROM @Example AS e WHERE col3 = 5; | |
SELECT * FROM @Example AS e WHERE col1 * col2 = 5; | |
SELECT * FROM @Example AS e WHERE col2 * col1 = 5; | |
GO | |
-- Remove unnecessary joins | |
SELECT | |
th.ProductID, | |
SUM(th.ActualCost) | |
FROM Production.TransactionHistory AS th | |
JOIN Production.Product AS p ON | |
p.ProductID = th.ProductID | |
GROUP BY | |
th.ProductID; | |
GO | |
-- Outer join to join (null rejection) | |
SELECT * | |
FROM Production.Product AS p | |
LEFT JOIN Production.TransactionHistory AS th ON | |
th.ProductID = p.ProductID | |
WHERE th.ProductID < 10; | |
GO | |
-- Complex example combining multiple simplifications | |
WITH Complex AS | |
( | |
SELECT | |
pc.ProductCategoryID, pc.Name AS CatName, | |
ps.ProductSubcategoryID, ps.Name AS SubCatName, | |
p.ProductID, p.Name AS ProductName, | |
p.Color, p.ListPrice, p.ReorderPoint, | |
pm.Name AS ModelName, pm.ModifiedDate | |
FROM Production.ProductCategory AS pc | |
FULL JOIN Production.ProductSubcategory AS ps ON | |
ps.ProductCategoryID = pc.ProductCategoryID | |
FULL JOIN Production.Product AS p ON | |
p.ProductSubcategoryID = ps.ProductSubcategoryID | |
FULL JOIN Production.ProductModel AS pm ON | |
pm.ProductModelID = p.ProductModelID | |
) | |
--SELECT * FROM Complex; | |
SELECT c.ProductID, c.ProductName | |
FROM Complex AS c | |
WHERE c.ProductName LIKE N'G%'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
-- Trivial plan | |
SELECT p.ProductID | |
FROM Production.Product AS p | |
WHERE p.Name = N'Blade'; | |
GO | |
-- Still trivial | |
SELECT | |
p.Name, | |
RowNumber = | |
ROW_NUMBER() OVER ( | |
ORDER BY p.Name) | |
FROM Production.Product AS p | |
WHERE p.Name LIKE N'[A-G]%'; | |
GO | |
-- 'Subquery' prevents a trivial plan | |
SELECT (SELECT p.ProductID) | |
FROM Production.Product AS p | |
WHERE p.Name = N'Blade'; | |
GO | |
-- Inequality | |
SELECT p.ProductID | |
FROM Production.Product AS p | |
WHERE p.Name <> N'Blade'; | |
GO | |
-- Cost threshold for parallelism | |
EXECUTE sys.sp_configure | |
@configname = 'show advanced options', | |
@configvalue = 1; | |
RECONFIGURE; | |
EXECUTE sys.sp_configure | |
@configname = 'cost threshold for parallelism', | |
@configvalue = 0; | |
RECONFIGURE; | |
GO | |
-- Full optimization | |
SELECT p.ProductID | |
FROM Production.Product AS p | |
WHERE p.Name = N'Blade'; | |
GO | |
-- Reset to default | |
EXECUTE sys.sp_configure | |
@configname = 'cost threshold for parallelism', | |
@configvalue = 5; | |
RECONFIGURE; | |
GO | |
-- Check | |
SELECT c.[description], c.value_in_use | |
FROM sys.configurations AS c | |
WHERE c.name = N'cost threshold for parallelism'; | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
-- Input tree to CBO | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (RECOMPILE, QUERYTRACEON 8606); | |
GO | |
SELECT decoded = | |
NCHAR(91) + | |
NCHAR(65) + | |
NCHAR(45) + | |
NCHAR(71) + | |
NCHAR(93) + | |
NCHAR(37); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
DECLARE @x varchar(30) = 0x444243432052554C454F46462028274275696C644762546F7027293B0000; | |
EXECUTE (@x); | |
GO | |
-- Without segment top | |
SELECT | |
inv.ProductID, | |
inv.LocationID, | |
inv.Shelf, | |
inv.Bin, | |
inv.Quantity | |
FROM Production.ProductInventory AS inv | |
WHERE inv.Quantity = | |
( | |
SELECT MIN(inv2.Quantity) | |
FROM Production.ProductInventory AS inv2 | |
WHERE inv2.Shelf = inv.Shelf | |
AND inv2.Bin = inv.Bin | |
) | |
OPTION (RECOMPILE); | |
GO | |
DECLARE @x varchar(30) = 0x444243432052554C454F4E2028274275696C644762546F7027293B000000; | |
EXECUTE (@x); | |
GO | |
-- With segment top | |
SELECT | |
inv.ProductID, | |
inv.LocationID, | |
inv.Shelf, | |
inv.Bin, | |
inv.Quantity | |
FROM Production.ProductInventory AS inv | |
WHERE inv.Quantity = | |
( | |
SELECT MIN(inv2.Quantity) | |
FROM Production.ProductInventory AS inv2 | |
WHERE inv2.Shelf = inv.Shelf | |
AND inv2.Bin = inv.Bin | |
) | |
OPTION (RECOMPILE); | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
-- Initial memo contents | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (QUERYTRACEON 8608); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
-- Supported DMV | |
SELECT | |
deqoi.[counter], | |
deqoi.occurrence | |
FROM sys.dm_exec_query_optimizer_info AS deqoi | |
WHERE | |
deqoi.[counter] IN | |
( | |
N'trivial plan', | |
N'search 0', | |
N'search 1', | |
N'search 2' | |
); | |
GO | |
-- Search 0 (TP) | |
SELECT | |
p.ProductID, | |
inv.Shelf, | |
inv.Bin, | |
l.Name | |
FROM Production.Product AS p | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
JOIN Production.Location AS l ON | |
l.LocationID = inv.LocationID | |
WHERE | |
p.Name = N'Blade' | |
OPTION (QUERYTRACEON 8675); | |
GO | |
-- Search 1 (QP) | |
SELECT | |
p.Name, | |
average_qty = AVG(th.Quantity) | |
FROM Production.Product AS p | |
JOIN Production.TransactionHistory AS th ON | |
th.ProductID = p.ProductID | |
GROUP BY | |
p.Name | |
OPTION (QUERYTRACEON 8675); | |
GO | |
-- Search 0, then search 1 twice (parallel) | |
SELECT COUNT_BIG(*) | |
FROM Production.TransactionHistory AS th | |
CROSS JOIN Production.TransactionHistory AS th2 | |
CROSS JOIN Production.TransactionHistory AS th3 | |
OPTION (QUERYTRACEON 8675); | |
GO | |
-- Search 1 twice (parallel) | |
SELECT COUNT_BIG(*) | |
FROM Production.TransactionHistory AS th | |
CROSS JOIN dbo.bigTransactionHistory AS bth | |
OPTION (QUERYTRACEON 8675); | |
GO | |
-- Time out | |
SELECT * | |
FROM HumanResources.vEmployee AS ve | |
JOIN HumanResources.vEmployeeDepartmentHistory AS vedh ON | |
vedh.BusinessEntityID = ve.BusinessEntityID | |
OPTION (QUERYTRACEON 8675); | |
GO | |
-- Search 2 | |
SELECT * FROM HumanResources.vJobCandidate AS vjc | |
JOIN HumanResources.vEmployeeDepartmentHistory AS vedh ON | |
vedh.BusinessEntityID = vjc.BusinessEntityID | |
OPTION (QUERYTRACEON 8675); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
-- -- 0.0295 | |
--DBCC RULEOFF ('GbAggBeforeJoin'); -- 0.0351 | |
--DBCC RULEOFF ('JNtoIdxLookup'); -- 0.0431 | |
--DBCC RULEOFF ('JNtoSM'); -- 0.0500 | |
--DBCC RULEOFF ('SelIdxToRng'); -- 0.0531 | |
--DBCC RULEOFF ('GetIdxToRng'); -- 0.0598 | |
--DBCC RULEOFF ('SelOnJN'); -- 3.1244 | |
--DBCC RULEOFF ('BuildSpool'); -- 3.5980 | |
--DBCC RULEOFF ('NormalizeGbAgg'); -- 3.5980 | |
GO | |
SET STATISTICS XML OFF; | |
GO | |
SELECT * INTO #q1 FROM sys.dm_exec_query_transformation_stats AS deqts; | |
GO | |
SELECT * INTO #q2 FROM sys.dm_exec_query_transformation_stats AS deqts; | |
GO | |
DROP TABLE #q1, #q2; | |
GO | |
SELECT * INTO #q1 FROM sys.dm_exec_query_transformation_stats AS deqts; | |
GO | |
SET SHOWPLAN_XML ON; | |
GO | |
-- TEST QUERY | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name; | |
GO | |
SET SHOWPLAN_XML OFF; | |
GO | |
SELECT * INTO #q2 FROM sys.dm_exec_query_transformation_stats AS deqts; | |
GO | |
SELECT | |
q.name, | |
q.promised, | |
q.built_substitute, | |
q.succeeded, | |
q2.succeeded | |
FROM #q1 AS q | |
JOIN #q2 AS q2 ON | |
q.name = q2.name | |
AND q.succeeded < q2.succeeded; | |
GO | |
DBCC RULEON ('GbAggBeforeJoin'); | |
DBCC RULEON ('JNtoSM'); | |
DBCC RULEON ('JNtoIdxLookup'); | |
DBCC RULEON ('SelOnJN'); | |
DBCC RULEON ('BuildSpool'); | |
DBCC RULEON ('SelIdxToRng'); | |
DBCC RULEON ('GetIdxToRng'); | |
DBCC RULEON ('NormalizeGbAgg'); | |
GO | |
DROP TABLE #q1, #q2; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE AdventureWorks2008R2; | |
GO | |
SET NOCOUNT ON; | |
DBCC FREEPROCCACHE; | |
DBCC TRACEON(3604); | |
GO | |
-- Final memo | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (RECOMPILE, QUERYTRACEON 8615); | |
GO | |
-- Output tree | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (RECOMPILE, QUERYTRACEON 8607); | |
GO | |
-- Final form | |
SELECT | |
p.Name, | |
Total = SUM(inv.Quantity) | |
FROM Production.Product AS p | |
JOIN Production.ProductInventory AS inv ON | |
inv.ProductID = p.ProductID | |
WHERE | |
p.Name LIKE N'[A-G]%' | |
GROUP BY | |
p.Name | |
OPTION (RECOMPILE, QUERYTRACEON 7352); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment