SQL Server Performance

SPROC Executes Much Faster on LocalHost

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by chammock, Sep 3, 2007.

  1. chammock New Member

    I have a high end dedicated server for a web application under construction. Full MS SQL Server 2005, 4GB RAM, Quad Xeon, etc.
    With no users on the application, if I run a certain SPROC it takes about 30 seconds. In this instance, it builds 48 records in one table (participants) and then from 71 to 196 detail records for each participant record in another table with only a few columns. In this test, it builds slightly less than 5,000 detail records.
    I was working with a developer on his local machine where he has SQL Server Express 2005. He was able to run the same test for the SPROC and created all the nearly 5,000 records in just a couple of seconds. Big difference between 30 sec and 2 secs. We thought it must have not built the records correctly, but they are all there.
    So what is up with that? Is there some basic configuration that is not allowing the SQL Server to take advantage of the full resources of the dedicated server? When I run the SPROC on the server, it only shows about 1-2% CPU load on the SQLServer process.
    Anytime I ask people, they want to start talking about tuning the query, etc. But it looks like this is a hardware / software config issue...doesn't it??
    Thanks for any help you can provide on what is probably a pretty basic question.
    Cliff
  2. ghemant Moderator

    Welcome aboard!!!
    first question, is this SQL Server is dedicated one or IIS server is also installed on the same box on which you have SQL Server!!! What is edition of SQL Server ? Did you say SQL Server 2005 Enterprise edition when you say Full SQL Server 2005?
    Ensure you have updated your box with latest service pack and hotfix.
  3. satya Moderator

  4. Adriaan New Member

    If the localhost is a single-processor machine, it could also be unwanted parallellism. Try executing the main query/queries with OPTION (MAXDOP 1) as the very last part of each SELECT statement.

Share This Page