Clicky

I am hoping that some of you SQL experts can help me.  We have a virtual SQL 2005 server running on Windows Server 2008 R2, the virtual platform is ESXi 4.1.  It has two virtual CPUs and 6 GB of RAM.  The two main databases we have on there are our Blackberry Server and our Fax Servers DB.

About once a week or week and a half, sqlservr.exe will shoot up and bounce between 80-90% usage until we reboot the server.  I can't figure out what is causing it.  I did read online somewhere to use the SQL Profiler while the event is happening but when I try and connect to it while it is happening, the connection just times out.  Anyone have any ideas?

asked 08/18/2011 06:53

ASA_MIS's gravatar image

ASA_MIS ♦♦


34 Answers:
Or else you can use activity monitor or the following dmv to find top cpu consuming queries.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
-- To find the performance of stored procedures by CPU time

SELECT CASE when dbid = 32767 
            then 'Resource' 
            else DB_NAME(dbid) end [DB_NAME], 
       OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME], 
       OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME], 
       SUM(usecounts) AS [Use_Count],        
       SUM(total_elapsed_time) AS [total_elapsed_time],
       SUM(total_elapsed_time) / SUM(usecounts) * 1.0 AS [avg_elapsed_time],
       substring(convert(char(23),DATEADD(ms,sum(total_elapsed_time)/1000,0),121),12,23)  total_elapsed_time_ms,
       dbid, 
       objectid  
FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)  
   JOIN 
   (SELECT SUM(total_elapsed_time) AS [total_elapsed_time],
           plan_handle  
      FROM sys.dm_exec_query_stats  
      GROUP BY plan_handle) qs
    ON cp.plan_handle = qs.plan_handle  GROUP BY dbid, objectid
ORDER BY SUM(total_elapsed_time) / SUM(usecounts) * 1.0 DESC

Based on the above list, you can fine tune the expensive query
Most of the time because of use of aggregates or missing indexes impact high CPU usage due to bottleneck on IO, have a look in to it.

Also, on virtual environment task manager performance counter is not accurate.  
link
anujnb's gravatar image

anujnb

<<Or else you can use activity monitor or the following dmv to find top cpu consuming queries.>>
Actually, the most time consuming processes are NOT necessarily the most CPU consuming processes.  You may have very time consuming processes that have a very low CPU usage.  These processes are usually related to high IO contention, blocking, deadlocking or inner memory pressure.
link
Racimo's gravatar image

Racimo

Have you by any chance enabled Full text Search on the database?

Run the below query for  troublehooting CPU Intensive queries:

SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
  ORDER BY total_seconds DESC;
link
venk_r's gravatar image

venk_r

Do these queries/suggestions need to run while the event is occurring?
link
ASA_MIS's gravatar image

ASA_MIS

The answer is yes. And these queries arent that resource intensive.
Can you tell me if the Database has fultext Search enabled?
link
venk_r's gravatar image

venk_r

Ok, I have ran the two provided queries against almost all the databases.  We have one that throws a syntax error for both queries.  Everything else seemed ok.  I saw one query that took 11 seconds.  
link
ASA_MIS's gravatar image

ASA_MIS

First point:      Can you tell me if the Database has fultext Search enabled?
Second point: Do you have the recent service packs installed up to date?

Third  point:  Take a look at the below link

http://support.microsoft.com/kb/931821
link
venk_r's gravatar image

venk_r

As far as I know it has not.  I am not very savy with SQL though so I am not sure how I would tell.
link
ASA_MIS's gravatar image

ASA_MIS

Look at the services and see if you find with name "SQL Server FullText Search" installed and started.
link
venk_r's gravatar image

venk_r

It is started.
link
ASA_MIS's gravatar image

ASA_MIS

>>sqlservr.exe will shoot up and bounce between 80-90% usage <<
In of itself this is not a problem.  Perhaps you have a query which uses a lot of CPU.

>>until we reboot the server. <<
Why in the world are you doing this?  This is like a car speeding up to pass another and you turn off the engine.  Why not find out the reason why before resorting to such a drastic step?
link
acperkins's gravatar image

