Running a lengthy query on SQL Server 2005

Tags:
Query
SQL Server 2005
SQL Server performance
Windows Server 2003
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?
ASKED: July 16, 2008  8:21 PM
UPDATED: May 28, 2009  7:54 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    65,450 pointsBadges:
    report
  • Gaiusgracchus
    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.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following