--Part 1: Gathering metrics
--performance counters
SELECT  *
FROM    sys.dm_os_performance_counters AS dopc ;














SELECT  *
FROM    sys.dm_os_performance_counters AS dopc
WHERE   counter_name = 'Active Transactions'
        AND instance_name = 'AdventureWorks2008R2' ;













--wait states

SELECT  *
FROM    sys.dm_os_wait_stats AS dows
ORDER BY wait_time_ms DESC ;













SELECT  *
FROM    Sys.dm_exec_requests AS der
WHERE   session_id > 50 ;















SELECT  *
FROM    Sys.dm_os_waiting_tasks AS dowt ;

























--Part 2: Optimizer
SELECT  *
FROM    sys.dm_exec_query_optimizer_info AS deqoi ;



























--pause any automated tasks at this point
DBCC freeproccache ;
GO
SELECT  *
INTO    OpInfoAfter
FROM    sys.dm_exec_query_optimizer_info AS deqoi
GO
DROP TABLE OpInfoAfter
GO
--gather the existing optimizer information
SELECT  *
INTO    OpInfoBefore
FROM    sys.dm_exec_query_optimizer_info AS deqoi ;
GO

--run a query
SELECT  pp.PhoneNumber,
        pnt.Name AS PhoneType
FROM    Person.PersonPhone AS pp
        JOIN Person.PhoneNumberType AS pnt
        ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID
WHERE   pp.BusinessEntityID = 6571 ;


GO 
SELECT  *
INTO    OpInfoAfter
FROM    sys.dm_exec_query_optimizer_info AS deqoi
GO
--display the data that has changed
SELECT  oia.counter,
        (oia.occurrence - oib.occurrence) AS ActualOccurence,
        (oia.occurrence * oia.value - oib.occurrence * oib.value) AS ActualValue
FROM    OpInfoBefore AS oib
        JOIN OpInfoAfter AS oia
        ON oib.counter = oia.counter
WHERE   oia.occurrence <> oib.occurrence ;
GO

DROP TABLE OpInfoBefore ;
DROP TABLE OpInfoAfter ;
GO










--dbcc showstatistics
DBCC SHOW_STATISTICS ("Production.TransactionHistoryArchive",IX_TransactionHistoryArchive_ProductID) ;








DBCC SHOW_STATISTICS ("Production.TransactionHistoryArchive",IX_TransactionHistoryArchive_ProductID)
WITH HISTOGRAM ;






DBCC SHOW_STATISTICS ("Production.TransactionHistoryArchive",IX_TransactionHistoryArchive_ProductID)
WITH STAT_HEADER ;















--Indexes
SELECT  *
FROM    sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'), NULL,
                                       NULL, NULL, 'Limited') AS ddips









SELECT  *
FROM    sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008R2'),
                                       OBJECT_ID('Sales.SalesOrderDetail'), 1,
                                       NULL, 'Sampled') AS ddips










--Constraints

SELECT  p.LastName + ', ' + p.FirstName AS 'PersonName'
FROM    Person.Address AS a
        JOIN Person.BusinessEntityAddress AS bea
        ON a.AddressID = bea.AddressID
        JOIN Person.BusinessEntity AS be
        ON bea.BusinessEntityID = be.BusinessEntityID
        JOIN Person.Person AS p
        ON be.BusinessEntityID = p.BusinessEntityID ; 

--create some dummy tables
SELECT  *
INTO    dbo.MyAddress
FROM    Person.Address ;

SELECT  *
INTO    dbo.MyBusinessEntityAddress
FROM    Person.BusinessEntityAddress ;

SELECT  *
INTO    dbo.MyBusinessEntity
FROM    Person.BusinessEntity ;

SELECT  *
INTO    dbo.MyPerson
FROM    Person.Person ;



---new query
SELECT  p.LastName + ', ' + p.FirstName AS 'PersonName'
FROM    dbo.MyAddress AS a
        JOIN dbo.MyBusinessEntityAddress AS bea
        ON a.AddressID = bea.AddressID
        JOIN dbo.MyBusinessEntity AS be
        ON bea.BusinessEntityID = be.BusinessEntityID
        JOIN dbo.MyPerson AS p
        ON be.BusinessEntityID = p.BusinessEntityID





--add clustered indexes

ALTER TABLE dbo.MyAddress ADD CONSTRAINT PK_MyAddress_AddressID PRIMARY KEY CLUSTERED
(
AddressID ASC
)

CREATE NONCLUSTERED INDEX IX_MyBusinessEntityAddress_AddressID ON dbo.MyBusinessEntityAddress
(
AddressID ASC
)

ALTER TABLE dbo.MyBusinessEntityAddress ADD CONSTRAINT PK_MyBusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID PRIMARY KEY CLUSTERED
(
BusinessEntityID ASC,
AddressID ASC,
AddressTypeID ASC
)

ALTER TABLE dbo.MyBusinessEntity ADD CONSTRAINT PK_MyBusinessEntity_BusinessEntityID PRIMARY KEY CLUSTERED
(
BusinessEntityID ASC
)
USE [AdventureWorks2008R2]
GO

ALTER TABLE dbo.MyPerson ADD CONSTRAINT PK_Person_BusinessEntityID PRIMARY KEY CLUSTERED
(
BusinessEntityID ASC
)







SELECT  p.LastName + ', ' + p.FirstName AS 'PersonName'
FROM    Person.Address AS a
        JOIN Person.BusinessEntityAddress AS bea
        ON a.AddressID = bea.AddressID
        JOIN Person.BusinessEntity AS be
        ON bea.BusinessEntityID = be.BusinessEntityID
        JOIN Person.Person AS p
        ON be.BusinessEntityID = p.BusinessEntityID
WHERE   p.LastName LIKE 'Ran%'

SELECT  p.LastName + ', ' + p.FirstName AS 'PersonName'
FROM    dbo.MyAddress AS a
        JOIN dbo.MyBusinessEntityAddress AS bea
        ON a.AddressID = bea.AddressID
        JOIN dbo.MyBusinessEntity AS be
        ON bea.BusinessEntityID = be.BusinessEntityID
        JOIN dbo.MyPerson AS p
        ON be.BusinessEntityID = p.BusinessEntityID
WHERE   p.LastName LIKE 'Ran%'






--Part 3: Execution Plans

--Simple Select
SELECT  *
FROM    [dbo].[DatabaseLog] ;
GO












--Join
SELECT  e.[JobTitle],
        a.[City],
        p.[LastName] + ',' + p.[FirstName] AS EmployeeName
