Slow running Query? Why?

There are so many ways we can find the slow running query and solve it. First we have to know why it is running slow and then once we find out the reason we can apply the solution based on the information we get.  The query could be running slow as simple as it is not written properly. Other reasons are maybe there is a problem with the CPU, problem with Memory, problem with I/0, problem with Space issue. Or maybe database is poorly design, server is not configure properly, maybe there is problem with index like missing index or duplicate index, statistics is not updated, blocking issue or excessive recompilation of store procedure.  And we have various tools to find out the problem. We have Task Manager, PerfMon, Resource Manager, Event Viewer, Extended Event, DBCC, DMVs, Error Log, Job Activity Monitor, Trace Flag and Execution Plan. I personally look at the slow running query is like that…. The query is waiting somewhere in someplace for the resources. So I use Execution Plan and look through each item of the Execution Plan with Set Statistics I/O and time On. (We can use Statisticsparser.com to copy and paste the message from execution plan to analyze details about I/O usage).  I look at the Select Statement and see if there is any problem with Sorting. In the execution plan, if we see a warning sign on the sort operator that means that the memory grant is not enough for the sort operation. So the query required more memory to perform the sort operation. In this case, the sort operator uses the tempdb database to meet the memory deficit. Keep in mind Memory grant can be might be calculated wrong if you have outdated statistics, wrong cardinality estimation, parameter sniffing, bad data types. We must place ORDER BY clause last in the SELECT statement and Rows can be sorted by either ascending order or descending order to get right sorting in the query.  We look at and see if there any Lazy Spool occur. Spool by itself it’s not bad because it solves problem but Lazy Spool comes with a price. Lazy Spool happen when there is duplicate aggregate function. If I calculate my aggression before the query it will solve the problem for Lazy Spool.  (Spool operations are temporary storage of the data for later reuse in a query plan. If the sql server query needs the same data again during operation, it will create a work table under tempdbwithin the execution plan and insert it into it. And when this query is needed during the operation, data is retrieved from this work table. There are two spool operators eager Spool and Lazy Spool. Eager Spool retrieve the data at once and transfer them into Tembdb and Lazy Spool works in a lazy manner and retrieve the data and transfer into Tembdb ). Then we look at if we have nay Implicit Conversion? If we have Implicit Conversion that’s mean we have performance issue. What it is does in Implicit Conversion it automatically change data one type to other data type. When these values changes it put lot of pressure on the system. Scan will happen instead of Seek because of that. (We can use Explicit Conversion to avoid Implicit Conversion).    Then I look at if Key look up happen- it means the query is missing covering index. I look at to if ROW ID look up happens or not. If ROW ID Look up happens that means query is missing cluster Index or it has heap table. Then I look at if it has Index seek or not. If it has Index seek that means it great but If it has Index Scan then its problem but if it has Table Scan then even it’s a bigger problem. I need to work on Index.  Then I see what kind of joining happening. If it has Merge Joint it greats but if it has Nested Loop that means one of the joining column is missing index and that’s a problem but If it has Hash Join it is even bigger problem because two of the joining column have no index.  Then I look at what type of Wait happens in the query. If it is CXPACKET then it’s a problem with Parallelism/CPU problem. Then should I fix it with MAXDOP1 or changing cost threshold of parallelism number. Or it has other wait types such as Pageiolatch, Pageltech, Writelog or any other lock.  So I solve the problem based on the information I got from the execution.