SQL Server数据库性能缓慢

Question

我在生产中有一个Server数据库，通常连接数据库的应用程序的性能是令人满意的。几天来，许多客户一直抱怨系统性能缓慢。在几秒钟内加载的报告现在以分钟为单位加载。我已经很久没有更新统计数据了，所以我尝试了index defragmentation和updating statistics来提高性能，但都没有帮助。

突然，我想这个问题可能是由于缓存的昂贵的执行计划导致的，运行了以下两个DBCC命令：

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

通过运行上述命令，我能够将性能提高到正常状态。

我的猜测是正确的还是还有什么我不知道的？

Answer 1

下一次，这个查询将按查询时间收集前25位缓存的存储过程(我想我是从Paul White那里得到的，但这可能不准确)：

-- 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);

这将帮助您确定哪些过程运行缓慢。(还请注意Worker Time列，并在需要时按其更改顺序)。找到问题存储过程后，将其设置为下次运行时重新编译：

EXEC sp_recompile N'Your_Stored_Procedure_Name'; 

有关sp_recompile的更多信息可以在这里找到：https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-recompile-transact-sql

清除计划缓存，就像您在以下方面所做的：

DBCC FREEPROCCACHE

与sp_recompile一样，清除了报表的执行计划，但freeproccache也带走了它的其他所有内容。来自Microsoft站点：https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql

FREEPROCCACHE --“随着新编译数的增加，查询性能可能会突然暂时下降。”

你也应该保持你的统计数据更新。更新的最简单方法是：

EXEC sp_updatestats; 

但这更像是一个锤子(比如FREEPROCCAHE)，因为它更新了所有内容，而不是针对所使用的表。