sql server uses up too much cpu

Doesn't use a lot of ram but makes windows server 2008 freeze by using close to 100 CPU.
LVL 1
rgb192Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
rgb192Author Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Daniel_PLDB Expert/ArchitectCommented:
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
 
rgb192Author Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
rgb192Author Commented:
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
 
rgb192Author Commented:



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
 
rgb192Author Commented:
the query to improve ram, cpu only took 5 seconds to complete
0
 
rgb192Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
rgb192Author Commented:
no query is running slow

running sql serer makes windows server 2008 freeze
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
rgb192Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
rgb192Author Commented:
>>disk perfmon counters

how do to this
0
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.