Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

The Windows XP configuration is a generic computer with a Pentium D 2.8GHz, 1GB of RAM, with Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Build 3790: Service Pack 2)

The Windows Server 2003R2 is a Dell PowerEdge 2950, with a Pentium Xeon quadcore 2.0GHz, 14GB of RAM, the same SQL Server than previous one.

The database is 4GB, running an stored procedure.

The elapsed time to complete the process is: XP = 14 min. 2k3 = 38 min.

In both cases the SQL server is configure to use all cores. There is no other task running.

How can we increase performance in the Server to out perform the XP box?

share|improve this question
    
Also: is there any other load on the server vs the desktop? –  Joel Coel Jun 9 '09 at 18:01

3 Answers 3

up vote 1 down vote accepted

What do you have your max degrees of parallelism set to on the server? Have you tried tweaking that for the query in question?

http://support.microsoft.com/default.aspx/kb/329204

When you run the query on the quad-core box, do you see all the cores light up busy?

share|improve this answer
    
Yes, all fire up, but eventually two remain almost inactive during the process. The SQL Server is configure with the Automatically set processor affinity mask for all processors and Automatically set I/O affinity mask for all processors set active. Additionally the Boost SQL Server priority is activated. –  Gilberto Ibarra Jun 9 '09 at 18:16
1  
I would try the query with the parallelism override in place set to your number of cores just to compare the results. –  Kevin Kuphal Jun 9 '09 at 18:21
    
Can't used with stored procedure. Is the same if I disable all but one processor in the server configuration? –  Gilberto Ibarra Jun 9 '09 at 20:53
    
It is entirely possible that your query is actually hampered by using multiple processors due to cache synchronization, context switches, etc. You could try to set the parallelism of the server to 1 and see what happens. That should give you an apples to apples comparison of the two boxes. Then you could increase it and test again to see where the performance is the best. –  Kevin Kuphal Jun 9 '09 at 21:00
    
We are running more tests but it worked. Now the XP takes 8 min. and the server (with one core enable) 6 min. –  Gilberto Ibarra Jun 9 '09 at 23:03

Run Performance Monitor on both computers and start by measuring the CPU, average disk queue length and disk bytes by second. Comparing the two should quickly identify the bottleneck.

JR

share|improve this answer
    
Average disk queue on the server is always on top when we fireup the stored procedure. We just update the driver for the SAS 6/iR controller (There is no RAID and we use two 500GB SATA HDD 7.2K rpm). We are checking right now in the XP machine. –  Gilberto Ibarra Jun 9 '09 at 18:24
    
What sort of Disks on in the XP machine, and what do the disk queues look like when the query is running on the XP machine. –  mrdenny Jun 9 '09 at 19:53
    
Is a SATA too 320 GB partitioned in half. Same 7.2K rpm –  Gilberto Ibarra Jun 9 '09 at 20:01
    
Disk queues go to the top as in the server when we fireup the stored procedure. –  Gilberto Ibarra Jun 9 '09 at 20:02
    
I see you've resolved the problem, but in case anyone else is reading these comments, if the disk queue goes to the top of the graph you need to change the scale. The default has the top of the graph at a queue length of 1.0 and SQL Server will usually exceed this. –  John Rennie Jun 10 '09 at 7:39

14gb of RAM on the 2003 server? are you running windows X64? is the SQL server x64 as well? Also, 14GB doesn't sound like an ideal config of RAM sticks. when you boot, does the server give you a warning about ideal RAM configuration?

share|improve this answer
    
Is running in 32 bits. There is no Warning at boot time. –  Gilberto Ibarra Jun 9 '09 at 18:05
    
Sorry, my mistake, is 12 GB –  Gilberto Ibarra Jun 9 '09 at 19:05
    
11GB went unused previously. Same result. Setting now to 8GB. –  Gilberto Ibarra Jun 9 '09 at 20:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.