Geek Answers Handbook

Strange SQL Server Procedure Execution Plan Issue

I was wondering if you guys could help me sort out some strange problem that I recently had on SQL Server.

I have a stored procedure (allows you to call SPold ), which is quite large with a lot of calculations (maybe this cannot be done in the application, since information for about 6,000 users should be returned in one-er (I reduce this to 1000 based on Last Name) ) A stored procedure usually runs after a couple of seconds and is called once every couple of minutes.

This morning, the stored procedure suddenly lasted 4-10 times, which caused a lot of timeouts. I found that by making a copy of the procedure with a new name ( SPnew ) and executing it, I again got a quick execution sequence. This indicated that the execution plan was a problem with the original, SPold , so I decided to execute it with recompilation. This will return results faster (although not as fast as SPnew ), but subsequent calls from users to SPold were again slow. It was like the new plan was not preserved.

What I did was fix it by placing Exec SPnew in SPold , and now the calls to SPold return again.

Does anyone know what is going on here? The only thing that was updated in one night is the statistics, although I think this should affect both SPold and SPnew .

+4
source share
2 answers

I also came across two “strange” cases with Sql Server 2005, which may also be related to your problem.

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

In the second case, the procedure request plan was optimized for the parameter values ​​with which the procedure was called for the first time, and this plan was subsequently reused for other parameter values, which led to slow execution.

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

+4
source

It looks like you are experiencing an incorrectly cached query plan due to the sniffing parameter.

Can you publish a stored procedure?

In SQL Server 2005, you can use the OPTIMIZE FOR query hint for preferred parameter values ​​to resolve some issues related to the sniffing parameter

OPTIMIZE FOR Tells the query optimizer to use a specific value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution. OPTIMIZE FOR can counteract the optimizer parameter detection behavior or can be used when you create plan guides. For more information, see Recompile Stored Procedures and Query Optimization in Deployed Applications using the configuration guides .

Although SQL Server 2005 does not support OPTIMIZE FOR UNKNOWN (introduced in SQL Server 2008), which will eliminate the sniffing parameter for the given parameter:

 OPTION (OPTIMIZE FOR (@myParam UNKNOWN))

You can achieve the same effect in SQL Server 2005 by copying this parameter to a local variable, and then use the local variable in the query.

+5
source

More articles:


All Articles