| Author |
Topic |
|
wisertime
Starting Member
28 Posts |
 Posted - 2008-05-07 : 11:04:03
|
| I have a very small system with only one cpu and about 8 -10 users accessing SQL from a front end app. They are experiencing extremely slow times while running reports during the day. sqlserv.exe uses about 99% of the CPU during these slow times and I cannot figure out why. Here is what I have so far.The processor queue length is consistently above 5. The Sqlagent Alert Engine is the biggest hog of CPU (at about 50,000 now). There are no long running jobs and our tables are re-indexed and re-org'd weekly. We restarted the box yesterday and it ran smoothly until later in the day, when it became slow again. I am planning to restart SQL soon, but would like to see if there is anything that I can configure/research to determine a possible solution.Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-07 : 11:09:18
|
| http://www.sqlsolutions.com/articles/articles/Diagnosing_Database_Performance_Problems_with_SQL_Profiler_and_Lakeside_SQL_Trace_Analyzer.htmhttp://vyaskn.tripod.com/analyzing_profiler_output.htm |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-05-07 : 11:11:04
|
| What service pack level do you have? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-07 : 11:15:12
|
| your Processor queue length shows you have CPU bottleneck.What about %processor time and Memory counters? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-07 : 11:17:34
|
| Here is the article i like most about correlating performance counters with Profilerhttp://www.sql-server-performance.com/article_print.aspx?id=785&type=art |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-05-07 : 11:19:59
|
| If you are measuring the CPU used by Sqlagent Alert Engine with sp_who2, then you are looking at a red herring. That shows the cummulative CPU used by the connection for the life of the connection. in this case, it would likely be since the last reboot.Some things to look for are:1) SQL Slammer virus: You need to have at least SP3a applied, but you would be better off with SP4, since SP3a is not even supported anymore.2) Excessive table scans: Fire up profiler to see if individual queries are using more than say 100 CPU. note that the Logoff event shows the cummulative CPU for the life of the connection, as well, so that is not a good guide as to where to invest your time. |
|
|
|
wisertime
Starting Member
28 Posts |
Posted - 2008-05-07 : 11:21:33
|
User time spikes consistently from around 20% up to 50% when the queue length jumps to 20. Privelaged time stays around 10%.Memory paging usually stays around 5-10 pps. But occasionally jumps well over 25.quote:
Originally posted by sodeep
your Processor queue length shows you have CPU bottleneck.What about %processor time and Memory counters?
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-07 : 11:34:13
|
| Give us info about your service packs and is this server dedicated to SQL server only or any other apps? |
|
|
|
wisertime
Starting Member
28 Posts |
Posted - 2008-05-07 : 11:44:35
|
Well, on the box itself it says that it has SP4. But I have a tool from Idera (Patch analyzer) says that it only has SP3. Which confuses me.We have Tivoli Storage on this box and SQL safe backup services and diagnostics services from Idera. There is only one application that uses SQL heavy enough to mention, which does use some decent sized queries. The SQL workload from apps just seems to little to cause such high cpu usage.quote:
Originally posted by sodeep
Give us info about your service packs and is this server dedicated to SQL server only or any other apps?
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-07 : 11:51:00
|
| I would correlate SQL profiler with Performance monitor and know exactly what is going on |
|
|
|
wisertime
Starting Member
28 Posts |
Posted - 2008-05-07 : 11:58:36
|
Both the Idera tool and xp_msver tells me that it still has SP3 installed on it. I am going to install SP4 to make sure that this is the SP it has. Not sure why the box tells me it has Sp4.quote:
Originally posted by sodeep
I would correlate SQL profiler with Performance monitor and know exactly what is going on
|
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-05-07 : 13:22:33
|
| If it is a Windows 2000 server, it may be that the OS is at SP4, while SQL Server is at SP3. |
|
|
|
|
CPU Usage Problem.