June 3, 2016 at 3:28 am
We migrated one DB SQL 2005 to SQL 2014 with all objects. Compatibility mode was changed to 120.
The problem is happening to us is that after migration, implementation of procedures was fast but occasionally when running from the application procedures gives time out. If you then recompile the procedure works well.
Before making the Recompile, if I delete the statistics the procedure continues to fail, it only works when recompiled.
I would like to know why the error occurs and how to fix it.
regards
June 3, 2016 at 5:34 am
There's not enough to go on, but I can make a couple of suggestions.
First, it sounds like you might be hitting bad parameter sniffing. If a recompile changes the behavior of the procedure, that's frequently the cause. You should capture the execution plan when the query is running slow and when it's running normally to compare the two. In all likelihood, there's a difference there, probably in the statistics, that can help explain what's happening.
Another issue is that the Cardinality Estimation engine within SQL Server 2014 is different than in 2005. By setting the compatibility mode to 120, you've enabled the new estimation engine. For most queries, most of the time, this will improve the execution plans generated and you'll get better performance. However, some queries actually benefited from less accurate row estimates. You might be seeing that now, probably in combination with parameter sniffing. The same process as above will help you to explain this, focusing on the row estimates to understand what you might be seeing.
Depending on the situation, you may be able to solve this using one of the many approaches to bad parameter sniffing resolution such as OPTIMIZE FOR some value or UNKNOWN, or using a RECOMPILE hint. However, you may also need to look to the TRACE FLAGs that can help you control how the Cardinality Estimator behaves.
You can read more about the Cardinality Estimator here. The TRACE FLAGs are detailed here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply