SQL Server database slow performance [closed]

Question

I have a SQL Server database in production, and typically the performance of the application connected the database is satisfying. For some days many clients were complaining about slow performance of the system. Reports that were being loaded in seconds are now loading in minutes. I haven't updated the statistics for a long time so I have tried index defragmentation and updating statistics for improving the performance but neither helped.

Suddenly I thought that the problem may be caused by expensive execution plan that is cached ran the following two DBCC commands:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

By running the above commands I was able to improve the performance back to normal state.

Was my guess correct or is there something else I'm not aware of?

Answer 1

Next time, this query will collect the top 25 cached stored procedures by query time (I think I got this from Paul White, but that may not be accurate):

-- Top Cached SPs By Avg Elapsed Time
SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, 
GETDATE()), 0) AS [Calls/Minute], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
qs.total_worker_time AS [TotalWorkerTime], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);

That will help you determine what procedures are running slow. (Also take note of the Worker Time columns and change the ORDER BY to them if needed). Once you find the problem stored procedure, set it to RECOMPILE the next time it is run:

EXEC sp_recompile N'Your_Stored_Procedure_Name'; 

More info on sp_recompile can be found here: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-recompile-transact-sql

Clearing the plan cache like you did with the:

DBCC FREEPROCCACHE

Cleared the execution plan for the report, same as the sp_recompile does, but freeproccache takes everything else with it too. From the Microsoft site: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql

FREEPROCCACHE - "This can cause a sudden, temporary decrease in query performance as the number of new compilations increases."

You should also keep your stats updated. The simplest way to update is:

EXEC sp_updatestats; 

But that is more a hammer (like FREEPROCCAHE), as it updates everything rather than targeting the tables used.