SQL Server Performance

What could be slowing machine

Discussion in 'Performance Tuning for Hardware Configurations' started by DavidHadley, Jun 28, 2005.

  1. DavidHadley New Member


    A Client has a machine :
    4 x 3ghz Zeon
    4 gb Memory
    Windows Enterprise Server 2003
    SQL Server Enterprise Sp3
    (it also has some form of Raid which I am waiting for more details on)
    The db is about 1gb on a 20gb drive with 16gb free.

    which takes 191 seconds to run a particular query. This machine is doing nothing else apart from running SQL Server, not even Anti Virus software.

    My local machine :
    1 x 3ghx P4
    1 gb Memory
    Windows XP Pro Sp2
    SQL Server Personnal edtion Sp3

    takes 73 seconds to run exactly the same query.

    Both machines have the same database on them and the query was run through Query Analyser. On paper the client's machine should be upto 4x faster rather than almost 3x slower. The client has had a 3rd party check the machine who say it is working fine.

    Does anybody have any ideas what could be causing this kind of difference?
  2. ranjitjain New Member

    i feel check in the profiler by running a trace.
    Also check the task manager by any chance resources are being used.
    check the performance monitor which is handy in this case.
  3. satya Moderator

  4. joechang New Member

    does the query in question show a parallel execution plan?
    each parallel plan needs to be verified individually,
    try running at different settings for OPTION (MAXDOP x)
    some will show good gains, even better than linear, other will show degradation
  5. deepakontheweb New Member

    Run performance monitor on both machine while query is executing.. also either restart both machine prior to executing or to clear the buffers, run below two DBCC commands..


    MemoryAvailable MBytesMemoryPages/sec
    Paging File _total
    SQL Data disk : Avg. Disk Queue Length
    SQL Data disk : Disk Reads/sec
    SQL Data disk : Disk Writes/sec
    SQL Log disk : Avg. Disk Queue Length
    SQL Log disk :Disk Reads/sec
    SQL Log disk :Disk Writes/sec
    Processor(_Total)% Processor Time
    SQLServer:Buffer ManagerBuffer cache hit ratio
    SQLServer:Buffer ManagerProcedure cache pages
    SQLServer:General StatisticsUser Connections
    SQLServer:Locks(_Total)Number of Deadlocks/sec
    SystemContext Switches/sec
    SystemProcessor Queue Length

    Also in SQL profiler, select "Execution plan" from Performance event category.. this will help to understand better..how query is being optimized by SQL Engine.

    Check for Parallel Execution settings as given linkhttp://support.microsoft.com/default.aspx?scid=kb;en-us;243589 says : -

    Parallel Execution

    If you are using a multiprocessor computer, you can also investigate whether a parallel plan is in use. If parallelism is in use, you see a PARALLELISM (Gather Streams) event. If a particular query is slow when it is using a parallel plan, you can try forcing a non-parallel plan by using the OPTION (MAXDOP 1) hint. See the "SELECT (T-SQL)" topic in SQL Server 7.0 Books Online for more details.

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  6. DavidHadley New Member

    Many thanks for all the responses.

    Embarassingly it turns out I didn't have the same database , but one that was three weeks old. Once I had the uptodate database the timings were pretty much identical.
  7. satya Moderator

    Ensure to maintian regular database consistency checks and update statistics to maintin optimum performance.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page