T-SQL Query runs quickly on local PC, but slow on SQL Server?

Question

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!

Answer 1

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.

The 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.