FROM    [HumanResources].[Employee] e
        JOIN [Person].[BusinessEntityAddress] bea
        ON e.[BusinessEntityId] = bea.[BusinessEntityId]
        JOIN [Person].[Address] a
        ON [bea].[AddressID] = [a].[AddressID]
        JOIN [Person].[Person] p
        ON e.[BusinessEntityId] = p.[BusinessEntityId] ;












--Update
BEGIN TRAN ;
UPDATE  [Person].[Address]
SET     [City] = 'Munro',
        [ModifiedDate] = GETDATE()
WHERE   [City] = 'Monroe' ;
ROLLBACK TRAN ;


















--Delete
BEGIN TRAN ;
DELETE  FROM [Person].[PersonPhone]
WHERE   [BusinessEntityId] = 5695 ;
ROLLBACK TRAN ;






















--Insert
BEGIN TRAN ;
INSERT  INTO [AdventureWorks].[Person].[Address]
        ([AddressLine1],
         [AddressLine2],
         [City],
         [StateProvinceID],
         [PostalCode],
         [rowguid],
         [ModifiedDate]
        )
VALUES  ('1313 Mockingbird Lane',
         'Basement',
         'Springfield',
         24,
         '02134',
         NEWID(),
         GETDATE()
        ) ;
ROLLBACK TRAN ;

























--Sub-select
SELECT  [p].[Name],
        [p].[ProductNumber],
        [ph].[ListPrice]
FROM    [Production].[Product] p
        INNER JOIN [Production].[ProductListPriceHistory] ph
        ON [p].[ProductID] = ph.[ProductID]
           AND ph.[StartDate] = (SELECT TOP (1)
                                        [ph2].[StartDate]
                                 FROM   [Production].[ProductListPriceHistory] ph2
                                 WHERE  [ph2].[ProductID] = [p].[ProductID]
                                 ORDER BY [ph2].[StartDate] DESC
                                )
WHERE   p.ProductID = 839 ;


















--Views
SELECT  *
FROM    [Sales].[vIndividualCustomer]
WHERE   [BusinessEntityID] = 3456 ;





















--Fixing a query
DROP INDEX [Sales].[SalesOrderDetail].[IX_SalesOrderDetail_ProductID]



SELECT  [sod].[ProductID],
        [sod].[OrderQty],
        [sod].[UnitPrice]
FROM    [Sales].[SalesOrderDetail] sod
WHERE   [sod].[ProductID] = 897













CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ([ProductID] ASC)
ON  [PRIMARY] ;

SELECT  [sod].[ProductID],
        [sod].[OrderQty],
        [sod].[UnitPrice]
FROM    [Sales].[SalesOrderDetail] sod
WHERE   [sod].[ProductID] = 897













--Fixing the bookmark lookup
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ([ProductID] ASC)
INCLUDE ([OrderQty], [UnitPrice])
WITH DROP_EXISTING
ON  [PRIMARY] ;


SELECT  [sod].[ProductID],
        [sod].[OrderQty],
        [sod].[UnitPrice]
FROM    [Sales].[SalesOrderDetail] sod
WHERE   [sod].[ProductID] = 897 ;

















--XML
SET STATISTICS XML ON ;
GO
SELECT  p.[LastName],
        a.[City],
        c.[AccountNumber],
        st.[Name] AS TerritoryName
FROM    [Person].[Person] p
        JOIN [Sales].[Customer] c
        ON p.[BusinessEntityId] = c.[PersonId]
        JOIN Person.BusinessEntityAddress AS bea
        ON p.BusinessEntityID = bea.BusinessEntityID
        JOIN [Person].[Address] a
        ON bea.[AddressId] = a.[AddressID]
        JOIN [Sales].[SalesTerritory] st
        ON [c].[TerritoryID] = [st].[TerritoryID]
WHERE   st.[Name] = 'Northeast'
        AND a.[StateProvinceID] = 55 ;
GO
SET STATISTICS XML OFF ;
GO










--Part 4: DMVs
-- What's running now?

-- all requests
SELECT  *
FROM    sys.dm_exec_requests AS der
















-- eliminate most system calls
SELECT  *
FROM    sys.dm_exec_requests AS der
WHERE   der.session_id > 50


















-- eliminate the local call and most system calls
SELECT  *
FROM    sys.dm_exec_requests der
WHERE   der.session_id > 50
        AND der.session_id <> @@SPID









--eliminate all system calls and the local call
SELECT  *
FROM    sys.dm_exec_requests AS der
        JOIN sys.dm_exec_sessions AS des
        ON der.session_id = des.session_id
WHERE   der.session_id <> @@SPID
        AND des.is_user_process = 1








--active transactions
SELECT  *
FROM    sys.dm_tran_active_transactions AS dtat


















--waiting tasks
SELECT  *
FROM    sys.dm_os_waiting_tasks 













--What ran recently

-- raw data
SELECT  *
FROM    sys.dm_exec_query_stats AS deqs












--refining the request
SELECT  deqs.execution_count,
        deqs.total_worker_time,
        deqs.total_elapsed_time,
        deqs.total_logical_reads,
        deqs.total_logical_writes,
        deqs.query_plan_hash
FROM    sys.dm_exec_query_stats AS deqs










-- Get the execution Plan

--Just the cached plans
SELECT  *
FROM    sys.dm_exec_cached_plans AS decp












-- to see the execution plan
SELECT  *
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp













-- only for the right database
SELECT  *
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
WHERE   deqp.dbid = 7












-- combining with running processes
SELECT  der.session_id,
        der.start_time,
        der.wait_type,
        der.wait_time,
        der.last_wait_type,
        der.wait_resource,
        der.open_transaction_count,
        der.cpu_time,
        der.total_elapsed_time,
        der.logical_reads,
        der.writes,
        der.granted_query_memory,
        deqp.query_plan
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp
WHERE   der.session_id <> @@SPID







-- combining with query stats
SELECT  deqs.execution_count,
        deqs.total_worker_time,
        deqs.total_elapsed_time,
        deqs.total_logical_reads,
        deqs.total_logical_writes,
        deqs.query_plan_hash,
        deqp.query_plan
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE   deqp.dbid = 7








--text plan with requests
SELECT  der.session_id,
        der.start_time,
        der.wait_type,
        der.wait_time,
        der.last_wait_type,
        der.wait_resource,
        der.open_transaction_count,
        der.cpu_time,
        der.total_elapsed_time,
        der.logical_reads,
        der.writes,
        der.granted_query_memory,
        deqp.query_plan
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_text_query_plan(der.plan_handle,
                                                der.statement_start_offset,
                                                der.statement_end_offset) AS deqp
        JOIN sys.dm_exec_sessions AS des
        ON der.session_id = des.session_id
WHERE   der.session_id <> @@SPID
        AND des.is_user_process = 1






