Solved

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

Posted on 2011-09-27
16
1,301 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

18 Experts available now in Live!

Get 1:1 Help Now