SQL Server slows down drastically at peak usage hours [closed]-CodePudding

We are maintaining database in SQL Server and it works pretty well normally, but slows down drastically during the peak usage time.

What is the best way of handling this scenario? TIA

CodePudding user response：

You can view a list of the most expensive queries for the current DB with this query:

SELECT TOP(50) 
    qs.execution_count AS [Execution Count],
    (qs.total_logical_reads) * 8 / 1024 AS [Reads (MB)],
    (qs.total_worker_time) / 1000000 AS [Total Time (s)],
    (qs.total_worker_time / qs.execution_count) / 1000.0 AS [Avg Time (ms)],
    t.text AS [Complete Query Text], 
    qp.query_plan AS [Query Plan]
FROM 
    sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY 
    sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY 
    sys.dm_exec_query_plan(plan_handle) AS qp
WHERE 
    t.dbid = DB_ID()
ORDER BY 
    [Total Time (s)] DESC 
OPTION (RECOMPILE);

Once you have identified the most expensive queries, try to mitigate the main issues. First strategies that come to mind are:

caching frequently queried data in your application
adding indexes for tables that increase frequent queries performance
optimize your queries (for example by eliminating n 1 mistakes: see https://medium.com/doctolib/understanding-and-fixing-n-1-query-30623109fe89)