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
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.
Hi Thanks for your response , I've changed the duration times around , I posted incorrectly earlier . Please read first post again , thanks
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.
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.