SQL Server 2008 running 50% faster on desktop than server - what to look for?

  • Lynn Pettis (2/20/2010)


    Too lazy to look back, did you post the table and index definitions and the actual execution plans from running the queries on the two systems? You can save the graphical plans as .sqlplan files, zip them up together and post them. This will help us figure out what maybe happening.

    Yes, they have been posted. Here is the link to the post: http://www.sqlservercentral.com/Forums/FindPost867198.aspx

    And you're right, that was very lazy.

    Paul

  • isuckatsql (2/20/2010)


    Defragged all drives and rebooted the server, no improvement in query performance 🙁

    Was the disk defrag performed while sql server was stopped?

    Second, did you try creating a clustered index on the table?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (2/20/2010)


    isuckatsql (2/20/2010)


    Defragged all drives and rebooted the server, no improvement in query performance 🙁

    Was the disk defrag performed while sql server was stopped?

    Second, did you try creating a clustered index on the table?

    Actually no, Sql Server was still running. Should i have stopped it prior to the defrag?

    I did not create a clustered index on the table, because i am trying to compare apples with apples and determine why the desktop is running the query faster that the server.

    The query performance is not my goal, server performance is my goal.

  • isuckatsql (2/20/2010)


    CirquedeSQLeil (2/20/2010)


    isuckatsql (2/20/2010)


    Defragged all drives and rebooted the server, no improvement in query performance 🙁

    Was the disk defrag performed while sql server was stopped?

    Second, did you try creating a clustered index on the table?

    Actually no, Sql Server was still running. Should i have stopped it prior to the defrag?

    I did not create a clustered index on the table, because i am trying to compare apples with apples and determine why the desktop is running the query faster that the server.

    The query performance is not my goal, server performance is my goal.

    I would suggest you check the fragmentation level of your tables on both your desktop and on your server. I suspect the table on the server is more fragmented than your desktop. Thus you may not be comparing apples to apples. The only way to defrag that is to add a clustered index to the table.

    In order to defragment a drive that has data files on it, you should stop sql server services.

    Also, are your disks aligned on the server?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • isuckatsql (2/20/2010)


    CirquedeSQLeil (2/20/2010)


    isuckatsql (2/20/2010)


    Defragged all drives and rebooted the server, no improvement in query performance 🙁

    Was the disk defrag performed while sql server was stopped?

    Second, did you try creating a clustered index on the table?

    Actually no, Sql Server was still running. Should i have stopped it prior to the defrag?

    I did not create a clustered index on the table, because i am trying to compare apples with apples and determine why the desktop is running the query faster that the server.

    The query performance is not my goal, server performance is my goal.

    Actually, both are the same.

  • Lynn Pettis (2/20/2010)


    Actually, both are the same.

    Both what? Query performance and server performance? :blink: :unsure:

    The whole thread is about why a given query appears to run faster on a desktop installation than on a server.

    Given the same plan on both, it seems reasonable to investigate hardware and configuration differences to explain the anomaly...?

    Paul

  • I got this query from the Blog of Jimmy May. This will show all your waitstats. Also look at this Link.

    http://blogs.msdn.com/jimmymay/archive/2008/10/30/drum-roll-please-the-debut-of-the-sql-dmv-all-stars-dream-team.aspx

    There is a Hyperlink on "DMV All-Stars Dream Team" Download the SQL and try running the Virtual File Stats. This will show if you have any IO latency on the server.

    The query provided will show all your WAITSTATS

    SELECT * , (wait_time_ms - signal_wait_time_ms) as resource_wait_time_ms

    FROM sys.dm_os_wait_stats

    ORDER BY resource_wait_time_ms DESC

    -Roy

Viewing 7 posts - 61 through 66 (of 66 total)

You must be logged in to reply to this topic. Login to reply