Monday, 7 June 2010

Bookmark : Create Indexes from the Missing Indexes DMV

I looked into Using Missing Indexes DMVs to generate index suggestions a while back. I've just found a better version on Seth Phelabaum's site however.

Create Indexes from the Missing Indexes DMV utilises one of Glenn Berry's performance queries and constructs CREATE INDEX statements for you.

It's a handy script that I've already used.
I did have to add the following to the statements though
WITH (MAXDOP=1,ONLINE=ON,DATA_COMPRESSION=PAGE)

MAXDOP because I don't want index creation to use multiple processors.
ONLINE because I'm using Enterprise SQL 2008 and don't want to lock tables
DATA_COMPRESSION because again I'm on Enterprise SQL 2008 and am utilising it's excellent inbuilt table and index compression.

No comments: