SQL Server Performance

Index on #temp table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, May 31, 2009.

  1. atulgoswami New Member

    (1) #temp tables used in SP causes recompilation and i read that creating indexes on #temp table reduces the recompilation.
    (2) Declaring all your #temp tables at one place will also contribute to reduce the recompilation.
  2. ndinakar Member

    (1) Not necessarily true. You can create temp tables in SPs.What causes recompilation when temp tables are used in sp's is the amount of data that is changed. If you have heavy inserts, updates, deletes into the temp tables it can trigger a recompile. I dont know where you got the "creating indexes on #temp table reduces the recompilation". Also changing the schema of the temp table can cause recompile.
    (2) True. And that "one place" is at the beginning of the query.
    in SQL 2005, there are DMVs that you can use t oidentify which procs are being recompiled. You can collect this info over time and identify any patterns and fix the procs accordingly.
  3. atulgoswami New Member

  4. FrankKalis Moderator

    From this article:
    "When using temporary tables always create them and create any indexes and then use them."
    Sorry, it's probably because I am not a native English speaker, but to me that very first sentence seems to be incomplete and vague in its meaning. I can only guess what graz had in mind with this, but I would say this should read something like "When using temporary tables always create them explicitly. It may be useful to create indices for some later queries that use this table". But that is just a guess.
    For recompilation issues in general you might want to read this: http://technet.microsoft.com/en-us/library/cc966425.aspx
  5. ndinakar Member

    I think what graz meant was, create your temp tables first, then create any indexes and then start using the temp tables. He said "this will reduce recompilations". What needs to be added to above statement is, create all the above stuff at the begining of the procedure. any DDL statements in the middle of a proc can cause the optimizer to throw away the plan that is has compiled until then because it thinks there is some schema change and needs to recompile the plan. So if you put all your CREATE statements at the begining of the proc, its taken into account while the plan is created. However, multiple data changes on the temp table can still cause recompiles.
  6. madhuottapalam New Member

    I agree with Dinakar... Index creation eliminates Re-compilation is a news to me. If you could post some reference it would be greate.
    The second point is basically, dont mixup DDL and DML inside a sp when you deal with Temp table.

Share This Page