Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 5 at 11:15

This question came from our site for professional and enthusiast programmers.

    
Start by determining what changed which might cause a difference: the data, indexes, server configuration and usage, constants, query conditions, connectivity, interfaces, etc. –  wallyk Mar 4 at 20:01
5  
You could try updating statistics. A good recommendation is to take baseline execution plans of your procedures when all is running well to compare with when you encounter issues like this. –  Martin Smith Mar 4 at 20:01

3 Answers 3

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 exec 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 proove 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

share|improve this answer

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.

share|improve this answer
    
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. –  Trevor Mar 4 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 4 at 20:40

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.