So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' into a third temp table which is created using CREATE. Then this resulting temp table is joined with other tables in the DB to generated the result after some grouping etc. The problem is that this SP had been running fine until now returning results in 1-2 minutes. And now suddenly its taking 12-15 minutes. If I extract the query from the SP and executed it in the management studio by manually setting the same parameters it returns results in 1-2 minutes but the SP takes very long. Any idea what could be happening. I tried to generate the Actual Execution plans of both the Query and the SP but it couldn't generate it because of the cursor. Any idea why the SP takes so long while the query doesn't?
基本上我有这个相对长的存储过程。基本的执行流程是，它将一些数据选择到用he #符号声明的临时表中，然后在这些表中运行一个游标，生成一个“正在运行的total”到第三个临时表中，该表是使用CREATE创建的。然后，这个生成的临时表与数据库中的其他表一起，在一些分组等之后生成结果。问题是，这个SP一直运行良好，直到现在返回结果1-2分钟。现在突然花了12-15分钟。如果我从SP中提取查询并在management studio中手动设置相同的参数来执行，它会在1-2分钟内返回结果，但是SP需要很长时间。知道会发生什么。我尝试生成查询和SP的实际执行计划，但由于光标的原因，它无法生成。您知道为什么SP需要这么长时间，而查询却没有吗?
10 个解决方案
#1
34
This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance - parameter sniffing
这是参数嗅探的足迹。关于它的另一个讨论见这里;SQL糟糕的存储过程执行计划性能-参数嗅探。
There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time.
有几种可能的修复方法，包括将RECOMPILE添加到存储过程中，这一过程的工作时间大约为一半。
The recommended fix for most situations (though it depends on the structure of your query and sproc) is to NOT use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries.
对于大多数情况(尽管这取决于查询和sproc的结构)，建议的修复方法是不直接在查询中使用参数，而是将它们存储到本地变量中，然后在查询中使用这些变量。
#2
2
I'd also look into parameter sniffing. Could be the proc needs to handle the parameters slighlty differently.
我还将研究参数嗅探。可能是proc需要处理的参数稍有不同。
#3
2
its due to parameter sniffing. first of all declare temporary variable and set the incoming variable value to temp variable and use temp variable in whole application here is an example below.
其原因是参数嗅探。首先，声明临时变量并将传入的变量值设置为temp变量，并在整个应用程序中使用临时变量。
ALTER PROCEDURE [dbo].[Sp_GetAllCustomerRecords]
@customerId INT
AS
declare @customerIdTemp INT
set @customerIdTemp = @customerId
BEGIN
SELECT *
FROM Customers e Where
CustomerId = @customerIdTemp
End
try this approach
试试这个方法
#4
1
Try recompiling the sproc to ditch any stored query plan
尝试重新编译sproc来丢弃任何存储的查询计划。
exec sp_recompile 'YourSproc'
Then run your sproc taking care to use sensible paramters.
然后运行你的sproc小心使用明智的参数。
Also compare the actual execution plans between the two methods of executing the query.
还比较了执行查询的两种方法的实际执行计划。
It might also be worth recomputing any statistics.
它也可能值得重新计算任何统计数据。
#5
1
I usually start troubleshooting issues like that by using "print getdate() + ' - step '". This helps me narrow down what's taking the most time. You can compare from where you run it from query analyzer and narrow down where the problem is at.
我通常通过使用“print getdate() + ' - step '”来开始解决类似的问题。这可以帮助我缩小花费最多时间的范围。您可以从查询分析器中运行它，并在问题所在的地方进行比较。
#6
0
I would suggest the issue is related to the type of temp table (the # prefix). This temp table holds the data for that database session. When you run it through your app the temp table is deleted and recreated.
You might find when running in SSMS it keeps the session data and updates the table instead of creating it. Hope that helps :)
我认为这个问题与temp表的类型(#前缀)有关。这个临时表保存该数据库会话的数据。当您在应用程序中运行时，temp表被删除并重新创建。您可能会发现，在SSMS中运行时，它保存会话数据并更新表，而不是创建表。希望帮助:)
#7
0
I would guess it could possible be down to caching. If you run the stored procedure twice is it faster the second time?
我猜可能是缓存。如果您运行两次存储过程，第二次会更快吗?
To investigate further you could run them both from management studio the stored procedure and the query version with the show query plan option turned on in management studio, then compare what area is taking longer in the stored procedure then when run as a query.
为了进一步研究，您可以在management studio中运行存储过程和查询版本，并在management studio中打开show query plan选项，然后比较在存储过程中以查询的形式运行的时间更长。
Alternativly you could post the stored procedure here for people to suggest optimizations.
另外，您可以在这里发布存储过程，让人们建议进行优化。
#8
0
For a start it doesn't sound like the SQL is going to perform too well anyway based on the use of a number of temp tables (could be held in memory, or persisted to tempdb - whatever SQL Server decides is best), and the use of cursors.
首先，由于使用了大量的临时表(可以保存在内存中，或者持久化到tempdb——无论SQL Server决定如何最好)，以及游标的使用，SQL似乎不会表现得太好。
My suggestion would be to see if you can rewrite the sproc as a set-based query instead of a cursor-approach which will give better performance and be a lot easier to tune and optimise. Obviously I don't know exactly what your sproc does, to give an indication as to how easy/viable this is for you.
我的建议是看看是否可以将sproc重写为基于集合的查询，而不是基于指针的方法，这种方法将提供更好的性能，并且更容易调优和优化。很明显，我不知道你们的sproc具体做什么，来说明这对你们来说有多简单/可行。
As to why the SP is taking longer than the query - difficult to say. Is there the same load on the system when you try each approach? If you run the query itself when there's a light load, it will be better than when you run the SP during a heavy load.
至于为什么SP比查询花费的时间更长——很难说。当您尝试每种方法时，系统上是否有相同的负载?如果在负载较轻的情况下运行查询本身，将比在负载较重的情况下运行SP要好。
Also, to ensure the query truly is quicker than the SP, you need to rule out data/execution plan caching which makes a query faster for subsequent runs. You can clear the cache out using:
此外，为了确保查询的速度比SP更快，您需要排除数据/执行计划缓存，以便在后续的运行中更快地查询。您可以使用以下方法清除缓存:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
But only do this on a dev/test db server, not on production. Then run the query, record the stats (e.g. from profiler). Clear the cache again. Run the SP and compare stats.
但只在开发/测试db服务器上执行此操作，而不是在产品上。然后运行查询，记录统计数据(例如从剖析器)。清除缓存。运行SP并比较统计数据。
#9
0
1) When you run the query for the first time it may take more time. One more point is if you are using any corellated sub query and if you are hardcoding the values it will be executed for only one time. When you are not hardcoding it and run it through the procedure and if you are trying to derive the value from the input value then it might take more time.
1)第一次运行查询时，可能需要更多的时间。还有一点是，如果您使用的是corellated子查询，如果您是硬编码的，它将只执行一次。当您没有对它进行硬编码并在过程中运行时，如果您试图从输入值中获得值，那么它可能需要更多的时间。
2) In rare cases it can be due to network traffic, also where we will not have consistency in the query execution time for the same input data.
2)在极少数情况下，它可能是由于网络流量，也就是我们在同一输入数据的查询执行时间上没有一致性。
#10
0
I too faced a problem where we had to create some temp tables and then manipulating them had to calculate some values based on rules and finally insert the calculated values in a third table. This all if put in single SP was taking around 20-25 min. So to optimize it further we broke the sp into 3 different sp's and the total time now taken was around 6-8 mins. Just identify the steps that are involved in the whole process and how to break them up in different sp's. Surely by using this approach the overall time taken by the entire process will reduce.
我也遇到了一个问题，我们必须创建一些临时表，然后操作它们，必须基于规则计算一些值，最后将计算值插入第三个表中。如果把SP放在一个SP里，大约需要20-25分钟，为了进一步优化，我们把SP分成3个不同的SP，现在总共花了6-8分钟。只需确定整个过程中涉及的步骤，以及如何在不同的sp中分解它们。当然，通过使用这种方法，整个过程所花费的时间将会减少。
#1
34
This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance - parameter sniffing
这是参数嗅探的足迹。关于它的另一个讨论见这里;SQL糟糕的存储过程执行计划性能-参数嗅探。
There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time.
有几种可能的修复方法，包括将RECOMPILE添加到存储过程中，这一过程的工作时间大约为一半。
The recommended fix for most situations (though it depends on the structure of your query and sproc) is to NOT use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries.
对于大多数情况(尽管这取决于查询和sproc的结构)，建议的修复方法是不直接在查询中使用参数，而是将它们存储到本地变量中，然后在查询中使用这些变量。
#2
2
I'd also look into parameter sniffing. Could be the proc needs to handle the parameters slighlty differently.
我还将研究参数嗅探。可能是proc需要处理的参数稍有不同。
#3
2
its due to parameter sniffing. first of all declare temporary variable and set the incoming variable value to temp variable and use temp variable in whole application here is an example below.
其原因是参数嗅探。首先，声明临时变量并将传入的变量值设置为temp变量，并在整个应用程序中使用临时变量。
ALTER PROCEDURE [dbo].[Sp_GetAllCustomerRecords]
@customerId INT
AS
declare @customerIdTemp INT
set @customerIdTemp = @customerId
BEGIN
SELECT *
FROM Customers e Where
CustomerId = @customerIdTemp
End
try this approach
试试这个方法
#4
1
Try recompiling the sproc to ditch any stored query plan
尝试重新编译sproc来丢弃任何存储的查询计划。
exec sp_recompile 'YourSproc'
Then run your sproc taking care to use sensible paramters.
然后运行你的sproc小心使用明智的参数。
Also compare the actual execution plans between the two methods of executing the query.
还比较了执行查询的两种方法的实际执行计划。
It might also be worth recomputing any statistics.
它也可能值得重新计算任何统计数据。
#5
1
I usually start troubleshooting issues like that by using "print getdate() + ' - step '". This helps me narrow down what's taking the most time. You can compare from where you run it from query analyzer and narrow down where the problem is at.
我通常通过使用“print getdate() + ' - step '”来开始解决类似的问题。这可以帮助我缩小花费最多时间的范围。您可以从查询分析器中运行它，并在问题所在的地方进行比较。
#6
0
I would suggest the issue is related to the type of temp table (the # prefix). This temp table holds the data for that database session. When you run it through your app the temp table is deleted and recreated.
You might find when running in SSMS it keeps the session data and updates the table instead of creating it. Hope that helps :)
我认为这个问题与temp表的类型(#前缀)有关。这个临时表保存该数据库会话的数据。当您在应用程序中运行时，temp表被删除并重新创建。您可能会发现，在SSMS中运行时，它保存会话数据并更新表，而不是创建表。希望帮助:)
#7
0
I would guess it could possible be down to caching. If you run the stored procedure twice is it faster the second time?
我猜可能是缓存。如果您运行两次存储过程，第二次会更快吗?
To investigate further you could run them both from management studio the stored procedure and the query version with the show query plan option turned on in management studio, then compare what area is taking longer in the stored procedure then when run as a query.
为了进一步研究，您可以在management studio中运行存储过程和查询版本，并在management studio中打开show query plan选项，然后比较在存储过程中以查询的形式运行的时间更长。
Alternativly you could post the stored procedure here for people to suggest optimizations.
另外，您可以在这里发布存储过程，让人们建议进行优化。
#8
0
For a start it doesn't sound like the SQL is going to perform too well anyway based on the use of a number of temp tables (could be held in memory, or persisted to tempdb - whatever SQL Server decides is best), and the use of cursors.
首先，由于使用了大量的临时表(可以保存在内存中，或者持久化到tempdb——无论SQL Server决定如何最好)，以及游标的使用，SQL似乎不会表现得太好。
My suggestion would be to see if you can rewrite the sproc as a set-based query instead of a cursor-approach which will give better performance and be a lot easier to tune and optimise. Obviously I don't know exactly what your sproc does, to give an indication as to how easy/viable this is for you.
我的建议是看看是否可以将sproc重写为基于集合的查询，而不是基于指针的方法，这种方法将提供更好的性能，并且更容易调优和优化。很明显，我不知道你们的sproc具体做什么，来说明这对你们来说有多简单/可行。
As to why the SP is taking longer than the query - difficult to say. Is there the same load on the system when you try each approach? If you run the query itself when there's a light load, it will be better than when you run the SP during a heavy load.
至于为什么SP比查询花费的时间更长——很难说。当您尝试每种方法时，系统上是否有相同的负载?如果在负载较轻的情况下运行查询本身，将比在负载较重的情况下运行SP要好。
Also, to ensure the query truly is quicker than the SP, you need to rule out data/execution plan caching which makes a query faster for subsequent runs. You can clear the cache out using:
此外，为了确保查询的速度比SP更快，您需要排除数据/执行计划缓存，以便在后续的运行中更快地查询。您可以使用以下方法清除缓存:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
But only do this on a dev/test db server, not on production. Then run the query, record the stats (e.g. from profiler). Clear the cache again. Run the SP and compare stats.
但只在开发/测试db服务器上执行此操作，而不是在产品上。然后运行查询，记录统计数据(例如从剖析器)。清除缓存。运行SP并比较统计数据。
#9
0
1) When you run the query for the first time it may take more time. One more point is if you are using any corellated sub query and if you are hardcoding the values it will be executed for only one time. When you are not hardcoding it and run it through the procedure and if you are trying to derive the value from the input value then it might take more time.
1)第一次运行查询时，可能需要更多的时间。还有一点是，如果您使用的是corellated子查询，如果您是硬编码的，它将只执行一次。当您没有对它进行硬编码并在过程中运行时，如果您试图从输入值中获得值，那么它可能需要更多的时间。
2) In rare cases it can be due to network traffic, also where we will not have consistency in the query execution time for the same input data.
2)在极少数情况下，它可能是由于网络流量，也就是我们在同一输入数据的查询执行时间上没有一致性。
#10
0
I too faced a problem where we had to create some temp tables and then manipulating them had to calculate some values based on rules and finally insert the calculated values in a third table. This all if put in single SP was taking around 20-25 min. So to optimize it further we broke the sp into 3 different sp's and the total time now taken was around 6-8 mins. Just identify the steps that are involved in the whole process and how to break them up in different sp's. Surely by using this approach the overall time taken by the entire process will reduce.
我也遇到了一个问题，我们必须创建一些临时表，然后操作它们，必须基于规则计算一些值，最后将计算值插入第三个表中。如果把SP放在一个SP里，大约需要20-25分钟，为了进一步优化，我们把SP分成3个不同的SP，现在总共花了6-8分钟。只需确定整个过程中涉及的步骤，以及如何在不同的sp中分解它们。当然，通过使用这种方法，整个过程所花费的时间将会减少。