Yesterday's news

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!

SQL Prompt ($195, 14 days free trial) is an add-on for SQL Management Studio (and Visual Studio too) similar in its idea to SQL Assistant, which I covered last time.

While definitely targeting the same auditory of hard-core SQL developers, SQL Prompt arguably delivers less bang for its higher buck. While SQL Assistant can, with some degree of wishfull thinking, be called “Resharper for SQL”, this honorary title does not feel appropriate for SQL Prompt. While a cursory look will find the same core features as in SQL Assistant — better Intellisense, code auto-formating and customizable snippets, what strikes is complete absence of in-editor enhancements. Here’s a list of features I particularly missed:

• commenting out selected block of code by pressing Ctrl+/. 
• jumping to matching )s and BEGIN/END statements by pressing Ctrl+Alt+B
• auto-highlighting all occurances of the word the text cursor is on
• interactive syntax helper (Ctrl+F1)
• real-time syntax validation status, in the editor as well as in the scrollbar area

In simple language, what I actually want is just that — Resharper for SQL — complete with “Find Usages” and “Go To Declaration”.

The good news is, SQL Prompt does feel faster, and its fewer features have subtly more refined polish. By that I mean little quirks of SQL Assistant like swallowing some space bar hits or displaying intellisense menu when not needed, seem less prominent with SQL Prompt.

The bottom line: if I were you I would not buy either of these. Let’s wait until one of the companies reads this blog. Or, who knows, maybe one day JetBrains will come up with Resharper for SQL?

UPDATE: found a free SSMS add-in called “SSMS Tools Pack“, will check it out and post soon.