Prev: Transaction_Status2
Next: Trouble Installing Cumulative Update 2 to SQL Server 2008 SP1, Hotfix KB958186
From: Steven on 4 Nov 2009 15:16
The top wait_type on my SQL 2005 x64 Standard box is SOS_Scheduler_Yield.
I've read that this may indicate a CPU bottleneck. I've also run the
following query

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

and found that runnable_tasks_count is often above zero, indicating cpu
pressure.

I've also read that CPU pressure can indicate a memory or IO bottleneck.
I've checked Page Life Expectancy and Buffer cache hit ratio and the values
seem fine

PLE - fluctuates between 8000 and 15000
Buffer Cache Hit Ratio - 99-100%

Other counters I checked.
Memory Grants Pending - 0
Lazy Writes/Sec - 0
Avg Disk sec/Read on the data drive- .02-.03 seconds
Avg Disk sec/Write on the data drive - .02-.03 seconds

Are there any other counters, I can look at to determine if I have a memory
or IO bottleneck, rather than a CPU bottleneck?

Thanks!



From: Dinesh on 4 Nov 2009 15:28
SOS_Scheduler_Yield waittype can be ignored as everytime the thread is
yielded you will see that wait_type. Why do you think there is a CPU
bottleneck. Have you checked the CPU idle time? What is the CPU
utilization, and how much of it is SQL Server's utilization?
From: Steven on 4 Nov 2009 15:40
CPU Time is generally over 80 %, while %Idle time is under 20 %

Just about all of CPU time is the SQL Server Process.

Thanks,
Steven


"Dinesh" <dineshbabu.munugala(a)gmail.com> wrote in message
news:a07065e0-af33-4013-891d-beec95269495(a)v15g2000prn.googlegroups.com...
> SOS_Scheduler_Yield waittype can be ignored as everytime the thread is
> yielded you will see that wait_type. Why do you think there is a CPU
> bottleneck. Have you checked the CPU idle time? What is the CPU
> utilization, and how much of it is SQL Server's utilization?


From: Linchi Shea on 4 Nov 2009 16:48
If you see a lot of waits on SOS_Scheduler_Yield, it's often an indicator of
the SQL schedulers being busy, which often, but not all the time, means the
workload is keeping the CPUs busy, i.e. CPU pressure.

Linchi

"Steven" wrote:

> The top wait_type on my SQL 2005 x64 Standard box is SOS_Scheduler_Yield.
> I've read that this may indicate a CPU bottleneck. I've also run the
> following query
>
> SELECT scheduler_id, current_tasks_count, runnable_tasks_count
>
> FROM sys.dm_os_schedulers
>
> WHERE scheduler_id < 255
>
> and found that runnable_tasks_count is often above zero, indicating cpu
> pressure.
>
> I've also read that CPU pressure can indicate a memory or IO bottleneck.
> I've checked Page Life Expectancy and Buffer cache hit ratio and the values
> seem fine
>
> PLE - fluctuates between 8000 and 15000
> Buffer Cache Hit Ratio - 99-100%
>
> Other counters I checked.
> Memory Grants Pending - 0
> Lazy Writes/Sec - 0
> Avg Disk sec/Read on the data drive- .02-.03 seconds
> Avg Disk sec/Write on the data drive - .02-.03 seconds
>
> Are there any other counters, I can look at to determine if I have a memory
> or IO bottleneck, rather than a CPU bottleneck?
>
> Thanks!
>
>
>
> .
>
From: Greg Linwood on 5 Nov 2009 01:16
Hi Steven

High CPU utilisation is always a consequence of underlying activity in the
DB so you need to get down to the source of the problem by looking at SQL
Profiler or SQL Trace and see which SQL commands / stored procedures are
running either inefficiently or too frequently and generating the workload.

It also pays to ensure that whatever CPU utilisation is occuring on the
server actually relates to SQL Server - you do this by comparing the
"sqlservr" & "total" instances of the process\%ProcessorTime perfmon
counter. If there's a big gap between the two, something other than SQL
Server is consuming significant CPU resource

Regards,
Greg Linwood
SQL Server MVP

"Steven" <Sykong(a)hotmail.com> wrote in message
news:%23ZRs9uYXKHA.4780(a)TK2MSFTNGP05.phx.gbl...
> The top wait_type on my SQL 2005 x64 Standard box is SOS_Scheduler_Yield.
> I've read that this may indicate a CPU bottleneck. I've also run the
> following query
>
> SELECT scheduler_id, current_tasks_count, runnable_tasks_count
>
> FROM sys.dm_os_schedulers
>
> WHERE scheduler_id < 255
>
> and found that runnable_tasks_count is often above zero, indicating cpu
> pressure.
>
> I've also read that CPU pressure can indicate a memory or IO bottleneck.
> I've checked Page Life Expectancy and Buffer cache hit ratio and the
> values seem fine
>
> PLE - fluctuates between 8000 and 15000
> Buffer Cache Hit Ratio - 99-100%
>
> Other counters I checked.
> Memory Grants Pending - 0
> Lazy Writes/Sec - 0
> Avg Disk sec/Read on the data drive- .02-.03 seconds
> Avg Disk sec/Write on the data drive - .02-.03 seconds
>
> Are there any other counters, I can look at to determine if I have a
> memory or IO bottleneck, rather than a CPU bottleneck?
>
> Thanks!
>
>
>

 |  Next  |  Last
Pages: 1 2
Prev: Transaction_Status2
Next: Trouble Installing Cumulative Update 2 to SQL Server 2008 SP1, Hotfix KB958186