SQL Query Performance & Optimization – What you should look at execution plan?

So I haven’t posted anything, kind of busy with some other stuff. Basically last week was very painful in terms of professional achievements. Moving on today let’s talk about what we should look at in execution plan. The question is very common in technical interviews. What the interviewer normally asks is you have got a slow query, what you do to make it fast or how do you tune that query. So I am here jotting down the top 10 things we should look at while analyzing the query plan. Also please note that by looking at the operators you will not get the solution but you will know where your problem lies. Nothing is right or wrong in the execution plan. It’s just that sometimes optimizer does not gives us optimal plan and it may be due to the following.

1. We are unable to provide the updates statistical information.
2. Or optimizer losses it along the way due to other operations
3. Or we wrote extremely bad query
4. Or maybe something else 

Please note that if your query is taking too long to complete go with the estimated plan.

So the things you should in the execution plan are-

1. First Operator –

Always start with checking first operator. First operator can be Select, Insert and Delete. Please note that we don’t have Update as the first operator. Right click on the first operator to get tons of properties and information. Major properties you should check here are given below-

a. Check optimization level – I think it should be FULL.
b. Reason for Early Termination of Statement – I think it should be “Good Enough Plan Found”.
c. Parameter compiled value and Parameter runtime value – This will help in finding parameter sniffing issues.
d. Estimated Sub tree cost

Example

--

CREATE PROC FindCounts
(
	 @Female VARCHAR(1)
	,@Male VARCHAR(1)
)
AS
	SELECT c1.DeptID, COUNT(*) TotalEmpPerDept
		,SUM( CASE WHEN EmpGender = @Male THEN 1 ELSE 0 END ) MaleCount
		,SUM( CASE WHEN EmpGender = @Female THEN 1 ELSE 0 END ) FemaleCount
	FROM [dbo].[CountMaleFemale] c1
	GROUP BY DeptID

--

CHECKPOINT
DBCC FREEPROCCACHE
GO

EXEC FindCounts 'F', 'M'
GO

Execution Plan

Pawan Khowal – First Operator in Execution Plan

Pawan Khowal – First Operator in Execution Plan – Properties

2. High cost operators –

Okay we should look at physical operations with high cost. What is the most expensive piece of this query? E.g. what is taking up 95% of the cost is. 95% is where your problem is. So always keep an eye on the most costly operators.

So one may say that this cost is only based on the estimates and it is same in estimated and actual execution plan. We don’t have any mechanism to calculate the actual cost. So we use estimated numbers only. We should still use them. These are accurate more often than not and can quickly inform you to the possible source of the problem.

Example

SELECT c1.DeptID, COUNT(*) TotalEmpPerDept
	,SUM( CASE WHEN EmpGender = 'M' THEN 1 ELSE 0 END ) MaleCount
	,SUM( CASE WHEN EmpGender = 'F' THEN 1 ELSE 0 END ) FemaleCount
FROM [dbo].[CountMaleFemale] c1
GROUP BY DeptID

Execution Plan

Pawan Khowal – High Cost Operators in Execution Plan

In the above example index scan is the most costly operation. Here I am just showing the costly operation there is nothing incorrect about the query.

3. Warnings

This means exclamation marks. Exclamation marks are bad. Hover over the execution plan, it may or may not be bad. But always check that because it’s an exclamation mark.

Execute the below query on AdventureWorks2014

SELECT
    ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint
  , StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture
  , ProductLine, Class, Style, ProductSubcategoryID, ProductModelID
  , SellStartDate, SellEndDate, DiscontinuedDate FROM [dbo].[bigProduct]
WHERE CAST(ProductID AS NVARCHAR(10)) = N'1001'

Warning from properties-

Type conversion in expression (CONVERT(nvarchar(10),[AdventureWorks2014].[dbo].[bigProduct].[ProductID],0)) may affect “CardinalityEstimate” in query plan choice, Type conversion in expression (CONVERT(nvarchar(10),[AdventureWorks2014].[dbo].[bigProduct].[ProductID],0)=[@1]) may affect “SeekPlan” in query plan choice

Execution Plan of above query

Pawan Khowal – Warnings in Execution Plans

4. Thick lines / Fat pipes

Here always look at the think lines from the execution plan, you have seeks and scans with a huge fat pipe with millions of rows. So this can be the source of your problem is. Look at the estimated and actual number of rows. If they are way of then that might be the source of your problem. So always keep you data size as small as possible. Try to reduce the data size as early as possible.

Example

–Note – this is a sample query. Don’t use * in your queries.

SELECT
	*
FROM bigTransactionHistory  

Pawan Khowal – Thick Lines or Fat Pipes in Execution Plan

5. Scans

Scans are basically are of three types – Table scan, Clustered Index Scan and Non Clustered Index Scan. Scan means you are reading all data from your table. Meaning if you have billions of rows then we might have a problem there. So you must need to check what’s going to on there. Please understand that nothing is bad or good in execution plans, on the same lines scans are not necessarily bad for performance. In general try to reduce the dataset as soon as possible and try to work over smaller datasets.

Scans are bad if seek is possible and you have written bad query and because of that you are getting a scan.

Example

–Check below queries

SELECT c1.EmpId, c1.EmpName
FROM [dbo].[CountMaleFemale] c1
WHERE LEFT(EmpName,1) = 'P'

SELECT c1.EmpId, c1.EmpName
FROM [dbo].[CountMaleFemale] c1
WHERE EmpName LIKE 'P%'

Execution Plans – In one case we are getting scan and in one case we are getting seek.

6. Actual no of rows and Estimated number of rows

Ok this is one is really important. If we failed to inform the proper information about the data to the optimizer we will get huge difference in actual and estimated number of rows. This information you can get by hovering over the iterator using tool tip or by using properties of iterators.

Example

SELECT c1.EmpId, c1.EmpName
FROM [dbo].[CountMaleFemale] c1
WHERE EmpName LIKE 'P%'

Execution plan

Pawan Khowal – Actual and Estimated Number or Rows

Pawan Khowal – Actual and Estimated Number or Rows – Properties

7. Check for set options

If you are two execution plans for the same query then you need to check set options I mentioned in the First Operator. This is common if you running a query for application and executing the same query from SSMS and for both queries you are getting different execution plans. Most of the time the issue lies with Set Options and one of them behaves badly.

Example- Right Click on First Operator

8. Missing indexes/ Stats

These comes with a green colour and SQL Server informs us that if you create this index you can get performance improvement of this much percentage. Don’t always go for creating the index. First check the percentage impact and check how many indexes are already present for the table. My rule of thumb is 5 indexes per table.

9. Extra operations

Okay one simple example of extra operation is sort. You will get a expensive sort operator if you don’t have index on the column(s) you are ordering. Other example is spools. If optimizer thinks that its better to save data in tempdb and use it further, Spools are used for that purpose. So these extra operations can also be painful sometimes.

In the upcoming posts, I will explain different types of spools and how we can avoid then(if possible).

I hope you have enjoyed the article. Thanks for reading !

-Pawan Khowal

MSBISkills.com