acperkins

A very nice analogy....You made me imagine a car with two steering wheels, brakes and drivers.

thanks antony :)
link
Racimo's gravatar image

Racimo

Well, the main reason would be that the SQL server stops responding to the Blackberry Server and fax server which is pretty crippling.  We are not talking about the server jumping to 80 or 90% for 5-10 minutes, it does it for hours.  If I knew how to fix or find out what the problem was I wouldn't be here asking for help.  

As far as rebooting it, if you can provide a way for me to find out why, I would prefer to do that.  All the suggestions I've gotten in the past are run SQL profiler while the event is happening, however, SQL profiler will not connect to the server while the event is happening, it just times out.
link
ASA_MIS's gravatar image

ASA_MIS

You can program a server side trace that will run in the timeshift where you expect the problems to occur.  THe below link will tell how to do that.

http://sqlserverpedia.com/blog/sql-server-bloggers/how-to-create-a-server-side-trace-with-sql-profiler/

Once the problem occurs, you can analayze the trace collected.

Hope this helps...
link
Racimo's gravatar image

Racimo

>>Well, the main reason would be that the SQL server stops responding to the Blackberry Server and fax server which is pretty crippling.<<
You are running other applications on the same server as SQL Server?

>>If I knew how to fix or find out what the problem was I wouldn't be here asking for help.  <<
And there is nothing wrong with that.  I am merely pointing out that what you are doing can cause more harm than good.

If you suspect a SQL query is the cause consider using Adam Machanic's sp_WhoIsActive script:
http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx
link
acperkins's gravatar image

acperkins

No, the applications are not on the same server as the SQL server, they use the SQL server for their databases.

I am not sure what it is.  I am looking at the CPU history chart in VCenter and it looks like something that runs every night that sometimes just hangs up and consumes about 90-100% of the CPU.  For instance, when it happened Thursday morning, it started at around 1 AM and went till about 6 AM when I got in to restart the server.  On a normal day, the processor slides up to around 32% usage at 1 AM, drops back down to almost nothing, then slides back up to around 41% at about 3 AM, then of course back down to almost nothing.  Now that I have a timeline, I will try and get a trace of it with Racimo's suggestion and look at scheduled jobs to see if there is anything that runs in that time frame.
link
ASA_MIS's gravatar image

ASA_MIS

The below query should give you a clue...

Hope this helps...
1:
2:
3:
4:
5:
select top 100  (total_worker_time * 1.0) / 1000000 AS [CPU_usage_seconds], 
text 
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
order by total_worker_time desc
link
Racimo's gravatar image

Racimo

It could be a maintenance plan that updates statistics on a large database
link
NormanMaina's gravatar image

NormanMaina

I believe that this issue has been resolved.  We've went 5 weeks now without having it happen.  It seems that the default setting for the instance was set to use some incredible amount of ram.  After setting this back to about 1 GB less than what was in the machine we haven't seen the issue resurface.

Thanks to everyone for the suggestions.
link
ASA_MIS's gravatar image

ASA_MIS

Found some articles online about the default memory setting on SQL 2005 instances being incredibly high.  After checking this and finding out that was true in my case, I changed it and have not seen the issue resuface.
link
ASA_MIS's gravatar image

ASA_MIS

Are you really saying that none of the comments from any of the members here helped you?
link
acperkins's gravatar image

acperkins

I've just re-read the entire posting here to see if anyone mentioned anything about memory or checking how much memory that particular instance of SQL was permitted to use and there were NONE.  Everything referred to checking SQL queries and seeing which ones were consuming the most CPU etc.  These were all informative posts and I am glad that people tried to help and am thankful for the help I received.  However, NONE of the offered solutions fixed the problem or were even close to checking that setting.  Changing the memory usage that particular instance of SQL was permitted to use seems to have FIXED the issue.  If you go back and review and find one that did mention something about it I will give them the points.  I am sure that you will not as I have just re-read it.