--text plan with stats & some filtering
SELECT TOP 2
        deqs.execution_count,
        deqs.total_worker_time,
        deqs.total_elapsed_time,
        deqs.total_logical_reads,
        deqs.total_logical_writes,
        deqs.query_plan_hash,
        deqp.query_plan
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, DEFAULT,
                                                DEFAULT) AS deqp
WHERE   deqp.dbid = 7
ORDER BY deqs.total_elapsed_time DESC







--Get the query

--The whole query
SELECT TOP 5
        dest.text,
        deqs.execution_count,
        deqs.total_elapsed_time,
        deqs.max_elapsed_time
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.max_elapsed_time DESC





--get the statement
--NOTE statement offset is divided by 2 because it measures unicode characters, not characters
SELECT  SUBSTRING(dest.text, (der.statement_start_offset / 2) + 1,
                  (der.statement_end_offset - der.statement_start_offset) / 2
                  + 1),
        LEN(dest.text) AS CharLength,
        DATALENGTH(dest.text) AS DLength,
        DATALENGTH(dest.text) / 2 AS HalfDLength,
        der.statement_start_offset,
        der.statement_end_offset
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE   der.statement_end_offset > -1


SELECT  SUBSTRING(dest.text, (der.statement_start_offset / 2) + 1,
                  (der.statement_end_offset - der.statement_start_offset) / 2
                  + 1),
        LEN(dest.text) AS CharLength,
        DATALENGTH(dest.text) AS DLength,
        DATALENGTH(dest.text) / 2 AS HalfDLength,
        der.statement_start_offset,
        der.statement_end_offset
FROM    sys.dm_exec_query_stats AS der
        CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE   der.statement_end_offset > -1









--Optimizer behavior

SELECT  *
FROM    sys.dm_exec_query_optimizer_info deqoi









SELECT  *
FROM    Person.Address a
WHERE   a.AddressID = 348


SELECT  *
FROM    sys.dm_exec_query_optimizer_info deqoi







BEGIN TRAN
UPDATE  Person.Address
SET     City = 'Las Vegas'
WHERE   AddressID = 348
ROLLBACK TRAN




SELECT  *
FROM    sys.dm_exec_query_optimizer_info deqoi




SELECT  soh.AccountNumber,
        soh.PurchaseOrderNumber,
        sod.LineTotal,
        p.Name
FROM    Sales.SalesOrderHeader soh
        JOIN Sales.SalesOrderDetail sod
        ON soh.SalesOrderID = sod.SalesOrderID
--JOIN Production.Product p
        INNER LOOP JOIN Production.Product p
        ON sod.ProductID = p.ProductID
WHERE   soh.PurchaseOrderNumber LIKE 'PO124%'


SELECT  *
FROM    sys.dm_exec_query_optimizer_info deqoi



--Index use

-- on a particular DB

SELECT  s.name + '.' + t.name AS TableName,
        i.name AS IndexName,
        i.index_id,
        ddius.user_seeks,
        ddius.user_scans,
        ddius.user_updates,
        ddius.user_lookups
FROM    sys.dm_db_index_usage_stats AS ddius
        JOIN sys.tables AS t
        ON ddius.object_id = t.object_id
        JOIN sys.schemas AS s
        ON t.schema_id = s.schema_id
        JOIN sys.indexes AS i
        ON ddius.index_id = i.index_id
           AND ddius.object_id = i.object_id
ORDER BY TableName,
        ddius.index_id







--current activity, very low level
SELECT  i.name,
        ddios.range_scan_count --range and table scans
        ,
        ddios.singleton_lookup_count --single row lookups
        ,
        ddios.row_lock_count,
        ddios.row_lock_wait_count,
        ddios.row_lock_wait_in_ms,
        ddios.page_lock_count,
        ddios.page_lock_wait_count,
        ddios.page_lock_wait_in_ms
FROM    sys.dm_db_index_operational_stats(14, --Database Id
                                          DEFAULT, --Object Id
                                          DEFAULT, --Index Id
                                          DEFAULT--partition number
										) AS ddios
        JOIN sys.indexes AS i
        ON ddios.object_id = i.object_id
           AND ddios.index_id = i.index_id
WHERE   ddios.range_scan_count > 0
        AND ddios.singleton_lookup_count > 0






-- Fragmentation within the database
SELECT  i.name,
        ddips.avg_fragmentation_in_percent,
        ddips.fragment_count,
        ddips.page_count,
        ddips.avg_page_space_used_in_percent,
        ddips.record_count,
        ddips.avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(7, --Database ID
                                       DEFAULT, --Ojbect Id
                                       DEFAULT, --Index ID
                                       DEFAULT, --Partition
                                       'Limited'--Mode DEFAULT NULL LIMITED(fastest) SAMPLED DETAILED(slowest)
									) AS ddips
        JOIN sys.indexes AS i
        ON ddips.object_id = i.object_id
           AND ddips.index_id = i.index_id
ORDER BY ddips.page_count DESC






--Missing Indexes

--generate a missing index
--straight out books online
SELECT  City,
        StateProvinceID,
        PostalCode
FROM    Person.Address
WHERE   StateProvinceID = 9 ;


SELECT  *
FROM    sys.dm_db_missing_index_details AS ddmid







--straight from BOL
SELECT  mig.*,
        mid.statement AS table_name,
        column_id,
        column_name,
        column_usage
FROM    sys.dm_db_missing_index_details AS mid
        CROSS APPLY sys.dm_db_missing_index_columns(mid.index_handle)
        INNER JOIN sys.dm_db_missing_index_groups AS mig
        ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle,
        mig.index_handle,
        column_id ;






--Not from BOL
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan'
    AS sp)
SELECT  p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Database)[1]',
                           'NVARCHAR(256)') AS DatabaseName
       ,dest.text AS QueryText
       ,s.total_elapsed_time
       ,s.last_execution_time
       ,s.execution_count
       ,s.total_logical_writes
       ,s.total_logical_reads
       ,s.min_elapsed_time
       ,s.max_elapsed_time
       ,p.query_plan
       ,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]',
                           'NVARCHAR(256)') AS TableName
       ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]',
                           'NVARCHAR(256)') AS SchemaName
       ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]',
                           'DECIMAL(6,4)') AS ProjectedImpact
       ,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage
       ,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
        CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup')
        AS t1 (ColumnGroup)
        CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS dest
WHERE   p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1











--Part 5: Common Problems
-- 1. Query is just running too slow
SELECT  p.[Name],
        p.ProductNumber,
        plph.ListPrice
