Lesson Learned #289: Hands-On-Labs: Understand and reading an execution plan

[Guest Jose_Manuel_Jurado]

We used to have cases where our customers reported a performance problem, in the execution of a query, they need to know how to read it an execution plan. In this video we share with you several details about it.

 

 

 

A little bit of theory

 

• What Happens when a query is submitted?
  
  • Query Compilation:
    
    • Checks:
      
      • Parser: Check the syntax, resolve the schema and permissions.
        
      • Algebrizer: Bind objects.
        
      • Optimizer: Optimize the query.
        

    [*]Outcome (Execution Plan).

    • Generates an execution plan (series of instrucctions for processing the query).
      
    • Example: Lesson Learned #286: Compilation vs Execution time running a TSQL query - Microsoft Community Hub
      

 

 

 

• What Happens when a query is executed?
  
  • Input:
    
    • Consider many alternatives ways to achieve the query.
      
    • Estimates a “cost” for each possible alternative:
      
    • Full cost-based optimization
      
      • Query Processor Tree: Tables and indexes.
        
      • Statistics: index and column understanding of volume and distribution of data.
        
      • Constraints: primary key, foreign key, .. To know the limit of data stored within the tables referenced.
        

      [*]Trivial Plan

    [*]Outcome:

    • Applies a set of rules to transform the logical query tree into a plan containing a set of operators that, collecctively will physically execute the query.
      
    • Choose the lower cost. It is important is a heuristic process. Is not attempting to find the best posible plan.
      
    • Save the query in the plan cache.
      
    • For this Reason, DBCC FREEPROCCACHE it is important checking the execution.
      
    • Other factors, same TSQL Text, schema qualitifcation and SET OPTIONs.
      
    • Some DDL is not optimized.
      

 

 

 

• Possible issues for performance impact:
  
  • Parameter sniffing.
    
  • Query Parametrization.
    
  • Plan recompilation.
    
    • Update statistics used by query.
      
    • Calling sp_recompile.
      
    • Age of the execution plan (number of executions*cost)
      
    • SET options.
      
    • DDL
      
    • New Index.
      
    • DBCC FREEPROCCACHE.
      

 

 

 

• What to look for in an Execution Plan
  
  • First Operator left-hand side.
    
  • Warnings.
    
  • Estimated versus actual number of rows.
    
  • Operator cost.
    
  • Missing Indexes.
    
  • “Width Arrows".
    
  • Read operators.
    
  • Compatibility level
    

 

 

 

Please, review this video:

 

 

 

 

 

 

Script used for CompilationVSExecution

 

 

 

 

 

DBCC FREEPROCCACHE

 

CREATE Table Academy_BlobData

(ID INT IDENTITY(1,1) PRIMARY KEY,

Age INT,

CustomerData NVARCHAR(MAX) )

 

TRUNCATE TABLE Academy_BlobData

 

DECLARE @Times INTeger =0

 

WHILE(@Times <=100000)

begin

SET @Times=@Times+1

INSERT INTO Academy_BlobData (Age,CustomerData) VALUES(RAND()*(100-5)+5,REPLICATE('xyz',200000))

end

 

SET STATISTICS IO ON

SET STATISTICS TIME ON

 

SELECT * FROM Academy_BlobData

 

SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY max_wait_time_ms DESC

 

 

 

 

 

Script for Performance issue with Sort Operation

 

 

 

 

 

--------------------------------------------------

-- Initial Data --

--------------------------------------------------

DROP TABLE changegroup

CREATE TABLE changegroup (id int IDENTITY(1,1), issueid int default (1), CREATED datetime default(getdate()))

 

DROP TABLE ChangeItem

CREATE TABLE ChangeItem (id int IDENTITY(1,1), groupid int )

 

INSERT INTO changegroup (issueid,CREATED) VALUES(1,getdate());

INSERT INTO changegroup (issueid,CREATED) VALUES(2,getdate());

INSERT INTO changegroup (issueid,CREATED) VALUES(3,getdate());

INSERT INTO changegroup (issueid,CREATED) VALUES(4,getdate());

INSERT INTO changegroup (issueid,CREATED) VALUES(5,getdate());

 

INSERT INTO changegroup (issueid,CREATED) SELECT issueid,CREATED FROM changegroup

 

INSERT INTO changeITEM (GROUPID) VALUES(1)

INSERT INTO changeITEM (GROUPID) VALUES(2)

