Prev: Transaction_Status2
Next: Trouble Installing Cumulative Update 2 to SQL Server 2008 SP1, Hotfix KB958186
From: Uri Dimant on 5 Nov 2009 02:39
Steven
In addition to others
---This first thing to check if CPU is at 100% is to look for parallel
queries:

-- Tasks running in parallel (filtering out MARS requests below):

select * from sys.dm_os_tasks as t

where t.session_id in (

select t1.session_id

from sys.dm_os_tasks as t1

group by t1.session_id

having count(*) > 1

and min(t1.request_id) = max(t1.request_id));

-- Requests running in parallel:

select *

from sys.dm_exec_requests as r

join (

select t1.session_id, min(t1.request_id)

from sys.dm_os_tasks as t1

group by t1.session_id

having count(*) > 1

and min(t1.request_id) = max(t1.request_id)

) as t(session_id, request_id)

on r.session_id = t.session_id

and r.request_id = t.request_id;





"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!
>
>
>


From: TheSQLGuru on 6 Nov 2009 16:30
So, you are seeing all of the indicators you list below AND you state
elsewhere that CPUs run 80+% regularly. Exactly how many indicators do you
NEED before you accept that you do have CPU pressure and start looking for
ways to rectify said problem?? :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"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!
>
>
>


From: Steven on 19 Nov 2009 16:43
Sorry it took so long to get back to you all, but thanks everybody for your
responses. I will be adding more servers to our SQL Farm.


"TheSQLGuru" <kgboles(a)earthlink.net> wrote in message
news:8vadnV5j1MRPCWnXnZ2dnUVZ_gudnZ2d(a)earthlink.com...
> So, you are seeing all of the indicators you list below AND you state
> elsewhere that CPUs run 80+% regularly. Exactly how many indicators do
> you NEED before you accept that you do have CPU pressure and start looking
> for ways to rectify said problem?? :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "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!
>>
>>
>>
>
>


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