Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My current project will send queries to an sql server constantly and It may use 100% of the memory or CPU.

  • How can I check if the server is nearing full utilization in a stored procedure so that I can decide whether to execute the queries or not or save some settings in a table so that the following queries can know the workload is high and decide what to do

  • If not, how can I prevent SQL server to reach full utilization?

More information about the case: Right now I know our current test server can process 40-50 queries per second (one specific stored procedure). And now we'll decide how many queries are sent to the server every second. If we set the amount even 1 higher than the expected, in the long run, the queries will eventually fill the virtual memory and out client will have to restart their sql server instance periodically.

Expected Results (For bounty hunters):

@memory_usage float, @cpu_usage float; /* in percentage */

Any ideas are welcomed. Thanks.

share|improve this question
4  
Which version and edition of SQL Server, please? –  Jon Seigel Feb 8 '13 at 16:10
    
@JonSeigel 2008+ –  Uğur Gümüşhan Feb 8 '13 at 17:57
3  
That doesn't answer the edition question. Resource Governor is Enterprise Edition only. –  Martin Smith Feb 8 '13 at 18:07
1  
This part of your question seems a complete misapprehension. "If we set the amount even 1 higher than the expected, in the long run, the queries will eventually fill the virtual memory and out client will have to restart their sql server instance periodically.". If you don't want SQL Server using more than X amount of memory just set max server memory. –  Martin Smith Feb 11 '13 at 8:51
add comment

3 Answers

If you really want to limit SQL Server memory, look at the Maximum server memory option.

Getting memory usage is possible, but it depends on what you really want. Do you want to see memory usage as a percent of the "Maximum server memory" option? If so, look at sys.dm_os_process_memory:

select convert(float, memory_utilization_percentage) / 100 as memory_usage
from sys.dm_os_process_memory

If you want physical memory use vs. total system memory, look in both sys.dm_os_process_memory and sys.dm_os_sys_info:

select * from sys.dm_os_sys_info

declare @physicalMemoryInUseKB bigint
declare @totalSystemMemoryBytes bigint

select @physicalMemoryInUseKB = physical_memory_in_use_kb from sys.dm_os_process_memory
select @totalSystemMemoryBytes = physical_memory_in_bytes from sys.dm_os_sys_info
select convert(float, @physicalMemoryInUseKB) * 1024
       / convert(float, @totalSystemMemoryBytes) as memory_usage

I don't think you can get the CPU usage unless you have the Resource Governor enabled. If you do, look at sys.dm_os_performance_counters:

select
    case CPUUsageBase
        when 0 then 0
        else convert(float, CPUUsage) / convert(float, CPUUsageBase)
    end as 'cpu_usage'
from
(select cntr_value as [CPUUsage]
from sys.dm_os_performance_counters
where counter_name like 'CPU usage%'
and object_name like '%Workload Group Stats%'
and cntr_type = 537003264) Usage
cross join
(select cntr_value as [CPUUsageBase]
 from sys.dm_os_performance_counters
 where counter_name like 'CPU usage %'
 and object_name like '%Workload Group Stats%'
 and cntr_type = 1073939712) UsageBase
share|improve this answer
    
select * from sys.dm_os_performance_counters doesn't return anything relevant to CPU the rest of your answer about memory is accurate –  Uğur Gümüşhan Feb 18 '13 at 7:50
add comment

Within a stored procedure? You won't be able to as once the query which was causing the CPU to go to 100% is finished you can't monitor for that metric anymore which would be done by the next query within the stored procedure.

If you are using SQL Server 2008 or higher (and Enterprise Edition) you can use the resource governor to ensure that other processes have CPU power and memory available to them.

share|improve this answer
add comment

DMV's provide both cpu and memory usage information. These queries require the VIEW_SERVER_STATE permission to run.

DECLARE @memory_usage FLOAT
    , @cpu_usage FLOAT

SET @memory_usage = ( SELECT    1.0 - ( available_physical_memory_kb / ( total_physical_memory_kb * 1.0 ) ) memory_usage
                        FROM      sys.dm_os_sys_memory
                    )

SET @cpu_usage = ( SELECT TOP ( 1 )
                            [CPU] / 100.0 AS [CPU_usage]
                    FROM     ( SELECT    record.value('(./Record/@id)[1]', 'int') AS record_id
                                        , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [CPU]
                                FROM      ( SELECT    [timestamp]
                                                    , CONVERT(XML, record) AS [record]
                                            FROM      sys.dm_os_ring_buffers WITH ( NOLOCK )
                                            WHERE     ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                                                    AND record LIKE N'%<SystemHealth>%'
                                        ) AS x
                            ) AS y
                    ORDER BY record_id DESC
                    )


SELECT  @memory_usage [memory_usage]
        , @cpu_usage [cpu_usage]
share|improve this answer
    
+1 For mentioning the system health session –  Paul Williams Feb 12 '13 at 19:56
    
doesn't return cpu usage. any ideas? –  Uğur Gümüşhan Feb 13 '13 at 14:24
    
@StrayCatDBA Is the CPU usage in the ring buffer a discrete measurement (i.e. right now) or an average since the last measurement (i.e. in the last X seconds)? –  Paul Williams Feb 18 '13 at 18:57
    
My understanding is that it's discrete. –  StrayCatDBA Feb 18 '13 at 18:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.