FROM    Production.Product AS p
        JOIN Production.ProductListPriceHistory AS plph
        ON p.ProductID = plph.ProductID
           AND plph.StartDate = (SELECT TOP (1)
                                        plph2.StartDate
                                 FROM   Production.ProductListPriceHistory plph2
                                 WHERE  plph.ProductID = p.ProductID
                                 ORDER BY plph2.StartDate DESC
                                )
WHERE   p.ProductID = 839 ;







-- Better query
SELECT  p.[Name],
        p.ProductNumber,
        plph.ListPrice
FROM    Production.Product AS p
        CROSS APPLY (SELECT TOP (1)
                            plph2.ProductId,
                            plph2.ListPrice
                     FROM   Production.ProductListPriceHistory AS plph2
                     WHERE  plph2.ProductID = p.ProductID
                     ORDER BY plph2.StartDate DESC
                    ) AS plph
WHERE   p.ProductID = 839 ;








-- 2. Key Lookup
SELECT  sod.ProductID,
        sod.OrderQty,
        sod.UnitPrice
FROM    Sales.SalesOrderDetail AS sod
WHERE   sod.ProductID = 897 ;









IF EXISTS ( SELECT  *
            FROM    sys.indexes
            WHERE   object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]')
                    AND name = N'ix_SalesOrderDetail_ProductID' ) 
    DROP INDEX [ix_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] WITH ( ONLINE = OFF )
GO

