Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/pr...005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn th
is
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it.
If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where th
e
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled pla
n.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead o
f
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creati
ng
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote: