Subscribe to:
Post Comments (Atom)
Labels
About Me
Best Practices
Career
Data Mining
Documentation
Feature Requests
Humor
MagicPASS
Meme Monday
Mirroring
Parameter Sniffing
PASS
Performance
PowerShell
Presentations
Query Tuning
Recognition
Replication
Scripts
Security
SQL Power Doc
SQL Server 2005
SQL Server 2008
SQLH2
SQLRally
SQLSaturday
SYDI
T-SQL Tuesday
Tips
Troubleshooting
Updates
VirtualBox
Windows
XML
Copyright © 2015 Kendal Van Dyke. All rights reserved.
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
[About Kendal]
(http://lh3.googleusercontent.com/-wrW1fk8IiFE/Vr36w9dtRxI/AAAAAAAADCw/tVa4vTgLWIw/w139-h140-p/IMG_3503.JPG)
4 comments
This week I experienced a similar issue and I saw different query plans which came from the first parameters that were passed into a stored procedure when the query was compiled. The ultimate goal was to get a stable query plan, and that ultimately led to this question on Stack Overflow.
Maybe try to dig into the query plans on your server to try to determine what values (if any) the query was compiled with. In my case the differences boiled down to different "estimated row count" values even though the query was the same, stats were the same and the only thing different was the parameters that the query got compiled with.
This ultimately led to looking at query hints like: OPTIMIZE FOR() and OPTIMIZE FOR UNKNOWN
I'm gonna have to go with Michael on this, and it's not just a name thing {=0).
It sounds like it's related to parameter sniffing. The first time the proc compiles the optimizer uses the actual parameters passed in to the proc. This can cause a less than optimal plan to be cached depending on what parameters are used first.
Another option for testing the theory is to start the proc by copying the parameters to local variables and using the variables in the rest of the proc. Since the variables aren't calculated until the actual execution the optimizer will make a guess as to what the value might be based on data distribution in the table. This may not give you the best plan, but it should be a consistent one. Useful for troubleshooting, if nothing else.
I didn't see anyone else comment on this, but Plan A is terminating early within the optimization process because of a timeout. Plan B is completing it's optimization. That would explain the differences. Why precisely Plan is terminating is hard to know, but the differences in SQL Server version and any other load on the server could suggest that one of the servers isn't able to complete the optimization process, hence the different plan.
You can see this by looking at the Properties for the INSERT operator on each plan.
Post a Comment