第一次在SQL Server存储过程中运行缓慢 [英] First run slowness in a sql server stored procedure

问题描述

我有一个存储过程，该过程每天运行9次，仅在午夜之后.它不是理想的存储过程，但是您知道它是怎么回事.没有计划能够与现实相遇.

I have a stored procedure that is run nine times a day, just after midnight. It isn't an ideal stored procedure, but you know how it is. No plan survives contact with reality.

此存储过程通常需要大约一分钟来运行，要花一些时间才能处理它要处理的数据量.但是，在给定的早晨进行第一次跑步时，有时会花很长时间，有时甚至比正常情况下要花费的时间长很多(如果完成了).如果我杀死它并重新启动它，它将正常运行.

This stored procedure normally takes about a minute to run, give or take time for the volume of data it processes. However, on the first run for a given morning, sometimes it will take an inordinate amount of time, sometimes well over an order of magnitude longer than the amount of time that it normally takes (if it finishes at all). If I kill it and start it again, it runs normally.

我正在为此寻求一个完美的解决方案-至少比我的第一个想法更优雅，这是要先花一点额外的精力，因为它不会生成我使用的数据，并且可以容忍其失败.

I'm looking for an elegant fix for this - at least more elegant than my first idea, which is to slap an extra run to go first which doesn't generate data I use and failure of which can be tolerated.

有人以前见过这种行为吗?您是如何解决的?

Has anyone seen this behavior before? How did you resolve it?

推荐答案

可能是编译时间和冷数据缓存(缓冲池).而且，如果通常只需要一分钟，那么我想它也很笨拙.

It's probably compile time and a cold data cache (buffer pool). And if it takes one minute normally, then I guess it's quite chunky too.

编译时间:执行计划在统计信息更新时失效.如果您有大量流程或通宵维护，则可能会遇到此问题

Compile time: an execution plan is invalidated on statistics update. If you have bulk processes or overnight maintenance you will probably hit this

冷缓存:数据/索引页必须从磁盘进入内存.

Cold cache: data/index page have to come from disk into memory.

为减轻这些压力:

• 假人跑(如前所述)
• 更快的IO或更多的内存
• 计划指南

例如，在我们的网站超时的情况下，有时我们会遇到相同的问题，尤其是在开发箱上.我们只是再次点击...

We have the same issues, sometimes, especially on development boxes, to the extent that our websites time out for example. We just click again...

这篇关于第一次在SQL Server存储过程中运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！