The thing that gave me what to search for was an error I found in the SQL logs, which again, unless I didn't see it, nobody even told me to check.  I started searching on that error and found the article that someone wrote who was having almost identical problems.

So the short answer is NO none of these responses were a solution or help found a solution.  They were informative and I appreciate the people that tried to help without the sarcasm that you provided.
link
ASA_MIS's gravatar image

ASA_MIS

And that would be because you never asked anything about memory consumption in your original question.  Your question had to do with high CPU usage.

>>I've just re-read the entire posting here to see if anyone mentioned anything about memory <<
I guess you missed Racimo's comment here http:#a36390046, unfortunately you totally ignored the comment.
link
acperkins's gravatar image

acperkins

Really?  You are correct, I never asked specifically about memory.  I gave you guys the symptoms and was asking why it happened.  Again, had I known the diagnostic, I wouldn't have posted the question here.  How did I totally ignore that comment?  It is talking about using task manager to find top cpu consuming QUERIES and MENTIONS memory pressure and gives no suggestions of what to check, or did I miss that?

The bottom line is I had no idea what to check until I looked in the logs (which no one mentioned) and searched on the error that I kept getting.  That is what solved the problem thus far and that is why I did put a post up and told everyone what I did to fix it then accepted it as an answer.
link
ASA_MIS's gravatar image

ASA_MIS

<<It is talking about using task manager to find top cpu consuming QUERIES and MENTIONS memory pressure and gives no suggestions of what to check, or did I miss that?>>
You got some nerves claiming that nobody suggested a memory problem and then blaming the same people for not spoonfeeding you with an impossible to provide answer given the little information you gave.

It is true that called you specifically for checking the log but you were indeed put on the right track with inner memory pressure.

You need to be aware that the symptoms you described and questions asked are way too broad to call immediately for a precise course of inquiry.   We usually try to narrow down things based on the user input and his motivation to solve his issue: you have shown none or very little in this thread.

About your wonderful discovery : Found some articles online about the default memory setting on SQL 2005 instances being incredibly high

Just know that by default, these settings are just a default meaningless value to say that SQL will occupy all available OS memory.  Had you bothered just searching or asking about inner memory pressure or running the query provided, it would have give you direct clues to what your problem is.

As far as I am concerned, I have no objection for you to keep your points but just know this: no expert owes you a helping hand.  
link
Racimo's gravatar image

Racimo

I AM NOT BLAMING ANYONE FOR ANYTHING...  In case you missed it, I THANKED EVERYONE FOR the suggestions they gave.  Then I am being chewed out by someone that NEVER MENTIONED ANYTHING ABOUT MEMORY, and now, the one person that made mention of “inner   memory pressure” and three other things.

I am sorry that my description I gave you all the information that I knew and it wasn't enough.  Databases are a little outside my territory and I do not dispute that at all..

I ran several of the queries provided and most of them showed nothing because I couldn't run them while the event was taking place AS I mentioned before, when it was happening the server was unresponsive.

I was not "indeed" put on to a solution by searching for inner memory pressure.  I was put on a solution by going and reading through the LOG file to find SEVERAL of the same error, then searching on that error.  Just like I stated earlier.  Most people on other forms Congratulate people when they find a solution and come back to post it.

I am glad to know that you guys are stamping your feet on the ground over some points.  How many points would you like Racimo, because you mentioned “inner memory pressure”?
link
ASA_MIS's gravatar image

ASA_MIS

>>I AM NOT BLAMING ANYONE FOR ANYTHING...  <<
Lose the all all caps, it is plain rude.  If you cannot be civil than than don't try and fake it.

>>Then I am being chewed out by someone that NEVER MENTIONED ANYTHING ABOUT MEMORY<<
When you have cooled down, please re-read you comments objectively as well as everyone else's.  I am hopeful you will actually see them in a different light.

>>Databases are a little outside my territory and I do not dispute that at all.. <<
We realized that, and we tried very patiently to get additional information from you, unfortunately none was forthcoming.

