Name that cartoon and if you remember that cartoon (cica early 90′s) I just dated myself a bit.
Any guesses?? I know, the suspense is killing you.
So, back in the day, there used to be a cartoon, Captain Planet. The basis of the story is that 5 teenagers each had a ring that represented different elements, namely wind, water, earth, fire and heart (is that an element?). When the planet was facing some ecological disaster, the would bring the rings together and summon Captain Planet to save the world and remove the ecological evil doers! His catch phrase was “Our powers combined, I am Captain Planet!”.
Uhhh, So What?
This will make sense, I promise. (I hope anyway!) So, Thursday evening the development team that I work on released our first version of our application. Essentially, it’s a SilverLight application that works with GIS data, which is stored in a database located on a 2008 SQL Server. The application allows the user to select certain parameters and submit a SELECT query to the database. Pretty simple right? Everything deployed nicely. No major hiccups.
Until we verified the search part. Attempting to return 6k or so rows caused the application to time out. This basically means that the SQL Server didn’t return the 6k or so rows within 30 seconds.
You see, my jaw hit the floor because we throughly test things in several lower environments (3 of them to exact) before moving to production. We had one performance issue which was resolved by an index change (Power of the Index!) but other than that, it was all good. Life was good.
Get to the point right?
So, after some investigation it was determined that the search query was getting parameter sniffed. Cool. Easy Peasy and relatively easy to get fixed that up (depending on what you want to do).
However, in the midst of our trouble shooting, we stumbled onto the fact that the environmental settings has an impact on the query plan that is generated. This “environmental” tidbit combined with the fact that there are other “elements” which cause the engine to select the appropriate plan caused me to think about Captain Planet. See how I put those together? Environment and element? Ahh, I kill me sometimes!
Let’s look at our “Environmental” Ring
Let’s say we have the following query:
-- Using AdventureWorks on SQL 2008 Developer SET ANSI_WARNINGS ON SELECT Title, FirstName, LastName, EmailAddress FROM Person.Contact GO
And we look at the query plan for using
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as st WHERE st.text like '%Person.Contact%' and st.text not like '%sys.%
You can see that we have a single cached plan.
What if we added another environment setting? Say
-- Using AdventureWorks on SQL 2008 Developer SET ANSI_WARNINGS ON SET ANSI_PADDING ON SELECT Title, FirstName, LastName, EmailAddress FROM Person.Contact GO
What do we get? Another execution plan!!
Why do you ask? Excellent question! You’ll notice that the text for the 2 plans are different because the execution plan is generated from what ever you pass into the engine in a single batch. In this case, the environment options are being explicitly defined thus overriding the defaults of SQL Server. These explicit values are hashed together to form a unique execution plan, thus when you add/change/delete one of the options explicitly, the hash for the query has changed thus a new and different execution plan is created!
If you were to separate the SET commands like so:
SET ANSI_WARNINGS ON GO SET ANSI_PADDING ON GO SELECT Title, FirstName, LastName, EmailAddress FROM Person.Contact GO
Then you would get the same execution plan for the SELECT statement as the environment options are handled as separate queries (batches) to the engine.
You can see above that the SELECT statement has it’s own plan and there isn’t even a plan for the explicit SET commands. You would be able to execute the SELECT statement countless times and assuming that nothing else changes in the text of the query, it would continue to utilize the same query plan.
Maybe I’m slow (no jokes please!), but I never knew this! In thinking about it, however, it makes total sense. I know that you are all probably going “Duh John, this is junior level DBA stuff”, but I must admit that I didn’t know this. Knowing is half the battle right? (Whoops! Wrong cartoon!)
When we were trouble shooting our performance issue, this didn’t even cross my mind. So I guess the point is that when trouble shooting execution plans, pay attention to what environmental variables are being set as they will affect how the plan is generated. In our case, this realization helped us to realize that we were getting a bad plan and helped us to get the issue resolved.
Knowing what elements to look for in troubleshooting an query plan will help you to solve the problem. When all of the elements are combined, you just might see Captain Execution Plan!