Hi,

Im looking at my server task manager and im seeing the SQLSERVR.exe procees
spike up to 90% every min for a few seconds. Connection to my DB freeze
during this time. I run the profiler looking for queries that take more than
5 seconds to run but not much appears (i.e i can see reports run for 2 mins
for example that can cause the same problem but this is not the problem in
this case).

Something is causing the leap in CPU usage, task manager IDs the SQLSERVER
process, profiler does not ID a query. What else can i examine ?

SQL standard 2005
Windows 2003 server
8 GB Ram
4 X dual core 2.4

How can i examine how efficent the write to the MDF LDF physical disks are
for example ?

Thanks for any at all.
Scott

Do you have replication on your server? Large replicated volumes will cause
very high cpu taken by server but won't show up in profiler.

Gene.

"Scott" wrote:

> Hi,
>
> Im looking at my server task manager and im seeing the SQLSERVR.exe procees
> spike up to 90% every min for a few seconds. Connection to my DB freeze
> during this time. I run the profiler looking for queries that take more than
> 5 seconds to run but not much appears (i.e i can see reports run for 2 mins
> for example that can cause the same problem but this is not the problem in
> this case).
>
> Something is causing the leap in CPU usage, task manager IDs the SQLSERVER
> process, profiler does not ID a query. What else can i examine ?
>
> SQL standard 2005
> Windows 2003 server
> 8 GB Ram
> 4 X dual core 2.4
>
> How can i examine how efficent the write to the MDF LDF physical disks are
> for example ?
>
> Thanks for any at all.
> Scott
>
>
>

Well something is using the resources so you should be able to identify what
is using the most CPU. Don't look at duration but more at CPU since that is
what is spiking. Run a trace for a while and then look for the CPU spikes
or large consumptions. I don't think it is disk related since if the disks
are a bottleneck you will usually have low CPU since they are always waiting
on the disks. But for FYI you can look at the filestats and waitstats DMV's
to get some pretty good info as to what is the bottleneck etc. Have a look
at these:

http://www.microsoft.com/technet/pro...ts_queues.mspx

http://www.microsoft.com/technet/pro.../tsprfprb.mspx

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Scott" <scott_lotus@yahoo.co.uk> wrote in message
news:OKklFwMFJHA.4784@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> Im looking at my server task manager and im seeing the SQLSERVR.exe
> procees spike up to 90% every min for a few seconds. Connection to my DB
> freeze during this time. I run the profiler looking for queries that take
> more than 5 seconds to run but not much appears (i.e i can see reports run
> for 2 mins for example that can cause the same problem but this is not the
> problem in this case).
>
> Something is causing the leap in CPU usage, task manager IDs the SQLSERVER
> process, profiler does not ID a query. What else can i examine ?
>
> SQL standard 2005
> Windows 2003 server
> 8 GB Ram
> 4 X dual core 2.4
>
> How can i examine how efficent the write to the MDF LDF physical disks are
> for example ?
>
> Thanks for any at all.
> Scott
>

thanks for the reply. No replication ... was thinking about it however.

thanks for the reply Andrew.

Starting to think it maybe is the disks.The CPU spike does not seem to be as
prevelant as the problem. Im examining disk usage via the performance
monitor and the spike is more closely matched to this (although im not l00%
sure what counter is best to use).

Thank you for the links, they look very interesting.

All the best.