After seeing that my online application was frequently experiencing SQL Server Request Timeouts, I examined the server and discovered that the CPU was nearly entirely utilized.

SQL Server used up most of the resources.

I was aware that there was a problem with my database query function, but I'm not sure which specific queries from my online application are to blame. Thus, I run the following SQL Studio Management Studio query to obtain the top ten database queries in terms of resource consumption.

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 examine your most costly queries by looking at the 'query_text' column, or you can look for ways to optimize your query by looking at the query_plan column.

In my situation, I've come to the conclusion that improper indexing is the main reason behind one of my tables, which has millions of rows in it.

Additionally, I was able to spot a few queries that had string manipulation functions like SUBSTRING, REVERSE, CHARINDEX, and others but did not employ an appropriate join statement. In order to make it more effective and less resource-intensive, I must rework a few queries.

HostForLIFEASP.NET SQL Server 2022 Hosting