sqlservr.exe high cpu usage, ran query, now what?

I have XP sp3 and have been running one database for two years, now just recently the cpu usage is bouncing between 70 and 99 percent all the time.  Have scanned for viruses, rebooted, defragged, backed up the entire database and log file.

I found this answer below to another post and ran the query.  I just don't know what to do with the results.  I am running SQL Server 2005 SP2 but think the below information is good for me too.  Please advise if this query would have to be different with 2005 version.
Otherwise, the query shows one result, session_52, status suspended.  I just don't know where to go from here.
I also have trace results from the sql profiler that I ran yesterday.  Don't know what to do with those either.

Any help with be greatly appreciated.

Answer that I got query from:
1. You should start by checking what processes are currently executing on the server. If you run the statement below in SQL Management studio, it will display a list of user processes currently executing:


FROM sys.dm_exec_requests a

OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b

WHERE session_id > 50

and session_id <> @@spid

2. If nothing is currently running on the server. Open sql profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output)

RPC: Completed (Under stored procedures)

SQL: BatchCompleted (Under TSQL)

Profiling should help identify the bottleneck. You will need to look for rows which have a high cpu value.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x-menConnect With a Mentor IT super heroCommented:
this will help with DTA: http://msdn.microsoft.com/en-us/library/ms166575.aspx
and here:

you could have the memory limited, that why you should check the performance counters:

SQLServer:Memory Manager:Total Server Memory (KB) should be considerably less than:
SQLServer:Memory Manager:Target Server Memory (KB)

SQLServer:Memory Manager:Memory Grants Pending:
Memory is needed to process each user request. If enough memory is not available then the user request waits for memory which cause performance hitch in executing the query.
This can be avoided by the following
1.Adding more memory to the server
 2.Adding memory to SQL Server
 3.Creating proper Indexes

SQLServer:Buffer Manager:Page Life Expectancy: This is one counter that I would love to monitor to check the memory pressure. This would give you the time in seconds the data pages reside in the SQL Server cache. If the value is low indicates the following problems.
1.SQL Cache is cold(Discussed in Page Faults)
 2.Memory Problems
 3.Missing indexes.
 If Checkpoints/Sec, Lazywriter/Sec and Page life expectancy together is less than 300 seconds then it indicates the cause of memory pressure and your server needs more memory.
x-menIT super heroCommented:
the profiler resolts will tell you what are / is the statements that are actualy consuming the CPU.

do you have a mantenance plan onn your DB? index defrag, update stats...?

4rgAuthor Commented:
All I have from the profiler is a .trc file.  I don't know how to read it.
At least I can read the results from the query, just don't know what them mean or where to look next.

I just got involved with this customer and am waiting for their season to be over so I can add maintenance and reindex their db, etc.
Still have to deal with this issue now though.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

4rgAuthor Commented:
I should not say that I don't know how to read the .trc, I have it open in the profiler.  I just don't know what to do with the information it is giving me.  I see the cpu at 1937 and reads at 509633 every other line (these numbers vary slightly).  THey are RPC:Completed event status and text data is as follows.
declare @p6 bigint
set @p6=9223372036854775807
exec sys.sp_MSenumgenerations90 2580414,'C02EE974-CA0D-4783-82F5-6D9865178C54',0,500,3277336,@p6 output
select @p6

As far as the query results, I am attaching the results in a .csv file in case someone needs them.
x-menIT super heroCommented:
try loading the .trc in the database Tunning advisor and see if it returns any sugestions.
x-menIT super heroCommented:
check the SQL memory, target vs total and pagelife expectancy
check for autogrows
4rgAuthor Commented:
Will do now.
4rgAuthor Commented:
Thanks for responding...

Been looking for a bit, do I import the .trc file once I have the DTA open or start a new session?

I found this by John Daskalasis
"Load the trace file you already collected and set the Tuning options. To get the best set of advice and also be on the safe side (i.e. keep all your current indexes!) the Tuning Options should look like this:
Don’t forget to also choose the correct database to use for the tuning (by default it is master!).
Finally click on the “Start Analysis” button to begin. Hopefully you should get some useful recommendations. If not, you can always get a detailed profiler trace and analyze the execution plan to see why the query is not performing well J"

But nowhere does it tell me how exactly to load the .trc file.    Arggg!  Can you advise.

My memory usage does not seem out of line 1.2GB out of 3.25gb on system.
I do not know how to check for autogrows but my Database is a bit over 8gb and my log file is a bit over 4gb.
Pagelife expectancy?
Thanks again!
4rgAuthor Commented:
Thanks X-Men,
You're the best!

My Total Server Memory running at 1267456 steady.
My Target Server Memory Runs at  1622080 steady.
My Memory Grants Pending steady at 0.

Pagelife Expectancy running in low 16000's  (scale 0.1000000)
Checkpoints/Sec and Lazywriter/Sec both 0

If I am reading correctly, I do not have a memory problem.  Do you agree?

Thanks for the links for running the DTA.  They look like they will provide me with enough info to get going.
I am concerned about doing this work while the database is in use and feel like I should wait until the business is closed tonight.
Do you agree with that?

x-menIT super heroCommented:
memory is fine.

alaways better to do maintenace after buisness hours. DTA has an option to stop analyze at DATE - TIME, so you can set this for tomorrow at 8AM (doesnt mean it will take all this time, is just a precaution).
4rgAuthor Commented:
Hi X-Men,
OK, the DTA tuning is done and I have saved the recommendations.  It has 3 pages of suggested indexes and stats that it says I should create.  This database is synchronized with a master database continually.  I don't know that I can just go creating indexes and stats without creating problems.  Don't understand SQL well enough.  Also, there is a message on the Progress tab.  "All events in the workload were not analyzed.  Check tuning log for more information."  Getting kind of tired now so will look for the log in the morning.

That link from zimbio was great!

If you would like me to close this question and award the points and then start another question to analyze the results and continue, I would do that happily.  I really appreciate your input.
x-menIT super heroCommented:
"All events in the workload were not analyzed..." usually because not all entries from your trace file are related to the database you're analyzing, could be system related, could be whatever SQL server does on it's own time ;)

backup your database, be careful about the index creation and your free disk space, the report should indicate how much space each index hill take. It might be a good idea to implement those recommendations in "chunks", always backing up before each, and after business hours.
4rgAuthor Commented:
Sorry it took so long to get back to you.

I have lots of hard drive space.
My major concern right now is creating problems with the Main site that we have constant replication with.

We have three locations, One publication and two subscriptions.   This location has one of the a subscriptions and it replicates constantly with the main publication.  (Hope I phrased that right.)  
Do you know if creating these indexes will have any affect on the database we are replicating with?
x-menIT super heroCommented:
changes made in the publisher are replicated to the subscriptors, unless it's both publisher and subscriptor, it will have no reprocurtion on the main site.
x-menIT super heroCommented:
besides, you would have to include these new indexes in the replication for them to be created on the subscribers.
4rgAuthor Commented:
You have been very helpful and I appreciate it very much. Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.