>>Most people on other forms Congratulate people when they find a solution and come back to post it. <<
But that is just the point you have not posted a solution, as it stands the comment you you indicated as the "solution" is useless as it stands and serves no purpose in my view in the PAQ database.

>>I am glad to know that you guys are stamping your feet on the ground over some points.<<
You really are missing the point.  This is a community to exchange ideas, solutions and workarounds.  No adult here cares about points.

>> How many points would you like Racimo, because you mentioned “inner memory pressure”? <<
Oh dear.

I wish you the best of luck.
link
acperkins's gravatar image

acperkins

Apparently they do.  In the comment that I marked as the solution I plainly stated that I corrected the amount of ram allocated to that SQL instance to 1 GB less than the actual memory in the machine.  What else would you like me to say?  Do you think that if I marked Racimo's answer that you referred to as the solution anyone would have benefited from “These processes are usually related to high IO contention, blocking, deadlocking or inner memory pressure“?

The bottom line here is that I found the answer/solution to my problem by checking the log files, finding an error, and then spending some time search the internet for that error and finding an article written by someone have a similar issue.

Anyone of you could have mentioned checking the log files at any time and you didn't.  It is partially my fault because I should have checked them before I even brought the question up as when I offer help to others that is one of the first things I have them check.  In fact, I did check the event logs, just never the SQL logs themselves.

Acperkins, you asked no question that I didn't answer.  You didn't offer a solution even close to what the issue was and yet you are the one that brought all this up over nothing.
link
ASA_MIS's gravatar image

ASA_MIS

My mistake, I see now you only recently joined and may not be totally familiar with the ethos here at Experts-Exchange.  I realize you will be more dilligent in future in complying with the EE Guidelines.

I had asked a Moderator to review this thread, but in view of the above I see no point.

Now let's move on.

Thanks,
Anthony
link
acperkins's gravatar image

acperkins

You are right I am not all that familiar with it.  My basic understanding was you assigned points for a solution as is outlined in the below in a snippet from the EE help page:

How Experts Exchange works can be summarized in 5 simple steps:

1. A member searches over 3.1 million solutions in Experts Exchange's knowledgebase to answer their problem.
2. If their solution is not found, they ask a question about their IT problem.
3. Our community of experienced IT Experts immediately works to solve the problem.
4. The Asker accepts the solution and rewards points to the Expert that posted it.
5. The solution is stored in Experts Exchange's knowledgebase.


I provided the solution I found online which I didn't think would be a problem.  Apparently it is.  Would it be better for me to delete this question instead of assigning an answer?  I have no problem with that.  I just thought it would be nice if anyone else had the same thing going on that they could see what I did.
link
ASA_MIS's gravatar image

ASA_MIS

>>Would it be better for me to delete this question instead of assigning an answer?<<
It is not a problem.  You have posted additional documentation and while I do not agree with the verdict, it is fine as is.

Let's move on,
Anthony
link
acperkins's gravatar image

acperkins

SouthMod,

I am fully aware of that.  And as I have suggested here, I didn't use any of their comments to arrive at my solution.  I looked through the log files (with no mention of it from an expert), found an error (LazyWriter: warning, no free buffers found), searched around using that error, and then found solutions in some of the links provided.  Had I searched around using one of the 4 terms that Racimo suggested in his one post (high IO contention, blocking, deadlocking or inner memory pressure), I would have assigned him points.  I am a member of another community that is similar to this one and I assign helpful or best answers when I use one of their suggestions and or they provide a solution.  I was in no way trying to rip points off, knock experts, or blame anyone for anything.  I simply did not use the suggestions to solve the problem.  I did get some nice information and I am thankful for that and I thanked everyone for their help.

Also, I did read the EE help page.  I posted a snippet from it in this thread.  I haven't read the New to EE yet but I will.
link
ASA_MIS's gravatar image

ASA_MIS

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×3
×7
×1
×132
×45
×1

Asked: 08/18/2011 06:53

Seen: 610 times

Last updated: 11/04/2011 09:16