SQL Server 2005 - Queries going into suspended state immediately

Question

I'm having a problem with an ad-hoc query that manages a fairly high amount of data. Upon executing the query, the status immediately goes into suspended state. It will stay suspended for around 25 minutes and then complete execution.

I have a mirror environment with SQL2K and the same query executes in around 2 minutes and never goes into suspended state.

@@version = Microsoft SQL Server 2005 - 9.00.3068.00 (Intel IA-64) Feb 26 2008 21:28:22 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Answer 1

Perhaps the statistics are out of date and need updated. Update them but better to rebuild indexes at the same time.

Or, you don't have any. Are stats set to create and update automatically? I've seen cases where they're switched off because someone does not understand what they are for or how updates happen.

Note: the sampling rate of stats is based on the last stats update. So if you last sampled 100%, it may take some time.

What happens when you run the query twice? Is it quicker the second time?

Answer 2

It's hard to tell from the limited information, but I'd be curious to know what's happening from a performance perspective on the server while the query is running. You can capture performance metrics with Perfmon, and I've got a tutorial about it here:

http://www.brentozar.com/perfmon

While the query's running, what's the statistics each of those counters look like? If you capture the statistics as described in that article, you can email 'em to me at [email protected] and I'll take a look at 'em to see what's going on.

Another thing that'd help is the execution plan of the query. Go into SQL Server Management Studio, put the query in, and click Query, Display Estimated Execution Plan. Right-click anywhere on the plan and save it as a file, and then other people can see what the query looks like.

Then ideally, click Query, Include Actual Execution Plan, run the query, and then go to the Execution Plan tab. Save that one too. If you post the two plans (or email 'em to me) you'll get better answers about what's going on.