40

Background

I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 different tables. It's for a large-ish company that has 10 warehouses across the country. All the warehouses read and write to the database. So it's pretty large and pretty busy.

The query I'm having trouble with looks something like this:

select t1.something, t2.something, etc.
from Table1 t1
    inner join Table2 t2 on t1.id = t2.t1id
    left outer join (select * from table 3) t3 on t3.t1id = t1.t1id
    [etc]...
where t1.something = 123

Notice that one of the joins is on a non-correlated sub-query.

The problem is that starting this morning, without any changes (that I or anybody on my team knows of) to the system, the query which usually takes about 2 mins to run, started taking an hour and a half to run -- when it ran at all. The rest of the database is humming along just fine. I've taken this query out of the sproc that it usually runs in and I've run it in SSMS w/ hard-coded parameter variables with the same slowness.

The strangeness is that when I take the non-correlated sub-query and throw it into a temp table, and then use that instead of the sub-query, the query runs fine. Also (and this is the strangest to me) if I add this piece of code to the end of the query, the query runs great:

and t.name like '%'

I've concluded (perhaps incorrectly) from these little experiments that the reason for the slow-down is due to how SQL's cached execution plan is set up -- when the query is a little different, it has to create a new execution plan.

My question is this: When a query that used to run fast suddenly starts running slowly in the middle of the night and nothing else is affected except for this one query, how do I troubleshoot it and how do I keep it from happening in the future? How do I know what SQL is doing internally to make it so slow (if the bad query ran, I could get its execution plan but it won't run -- maybe the expected execution plan would give me something?)? If this issue is with the execution plan, how do I keep SQL from thinking that really crappy execution plans are a good idea?

Also, this is not a problem with parameter sniffing. I've seen that before, and this is not it, since even when I hard-code the varaibles in SSMS, I still get slow performance.

Trevor
  • 503
  • 1
  • 4
  • 5

5 Answers5

34

When a query that used to run fast suddenly starts running slowly in the middle of the night and nothing else is affected except for this one query, how do I troubleshoot it...?

You can start by checking if the execution plan is still in the cache. Check sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_cached_plans. If the bad execution plan is still cached you can analyze it, and you can also check the execution stats. The execution stats will contain information as logical reads, CPU time and execution time. These can give strong indications what the problem is (eg. large scan vs. blocking). See Identifying problem queries for an explanation how to interpret the data.

Also, this is not a problem with parameter sniffing. I've seen that before, and this is not it, since even when I hard-code the varaibles in SSMS, I still get slow performance.

I'm not convinced. Hard-coding variables in SSMS does not prove that the past bad execution plan was not compiled against a skewed input. Please read Parameter Sniffing, Embedding, and the RECOMPILE Options for a very good article on the topic. Slow in the Application, Fast in SSMS? Understanding Performance Mysteries is another excellent reference.

I've concluded (perhaps incorrectly) from these little experiments that the reason for the slow-down is due to how SQL's cached execution plan is set up -- when the query is a little different, it has to create a new execution plan.

This can be easily tested. SET STATISTICS TIME ON will show you the compile vs. execution time. SQL Server:Statistics performance counters will also reveal whether compilation is an issue (frankly, I find it unlikely).

However, there is something similar that you may hit: the query grant gate. Read Understanding SQL server memory grant for details. If your query requests a large grant at a moment no memory is available, it will have to wait, and it will all look as 'slow execution' to the application. Analyzing wait info stats will reveal if this is the case.

For a more general discussion about what to measure and what to look for, see How to analyse SQL Server performance

Glorfindel
  • 2,201
  • 5
  • 17
  • 26
Remus Rusanu
  • 51,846
  • 4
  • 95
  • 172
6

This is a bane of running complex queries in SQL Server. Fortunately, it doesn't happen that often.

Look at the query plan for the query (when it is running slow). I am guessing you will find a nested loop join occurring one or more times on tables with no indexes for the join. This really slows things down. To fast forward, the way to fix this is with a hint. Add the following at the end of the query:

OPTION (MERGE JOIN, HASH JOIN)

This has generally fixed this problem for me in the past.

What may be happening is that subtle changes to the table (or to the availability of temporary space) cause the SQL optimize to prefer a slower join algorithm. This can be quite subtle and quite sudden. When you create a temporary table, the optimizer has more information about the table (such as its size), so it can generate a better plan.

Gordon Linoff
  • 2,232
  • 1
  • 17
  • 18
  • 1
    The execution plan is indeed using a nested loop join. However, when I place the hint as you suggest, I get this error: "Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN." When I add OPTION (LOOP JOIN) of course, it will create an execution plan but I still have a problem with it running slowly. Have you run into this issue? Looks like it thinks it NEEDS to loop join. –  Mar 04 '14 at 20:36
  • @Trevor . . . No I haven't actually seen this problem. Perhaps your query is doing some non-equijoins (not using equal signs) and the optimizer has to use nested loop joins there. – Gordon Linoff Mar 04 '14 at 20:40
3

I recently experienced this same issue which brought me to this page.

@MartinSmith was on to something when he recommended updating your statistics and explain plan. I will like to add that you should also try to ensure you take a look at running jobs/queries which may create locks and thereby slow down response time.

In my case the culprit was the job gathering table statistics. For some reason it did not complete in the window it should have, and kept running when users had resumed. I found the process, killed it and the queries started responding again.

I hope this helps someone else

daffyjeje
  • 111
  • 1
  • 10
3

Usually it's a missing index causing this kind of issue.

What I usually do is running the query using the SQL Management Studio and enabling 'Include Actual Execution Plan (CTRL+M)' and find out which join is having the biggest percentage.

The application doesn't focus on the bottleneck, but you can find it "quickly" just looking into the result.

example here: 48PercentForTop

Xavier
  • 150
  • 3
  • 2
    an index won't suddenly go "missing" though, so while this may improve the performance it doesn't explain the problem – Fowl Dec 14 '16 at 05:01
  • Azure SQL Database does have the ability to automatically create and delete indexes; just something to be aware of. – Hannah Vernon Aug 16 '21 at 17:44
0

You also need to check if any server backup or any Archiving\indexing jobs is running when you see performance issue in T-SQL\Procedure.

Amol Nimbalkar
  • 1