?
Solved

sql server uses up too much cpu

Posted on 2011-04-26
19
Medium Priority
?
557 Views
Last Modified: 2012-05-11
Doesn't use a lot of ram but makes windows server 2008 freeze by using close to 100 CPU.
0
Comment
Question by:rgb192
  • 10
  • 6
  • 2
  • +1
19 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 35472566
lots of factors ; you can use sql profiler, windows performace monitor ,dmvs to get those

The queries should show in the normal DMVs to find these bottlenecks.  Look into dm_exec_query_stats


 
SELECT  creation_time  
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads  
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC

for Windows performance monitor check this

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35473200
Are you maintaining your indexes, updating statistics?
If not, start from rebuilding indexes (statistic will update also).
Read whole article before running any code, it may take significant ammount of time to finish.
SQL Server script to rebuild all indexes for all tables and all databases
0
 

Author Comment

by:rgb192
ID: 35476492
I ran command to rebuild index but I am using the same amount of ram and cpu after I restart iis

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')  
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' +
              table_name as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
              WHERE table_type = ''BASE TABLE'''  

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       -- SQL 2000 command  
       --DBCC DBREINDEX(@Table,' ',@fillfactor)  
         
       -- SQL 2005 command  
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' +
               CONVERT(VARCHAR(3),@fillfactor) + ')'  
       EXEC (@cmd)  

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35476695
I ran command to rebuild index but I am using the same amount of ram and cpu after I restart iis
Could you clarify, I'm not sure what is the problem?
0
 

Author Comment

by:rgb192
ID: 35477137
the command to rebuild index
was in the link that you sent me
and then I restarted sql server and then I noticed that that I am using less ram, but am still slow

is there anything else I could do to lower cpu, ram
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35477273
Why did you restart SQL Server? Were commands from script finished?
After restart your server may slow because of flushed procedure cache. Check server wait stats, it would be good to run following code after some time from restart:

WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
    )
SELECT
    W1.wait_type AS WaitType, 
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO

Open in new window

0
 

Author Comment

by:rgb192
ID: 35477306
WaitType                                                     Wait_S                                  Resource_S                              Signal_S                                WaitCount            Percentage                              AvgWait_S                               AvgRes_S                                AvgSig_S
------------------------------------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
WRITELOG                                                     96.61                                   96.28                                   0.33                                    1483                 60.45                                   0.0651                                  0.0649                                  0.0002
PAGEIOLATCH_SH                                               20.75                                   20.66                                   0.09                                    1528                 12.98                                   0.0136                                  0.0135                                  0.0001
PAGEIOLATCH_EX                                               12.88                                   12.88                                   0.00                                    81                   8.06                                    0.1590                                  0.1590                                  0.0000
LCK_M_S                                                      11.22                                   11.08                                   0.14                                    3                    7.02                                    3.7393                                  3.6927                                  0.0467
PAGELATCH_UP                                                 6.98                                    6.95                                    0.03                                    2                    4.37                                    3.4920                                  3.4765                                  0.0155
SOS_SCHEDULER_YIELD                                          4.53                                    0.00                                    4.53                                    127638               2.84                                    0.0000                                  0.0000                                  0.0000

(6 row(s) affected)
0
 

Author Comment

by:rgb192
ID: 35477336



WaitType               Wait_S  Resource_S  Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S

WRITELOG      96.61      96.28      0.33      1488      60.30      0.0649      0.0647      0.0002
PAGEIOLATCH_SH      20.75      20.66      0.09      1528      12.95      0.0136      0.0135      0.0001
PAGEIOLATCH_EX      12.88      12.88      0.00      81      8.04      0.1590      0.1590      0.0000
LCK_M_S      11.22      11.08      0.14      3      7.00      3.7393      3.6927      0.0467
PAGELATCH_UP      6.98      6.95      0.03      2      4.36      3.4920      3.4765      0.0155
IO_COMPLETION      4.83      4.80      0.03      311      3.01      0.0155      0.0154      0.0001
0
 

Author Comment

by:rgb192
ID: 35477343
the query to improve ram, cpu only took 5 seconds to complete
0
 

Author Comment

by:rgb192
ID: 35477381
correction:
the query to improve ram, cpu only took 39 seconds to complete


i ran it again and will not restart sql server for a couple minutes
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35480743
>>is there anything else I could do to lower cpu, ram <<
Why don't you tell us what is the specific problem.  Running slow is meaningless.  Unless you are prepared to roll-up your sleaves and troubleshoot the entire SQL Server environment , you need to post here the specific queries that are taking a long time and if you are lucky we may provide a solution.
0
 

Author Comment

by:rgb192
ID: 35485949
no query is running slow

running sql serer makes windows server 2008 freeze
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35486555
Whre are your databases located? Locate your databases away from system drive. Ensure there is enough free memory for OS. Is server running any other applications than SQL Server database (IIS, Analysis Services, Reporting Services, etc.)
0
 

Author Comment

by:rgb192
ID: 35487576
I am running sql browser (i do not know what this does) and sql agent (for saving database to .bak file every 6 hours)  I am not sure other applications with sql server


sql server database is on same drive as  system drive  (maybe I can create a new drive, would this make it quicker)

i am running iis on same drive
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35488278
>>sql server database is on same drive as  system drive<<
That is a very bad idea.

>>i am running iis on same drive <<
That certainly explains why the server is "running slow", I wish you had told us that in the first place.  SQL Server is designed to run-standalone, if you have chosen to ignore that recommendation then you have no choice but to cripple SQL Server by setting the max memory it can use.  SQL Server will not perform as well, but your other apps should run faster.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35490116
Your server wait stats are caused by disk I/O contention. Your files should not be placed on the system drive. Additionally check free memory because running IIS and SQL Server on the same box could cause lack of memory for OS and create paging which also increases disk I/O (on drive where pagefile is placed).
Try to set lower max server memory setting for SQL Server to leave memory for IIS and OS, monitor OS level memory and disk perfmon counters.
0
 

Author Comment

by:rgb192
ID: 35505353
>>disk perfmon counters

how do to this
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 1000 total points
ID: 35506400
0
 

Author Closing Comment

by:rgb192
ID: 35695586
thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

809 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