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:


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?

  • It can be the environment. did you check some server stats? network? disks? you can run a trace to see whats slow and then checking the query plan. are you using Perfmon? you should, its great to understand what's going on on the server
    – Racer SQL
    Commented Jul 3, 2019 at 12:34
  • 5
    FREEPROCCACHE may have evicted bad plans that caused the symptoms. DBCC DROPCLEANBUFFERS probably hurt more than helped. It would have been best to identify expensive queries (e.g. use Query Store, if available) and take targeted corrective action.
    – Dan Guzman
    Commented Jul 3, 2019 at 12:35
  • Some parts of the system working normally mostly the problem was with one report of the system Commented Jul 3, 2019 at 12:35
  • 4
    Run sp_blitz and addressed the issues that it brings up. Start from there and see if you still experience issues.
    – Kin Shah
    Commented Jul 3, 2019 at 12:47
  • 3
    If you are going to be administering SQL Servers, you should definitely get knowledgeable. Try the "Free Fundamentals" course here: brentozar.com/training and/or read the "Accidental DBA" posts here: sqlskills.com/help/accidental-dba
    – DBADon
    Commented Jul 3, 2019 at 14:11

1 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()

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:


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.

  • Thanks, I knew the consequences of running the above commands but I had no choice since I had to solve the problems as soon as possible. Next time I will check for slow performance porcs Commented Jul 3, 2019 at 15:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.