CREATE  NONCLUSTERED INDEX [ix_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ([ProductId] ASC)
--INCLUDE (OrderQty,UnitPrice)
ON  [PRIMARY]
GO







-- 3. Parameter Sniffing
--a procedure that could lead to sniffing
IF (SELECT  OBJECT_ID('spAddressByCity')
   ) IS NOT NULL 
    DROP PROCEDURE dbo.spAddressByCity ;
GO
CREATE PROC dbo.spAddressByCity @City NVARCHAR(30)
AS 
    SELECT  a.AddressID,
            a.AddressLine1,
            a.AddressLine2,
            a.City,
            sp.[Name] AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
            JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
    WHERE   a.City = @City ;





--Two different sets of data
    EXEC dbo.spAddressByCity @City = N'London' ;
	

    EXEC dbo.spAddressByCity @City = N'Mentor' ;
	





-- to get the plan_handle	
    SELECT  decp.plan_handle
    FROM    sys.dm_exec_cached_plans AS decp
            CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
    WHERE   dest.[text] LIKE 'CREATE PROC dbo.spAddressByCity%' ;


--to just remove the one plan from cache
    DBCC freeproccache(0x05000700424E6416B8A0622B000000000000000000000000) ;





--running in the reverse order leads to a different plan for both
    EXEC dbo.spAddressByCity @City = N'Mentor' ;



    EXEC dbo.spAddressByCity @City = N'London' ;
	

	
	
	

--one way  to get a more generic plan	
    ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
    AS 
        DECLARE @LocalCity NVARCHAR(30) ;

        SET @LocalCity = @City ;

        SELECT  a.AddressID,
                a.AddressLine1,
                a.AddressLine2,
                a.City,
                sp.[Name] AS StateProvinceName,
                a.PostalCode
        FROM    Person.Address AS a
                JOIN Person.StateProvince AS sp
                ON a.StateProvinceID = sp.StateProvinceID
        WHERE   a.City = @LocalCity ;







        EXEC dbo.spAddressByCity @City = N'London' ;

        EXEC dbo.spAddressByCity @City = N'Mentor' ;







--one way to get a specific plan
        ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
        AS 
            SELECT  a.AddressID,
                    a.AddressLine1,
                    a.AddressLine2,
                    a.City,
                    sp.[Name] AS StateProvinceName,
                    a.PostalCode
            FROM    Person.Address AS a
                    JOIN Person.StateProvince AS sp
                    ON a.StateProvinceID = sp.StateProvinceID
            WHERE   a.City = @City
            OPTION  (OPTIMIZE FOR (@city = 'London')) ;



            EXEC dbo.spAddressByCity @City = N'Mentor' ;







--another way to get a generic plan
            ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
            AS 
                SELECT  a.AddressID,
                        a.AddressLine1,
                        a.AddressLine2,
                        a.City,
                        sp.[Name] AS StateProvinceName,
                        a.PostalCode
                FROM    Person.Address AS a
                        JOIN Person.StateProvince AS sp
                        ON a.StateProvinceID = sp.StateProvinceID
                WHERE   a.City = @City
                OPTION  (OPTIMIZE FOR (@city UNKNOWN)) ;



                EXEC dbo.spAddressByCity @City = N'London' ;







--4. effective index use
                SELECT  *
                FROM    Sales.SalesOrderDetail AS sod
                WHERE   sod.SalesOrderID IN (51825, 51826, 51827, 51828) ;





                SELECT  *
                FROM    Sales.SalesOrderDetail AS sod
                WHERE   sod.SalesOrderID BETWEEN 51825 AND 51828 ;










-- force the index where the query won't choose the right one
                SELECT  *
                FROM    Purchasing.PurchaseOrderHeader AS poh
                WHERE   poh.PurchaseOrderID * 2 = 3400 ;

                SELECT  *
                FROM    Purchasing.PurchaseOrderHeader AS poh
                WHERE   poh.PurchaseOrderID = 3400 / 2 ;

                SELECT  *
                FROM    Purchasing.PurchaseOrderHeader AS poh WITH (INDEX (PK_PurchaseOrderHeader_PurchaseOrderID))
                WHERE   poh.PurchaseOrderID * 2 = 3400 ;










-- 5. UDF's


                DROP FUNCTION dbo.ProductList
GO

CREATE FUNCTION dbo.ProductList (@ProductCategory INT)
RETURNS @ProductList TABLE (
     ProductId INT,
     [Name] NVARCHAR(50),
     Color NVARCHAR(15),
     CategoryName NVARCHAR(50),
     SubCategoryName NVARCHAR(50)
    )
AS 
    BEGIN
        IF EXISTS ( SELECT  *
                    FROM    Production.ProductSubcategory AS ps
                    WHERE   ps.ProductCategoryID = @productCategory ) 
            BEGIN
                INSERT  @ProductList
                        SELECT  p.ProductId,
                                p.NAME,
                                p.Color,
                                pc.[Name],
                                ps.[Name]
                        FROM    Production.Product AS p
                                JOIN Production.ProductSubcategory AS ps
                                ON p.ProductSubcategoryID = ps.ProductSubcategoryID
                                JOIN Production.ProductCategory AS pc
                                ON ps.ProductCategoryID = pc.ProductCategoryID
                        WHERE   pc.ProductCategoryID = @ProductCategory
                RETURN ;
            END
        RETURN 
    END




-- remember to run these seperately
SELECT  *
FROM    Sales.SalesOrderDetail AS sod
        JOIN dbo.ProductList (3) AS pl
        ON sod.ProductID = pl.ProductId
WHERE   sod.SalesOrderID = 43676






SELECT  sod.*,
        p.productId,
        p.[Name],
        p.Color,
        pc.[Name] AS CategoryName,
        ps.[Name] AS SubCategoryName
FROM    Sales.SalesOrderDetail AS SOD
        JOIN Production.Product AS p
        ON SOD.ProductID = p.ProductID
        JOIN Production.ProductSubcategory AS ps
        ON p.ProductSubcategoryID = ps.ProductSubcategoryID
        JOIN Production.ProductCategory AS pc
        ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE   pc.ProductCategoryID = 3
        AND SOD.SalesOrderID = 43676









--6. Triggers
--pre-run
CREATE TRIGGER Person.uPerson ON Person.Person
    AFTER UPDATE
AS
    BEGIN
        SET NOCOUNT ON ;
	
        UPDATE  Person.Person
        SET     ModifiedDate = GETDATE()
        FROM    inserted i
        WHERE   i.BusinessEntityID = Person.BusinessEntityID ;
    END ;







    BEGIN TRAN
    UPDATE  Person.Person
    SET     FirstName = 'Herman'
    WHERE   FirstName = 'Howard' ;
    ROLLBACK TRAN













--7. REBAR as example of individual statements

    DECLARE @ProductId INT ;
    DECLARE @SuccessList TABLE (
         ProductId INT,
         TransactionId INT
        ) ;

    DECLARE SillyCursor CURSOR
    FOR
        SELECT  p.ProductId
        FROM    Production.Product AS p
        WHERE   p.ProductID % 6 = 0 ;

    OPEN SillyCursor ;

    FETCH NEXT FROM SillyCursor INTO @ProductId ;

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            INSERT  INTO @SuccessList
                    (ProductId,
                     TransactionId
                    )
                    SELECT  @productId,
                            th.TransactionID
                    FROM    Production.TransactionHistory AS th
                    WHERE   th.ProductID = @ProductId 
	
            IF @ProductId % 48 = 0 
                BEGIN
                --SET STATISTICS XML ON ;
                    SELECT  sod.SalesOrderDetailID,
                            sod.ProductId
                    FROM    Sales.SalesOrderDetail AS sod
                    WHERE   sod.ProductID = @ProductId
                --SET STATISTICS XML OFF ;

                END
	
            FETCH NEXT FROM SillyCursor INTO @ProductId ;

        END


    CLOSE SillyCursor ;
    DEALLOCATE SillyCursor ;












--8. Trace Events
--generate a little workload, after setting up Profiler
    EXEC dbo.spr_ShoppingCart '20621' ;
GO
EXEC dbo.spr_ProductBySalesOrder 43867 ;
GO
EXEC dbo.spr_PersonByFirstName 'Gretchen' ;
GO
EXEC dbo.spr_ProductTransactionsSinceDate @LatestDate = '9/1/2004',
    @ProductName = 'Hex Nut%' ;
GO
EXEC dbo.spr_PurchaseOrderBySalesPersonName @LastName = 'Hill%' ;
GO









--eliminate the lookup
CREATE NONCLUSTERED INDEX [ix_PurchaseOrderHeader_EmployeeId] ON
Purchasing.PurchaseOrderHeader 
(employeeid ASC) 
INCLUDE (orderdate)
WITH (
DROP_EXISTING = ON)
ON  [PRIMARY] ;




--avoid the cluster index scan
CREATE INDEX IX_Test ON 
Purchasing.PurchaseOrderDetail 
(PurchaseOrderId, ProductId, LineTotal) ;













-- 9. DMV's

--Pre-run
DECLARE @n INT
SELECT  @n = COUNT(*)
FROM    sales.SalesOrderDetail AS sod
WHERE   sod.OrderQty = 1
IF @n > 0 
    PRINT 'Record Exists'
    
    
    


--sys.dm_exec_cached_plans

SELECT  *
FROM    sys.dm_exec_cached_plans AS decp



--add in sys.dm_exec_query_plan
SELECT  *
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp


--large plans
SELECT  *
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_text_query_plan(decp.plan_handle, 0, -1) AS detqp



--Let's find the slow query
SELECT  deqp.query_plan,
        dest.[text]
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
        CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
WHERE   dest.[text] LIKE 'declare @n INT%'



--Let's see some performance metrics
SELECT  *
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   dest.[text] LIKE 'declare @n INT%'




--plan attributes
SELECT  depa.*
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
        CROSS APPLY sys.dm_exec_plan_attributes(decp.plan_handle) AS depa




--fix for the query
IF EXISTS ( SELECT  sod.*
            FROM    Sales.SalesOrderDetail AS sod
            WHERE   sod.OrderQty = 1 ) 
    PRINT 'Record Exists'














--10. Hash


DBCC FREEPROCCACHE


SELECT  *
FROM    Production.Product AS p
        JOIN Production.ProductSubcategory AS ps
        ON p.ProductSubcategoryID = ps.ProductSubcategoryID
        JOIN Production.ProductCategory AS pc
        ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE   pc.[Name] = 'Bikes'
        AND ps.[Name] = 'Touring Bikes'
        

SELECT  *
FROM    Production.Product AS p
        JOIN Production.ProductSubcategory AS ps
        ON p.ProductSubcategoryID = ps.ProductSubcategoryID
        JOIN Production.ProductCategory AS pc
        ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE   pc.[Name] = 'Bikes'
        AND ps.[Name] = 'Road Bikes'
        


    
    
SELECT  deqs.execution_count,
        deqs.query_hash,
        deqs.query_plan_hash,
        dest.[text]
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS dest
   
   
 
 
 
 
 
SELECT  p.ProductID
FROM    Production.Product AS p
        JOIN Production.ProductSubcategory AS ps
        ON p.ProductSubcategoryID = ps.ProductSubcategoryID
        JOIN Production.ProductCategory AS pc
        ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE   pc.[Name] = 'Bikes'
        AND ps.[Name] = 'Road Bikes'








SELECT  p.[Name],
        tha.TransactionDate,
        tha.TransactionType,
        tha.Quantity,
        tha.ActualCost
FROM    Production.TransactionHistoryArchive tha
        JOIN Production.Product p
        ON tha.ProductID = p.ProductID
WHERE   P.ProductID = 461 ;



SELECT  p.[Name],
        tha.TransactionDate,
        tha.TransactionType,
        tha.Quantity,
        tha.ActualCost
FROM    Production.TransactionHistoryArchive tha
        JOIN Production.Product p
        ON tha.ProductID = p.ProductID
WHERE   P.ProductID = 712 ;


SELECT  deqs.execution_count,
        deqs.query_hash,
        deqs.query_plan_hash,
        dest.[text]
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS dest











-- 11.XML


SELECT  MAX(dest.Text) AS QueryText,
        MAX(p.query_plan.value('(//@StatementSubTreeCost)[1]', 'float')) AS QueryCost
FROM    sys.dm_exec_query_stats AS s
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS dest
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
GROUP BY s.query_plan_hash
ORDER BY QueryText







WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan'
    AS sp)