INSERT INTO changeITEM (GROUPID) VALUES(3)

INSERT INTO changeITEM (GROUPID) VALUES(4)

INSERT INTO changeITEM (GROUPID) VALUES(5)

 

INSERT INTO changeITEM (GROUPID) SELECT GROUPID FROM changeITEM

 

drop index changegroup_IX2 ON changegroup

drop index changeITEM_IX ON changeITEM

drop index changeITEM_IX1 ON changeITEM

drop INDEX changegroup_IX ON changegroup

 

drop view GiveAll

 

------------------------------------

--- Original Query from customer ---

------------------------------------

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

SELECT CI.ID, CG.CREATED, CG.issueid, CG.ID

FROM changegroup CG

INNER JOIN changeitem CI ON CG.ID = CI.groupid

WHERE CG.issueid=1

ORDER BY CG.CREATED ASC, CI.ID ASC

 

------------------------------------

--- Troubleshooting Step 1 ---

------------------------------------

SET STATISTICS IO ON

SET STATISTICS TIME ON

 

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

SELECT CI.ID, CG.CREATED, CG.issueid, CG.ID

FROM changegroup CG

INNER JOIN changeitem CI ON CG.ID = CI.groupid

WHERE CG.issueid=1 --Do a test with 1 and 2

ORDER BY CG.CREATED ASC, CI.ID ASC

 

SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY max_wait_time_ms DESC

select * from sys.dm_db_resource_stats

 

------------------------------------

--- Distribution ---

------------------------------------

select count(*), issueid from changegroup group by issueid

select count(*), groupid from changeitem group by groupid

 

------------------------------------

--- Troubleshooting Step 2 ---

------------------------------------

SET STATISTICS IO ON

SET STATISTICS TIME ON

 

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

SELECT CI.ID, CG.CREATED, CG.issueid, CG.ID

FROM changegroup CG

INNER JOIN changeitem CI ON CG.ID = CI.groupid

WHERE CG.issueid=1 --Do a test with 1 and 2

ORDER BY CG.CREATED ASC, CI.ID ASC option (recompile,max_grant_percent = 100)

 

SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY max_wait_time_ms DESC

select * from sys.dm_db_resource_stats

 

 

------------------------------------

--- Troubleshooting Step 3 -- The impact of heaptable with groupid=2

------------------------------------

 

CREATE UNIQUE CLUSTERED index changegroup_IX2 ON changegroup (id)

CREATE UNIQUE clustered index changeITEM_IX ON changeITEM (ID)

CREATE index changeITEM_IX1 ON changeITEM (GROUPID)

CREATE INDEX changegroup_IX ON changegroup (issueid) INCLUDE(CREATED)

 

 

------------------------------------

--- Troubleshooting Step 4 -- Indexed views

------------------------------------

 

CREATE or alter VIEW GiveAll

with schemabinding

AS

SELECT CI.ID, CG.CREATED, cg.issueid, ci.groupid

FROM dbo.changegroup CG

INNER JOIN dbo.changeitem CI ON CG.ID = CI.groupid

 

 

CREATE unique clustered index GiveAll_ix1 on GiveAll(ID)

CREATE NONCLUSTERED index GiveAll_ix3 on GiveAll(CREATED,ID)

 

 

------------------------------------

--- Troubleshooting Step 5 -- Indexed views

------------------------------------

 

CREATE NONCLUSTERED index GiveAll_ix4 on GiveAll(ISSUEID,CREATED,ID)

 

 

------------------------------------

--- Troubleshooting Step 6 ---

------------------------------------

SET STATISTICS IO ON

SET STATISTICS TIME ON

 

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

SELECT CI.ID, CG.CREATED

FROM changegroup CG

INNER JOIN changeitem CI ON CG.ID = CI.groupid

WHERE CG.issueid=1 --Do a test with 1 and 2

ORDER BY CG.CREATED ASC, CI.ID ASC option (recompile,max_grant_percent = 100)

 

SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY max_wait_time_ms DESC

select * from sys.dm_db_resource_stats

 

------------------------------------

--- Indexed Views ---

------------------------------------

SELECT ID, CREATED

FROM GIVEALL (NOEXPAND)

WHERE issueid=1

ORDER BY CREATED ASC, ID ASC option (recompile,max_grant_percent = 100)

 

 

 

 

 

Enjoy!

 

Continue reading...