Processor performance issues

I was hoping someone out there can give me some direction on some performance issues I've been having with SQL Server 2005. I have two SQL Servers instances configured almost identically (or so it seems) on two different machines.

I have a certain data process initiated from a database aplication that is giving me two different results depending on which sever processes it. One of my servers is taking considerable more processor time and effort than the other running at 100% utilization for a long period of time. I have double checked the settings on both SQL Server instances and there are very few differences. The Maximum worker threads on the poor one is set to 1024 while the better server is set to automatic (0). That is the only differnce I have found in the settings of these two servers and it's my understanding that I should not see a huge performance difference due to this setting.

I'm a bit at my wits end here and really need some more ideas at what other factors that I am not taking into account could effect Processor utilization.

Hi Joe,

you will need to run the explain on each machine and review the plan created by the optimiser for any piece of sql you think is running too slowly. If you have the same piece of sql that performs differently on the two machines even better.

It is not unusual for small differences in number of rows or an index that should be there that is not to significantly influence performance.

Alas, I wish there were more documentation around on how the optimiser chooses access paths but there seems to be not a lot of it around.......

(PS. In 2005 you generate plans from sql server studio in case you are not aware...)

Best Regards

Peter

|||

Try running these queries on both systems, to give a better idea of what is going on:

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms /SUM(wait_time_ms)OVER()AS pct,

ROW_NUMBER()OVER(ORDERBY wait_time_ms DESC)AS rn

FROMsys.dm_os_wait_stats

WHERE wait_type NOTLIKE'%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s ASDECIMAL(12, 2))AS wait_time_s,

CAST(W1.pct ASDECIMAL(12, 2))AS pct,

CAST(SUM(W2.pct)ASDECIMAL(12, 2))AS running_pct

FROM Waits AS W1

INNERJOIN Waits AS W2

ON W2.rn <= W1.rn

GROUPBY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVINGSUM(W2.pct)- W1.pct < 90 -- percentage threshold

ORDERBY W1.rn;

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT'%signal (cpu) waits'=CAST(100.0 *SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2)),

'%resource waits'=CAST(100.0 *SUM(wait_time_ms - signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

FROMsys.dm_os_wait_stats;

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROMsys.dm_os_schedulers

WHERE scheduler_id < 255