I recently released a Pluralsight Course Analyzing SQL Server Query Plans, and it is really doing great in terms of viewership and I have received some really great comments and feedback about this course. This week, I will be discussing different aspects of Analyzing SQL Server Query Plans. Today we will discuss Identify Poorly Performing Query Plan Operators.
Understanding Execution Plans
I personally believe there are two kinds of people.
- Type 1: Those who do not understand execution plans.
- Type 2: Those who do not trust execute plans.
The question is what type of person are you?
If you ask me, I think I consider myself as type 2 where I do not understand the execution plan completely. An execution plan is an amazing tool and without it, it would have been for sure difficult to tune queries but to master the subject, it is almost impossible. I have been working with SQL Server for over 20 years and I have been exclusively working on SQL Server Performance Tuning area for over 16 years now. I have seen lots of SQL Server Execution Plans and in another word, I can say that I have seen it all what is out there but still today quite often I get surprised from the execution plan.
While I build this course, I have kept my feeling of staying hungry constantly on. I made sure that I approach the execution plan from the point of view of the beginners and point of view of the expert. I wanted to make sure that everyone who attends the course, have some learning from this course.
Operator Scan
While just like many I would love to blame Scan operator for most of the performance problems the reality is not as easy as it sounds. There are moments when Scan is really bad and there are moments when we should ignore them. One needs to understand how the actual scan works and how to resolve them. Earlier I have created a video where I have explained that Scan is really not bad but an index was actually leading it to slow down. Here is the example of the same – SQL SERVER – An Index Reduces Performance of SELECT Queries.
Whenever I see Scan operator, I always suspect that it might be the reason for the performance slowness but I am never certain until inspect all the properties of it. In my experience, I have noticed that every 3 out of 10 index scans are not bad and should be ignored and 7 out of 10 index scan needs further improvement.
Operator Parallelism
I must say that the real world is totally confused about what is parallelism is actually and what is its actual purpose. I have seen many people really looking for actually parallel operations getting confused with a query running on multiple threads. The real world is a strange place where people want their query to run on multiple threads but the real work the query has to do is very little leading the query to run super slow.
I have seen many organizations having a server with over 40 processors, keeping the Max Degree of Parallelism to the default value of zero and wonder why their queries are running very slow. It is recommended that one should pay proper attention to their workload and adjust the MAXDOP.
Poorly Performing Implicit Conversion
Whenever developers ask me, why their query is not using the index they have created the primary subject is Implicit Conversion. Every single join and every single where condition in your SELECT statement may fall prey to this issue and if you see this happening to your query, it is very clear that your query may not use the index which you created and eventually end up doing a complete table scan. The bigger the table the poor the performance you get.
Earlier on this blog post, I posted a query that will go to your memory cache and will pull out all the cases of the implicit conversion Find All Queries with Implicit Conversion in SQL Server – Interview Question of the Week #107. Once you know them you will have to carefully look at the columns compared on both the side and appropriately match them. In this blog post, I explain how you can fix the issue with the implicit conversion SQL SERVER – How to Fix CONVERT_IMPLICIT Warnings?. I hope these tricks will help you to solve your poorly performing queries.
Pluralsight Course Analyzing SQL Server Query Plans
In the Pluralsight Course Analyzing SQL Server Query Plans, I discuss how to Identify Poorly Performing Query Plan Operators. I explain to you how you can implement your first extended event and use it later on to identify the troublemaking query plans. The course is of only 2 hours and 30 minutes and if you have a Pluralsight subscription, you can watch it for free. If you do not have a Pluralsight subscription, you can still watch the course for FREE by signing up for a trial account.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
