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.
- Checks:
- 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
- Query Compilation:
- 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.
- Input:
- 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!
Updated Jan 18, 2023
Version 2.0
Jose_Manuel_Jurado
Microsoft
Microsoft
