Feb 25 2010

Optimize SP recompilation in SQL Server 2008

Category: — zvolkov @ 07:40

In SQL Server 2000, SP recompilation was a subject of fear. No matter what one did one couldn't get it right. Since major causes of recompilation such as DDL, temp tables, and cursors are ever present in big SQL-based systems, recompilation was pretty much unavoidable. The good news is, with SQL 2005 statement-level recompilation feature most of these problems disappeared or at least became less prominent. Still, based on my experience, by following a few basic rules you can further minimize statement-level recompilation and make your heavily optimized SPs even more polished:

  1. Create all temp tables in one place, as early in the processing as possible, but not in the same SP that uses those tables. Ideally, your outermost SP should create all temp tables and execute a second level SP which will do all the processing. This way the queries in the second SP won’t be subjected to “deferred compilation”.
  2. Create all indexes on temp tables right after you create the tables, do not wait until the tables are populated with data. While deferring index creation until the data is inserted may make the index creation faster, it also causes recompilations of subsequent query plans due to “schema change”.
  3. Do not drop the temp tables when you done with them. They will be dropped automatically when the outermost proc that created them finishes. Dropping them explicitly, confuses SQL Server into thinking that these tables are not the same between executions of the proc, causing it to recompile the query plan due to “temp table change”.
  4. If outermost SP is called multiple times from your application, make sure it’s called on a new SQL Connection every time. Once again, calling it on the same connection confuses SQL Server into thinking you’re trying to create a new temp table with the same name, so it recompiles the query plan due to “temp table change”.

Not only these conclusions are unintuitive, they directly contradict some of the things I see in the MSDN whitepaper: Plan Caching in SQL Server 2008. However if you think I pulled them from [behind] my butt, you're wrong :). I used the SQL Profiler. Try it and see for yourself:

  1. Start SQL Management studio
  2. From Tools menu, start SQL Profiler
  3. In SQL Profiler, start a new trace
  4. Connect to your server
  5. Switch to the Events Tab
  6. Remove all default events by right-clicking on them and choosing Deselect Event Category
  7. Check the “Show All Events” checkbox
  8. In the TSQL category, add SQL:StmtRecompile
  9. If you want to determine the statement that causes the recompile, in Stored Procedure category, add SP:StmtStarting and SP:StmtCompleted
  10. The columns you will need are TextData and EventSubClass (check Show All Columns checkbox to see the latter)
  11. You can leave the trace without filters, but if you tracing a shared SQL server you may want to filter out other users. This can be done in Column Filters.

Or, if you shy of the profiler you can try the amazing SQLTRACE stored proc by Lee Tudor. Seeing is believing!

Tags:

Comments

Comments are closed