-4

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?

closed as too broad by Erik Darling, Marcello Miorelli, John Eisbrener, kevinsky, Jon of All Trades Jul 3 at 19:20

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 Jul 3 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 Jul 3 at 12:35
  • Some parts of the system working normally mostly the problem was with one report of the system – Asrar Ahmad Ehsan Jul 3 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 Jul 3 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 Jul 3 at 14:11
3

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://docs.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://docs.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 – Asrar Ahmad Ehsan Jul 3 at 15:23

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