SQL Server Performance

sql statement performanc disparity

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Jack Vamvas, Apr 25, 2008.

  1. Jack Vamvas Member

    Hi
    I have a sql statement running on 2 different servers , and getting a performance disparity.
    SERVER 1 - Windows 2003 Service Pack 1 , sql server 2000 Enterprise Edition Service Pack 4 , 16GB RAM - 16 processors
    SERVER 2 - Windows 2003 Service Pack 1 , sql server 2000 Enterprise Edition Service Pack 4 , 4GB RAM - 8 processors
    The database on SERVER 2 is a straight BACKUP/RESTORE from Server 1
    Server 1 executes the sql in 3500 ms , Server 2 executes the sql in 1438 .
    I run the DBCC DROPCLEANBUFFERS ,DBCC FREEPROCCACHE before every run
    The reads are almost the same . I've checked Execution Plans , everything is hitting Index Seek , and there are no obvious delays , everything is completely reindexed, but really the main issue is the duration disparity.
    Can anyone give me some systematic direction on how to troubleshoot , when there is a performance disparity b/w 2 machines
  2. Adriaan New Member

    What do you feel to be a disparity here?
    Server 1 has twice as many processors, and takes just under half the time that Server 2 does.
    You're sort of lucky that there does not appear to be unwanted parallellism on Server 1: SQL 2000 sometimes sets too many processors to work on relatively simple queries, which may result in very bad performance. Try adding OPTION (MAXDOP 1) as the very last clause in the SELECT query, see if that makes a difference.
    Besides the number of processors, is the hardware completely identical?
    The amount of RAM shouldn't make much of a difference.
    You should perhaps compare in a restored database on Server 1 too.
  3. Jack Vamvas Member

    Hi
    Thanks for your response , I've changed the duration times around , I posted incorrectly earlier . Please read first post again , thanks
  4. Adriaan New Member

    Then let me repeat my suggestion:
    Try adding OPTION (MAXDOP 1) as the very last clause in the SELECT query, see if that makes a difference.
  5. Jack Vamvas Member

    I've tried adding OPTION (MAXDOP 1) and in fact it's a few milliseconds slower?
  6. Adriaan New Member

    OK - then I'd start looking at differences in the hardware. Perhaps there's a disk array involved that is not properly setup for database usage - any sort of bottleneck.
    There is an excellent step-by-step performance troubleshooting document available from the main page of this site that covers pretty much everything from T-SQL to hardware.

Share This Page