Running a lengthy query on SQL Server 2005
We are running SQL Server 2005 and Windows 2003 Server. I ran a query in SQL on a PC with one dual code processor and 4GB and it took six hours. However, we ran the same query in SQL on a brand-new server that has 4 quad core processors and 32GB of RAM, and it took 12 hours. Why would this be?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 16, 2008 8:21 PM
UPDATED:
May 28, 2009 7:54 PM
Answer Wiki:
It could be any number of things.
The disks on the server may not be setup correctly. The additional load on the server by other users could easily slow down your query. If the indexes are different then the query will run at a very different speed. Did you check the execution plans between the two machines?
Are the two machines at the same patch level? When were the indexes and statistics last updated?
It could be that the 16 core machine had enough cores available that SQL tried to run the query in multiple threads, and it's a query which performs better as a single thread. You could have blocking issues on the production machine which are stopping your query.
Without more machine, database and query information it'll be hard to nail it down to a single cause.
A few things you can try off the top are:
Rebuild indexes and update statistics
Run the query as a single thread by using the MAXDOP=1 option
<pre>SELECT *
FROM Table
OPTION (MAXDOP=1)</pre>
Try running the query with the NOLOCK hint to prevent locking. This will give you dirty reads, but will stop the query from being blocked while others are writing to the table(s).
<pre>SELECT *
FROM Table WITH (NOLOCK)</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Did you check to make sure that SQL Server is using more than one cpu?
Is it really multithreading? Because we discovered the same thing. Faster on our dual core single socket cpu than the two socket quad core server with 8 GB RAM.
Turned out there is some sort of inane design bug in SQL Server where you have to have multiple .mdf files for there to be multiple threads. Do an Internet search on this. It is mind-boggling that this would be the case, but apparently it is. If your DB has only one .mdf file, you will get one thread, no matter how many cpus you have or what you set you max parallelism setting to be.
Crazy, but that’s microsquish for you.