only 1 out of 8 CPU maxed out

Hello
I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
running just fine but only one out of the 8 CPUs is running at 90%+
constantly. So avg is OK bit our alerting doesn't understand this.
Any way of determining what is running on that 1 CPU? This does not look
normal.
We dont have CPU affinitised and SQL Server is the only app on the box.
Are there some relevant perfmon counters I can check
tks
--
-- cranfield, DBAThis is not unusual. You have a SQL process that is not parallelizable.
Probably something with derived columns, cursors, or intense calculations.
Those are the "usual suspects" when it comes to single-threaded apps. SQL
runs processes across multiple processors where possible, but it is not
always possuble.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> Hello
> I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> running just fine but only one out of the 8 CPUs is running at 90%+
> constantly. So avg is OK bit our alerting doesn't understand this.
> Any way of determining what is running on that 1 CPU? This does not look
> normal.
> We dont have CPU affinitised and SQL Server is the only app on the box.
> Are there some relevant perfmon counters I can check
> tks
> --
> -- cranfield, DBA|||Thanks for the reply.
We have complete mirrored/parallel systems and on ServerB (which is
identical to the ServerA in question) we see similar CPU profile where 1 of
the CPUs spikes. Its not as pronounced as ServerA and load seems to be much
more evenly shared across the CPUs.
ServerA - CPU 0,1,2,3,4,5,7 - avg 5-10%; CPU6 - avg 90%
ServerB - CPU 0,1,2,3,4,5,7 - avg 10-30%; CPU6 - avg 60%
* exact same hardware
* exact same load (mostly continuous, batched, 1024 row bulk inserts)
*
I wonder what is causing the difference. Obviously I would prefer them to
use all CPUs.
--
-- cranfield, DBA
"Geoff N. Hiten" wrote:
> This is not unusual. You have a SQL process that is not parallelizable.
> Probably something with derived columns, cursors, or intense calculations.
> Those are the "usual suspects" when it comes to single-threaded apps. SQL
> runs processes across multiple processors where possible, but it is not
> always possuble.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> > Hello
> >
> > I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> > running just fine but only one out of the 8 CPUs is running at 90%+
> > constantly. So avg is OK bit our alerting doesn't understand this.
> >
> > Any way of determining what is running on that 1 CPU? This does not look
> > normal.
> >
> > We dont have CPU affinitised and SQL Server is the only app on the box.
> >
> > Are there some relevant perfmon counters I can check
> >
> > tks
> > --
> > -- cranfield, DBA
>|||If it's always the same CPU I'd be a bit suspicious. Check for an affinity
mask. What Sql Server product/licence are you using?
We get worse performance with parallel query. Although we have maxdup at 1
we still use all 8 cpus.
Regards
Paul Cahill
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:563A3C37-DDD5-4816-8898-C74966E33C00@.microsoft.com...
> Thanks for the reply.
> We have complete mirrored/parallel systems and on ServerB (which is
> identical to the ServerA in question) we see similar CPU profile where 1
> of
> the CPUs spikes. Its not as pronounced as ServerA and load seems to be
> much
> more evenly shared across the CPUs.
> ServerA - CPU 0,1,2,3,4,5,7 - avg 5-10%; CPU6 - avg 90%
> ServerB - CPU 0,1,2,3,4,5,7 - avg 10-30%; CPU6 - avg 60%
> * exact same hardware
> * exact same load (mostly continuous, batched, 1024 row bulk inserts)
> *
> I wonder what is causing the difference. Obviously I would prefer them to
> use all CPUs.
> --
> -- cranfield, DBA
>
> "Geoff N. Hiten" wrote:
>> This is not unusual. You have a SQL process that is not parallelizable.
>> Probably something with derived columns, cursors, or intense
>> calculations.
>> Those are the "usual suspects" when it comes to single-threaded apps.
>> SQL
>> runs processes across multiple processors where possible, but it is not
>> always possuble.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
>> > Hello
>> >
>> > I'm getting CPU alerts from one of our SQL servers. Problem is that SQL
>> > is
>> > running just fine but only one out of the 8 CPUs is running at 90%+
>> > constantly. So avg is OK bit our alerting doesn't understand this.
>> >
>> > Any way of determining what is running on that 1 CPU? This does not
>> > look
>> > normal.
>> >
>> > We dont have CPU affinitised and SQL Server is the only app on the box.
>> >
>> > Are there some relevant perfmon counters I can check
>> >
>> > tks
>> > --
>> > -- cranfield, DBA
>>|||What version of SQL Server and Service pack? The way in which the
connections are tied to schedulers is different in both with 2000 having
more issues. How many concurrent connections do you have and what processors
are most of them on?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> Hello
> I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> running just fine but only one out of the 8 CPUs is running at 90%+
> constantly. So avg is OK bit our alerting doesn't understand this.
> Any way of determining what is running on that 1 CPU? This does not look
> normal.
> We dont have CPU affinitised and SQL Server is the only app on the box.
> Are there some relevant perfmon counters I can check
> tks
> --
> -- cranfield, DBA|||morning!
Both servers on SP2 + hotfix:
SQL Server 2005 - 9.00.3152.00 (Intel X86) Enterprise Edition on Windows NT
5.2 (Build 3790: Service Pack 2)
* sp_who2 shows 69 SPIDs (28 active)
How do I know which processers my connections are on?
tks
--
-- cranfield, DBA
"Andrew J. Kelly" wrote:
> What version of SQL Server and Service pack? The way in which the
> connections are tied to schedulers is different in both with 2000 having
> more issues. How many concurrent connections do you have and what processors
> are most of them on?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> > Hello
> >
> > I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> > running just fine but only one out of the 8 CPUs is running at 90%+
> > constantly. So avg is OK bit our alerting doesn't understand this.
> >
> > Any way of determining what is running on that 1 CPU? This does not look
> > normal.
> >
> > We dont have CPU affinitised and SQL Server is the only app on the box.
> >
> > Are there some relevant perfmon counters I can check
> >
> > tks
> > --
> > -- cranfield, DBA
>