Solved

SQL server CPU usage

Posted on 2013-06-25
20
391 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

20 Experts available now in Live!

Get 1:1 Help Now