Detecting SQL Server Utilization with a query

Question

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.

Answer 1

Would something like this help you?

Script To Monitor Memory Usage By SQL Server Instance

Answer 2

For any such intensive use of SQL Server and effort to fine tune it, I assume that the (virtual) machine is dedicated to SQL Server.

Having said this, getting the machine's current percentages of CPU and memory used should do the trick:

CREATE PROCEDURE dbo.p_GetSystemUsage
    @cpuUsage float out,   -- % CPU usage
    @memoryUsage float out -- % memory usage
AS
BEGIN
    SET NOCOUNT ON;

    /*
     * % CPU usage
     */

    SELECT TOP 1
        @cpuUsage = 100 - r.SystemIdle
    FROM (
        SELECT
            rx.record.value('(./Record/@id)[1]', 'int') AS record_id,
            rx.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
        FROM (
            SELECT CONVERT(XML, record) AS record
            FROM sys.dm_os_ring_buffers
            WHERE
                ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND
                record LIKE '%<SystemHealth>%') AS rx
        ) AS r
    ORDER BY r.record_id DESC

    /*
     * % memory usage
     */

    SELECT
        @memoryUsage =
            (((m.total_physical_memory_kb - m.available_physical_memory_kb) /
              convert(float, m.total_physical_memory_kb)) *
             100)
    FROM sys.dm_os_sys_memory m
END

A few things to note:

• sys.dm_os_sys_memory is a good source for the machine's physical-memory usage. It provides similar information about the machine's page-file usage. In my experience, its information changes frequently - multiple queries against it within a second yielding different results.
• sys.dm_os_ring_buffers is a good source for the machine's CPU usage, but it is not updated as frequently - every minute from what I have seen. Maybe you can affect this if you need more real-time information.
• The CPU-usage value in sys.dm_os_ring_buffers is an integer, but I made @cpuUsage a float per your spec. Since the stored proc uses two float params, you could refactor CPU-usage determination to provide a fractional portion without changing its callers.