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.

I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters.

It's been running without issue for months - but has suddenly started running very slowly on the server (4 core, 10GB RAM, disks on fast SAN)

If I run the query locally on my PC (2 core, 2GB RAM, one local disc) then it takes approx 1:20 to run.

Prior to this week, run on the server took approx 0:15 to run.

This week, run on server taking approx 12:30 to run!!!

This is a massive difference, and I have no idea what to start looking for to resolve the issue.

What I HAVE noticed is that this does not seem to be getting cached...OR something is hogging/not releasing cache.

e.g.
Run this locally, 1:20...run it again immediately - almost instant.
Run on server 12:30...run it again immediately - 12:30 again?

Any suggestions very gratefully received!

share|improve this question
3  
First place to start is to compare actual execution plans. –  Aaron Bertrand Dec 9 '13 at 1:35
    
Running both server and local again now with actual plans...4:30am here in qatar so server quiet :) –  BlueChippy Dec 9 '13 at 1:38
    
Could be bad plans (possibly due to bad stats), or parameter sniffing, or it could be blocking. Tough for us to know without seeing actual execution plans when the query was slow, and knowing what the other activity is (e.g. if your query is being blocked). –  Aaron Bertrand Dec 9 '13 at 1:40
    
I'm no DBA - how do I tell about blocking? I am the only one doing anything against the database concerned. –  BlueChippy Dec 9 '13 at 1:45
    
OK, I have exe plans from both...I can't see anything obviously different. Any idea how/where to post them? –  BlueChippy Dec 9 '13 at 1:54
show 2 more comments

1 Answer

A few of the most common scenarios for occasionally slow, but usually fast, SELECT queries:

  1. A suboptimal plan is being used - perhaps due to bad stats, parameter sniffing, or other factors. In your case, seems unlikely that it would be parameter sniffing, if you really mean both no explicit parameters and no literals (e.g. hard-coding WHERE x = 1 is still parameterized in most cases). In order to make any useful guesses about this, you'll need to supply actual execution plans after the successful completion of one of the "slow" versions of the query.

  2. Your query is getting blocked - again, this is not likely to be your issue either, since you say you are the only one working on this database. But to determine if you are being blocked, when you observe the query is running slow, do this in one window:

    PRINT @@SPID;
    

    Once you have that output, run the slow query in that same window:

    SELECT ... query that runs slowly ...
    

    Now, in a new window, run this:

    SELECT command, status, wait_type, last_wait_type, blocking_session_id
      FROM sys.dm_exec_requests WHERE session_id = <@@SPID FROM ABOVE>;
    

    Make note of the wait type (see below), but if blocking_session_id is not 0, check what that user is running (run the query above again, changing the session_id to the blocker).

  3. Some other resource is bottlenecked - it could be that at certain times the underlying disk that the database is on or the network you're moving data across can be under extreme pressure, and this will filter down to just about every operation that uses them. This could also be true if your SQL Server is on a virtual machine and the underlying host is under some kind of resource pressure.

    In addition to the execution plan you get from the slow version of the query, you should examine the waits that are experienced while your query is running. This is beyond the scope of this localized answer, mostly because it is a very lengthy topic to digest, but you can start with this white paper from Microsoft.

With the disclaimer that I work for SQL Sentry, our free execution plan analysis tool, Plan Explorer, tries to make problems with a given execution plan a lot more obvious than it will ever be in Management Studio. As a bonus, the paid version, which you can trial for free for two weeks, also gives details about the exact waits that your session experienced - this is pretty cumbersome to do on your own. Sorry about the ad but it really can be a time-saver that helps isolate the real problem behind a particularly nasty query.

share|improve this answer
    
command status wait_type last_wait_type blocking_session_id SELECT runnable NULL SOS_SCHEDULER_YIELD 0 –  BlueChippy Dec 9 '13 at 2:06
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.