Example 19.
1
SET SHOWPLAN_TEXT ON;
GO
USE AdventureWorks;
SELECT * FROM HumanResources.Employee e JOIN Person.Address a
ON e.BusinessEntityID = a.AddressID
AND e.BusinessEntityID = 10;
GO
SET SHOWPLAN_TEXT OFF;
Example 19.2
USE sample;
SELECT * into new_addresses
FROM AdventureWorks.Person.address;
GO
CREATE INDEX i_stateprov on new_addresses(StateProvinceID);
Example 19.3
-- high selectivity
USE sample;
SELECT * FROM new_addresses a
WHERE a.StateProvinceID = 32;
Example 19.4
-- low selectivity
USE sample;
SELECT * FROM new_addresses a
WHERE a.StateProvinceID = 9;
Example 19.5
USE AdventureWorks;
SELECT * FROM HumanResources.Employee
WHERE HumanResources.Employee.BusinessEntityID = 10;
Example 19.6
USE AdventureWorks;
SELECT * FROM HumanResources.Employee e JOIN
Person.Address a
ON e.BusinessEntityID = a.AddressID
AND e.BusinessEntityID = 10;
Example 19.7
USE AdventureWorks;
SELECT * FROM Person.Address a JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID;
Example 19.8
USE AdventureWorks;
CREATE INDEX i_unitprice
ON Sales.SalesOrderDetail(UnitPrice)
WHERE UnitPrice > 1000;
SELECT SalesOrderDetailID, UnitPrice
FROM Sales.SalesOrderDetail
WHERE UnitPrice > 2000;
Example 19.9
USE master;
SELECT counter, occurrence, value
FROM sys.dm_exec_query_optimizer_info
WHERE value IS NOT NULL
AND counter LIKE 'search 1%';
Example 19.10
SELECT ecp.objtype AS Object_Type ,
(SELECT t. text FROM sys.dm_exec_sql_text(qs.sql_handle) AS t) AS
Adhoc_Batch ,qs. execution_count AS Counts ,
qs. total_worker_time AS Total_Worker_Time ,
(qs.total_physical_reads / qs.execution_count ) AS Avg_Physical_Reads ,
(qs.total_logical_writes / qs.execution_count ) AS Avg_Logical_Writes ,
(qs.total_logical_reads / qs.execution_count ) AS Avg_Logical_Reads ,
qs.total_elapsed_time AS Total_Elapsed_Time,
(qs.total_elapsed_time / qs.execution_count ) AS Avg_Elapsed_Time ,
qs.last_execution_time AS Last_Exec_Time,
qs.creation_time AS Creation_Time
FROM sys.dm_exec_query_stats AS qs
JOIN sys.dm_exec_cached_plans ecp ON qs.plan_handle = ecp.plan_handle
ORDER BY Counts DESC;
Example 19.11
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.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
ORDER BY total_worker_time/execution_count DESC;
Example 19.12
USE sample;
CREATE TABLE State
(State_ID int IDENTITY PRIMARY KEY,
State_name varchar(120) NOT NULL);
INSERT State (State_name)
VALUES ('Idaho'), ('Iowa'), ('Indiana'), ('Texas');
GO
CREATE STATISTICS State_Stats
ON State (State_Name) ;
GO
SELECT object_id,stats_id,range_high_key,range_rows,equal_rows
FROM sys.dm_db_stats_histogram(OBJECT_ID('State'), 2);
Example 19.13
USE sample;
SELECT * FROM new_addresses a WITH ( INDEX(i_stateprov))
WHERE a.StateProvinceID = 9;
Example 19.14
SET SHOWPLAN_TEXT ON;
GO
USE AdventureWorks;
SELECT * FROM Person.Address a
WITH(INDEX(0))
WHERE a.StateProvinceID = 32;
GO
SET SHOWPLAN_TEXT OFF;
Example 19.15
USE AdventureWorks;
SELECT e.BusinessEntityID, e.LoginID, d.DepartmentID
FROM HumanResources.Employee e, HumanResources.Department d,
HumanResources.EmployeeDepartmentHistory h
WHERE d.DepartmentID = h.DepartmentID
AND h.BusinessEntityID = e.BusinessEntityID AND h.EndDate IS NOT NULL
OPTION(FORCE ORDER);
Example 19.16
USE AdventureWorks;
SELECT * FROM Person.Address a JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID OPTION (MERGE JOIN);
Example 19.17
USE AdventureWorks;
SELECT * FROM Person.Address a INNER MERGE JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID;
Example 19.18
USE AdventureWorks;
DECLARE @city_name nvarchar(30)
SET @city_name = 'Newark'
SELECT * FROM Person.Address WHERE City = @city_name
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') );
Example 19.19
sp_create_plan_guide @name = N'Example_19_15',
@stmt = N'SELECT * FROM Person.Address a JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (HASH JOIN)'
