SQL Server Performance – How to Measure Database Server Load from Web Application

loadperformancesql server

I work on a dotnet / WebForms web application that's load balanced over a number of web servers. The web servers connect to a SQL Server 2016 instance.

This web application includes several ways for end-users to request reports. Some of those reports take a lot of resources. I'm hoping to add a feature to the web app that says "Sorry, we're too busy to honor this request now. Please try again later."

Is there a good in-band way — a good query or SP — to ask SQL server how loaded it is at the moment? It happens that most overloads in our environment involve high cpu utilization.

If there's no good query, can anyone suggest an out-of-band way to do this reliably and simply? Maybe some sort of WMI operation.

Once I know how loaded the DBMS machine is, then I can decide whether to ask my end-users to come back later.

Edit. Two reasons to do this rather than add a server.

  1. the user-frustration factor. If a request takes too long, the user may figure something's wrong and request it again. The way things work in web apps, SQL queries keep going even when the user has abandoned the request.

  2. more servers cost money. The business I'm serving is self-funded. Managing peak load is as good a strategy as adding 24×7 base load capacity.

Best Answer

In my view the challenge will be to define what is loaded. Unless you have a baseline tied to business hour (with full cycle, day/night, weekday/weekend etc) there is no way to determine if you are overloaded. I noticed you mentioned:

most overloads in our environment involve high cpu utilization

If you want to base you alert on one data point (high cpu) you can use this query to see what percent of cpu is being consumed at this moment. You can change the top(10) value and get back to 256 minutes.

I copied this query from here. 

DECLARE @ts_now BIGINT = (SELECT cpu_ticks / ( cpu_ticks / ms_ticks ) 
   FROM   sys.dm_os_sys_info); 

SELECT TOP(10) sqlprocessutilization AS [SQL Server Process CPU Utilization], 
              systemidle             AS [System Idle Process], 
              100 - systemidle - sqlprocessutilization AS [Other Process CPU Utilization], 
              Dateadd(ms, -1 * ( @ts_now - [timestamp] ), Getdate()) AS [Event Time] 
FROM   (SELECT record.value('(./Record/@id)[1]', 'int') 
                      AS record_id, 
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], 
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], 
            [timestamp] 
 FROM   (SELECT [timestamp], 
                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 x) AS y 
ORDER  BY record_id DESC;

With this approach you have no way to check if SQL Server (beside kernel process) is consuming all of the cpu. Hopefully in your case you do not have other applications installed in the same host.

Adding few PowerShell commands.

Get-WmiObject win32_processor | Measure-Object -property LoadPercentage -Average | Select Average

Get-Counter '\Processor(_Total)\% Processor Time'

I can make this answer to a question how to detect high cpu consuming sessions and try to fix those but assuming that is not what you are looking for. I hope you already know what is causing high cpu and at this moment you cannot do anything about that.

I want to point out high cpu is not a bad thing unless your ‘runnable’ tasks are waiting for scheduler to start working. CPU is expensive and we pay lot of money to license those, why not let our money work to the full capacity?

Related Question