Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Complete horlicks. Variables are not scoped in SQL Server except when declared and assigned in a loop (and then it isn't intentional) –  gbn Nov 1 '11 at 19:28
    
Hey Brad and welcome to dba.se! I made an edit to your question so that what you're asking is summarized at the bottom. –  Nick Chammas Nov 1 '11 at 19:45

2 Answers 2

up vote 14 down vote accepted

No.

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+):

  1. Schema changed.
  2. Statistics changed.
  3. Recompile DNR.
  4. Set option changed.
  5. Temp table changed.
  6. Remote rowset changed.
  7. For browse perms changed.
  8. Query notification environment changed.
  9. MPI view changed.
  10. Cursor options changed.
  11. 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.

share|improve this answer
    
@MikeWalsh - I feel the OP's question is specifically about validating the DBA's recommendation about grouping variable declarations. The problem of blocking (which your answer addresses) is just the background to the question. No? (I edited the OP's question on this basis.) –  Nick Chammas Nov 1 '11 at 20:12
    
I clarified my answer. My original answer did get at the asked question. I was simply stating where the DBA was thinking and the OP mentioned the compile lock issue (perhaps not by name) and I was directing to what can cause compile locks. Standing by my comment as well as my upvote of yours.... –  Mike Walsh Nov 1 '11 at 20:14
    
I was addressing the "schema lock" comment. Which is where the schema/owner qualifying comment came in. –  Mike Walsh Nov 1 '11 at 20:14
2  
+1 This is what J&J would call a canonical answer. –  Mark Storey-Smith Nov 2 '11 at 0:24
1  
@Nick - Here's a script. If you run it with the include actual execution plan option enabled notice that the estimated number of rows coming out of the table scan with the X < 0 predicate pushed into the scan is wrong (based on initial stats distribution adjusted by table cardinality) then corrects itself after the 7th row is added. –  Martin Smith Nov 2 '11 at 22:38

It should not make a difference or reduce compile locks or cause less recompilations to declare a variable half way down the stack or at the top. I happen to do this at the top for readability more often than not.

To get at the "what is my DBA thinking" part of the question, the only thing I can come up with (other than Nick's point that they are thinking of how something used to be) is perhaps they were talking about Parameter Sniffing (See Option 2 at this link on simple talk)

About your blocking --> If you are seeing true blocking, that isn't the type of compile lock contention that your DBA is talking about most likely. While it is true that there are certain things that affect this (not schema qualifying tables, not schema qualifying your stored procedure calls, for instance) this is not the cause of your high reads certainly and likely not the cause of your blocking. You should definitely do all you can to avoid these compile locks. But I would look at tuning and optimizing the rest of the stored procedure code as a more important task than worrying about where the variables are. You can also read How to identify and resolve compile locks if you want to verify you aren't experiencing issues here.

Post those before/after examples and we'll see what the DBA is driving at here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.