SELECT  p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Database)[1]',
                           'NVARCHAR(256)') AS DatabaseName
       ,s.sql_handle
       ,s.total_elapsed_time
       ,s.last_execution_time
       ,s.execution_count
       ,s.total_logical_writes
       ,s.total_logical_reads
       ,s.min_elapsed_time
       ,s.max_elapsed_time
       ,p.query_plan
       ,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]',
                           'NVARCHAR(256)') AS TableName
       ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]',
                           'NVARCHAR(256)') AS SchemaName
       ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]',
                           'DECIMAL(6,4)') AS ProjectedImpact
       ,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage
       ,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
        CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup')
        AS t1 (ColumnGroup)
        CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
WHERE   p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1
ORDER BY s.total_elapsed_time DESC









--Part 6: Advanced Techniques
--UNION
SELECT  pm.Name,
        pm.ModifiedDate
FROM    Production.ProductModel AS pm
UNION
SELECT  pm.Name,
        pm.ModifiedDate
FROM    Production.ProductModel AS pm






SELECT  pm.Name,
        pm.ModifiedDate
FROM    Production.ProductModel AS pm
UNION
SELECT  pm.Name,
        pm.ModifiedDate
FROM    Production.ProductModel AS pm
OPTION  (MERGE UNION)






SELECT  pm.Name,
        pm.ModifiedDate
FROM    Production.ProductModel AS pm
UNION
SELECT  pm.Name,
        pm.ModifiedDate
FROM    Production.ProductModel AS pm
OPTION  (HASH UNION)










--Joins
SELECT  s.Name AS StoreName,
        p.LastName + ', ' + p.FirstName
FROM    Sales.Store AS s
        JOIN Sales.SalesPerson AS sp
        ON s.SalesPersonID = sp.BusinessEntityID
        JOIN Person.Person AS p
        ON sp.BusinessEntityID = p.BusinessEntityID






SELECT  s.Name AS StoreName,
        p.LastName + ', ' + p.FirstName
FROM    Sales.Store AS s
        JOIN Sales.SalesPerson AS sp
        ON s.SalesPersonID = sp.BusinessEntityID
        JOIN Person.Person AS p
        ON sp.BusinessEntityID = p.BusinessEntityID
OPTION  (LOOP JOIN)







SELECT  s.Name AS StoreName,
        p.LastName + ', ' + p.FirstName
FROM    Sales.Store AS s
        JOIN Sales.SalesPerson AS sp
        ON s.SalesPersonID = sp.BusinessEntityID
        JOIN Person.Person AS p
        ON sp.BusinessEntityID = p.BusinessEntityID
OPTION  (HASH JOIN)













SELECT  s.Name AS StoreName,
        p.LastName + ', ' + p.FirstName
FROM    Sales.Store AS s
        JOIN Sales.SalesPerson AS sp
        ON s.SalesPersonID = sp.BusinessEntityID
        JOIN Person.Person AS p
        ON sp.BusinessEntityID = p.BusinessEntityID
OPTION  (MERGE JOIN)









--Force Order
SELECT  pc.Name AS ProductCategoryName,
        ps.Name AS ProductSubCategoryName,
        p.Name AS ProductName,
        pdr.Description,
        pm.Name AS ProductModelName,
        c.Name AS CultureName,
        d.FileName,
        pri.Quantity,
        pr.Rating,
        pr.Comments
FROM    Production.Product AS p
        LEFT JOIN Production.ProductModel AS pm
        ON p.ProductModelID = pm.ProductModelID
        LEFT JOIN Production.ProductSubcategory AS ps
        ON p.ProductSubcategoryID = ps.ProductSubcategoryID
        LEFT JOIN Production.ProductInventory AS pri
        ON p.ProductID = pri.ProductID
        LEFT JOIN Production.ProductReview AS pr
        ON p.ProductID = pr.ProductID
        LEFT JOIN Production.ProductDocument AS pd
        ON p.ProductID = pd.ProductID
        LEFT JOIN Production.Document AS d
        ON pd.DocumentNode = d.DocumentNode
        LEFT JOIN Production.ProductCategory AS pc
        ON ps.ProductCategoryID = pc.ProductCategoryID
        LEFT JOIN Production.ProductModelProductDescriptionCulture AS pmpdc
        ON pm.ProductModelID = pmpdc.ProductModelID
        LEFT JOIN Production.ProductDescription AS pdr
        ON pmpdc.ProductDescriptionID = pdr.ProductDescriptionID
        LEFT JOIN Production.Culture AS c
        ON c.CultureID = pmpdc.CultureID







SELECT  pc.Name AS ProductCategoryName,
        ps.Name AS ProductSubCategoryName,
        p.Name AS ProductName,
        pdr.Description,
        pm.Name AS ProductModelName,
        c.Name AS CultureName,
        d.FileName,
        pri.Quantity,
        pr.Rating,
        pr.Comments
FROM    Production.Product AS p
        LEFT JOIN Production.ProductModel AS pm
        ON p.ProductModelID = pm.ProductModelID
        LEFT JOIN Production.ProductSubcategory AS ps
        ON p.ProductSubcategoryID = ps.ProductSubcategoryID
        LEFT JOIN Production.ProductInventory AS pri
        ON p.ProductID = pri.ProductID
        LEFT JOIN Production.ProductReview AS pr
        ON p.ProductID = pr.ProductID
        LEFT JOIN Production.ProductDocument AS pd
        ON p.ProductID = pd.ProductID
        LEFT JOIN Production.Document AS d
        ON pd.DocumentNode = d.DocumentNode
        LEFT JOIN Production.ProductCategory AS pc
        ON ps.ProductCategoryID = pc.ProductCategoryID
        LEFT JOIN Production.ProductModelProductDescriptionCulture AS pmpdc
        ON pm.ProductModelID = pmpdc.ProductModelID
        LEFT JOIN Production.ProductDescription AS pdr
        ON pmpdc.ProductDescriptionID = pdr.ProductDescriptionID
        LEFT JOIN Production.Culture AS c
        ON c.CultureID = pmpdc.CultureID
        
OPTION (FORCE ORDER)








--MAXDOP
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'cost threshold for parallelism', 1;
go
RECONFIGURE WITH override;
go

