Solved

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

Posted on 2011-09-27
16
1,307 Views
Last Modified: 2012-05-12
Hello,
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:

SELECT *

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.
0
Comment
Question by:4rg
  • 8
  • 8
16 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 36708902
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...?

0
 
LVL 1

Author Comment

by:4rg
ID: 36708963
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.
0
 
LVL 1

Author Comment

by:4rg
ID: 36709144
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.
Lake-Orion-SQL-highCPUusage-Quer.csv
0
 
LVL 18

Expert Comment

by:x-men
ID: 36709483
try loading the .trc in the database Tunning advisor and see if it returns any sugestions.
0
 
LVL 18

Expert Comment

by:x-men
ID: 36709503
check the SQL memory, target vs total and pagelife expectancy
check for autogrows
0
 
LVL 1

Author Comment

by:4rg
ID: 36709647
Will do now.
0
 
LVL 1

Author Comment

by:4rg
ID: 36709835
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:
http://i139.photobucket.com/albums/q289/p3990013/BLOG/Advanced.jpg
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!
 
0
 
LVL 18

Accepted Solution

by:
x-men earned 500 total points
ID: 36710122
this will help with DTA: http://msdn.microsoft.com/en-us/library/ms166575.aspx
and here:
http://www.zimbio.com/SQL/articles/655/How+Tune+Database+Using+Database+Tuning+Advisor

Memory:
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.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:4rg
ID: 36710567
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?
J.

0
 
LVL 18

Expert Comment

by:x-men
ID: 36710759
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).
0
 
LVL 1

Author Comment

by:4rg
ID: 36714210
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.
J
0
 
LVL 18

Expert Comment

by:x-men
ID: 36715513
"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.
0
 
LVL 1

Author Comment

by:4rg
ID: 36775923
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?
0
 
LVL 18

Expert Comment

by:x-men
ID: 36813560
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.
0
 
LVL 18

Expert Comment

by:x-men
ID: 36813568
besides, you would have to include these new indexes in the replication for them to be created on the subscribers.
0
 
LVL 1

Author Closing Comment

by:4rg
ID: 36817154
You have been very helpful and I appreciate it very much. Thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now