In the last SQL Server users group meeting (#120) on July 9th I spoke about common pitfalls when analyzing execution plans. Here are the materials from my presentation:
One of the things I talked about is the fact that SQL Server Management Studio sets the ARITHABORT option to True by default, while most database providers set this option to False by default. This causes a different plan to be generated for the application and for SSMS. Stas Bogachinsky mentioned that there is an item in Connect about it. So if you agree that this default behavior is problematic, please vote. Thanks, Stas!
I also presented a user-defined function that extracts the plan of a specific statement (based on the statement text) from a complete execution plan of a whole batch (based on a plan handle). Michael Zilberstein mentioned that there is already a dynamic management function called sys.dm_exec_text_query_plan that does exactly that. This function can indeed return the plan of a specific statement within a batch based on the statement start offset and end offset. Notice that the function returns the ShowPlan representation, but in text format (NVARCHAR(MAX)). So you need to convert it to XML, and then you can get the graphical execution plan by clicking on the XML value.
I updated the script, so the zip file already includes this dynamic management function instead of the user-defined function that I presented. I wasn’t aware of this function, so thanks, Michael!
I plan to write soon a series of posts about these pitfalls and others, so stay tuned…