SELECT  wo.DueDate,
		p.class,
        MIN(wo.OrderQty) AS MinOrderQty,
        MIN(wo.StockedQty) AS MinStockedQty,
        MIN(wo.ScrappedQty) AS MinScrappedQty,
        MAX(wo.OrderQty) AS MaxOrderQty,
        MAX(wo.StockedQty) AS MaxStockedQty,
        MAX(wo.ScrappedQty) AS MaxScrappedQty
FROM    Production.WorkOrder AS wo
JOIN Production.Product AS p
ON wo.ProductID = p.ProductID
GROUP BY wo.DueDate,p.Class
ORDER BY wo.DueDate ;



SELECT  wo.DueDate,
		p.class,
        MIN(wo.OrderQty) AS MinOrderQty,
        MIN(wo.StockedQty) AS MinStockedQty,
        MIN(wo.ScrappedQty) AS MinScrappedQty,
        MAX(wo.OrderQty) AS MaxOrderQty,
        MAX(wo.StockedQty) AS MaxStockedQty,
        MAX(wo.ScrappedQty) AS MaxScrappedQty
FROM    Production.WorkOrder AS wo
JOIN Production.Product AS p
ON wo.ProductID = p.ProductID
GROUP BY wo.DueDate,p.Class
ORDER BY wo.DueDate  
OPTION (MAXDOP 1);


sp_configure 'cost threshold for parallelism', 25;
go
RECONFIGURE WITH override;
go



--OPTIMIZE FOR
--already did one
IF (SELECT  OBJECT_ID('spAddressByCity')
   ) IS NOT NULL 
    DROP PROCEDURE dbo.spAddressByCity ;
GO
CREATE PROC dbo.spAddressByCity @City NVARCHAR(30)
AS 
    SELECT  a.AddressID,
            a.AddressLine1,
            a.AddressLine2,
            a.City,
            sp.[Name] AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
            JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
    WHERE   a.City = @City ;





--Two different sets of data
    EXEC dbo.spAddressByCity @City = N'London' ;
	

    EXEC dbo.spAddressByCity @City = N'Mentor' ;
	





-- to get the plan_handle	
    SELECT  decp.plan_handle
    FROM    sys.dm_exec_cached_plans AS decp
            CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
    WHERE   dest.[text] LIKE 'CREATE PROC dbo.spAddressByCity%' ;


--to just remove the one plan from cache
    DBCC freeproccache(0x050008009BF9DC35402109B0000000000000000000000000) ;





--running in the reverse order leads to a different plan for both
    EXEC dbo.spAddressByCity @City = N'Mentor' ;



    EXEC dbo.spAddressByCity @City = N'London' ;
	









--another way to get a generic plan
            ALTER PROC dbo.spAddressByCity @City NVARCHAR(30)
            AS 
                SELECT  a.AddressID,
                        a.AddressLine1,
                        a.AddressLine2,
                        a.City,
                        sp.[Name] AS StateProvinceName,
                        a.PostalCode
                FROM    Person.Address AS a
                        JOIN Person.StateProvince AS sp
                        ON a.StateProvinceID = sp.StateProvinceID
                WHERE   a.City = @City
                OPTION  (OPTIMIZE FOR (@city UNKNOWN)) ;



                EXEC dbo.spAddressByCity @City = N'London' ;


--JOIN HINTS

SELECT  pm.Name,
        pm.CatalogDescription,
        p.Name AS ProductName,
        i.Diagram
FROM    Production.ProductModel AS pm
        LEFT JOIN Production.Product AS p
        ON pm.ProductModelID = p.ProductModelID
        LEFT JOIN Production.ProductModelIllustration AS pmi
        ON p.ProductModelID = pmi.ProductModelID
        LEFT JOIN Production.Illustration AS i
        ON pmi.IllustrationID = i.IllustrationID
WHERE   pm.Name LIKE '%Mountain%'
ORDER BY pm.Name


--Loop
SELECT  pm.Name,
        pm.CatalogDescription,
        p.Name AS ProductName,
        i.Diagram
FROM    Production.ProductModel AS pm
        LEFT LOOP JOIN Production.Product AS p
        ON pm.ProductModelID = p.ProductModelID
        LEFT JOIN Production.ProductModelIllustration AS pmi
        ON p.ProductModelID = pmi.ProductModelID
        LEFT JOIN Production.Illustration AS i
        ON pmi.IllustrationID = i.IllustrationID
WHERE   pm.Name LIKE '%Mountain%'
ORDER BY pm.Name


--Merge
SELECT  pm.Name,
        pm.CatalogDescription,
        p.Name AS ProductName,
        i.Diagram
FROM    Production.ProductModel AS pm
        LEFT MERGE JOIN Production.Product AS p
        ON pm.ProductModelID = p.ProductModelID
        LEFT JOIN Production.ProductModelIllustration AS pmi
        ON p.ProductModelID = pmi.ProductModelID
        LEFT JOIN Production.Illustration AS i
        ON pmi.IllustrationID = i.IllustrationID
WHERE   pm.Name LIKE '%Mountain%'
ORDER BY pm.Name







--Table Hints
-- NOEXPAND
SELECT a.City,
vspcr.StateProvinceName,
vspcr.CountryRegionName
FROM Person.Address AS a 
JOIN Person.vStateProvinceCountryRegion AS vspcr
ON a.StateProvinceID = vspcr.StateProvinceID
WHERE a.AddressID = 22701;



SELECT a.City,
vspcr.StateProvinceName,
vspcr.CountryRegionName
FROM Person.Address AS a 
JOIN Person.vStateProvinceCountryRegion AS vspcr WITH (NOEXPAND )
ON a.StateProvinceID = vspcr.StateProvinceID
WHERE a.AddressID = 22701;









-- INDEX()
SELECT  d.Name,
        e.JobTitle,
        p.LastName + ', ' + p.FirstName
FROM    HumanResources.Department AS d
        JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON d.DepartmentID = edh.DepartmentID
        JOIN HumanResources.Employee AS e
        ON edh.BusinessEntityID = e.BusinessEntityID
        JOIN Person.Person AS p
        ON e.BusinessEntityID = p.BusinessEntityID
WHERE   d.Name LIKE 'P%'






SELECT  d.Name,
        e.JobTitle,
        p.LastName + ', ' + p.FirstName
FROM    HumanResources.Department AS d WITH (INDEX (PK_Department_DepartmentID))
        JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON d.DepartmentID = edh.DepartmentID
        JOIN HumanResources.Employee AS e
        ON edh.BusinessEntityID = e.BusinessEntityID
        JOIN Person.Person AS p
        ON e.BusinessEntityID = p.BusinessEntityID
WHERE   d.Name LIKE 'P%'



