When queries / SP's hits those tables where appropriate indexes are not there then the database engine would automatically create indexes to be used by its execution plan.
The hitch is those dynamic indexes would not be reused by the engine the next time when it hits the same table / column. i.e., it would create another dynamic index each time when it hits the same table / column.
That said, normally till SQL Server 2000 I used to execute the below statement to list down those dynamically created indexes. So that i can analyze and create indexes appropriately.
Select [name] from sysindexes where [name] like '_WA_%'
I would write more on this shortly.
The hitch is those dynamic indexes would not be reused by the engine the next time when it hits the same table / column. i.e., it would create another dynamic index each time when it hits the same table / column.
That said, normally till SQL Server 2000 I used to execute the below statement to list down those dynamically created indexes. So that i can analyze and create indexes appropriately.
Select [name] from sysindexes where [name] like '_WA_%'
I would write more on this shortly.
Comments
Famed you and your blog on Blogging to Fame =)
Keep up the good work!