How To Find Problematic Database is Microsoft SQL Server Overloaded Due To SQL Service? Print

  • 0

If the Microsoft SQL server is overloaded due to SQL service, you will find the problematic database that causes the high server load by following the below steps.

Step 1. Launch SQL Server Management Studio.

Step 2. Click New Query.

Step 3. Paste code into the new query window and execute.

Run the following query to get an idea of how much CPU the queries are currently using:

DECLARE @init_sum_cpu_time int,

@utilizedCpuCount int

--get CPU count used by SQL Server

SELECT @utilizedCpuCount = COUNT( * )

FROM sys.dm_os_schedulers

WHERE status = 'VISIBLE ONLINE'

--calculate the CPU usage by queries OVER a 5 sec interval

SELECT @init_sum_cpu_time = SUM(cpu_time)

FROM sys.dm_exec_requests WAITFOR DELAY '00:00:05'SELECT CONVERT(DECIMAL(5,

2),

((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU FROM Queries AS Percent of Total CPU Capacity]

FROM sys.dm_exec_requests

To find out which queries are responsible for high CPU usage, run the following statement:

SELECT TOP 10 s.session_id,

r.status,

r.cpu_time,

r.logical_reads,

r.reads,

r.writes,

r.total_elapsed_time / (1000 * 60) 'Elaps M',

SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.TEXT)

ELSE r.statement_end_offset

END - r.statement_start_offset) / 2) + 1) AS statement_text,

COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))

+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,

r.command,

s.login_name,

s.host_name,

s.program_name,

s.last_request_end_time,

s.login_time,

r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

WHERE r.session_id != @@SPID

ORDER BY r.cpu_time DESC

Once you run the above script, you will get the list of queries that are causing the high CPU issue. From this you can find out which database is causing the issue.

You can use the following statement to look for historical CPU-bound queries:

SELECT TOP 10 st.text AS batch_text,

SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,

(qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,

(qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,

qs.total_logical_reads / qs.execution_count AS avg_logical_reads,

(qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,

(qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

ORDER BY(qs.total_worker_time / qs.execution_count) DESC

That's it.


Was this answer helpful?

« Back

chat