SQL Server CPU Usage

[admdev]

Hi all,

Hope somebody can help with this.

I notice that the CPU usage on our SQL server, which is hosting two applications designed in house, is constantly going to over 50 %, sometimes up to 90 %.

Is this normal?

It is doing it aproximatelly every couple seconds.

Thanks in advance for your help.

 

[onpnt]

what are you running on the server? features of sql server that is. Is this server hit every other second? Have you run profiler to see if it is even the sql server that is doing it? What about just looking at processes and seeing who is taking up that CPU time


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[ptheriault]

I would consider that high. What version of SQL are you running?

Here are some queries for 2005 that will help you identify the offending queries.

--highest CPU
select  
    highest_cpu_queries.plan_handle,  
    highest_cpu_queries.total_worker_time, 
    q.dbid, 
    q.objectid, 
    q.number, 
    q.encrypted, 
    q.[text] 
from  
    (select top 50  
        qs.plan_handle,  
        qs.total_worker_time 
    from  
        sys.dm_exec_query_stats qs 
    order by qs.total_worker_time desc) as highest_cpu_queries 
    cross apply sys.dm_exec_sql_text(plan_handle) as q 
order by highest_cpu_queries.total_worker_time desc


--If you are seeing lots of SOS_SCHEDULER_YIELD in 
--your Wait States, that is a very stong indicator of CPU pressure.
--You can run the DMV query to confirm that:

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

--If you see the runnable tasks count above zero, 
--that is cause for concern, and if you see it in double digits 
--for any length of time, that is cause for extreme concern!

--This query will give you an idea of 
--how many tasks are waiting in the system. 
--You can use this information to understand 
--blocking characteristics of your load
 
select count(*)
from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL'

--The following query gives you a high-level view of which 
--currently cached batches or procedures are using the most CPU
--The query aggregates the CPU consumed by all statements with the same
--plan__handle (meaning that they are part of the same batch or procedure).
--If a given plan_handle has more than one statement, you may have to drill in
--further to find
--the specific query that is the largest contributor to the overall CPU usage 

select top 50  
    sum(qs.total_worker_time) as total_cpu_time,  
    sum(qs.execution_count) as total_execution_count, 
    count(*) as  number_of_statements,  
    qs.plan_handle  
from  
    sys.dm_exec_query_stats qs 
group by qs.plan_handle 
order by sum(qs.total_worker_time) desc

--To find more details about plan run this query
-- takes either spid or plan_handle
SELECT *
FROM sys.dm_exec_query_plan(0x06000700185B93054043ED7F010000000000000000000000)

--   4. Q. Does my load have an active resource bottleneck?
--You can answer this question by looking at the resource address 
--that tasks are blocked on.  
--Keep in mind that not all wait types have resource associated with them. 

select resource_address, count (*)
from sys.dm_os_waiting_tasks
WHERE resource_address <> 0
group by resource_address
order by count (*) desc

--
--sys.dm_os_schedulers
--1. Q. Do I need to by more CPUs?
--In order to answer this question you have to find out if your 
--load is really CPU bounded.  Your load is really CPU bounded if a 
--number of runnable tasks per each scheduler always greater than 1 
--and all of your queries have correct plan.  
--The latter statement is very important, your load can be CPU bounded 
--due to the fact that somehow optimizer generated bad plan – 
--it can happen if your statistics out of date or you tried to 
--perform handcrafted optimization. In this case you don’t want to run to 
--Circuit City to buy more CPUs right a way – you want to fix the plan. 
--Here is the query to find out average length of a runable queue on the system:

select AVG (runnable_tasks_count)
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'

--1. Q. How many sockets does my machine have?

select cpu_count/hyperthread_ratio AS sockets
from sys.dm_os_sys_info


--Q. How many either cores or logical CPU share the same socket?

select hyperthread_ratio AS cores_or_logical_cpus_per_socket
from sys.dm_os_sys_info
 
--4. Q. How much physical memory my machine has?

select physical_memory_in_bytes/1024 AS physical_memory_in_kb
from sys.dm_os_sys_info

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[admdev]

Thanks for the reply guys.

I am running Windows 2003, SQL 2005, 2GB of RAM, and the server has an Intel Xeon(Dual Core).

I looked at the processes and it is in fact the SQL process using the CPU.

Thanks.

 

[ptheriault]

Well, run the queries I provided, They will help you determine the cause.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[admdev]

When I ran the code below, I noticed many UDF (User Defined Functions). Do UDFs take a lot of resources. By discusing it with the developer, I learned that most of the work performed by the application uses UDFs.

Is there anything that can be optimized for this purpose?

Thanks.

===========================================================
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
===========================================================

 

[onpnt]

It depends on what the UDF is doing.


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[onpnt]

btw...where do you see a UDF in that code? or did you mean DMV


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[admdev]

Last field on the select statement (q.[text])

Below is an example of one. There is more code after the line I provided, but it is too long to post here.

CREATE FUNCTION [dbo].[udf_GetLineResourceTime]


Thanks.

 

[onpnt]

ahh...thats not a UDF. Its a dynamic management function

Without us knowing what the UDF is doing there is no way we can say if its bad or good. it may be if there is a better way to do it or it may not be.

And yes, don't post all the code. jsut give us an expalanation. BUT is this in scope of this thread? I think you should take that question to forum183


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]