-- FAST N
SELECT  pm.Name AS ProductModelName,
        p.Name AS ProductName,
        SUM(piv.Quantity)
FROM    Production.ProductModel AS pm
        JOIN Production.Product AS p
        ON pm.ProductModelID = p.ProductModelID
        JOIN Production.ProductInventory AS piv
        ON p.ProductID = piv.ProductID
GROUP BY pm.Name,
        p.Name
        
        
        






SELECT  pm.Name AS ProductModelName,
        p.Name AS ProductName,
        SUM(piv.Quantity)
FROM    Production.ProductModel AS pm
        JOIN Production.Product AS p WITH (FASTFIRSTROW)
        ON pm.ProductModelID = p.ProductModelID
        JOIN Production.ProductInventory AS piv
        ON p.ProductID = piv.ProductID
GROUP BY pm.Name,
        p.Name







--Plan guides
IF (SELECT  OBJECT_ID('spAddressByCity')
   ) IS NOT NULL 
    DROP PROCEDURE dbo.spAddressByCity ;
GO
CREATE PROC dbo.spAddressByCity @City NVARCHAR(30)
AS 
    SELECT  a.AddressID,
            a.AddressLine1,
            a.AddressLine2,
            a.City,
            sp.[Name] AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
            JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
    WHERE   a.City = @City ;
    

EXEC sys.sp_create_plan_guide @name = 'SniffFix', -- sysname
    @stmt = N'SELECT  a.AddressID,
            a.AddressLine1,
            a.AddressLine2,
            a.City,
            sp.[Name] AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
            JOIN Person.StateProvince AS sp
            ON a.StateProvinceID = sp.StateProvinceID
    WHERE   a.City = @City ;', -- nvarchar(max)
    @type = N'Object', -- nvarchar(60)
    @module_or_batch = N'dbo.spAddressByCity', -- nvarchar(max)
    @params = NULL, -- nvarchar(max)
    @hints = N'OPTION(OPTIMIZE FOR(@City = ''Mentor''))' -- nvarchar(max)





EXEC dbo.spAddressByCity @City = N'London'




--clean up
EXEC sys.sp_control_plan_guide  @operation = N'DROP', -- nvarchar(60)
    @name = SniffFix -- sysname
    
    








--Plan Forcing
IF (SELECT  OBJECT_ID('Sales.uspGetCreditInfo')
   ) IS NOT NULL 
    DROP PROCEDURE Sales.UspGetCreditInfo ;
GO
CREATE PROCEDURE Sales.uspGetCreditInfo (@SalesPersonID INT)
AS 
    SELECT  soh.AccountNumber,
            soh.CreditCardApprovalCode,
            soh.CreditCardID,
            soh.OnlineOrderFlag
    FROM    Sales.SalesOrderHeader AS soh
    WHERE   soh.SalesPersonID = @SalesPersonId;


    
EXEC Sales.uspGetCreditInfo @SalesPersonID = 277


-- to get the plan_handle	
    SELECT  decp.plan_handle
    FROM    sys.dm_exec_cached_plans AS decp
            CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
    WHERE   dest.[text] LIKE 'CREATE PROCEDURE Sales.uspGetCreditInfo%' ;


--to just remove the one plan from cache
    DBCC freeproccache(0x050008000D42C53740215BA9000000000000000000000000) ;
    
    
EXEC Sales.uspGetCreditInfo @SalesPersonID = 288






    
 
 SET STATISTICS XML ON
 GO
 SELECT  soh.AccountNumber,
            soh.CreditCardApprovalCode,
            soh.CreditCardID,
            soh.OnlineOrderFlag
    FROM    Sales.SalesOrderHeader AS soh
    WHERE   soh.SalesPersonID = 288;
GO
SET STATISTICS XML OFF
GO





EXEC sys.sp_create_plan_guide @name = N'UsePlanGuide', -- sysname
    @stmt = N'SELECT  soh.AccountNumber,
            soh.CreditCardApprovalCode,
            soh.CreditCardID,
            soh.OnlineOrderFlag
    FROM    Sales.SalesOrderHeader AS soh
    WHERE   soh.SalesPersonID = @SalesPersonId;', -- nvarchar(max)
    @type = N'OBJECT', -- nvarchar(60)
    @module_or_batch = N'Sales.uspGetCreditInfo', -- nvarchar(max)
    @params = NULL, -- nvarchar(max)
    @hints = N'OPTION (USE PLAN N''<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1600.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="130" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.39206" StatementText="SELECT [soh].[AccountNumber],[soh].[CreditCardApprovalCode],[soh].[CreditCardID],[soh].[OnlineOrderFlag] FROM [Sales].[SalesOrderHeader] [soh] WHERE [soh].[SalesPersonID]=@1" StatementType="SELECT" QueryHash="0xA12014ED981E6E4B" QueryPlanHash="0xE527A9A568F4BC33">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="32" CompileTime="2" CompileCPU="2" CompileMemory="232">
            <RelOp AvgRowSize="40" EstimateCPU="0.0005434" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="130" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.39206">
              <OutputList>
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OnlineOrderFlag" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="AccountNumber" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="CreditCardID" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="CreditCardApprovalCode" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="130" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
                <OuterReferences>
                  <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesOrderID" />
                  <ColumnReference Column="Expr1003" />
                </OuterReferences>
                <RelOp AvgRowSize="11" EstimateCPU="0.0003" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="130" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.003425" TableCardinality="31465">
                  <OutputList>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesOrderID" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="130" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesOrderID" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="[IX_SalesOrderHeader_SalesPersonID]" Alias="[soh]" IndexKind="NonClustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(288)">
                                <Const ConstValue="(288)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="40" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="129" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.388092" TableCardinality="31465">
                  <OutputList>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OnlineOrderFlag" />
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="AccountNumber" />
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="CreditCardID" />
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="CreditCardApprovalCode" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="130" ActualEndOfScans="0" ActualExecutions="130" />
                  </RunTimeInformation>
                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OnlineOrderFlag" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="AccountNumber" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="CreditCardID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="CreditCardApprovalCode" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="[PK_SalesOrderHeader_SalesOrderID]" Alias="[soh]" TableReferenceId="-1" IndexKind="Clustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesOrderID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[AdventureWorks2008R2].[Sales].[SalesOrderHeader].[SalesOrderID] as [soh].[SalesOrderID]">
                                <Identifier>
                                  <ColumnReference Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesOrderID" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="(288)" ParameterRuntimeValue="(288)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>'')' -- nvarchar(max)






EXEC Sales.uspGetCreditInfo @SalesPersonID = 277;


--cleanup
exec sp_control_plan_guide 'DROP','UsePlanGuide'