26 performance tuning questions and solutions, Advanced SQL interview questions, Advanced SQL Queries, Advanced SQL tutorial, Advanced SQL tutorial pdf, Difficult SQL Interview Questions, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Execution plan, execution plan operators, Free Download SQL SERVER Interview questions, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, Spools in Execution Plan. Are they bad?, Spools in Execution Plan?, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL Complex Queries, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Queries asked in interviews, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Questions, SQL Server, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL SERVER Interview questions for experienced, SQL SERVER Interview questions pdf, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL SERVER Tricky questions, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, SQL Tricky question, SQL Tuning Overview, SQL Tutorial, T-SQL, T-SQL Interview questions, T-SQL Programming, T-SQL Server Interview Questions, T-SQL Tutorial, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, TOP 100 SQL SERVER INTERVIEW QUESTIONS, Top 50 SQL Server Questions & Answers, Tough PL SQL Interview Questions, Tough SQL Interview Questions, Tough SQL Queries, Tough SQL Queries Interview Questions, Tough SQL Questions, Tricky SQL Interview Questions, Tricky SQL Interview Questions and answers, Tricky SQL Queries for Interview, Tricky SQL SERVER Interview Questions and answers
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
-- 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
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.
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
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.
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
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.
–Note – this is a sample query. Don’t use * in your queries.
SELECT * FROM bigTransactionHistory
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.
–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.
SELECT c1.EmpId, c1.EmpName FROM [dbo].[CountMaleFemale] c1 WHERE EmpName LIKE 'P%'
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 !