Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query
understand statistics
DMVs
query hints
USE AdventureWorks2012;
GO
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.vStoreWithAddresses AS sa
ON c.CustomerID = sa.BusinessEntityID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO
USE AdventureWorks2012;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO
USE AdventureWorks2012;
GO
--Creates an infinite loop
WITH cte (CustomerID, PersonID, StoreID) AS
(
SELECT CustomerID, PersonID, StoreID
FROM Sales.Customer
WHERE PersonID IS NOT NULL
UNION ALL
SELECT cte.CustomerID, cte.PersonID, cte.StoreID
FROM cte
JOIN Sales.Customer AS e
ON cte.PersonID = e.CustomerID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT CustomerID, PersonID, StoreID
FROM cte
OPTION (MAXRECURSION 2);
GO
USE AdventureWorks2012;
GO
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
UNION
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO
USE AdventureWorks2012;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO
USE AdventureWorks2012 ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO
USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE e.OrganizationLevel = 2;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_OrganizationLevel_OrganizationNode)))';
GO
EXEC sp_create_plan_guide
@name = N'Guide2',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE e.OrganizationLevel = 2;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_BusinessEntityID, IX_Employee_OrganizationLevel_OrganizationNode)))';
GO
USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide
@name = N'Guide3',
@stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.JobTitle
FROM HumanResources.Employee
JOIN Person.Person AS c ON HumanResources.Employee.BusinessEntityID = c.BusinessEntityID
WHERE HumanResources.Employee.OrganizationLevel = 3
ORDER BY c.LastName, c.FirstName;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO
USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide
@name = N'Guide4',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE OrganizationLevel = 3;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode ) )
, TABLE HINT ( c, FORCESEEK) )';
GO
USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide
@name = N'Guide5',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_OrganizationLevel_OrganizationNode))
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE OrganizationLevel = 3;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT(e))';
GO
USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide
@name = N'Guide6',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE OrganizationLevel = 3;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode) , NOLOCK, FORCESEEK ))';
GO
USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide
@name = N'Guide7',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE OrganizationLevel = 2;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO
join hints
-- Using HASH
USE AdventureWorks2012;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product AS p
LEFT OUTER HASH JOIN Production.ProductReview AS pr
ON p.ProductID = pr.ProductID
ORDER BY ProductReviewID DESC;
-- Using LOOP
USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER LOOP JOIN Sales.SalesPerson AS sp
ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO
-- Using MERGE
USE AdventureWorks2012;
GO
SELECT poh.PurchaseOrderID, poh.OrderDate, pod.ProductID, pod.DueDate, poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID;
GO
table hints
FROM t (TABLOCK)
FROM t WITH (TABLOCK, INDEX(myindex))
FROM dbo.MyTable WITH (FORCESEEK)
FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))
FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WITH( INDEX (FIBillOfMaterialsWithComponentID) )
WHERE ComponentID in (533, 324, 753, 855, 924);
USE AdventureWorks2012;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO
USE AdventureWorks2012;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
USE AdventureWorks2012;
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID)))
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
USE AdventureWorks2012;
GO
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
WITH (FORCESCAN)
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
statistics IO
|