Sql-server – Query Performance Tuning

Since you are running SP3 in QA and SP1 in production (assuming you are running with Trace Flag 4199) you are missing out on a lot of performance related fixes in your production environment.

My first suggestion would be to test the query and compare the generated plan on servers of the same build number.

Without details of the query and the underlying tables (which may be complex to post if you're talking about views referencing views, joining views) it's hard to tell which issue you are running into exactly, but for example these have been fixed since SP1:

• FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012 or SQL Server 2014
• FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2 or in SQL Server 2012
• FIX: Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012 or 2014

But the list goes on and on.

Other than that it could be any configuration difference (MaxDOP, memory in the server, activated trace flags) or it could be statistics that are different.

In any case it's hard to compare between builds that are so far apart (SP1 and SP3)

I would suggest running the same build in QA as prod anyway because you want your QA-users to be testing the exact situation as they will be running in prod.

I will walk you through how I approach performance tuning a query like this.

First step is to read through the SQL query to see if there are any performance red flags. Your query joins five tables together and doesn't do anything really complex, so performance is very likely going to be dominated by how the five tables are accessed (index or table scan), the join types (hash, nested, or merge), and the join order. So if this query is running slower than it should then the optimizer is probably getting at least one of those wrong. The most common reason the optimizer gets one of those wrong is if it gets bad cardinality estimates at one or more steps in the plan.

There are a few things in your query that are likely to contribute to bad cardinality estimates. The first is the use of local variables. SQL Server creates the plan without knowing what the value of those variables are and a BETWEEN predicate with two unknown values gives a default cardinality estimate of 9%.

DATEADD(ss, ((c.time_stamp)),'1970-01-01') between @Date1 and @Date2  

You don't want that. You want the SQL Server query optimizer to have as much information as possible and to be aware of the real values of the variables. One way to do that is with an OPTION (RECOMPILE) hint. That can be a good option as long as this query isn't executed so many times per day that the compilation cost becomes an issue.

The DATEADD part of that is likely to contribute to bad cardinality estimates as well. The filter that you have right now is an indirect way of getting what you want. What if instead you converted @Date1 and @Date2 to the same datatype as time_stamp? Calculate the number of seconds since '01/01/1970' and use those as the variable values. That will allow SQL Server to use an index on time_stamp (if one exists) and it will be able to use the statistics on that column to better estimate the number of rows filtered out by that predicate.

The cardinality estimator often gets better results if you filter on what is there instead of trying to filter out values. Is it possible to write this filter as an IN() that contains all of the values that qualify?

and a.ticket_status not in ('test','cancelled') 

Those were the three things that jumped out at me. Now I will look at the plan. First look at the total cost and what SQL Server thinks will be the most expensive operators. The clustered index scan on current_ticket_details has 93% of the cost, but the subtree cost isn't very high. Try running a simple query that returns a scan on that same table. Does it take around 800 seconds? If so you have found the bottleneck and you may need to add an index the current_ticket_details table. If not, the query plan was much more expensive than SQL Server thought it would be which is useful information.

Comparing the actual number of rows versus the estimated number of rows is extremely useful when analyzing many query plans. If there is a large difference at a step then it's possible that SQL Server made a suboptimal choice for operators to the left of that bad estimate. In your query plan, the final estimated row count is 1 row. Do you really expect this query to only insert one row? That is a red flag right there. You can read to the right in the plan to figure out where the estimate went bad. The estimated row count dropped to 1 after the nested loop join to mail_details (node 16). IMO that is the most likely cause of the problem.

SQL Server went for nested loop joins and seeks for the other tables that were joined after it. Not surprisingly some of those tables have a high number of logical reads. If that estimate is incorrect (easy to tell with actual plan) then a good step is to fix that estimate. That may change the rest of the plan and the query could finish faster. In addition, the nested loop joins and seeks have a very low cost. If the number of rows processed by them was much greater than expected then that would explain why the scan on current_ticket_details finished much faster than 800 s (I am assuming that it did).

To state it more clearly, the query optimizer expected to do a single index seek on act_log, but the statistics output tells you that a scan count 28882 and 2631931 logical reads were done against act_log. That doesn't add up.

You haven't provided enough information to figure out exactly why that low estimate is happening for the antijoin to mail_details. As someone else suggested, you should look at the stats on that table to make sure they are up-to-date. If they are up-to-date you could consider a filtered statistic or a computed column. One minor thing worth mentioning is that NOT IN() will return no results if the subquery returns at least one NULL value. If you don't need that behavior then you should rewrite that join as a NOT EXISTS. That will also eliminate the row count spool.

In summary, rewrite these lines to enable the optimizer to get better cardinality estimates:

DATEADD(ss, ((c.time_stamp)),'1970-01-01') between @Date1 and @Date2  
and a.ticket_status not in ('test','cancelled')

and figure out why SQL Server thinks that only one row will remain after this join:

and a.assignee not in (select email_id from mail_details where frequency ='ALL' and tower = 'APPS' and parm_name = 'exclusion_list')

If you address all of that I expect that you'll see a different (and hopefully better) query plan.