SQL Server 过程执行计划的奇怪问题 [英] Strange problem with SQL Server procedure execution plan

问题描述

我想知道你们是否能帮我找出我最近在 SQL Server 上遇到的一个奇怪问题的根源.

I was wondering if you guys could help me get to the bottom of a weird problem I have recently had on SQL Server.

我有一个存储过程(让我们调用 SPold)，它相当大，有很多计算(不可能在应用程序中这样做，因为大约 6000 个用户的信息需要在一个one-er(我根据姓氏将其减少到 1000)).存储过程通常在几秒钟内执行，并且每几分钟调用一次.

I have a stored procedure (lets call SPold) which is reasonably large with a lot of calculations (can't possibly do this in app as info for around 6000 users needs to come back in a one-er (I reduce this to 1000 based on Surname)). The stored procedure usually executes in a couple of seconds, and is called once every couple of minutes.

今天早上，存储过程的执行时间突然增加了 4-10 倍，导致多次超时.我发现通过使用新名称 (SPnew) 制作过程副本并执行，我将再次获得快速执行时间.这向我表明执行计划是原始SPold 的问题，所以我决定重新编译执行它.这将更快地返回结果(虽然不如 SPnew 快)，但用户对 SPold 的后续调用再次变慢.就好像新计划没有保留一样.

Now this morning, the stored procedure was suddenly taking 4-10 times as long to execute, causing a number of timeouts. I discovered that by making a copy of the procedure with a new name (SPnew) and executing, I would get the fast execution times again. This indicated to me that the execution plan was the problem with the original, SPold, so I decided to execute it with recompile. This would return the results a quicker (although not as fast as SPnew), but subsequent calls from users to SPold were once again slow. It was like the new plan wasn't being kept.

我所做的是解决这个问题，将 Exec SPnew 放入 SPold 中，现在对 SPold 的调用再次快速返回.

What I have done is to fix this is put Exec SPnew into SPold, and now calls to SPold are returning fast again.

有人知道这里发生了什么吗?唯一一夜之间更新的是统计数据，尽管我认为这应该会影响 SPold 和 SPnew.

Does anyone have any idea what is going on here? The only thing that updated overnight was the statistics, although I think that this should affect both SPold and SPnew.

推荐答案

我在 Sql Server 2005 中也遇到过两个奇怪"的情况，它们也可能与您的问题有关.

I've also encounterred two "strange" cases with Sql Server 2005, which might relate to your problem as well.

在第一种情况下，我的程序在以 dbo 身份运行时执行得非常快，而在不同用户帐户下从应用程序运行时执行速度很慢.

In the first case my procedure executed prety fast, when being run as dbo, and it was slow when being run from the application, under a different user account.

在第二种情况下，该过程的查询计划针对第一次调用该过程的参数值进行了优化，然后该计划随后也被其他参数值重复使用，导致执行速度缓慢.

In the second case the query plan of the procedure got optimized for the parameter values with which the procedure was called for the first time, and this plan was then reused later for other parameter values as well, resulting in a slow execution.

对于第二种情况，解决方案是将参数值复制到过程中的局部变量中，然后在查询中使用变量而不是参数.

For this second case the solution was to copy the parameter values into local variables in the procedure, and then using the variables in the queries instead of the parameters.

这篇关于SQL Server 过程执行计划的奇怪问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！