Wednesday, July 11, 2007

Query to find out indexes created dynamically by SQL Server 2000

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.

1 comment:

fastfastlane said...

Nice site Vadivel.

Famed you and your blog on Blogging to Fame =)

Keep up the good work!