SQL Server 2005 64bit query blocking

Question

Hi

We are experiencing some difficulties with SQL Server performance and wanted some help.

Our environment is: -

Windows 2003 Enterprise x64 Edition R2 Intel E5450 Quad Core 3ghz Processor 16GB RAM SQL Server 2005 64bit Enterprise Edition (9.00.3282.00) Database compatibility is 8 (but tested on 9 as well) Hyperthreading is switched off

We have one database with a 1.2 million row table which is being queried (inefficiently), but is resulting in all 4 processors being saturated to the point where all other queries are blocked until the query is finished. This includes queries to separate databases and totally unrelated tables.

If the query is run with option MAXDOP 1 then all 4 cores run at 25% with the query taking 4 times as long, there is no blocking in this instance. In addition to this, we have run the same query on SQL 2000 and the response time is the same, but no CPU saturation.

We have a suspicion that the problem may be around contention over tempdb. In this particular instance we have a stored proc using a temp table and also the parallel query accessing the temp db I assume.

Obviously the standard response will be to re-write the queries. Firstly, this is not really a viable option and secondly this is only treating a symptom of the problem. Essentially the server is unable to process multiple requests, which is of great concern.

Does anyone know of any patches, config changes or known problems that might be causing this? Has anyone seen this before? Is it a 64bit nuance?

Regards

Lee

Answer 1

Sounds like locking on tempdb which effectively stops anything else that may use tempdb from running until it is finished. In particular it may be that the sysobjects table is locked.

The ideal step is to re-write the query to stop it locking tempdb for its entire duration, however, I'm guessing this is not easily possible.

You can try setting up a second instance of SQL to run this database. That way any temporary locking will only affect itself and not any other databases on the server.

To fix the problem for other queries running on the same database you can look into multiple files for the temp database.

However, once you're going this far for a solution you really need to go back to the problem query and try and make its footprint smaller. As Kristen commented, simply changing the way you create a temporary table can have a drastic effect on how things are locked.

Answer 2

Sounds like the table isn't properly indexed. A table with 1.2 million rows shouldn't take anything to query. I've got tables with 60+ million rows and I query that table in milliseconds.

What's the query look like, and whats the table look like?