September 1, 2015 at 7:36 am
After reading this article I have a question:
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
We use stored procedures which rely on temp tables created outside of the procedure to set up the data.
EX:
-- C# code does something like this:
Execute Select a, b, c, d INTO #tmp from myTable
-- SQL
create procedure foo AS BEGIN
Select x, y, z from another table where z IN
(SELECT D.a from #tmp D)
END
We also use various forms of inner, outer joins to the temp tables, derived queries against other tables using the temp data, etc.
My question is, is the execution plan for this procedure cached based on the first time the procedure is run and using the first-run temp data?
Edit: to ask a different way, if I were passing in parameters to the procedure, the execution plan would be cached the first time the procedure is run based upon the values that were passed in. Is the same true if you use temp tables instead of parameters?
September 1, 2015 at 11:59 am
Yes, it's going to compile based on the parameters you first pass in and whatever is in the temporary tables when the procedure gets compiled.
However, if anything modifies the data in those temporary tables and those modifications exceed the thresholds for automatic statistics update, you will see recompiles at the individual statement level for any references to the temporary table that had a statistics update.
So yeah, the plans will compile when they're first called based on the parameter values passed and the existing temporary table statistics. But, as those statistics change, you'll get recompiles.
Now, that could be good and that could be bad, but it's going to happen.
"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
September 1, 2015 at 12:09 pm
Thanks for the reply. In my case it may be beneficial. I'm trying to weigh whether or not to add the WITH RECOMPILE to these procedures. Sometimes they're called with little data passed in and sometimes with lots. Based on your thoughts, the procedure may be recompiling the execution plan each time; which in my case is preferred.
Is there a way to watch through Profiler to see that the plan is recompiled?
Thanks
ST
September 1, 2015 at 12:22 pm
Better to use Extended Events, but yeah, both have a recompile event that you can observe.
"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
September 1, 2015 at 2:53 pm
Back in SQL 2000 that construct would cause a recompile on every execution, no idea offhand for 2008. Fire up server-side trace or extended events (no GUI in 2008 though) and test.
If you do decide to recompile, put it as a hint on the queries that need it, not the procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2015 at 5:25 am
Thanks for the responses. Looking forward to testing and validating.
ST
September 2, 2015 at 12:07 pm
To follow up, I ran some tests in SQL2008R2. The execution plan is cached. I performed similar test as those in the article by running queries against the temp table containing many records and a single record. Different execution plans were generated. I then created a procedure and executed it with differing amounts of data in the temp tables. Same execution plan. I even dropped the temp table in between runs with the same result.
I added the OPTION (RECOMPILE) hint to the query in the procedure and the execution plan changed and the performance was better.
Good stuff and food for thought.
Thanks
ST
September 2, 2015 at 3:25 pm
souLTower (9/2/2015)
To follow up, I ran some tests in SQL2008R2. The execution plan is cached. I performed similar test as those in the article by running queries against the temp table containing many records and a single record. Different execution plans were generated. I then created a procedure and executed it with differing amounts of data in the temp tables. Same execution plan. I even dropped the temp table in between runs with the same result.I added the OPTION (RECOMPILE) hint to the query in the procedure and the execution plan changed and the performance was better.
Good stuff and food for thought.
Thanks
ST
I'd expect the drop & recreate to cause a plan recompile event. However, the other could be explained by the amount of data being changed. If it's less than 500 rows, you have to modify at least 500 before you get the automatic stats update. After that it's 20% of the total number rows (unless you've set a traceflag to affect that).
"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
September 3, 2015 at 5:05 am
To clarify, the drop/recreate of the procedure did likely recompile the procedure. My test was focused on whether or not the changing of the data in the temporary table, and even the drop/recreate of the temporary table would cause the resulting execution plans to differ. It did not.
Your note of 500 or 20% records is important. I worked with a query that I know behaves differently at 2 records versus 50 records in the temp table.
Thanks for the great discussion and forum
ST
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply