Solved

SQL server CPU usage

Posted on 2013-06-25
20
389 Views
Last Modified: 2013-07-02
Hello Experts

We have an application with SQL 2008 R2 SP1 back end. CPU usage gradually goes up through out the day and then gets to 100% and slow down the application. database size
is about 5 GB ; our server has 8 GB of memory and two CPUs .

we have about 40 users .

any method to start troubleshooting this ? is it safe to run "DBCC FREEPROCCACHE" abd clear CPU cache ?
0
Comment
Question by:akhalighi
  • 10
  • 9
20 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39275264
Well, I wouldn't use FREEPROCCACHE on a production server.  The question is to try to find out what part of the process is causing the issue.  Is an index problem?  Is it a tempdb problem?  Can you re-write or optimize any of queries?  Have you used the SQL Profiler to see if anything looks odd?  How about sp_who or sp_who2?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275458
You can start by running this query that will list you the top N most intensive CPU queries:

SELECT TOP 10
        RANK() Over (ORDER BY deqs.total_worker_time DESC) As [Rank],
        CONVERT(decimal(38,2), CONVERT(float, total_worker_time) / 1000) AS [Total CPU Time (ms)],
        execution_count AS [Execution Count],
        CONVERT(decimal(38,2), (CONVERT(float, total_worker_time) / execution_count) / 1000) AS [Average CPU Time (ms)] ,
        SUBSTRING(execText.text,
          -- starting value for substring 
          CASE WHEN deqs.statement_start_offset = 0 
             OR deqs.statement_start_offset IS NULL 
               THEN 1 
               ELSE deqs.statement_start_offset/2 + 1 END,
          -- ending value for substring
          CASE WHEN deqs.statement_end_offset = 0 
            OR deqs.statement_end_offset = -1 
            OR deqs.statement_end_offset IS NULL 
               THEN LEN(execText.text) 
               ELSE deqs.statement_end_offset/2 END - 
                 CASE WHEN deqs.statement_start_offset = 0 
                   OR deqs.statement_start_offset IS NULL 
                     THEN 1 
                     ELSE deqs.statement_start_offset/2  END + 1
        ) AS [Query Text],
        execText.text AS [Object Text]
FROM    sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;

Open in new window


After that you will have to start with the first one and check its execution plan, which will tell you if there is an index missing, which is most likely.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275486
If you realy want to be able to monitor what is going at a deeper level on I recommend you to install and use this procedure:

http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

You should create a dba database and create that procedure in it and then run it when your CPU is 100%. You will see what is going on at that moment.

Another way is to use the Activity monitor:

In Management Studio you right click on the server name > Activity Monitor.

After that you will expand the Resources Waits and Recent Expensive queries. You can do that with teh other 2 metrics. If you right click in the graphs area you can change the refresh rate to every second.
0
 
LVL 10

Author Comment

by:akhalighi
ID: 39275530
@Zberteoc

Thanks man , I ran that query and it shows me a list of expensive queries ; these are coming from our application . same quieries show up if I look into activity monitor. unfortunately there is nothing else I could do with the queries since they are built-in.

I guess I need to escalate this to our application vendor and add more CPU to the system for the time being .
0
 
LVL 10

Author Comment

by:akhalighi
ID: 39275557
@Zbertoc

Looking into execution plan of those expensive queries ; it looks like it's an indexing issue .

what do you think ?
indexing.png
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275587
You just create the index as suggested in the execution plan. That should fix it. Usually it comes in green in the top band just under the query text itself.

However you should let your vendor know about it.
0
 
LVL 10

Author Comment

by:akhalighi
ID: 39275606
can I just rebuild it ?
0
 
LVL 10

Author Comment

by:akhalighi
ID: 39275616
something may help , this database was hosted by our vendor ; it's transferred to our environment recently , would it be possible that indexes have to be rebuilt since database is running on a new server ?

Can I rebuild the index in production time or better to wait for after hours ?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275648
I only saw the attachment just now.

Maybe you can post the entire picture of the plan and not only part of it. If there is a suggestion it will also tell the % of improvement.

From that image it is an index seek on the cluster index which is not much you can do about. That is what you want, and index seek.

You can also try the Engine Tuning Adviser:

Right click somewhere in the query window background(not on text) > Analyze Query in Database Engine Tuning Adviser > click on Tuning tab in teh right panel > Advance Options button > check the Define Max Space for recommendation > Ok > and then click under the main menu on the Start Analysis button

Wait and see if there is any improvement recommendation, If yes click to generate the code and apply it to the database.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275683
If you want to rebuild you should do this in the maintenance window. Rebuilding locks the table. You can check the fragmentation with this query:

SELECT DB_NAME(DB_ID()) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFagmentationInPercent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
--and indexes.[name]='index_name_here' -- if ignored all indexes will be chhecked
--and objects.[name]='table_name_here' -- if ignored all tables will be checked
order by 
	DatabaseName,
	SchemaName,
	ObjectName,
	IndexName

Open in new window


If AvgFagmentationInPercent column value is:

1. Less then 10 do nothing
2. >=10 and <30- do a REORGANIZE
2. >=30- do a REBUILD .. WITH ONLINE=ON(the ONLINE+ON only works on 2008 Enterprise version or up)

WITH ONLINE will allow access during rebuild.

Best to use this procedure:

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Automatically does for you all the above.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Author Comment

by:akhalighi
ID: 39275782
full picture of execution plan is attached.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275831
Where?
0
 
LVL 10

Author Comment

by:akhalighi
ID: 39275861
sorry here
ep2.png
0
 
LVL 10

Author Comment

by:akhalighi
ID: 39275865
I ran the tuning adviser ; is it going to change the query that our application is using ?
0
 
LVL 10

Author Comment

by:akhalighi
ID: 39275876
above query ( regarding fragmentation ) returns this error :

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275883
Make sure you copy the text exactly as it is. I tested the query as posted and worked. Here it is again:

SELECT DB_NAME(DB_ID()) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFagmentationInPercent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
--and indexes.[name]='index_name_here' -- if ignored all indexes will be chhecked
--and objects.[name]='table_name_here' -- if ignored all tables will be checked
order by 
	DatabaseName,
	SchemaName,
	ObjectName,
	IndexName

Open in new window

0
 
LVL 10

Author Comment

by:akhalighi
ID: 39281332
ran fragmentation report ; those indexes in question are only 6% and 9% fragmented . I escalated the issue to our vendor . added two more VCPUs to the server improved the performance but still not perfect .

there are other indexes that are highly fragmented ; I probably rebuild them after hours. rebuilding indexes is a safe action , right ?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39281378
Yes. Just make sure you have enough space on the hard drive. I think you should still run the "who is active" procedure while the processor is high to see exactly what causes that. It could still be just an missing index issue that could be solved simply by creating it. You need to dig more as the processor upgrade will not help much if you don't eliminate the cause.
0
 
LVL 10

Author Comment

by:akhalighi
ID: 39282656
is this a 3rd party tool or a built-in stored procedure ?
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39282776
SQL commands. But as in my post above I recommend you to use Ola Halegren procedure, which actually builds the SQL statement based on your database/objects filter and applies the correct action.

Here is teh SQL documentation about the index reorganize and rebuild:

http://msdn.microsoft.com/en-us/library/ms189858.aspx
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

762 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

21 Experts available now in Live!

Get 1:1 Help Now