How to Check Your Most Resource-Intensive Queries in SQL Server

Faced with persistent SQL Server timeouts due to high CPU usage, I identified resource-intensive queries using a script to analyze the top 10 queries, optimizing and rewriting them for improved efficiency.

My web application was keep hitting sql server request time out, so I checked the server and found out that the CPU has reached almost 100% usage.

Most of the resources was consumed by SQL server.

I knew something was wrong on my database query function, but I do not know which queries from my web application that cause the error.

So, I execute following query in SQL Studio Management Studio to get the top 10 most resource-intensive queries of the database.

SELECT TOP 10
    qs.total_logical_reads + qs.total_logical_writes AS total_io,
    qs.execution_count,
    qs.total_worker_time AS total_cpu_time,
    qs.total_elapsed_time,
    st.text AS query_text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
    total_io DESC;

And the query will give me such result.

You can either review the 'query_text' column to review your most expensive queries, or check the query_plan column to find a way to optimize your query.

For my case, I realize my root cause is cause one of my table that contains millions of rows, do not have a proper indexing.

And I also able to identify few queries that do not use a proper join statement and have certain string manipulation functions like SUBSTRING, REVERSE, CHARINDEX etc. So I have to rewrite some queries to make it more efficient and less resource intensive.

After I implemented new queries to reduce the overload, now my SQL services not longer consume huge of CPU usage of the server ,and my Web App can works just fine without and request timeout error from the database.