While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should declare all variables (especially
TABLE ones) at the top of the sproc to avoid recompiles.
This is the first I have heard of this and was looking for some confirmation before revisiting all the different stored procedures we have. He was calling it "late viewing of the code", and the recompile was locking the schema which would account for the blocking.
Does moving all variable declarations to the top of your stored procedure reduce recompiles?
This either used to be true a long time ago (and no longer is, at least since SQL Server 2000), or it never was true and your DBA just confused his recommendation with the following one:
It is important to group together all DDL statements (like creating indexes) for temporary tables at the start of a stored procedure. By placing these DDL statements together unnecessary compilations due to schema change can be avoided.
You can find another explanation of the reasoning behind this recommendation on this page.
If we take a look at this Microsoft KB, we see that the cause of a stored procedure recompile can be one of the following (SQL Server 2005+):
- Schema changed.
- Statistics changed.
- Recompile DNR.
- Set option changed.
- Temp table changed.
- Remote rowset changed.
- For browse perms changed.
- Query notification environment changed.
- MPI view changed.
- Cursor options changed.
- With recompile option.
Declaring a variable -- even a table variable (i.e.
@table_variable) -- cannot trigger any of these events, obviously, because declaring a variable doesn't count as DDL. A variable (even a table variable) is a temporary object used exclusively for your T-SQL programming. That's why table variables get no statistics and are not bound by transactions. Declaring a variable (table or not) cannot trigger a proc recompile.
Creating a temp table (i.e.
#temp_table) or an index, however, is DDL that affects the physical definition of the database. Temp tables and indexes are "real" objects with statistics and transactional control, therefore creating them could fire any of events 1, 2, or 5 in the list above and thus trigger a proc recompile.