My photo
Auckland, New Zealand
Smurf sized geeky person with a penchant for IT, gaming, music and books. Half of industrial duo 'the craze jones'. Loves data, learning new things, teaching new things and being enthusiastic.

Wednesday 12 August 2009

Effective Indexes for Beginners

I've created a presentation for the August SQL User Group which is about using 'sys.dm_db_index_usage_stats' DMV in order to evaluate your index usage.

The presentation and code sample is available to download on the right.

2 comments:

Anonymous said...

Hi Amanda, thanks for making the defrag indexes document available for download.
I ran it for one of our databases. In the results grid are returned two tables - before and after. The first table has 110 rows and the second table has 644 rows. Do you know why the second table has more rows?
Jonathan

Manda QoP said...

Hi Jonathan,

I'm assuming you've run the script to defrag all indices? The one
with the huge comment at the top? If this is the case then the final
result set will show all indices on your table. The reason for this
is that the first result set only shows you indices that are greater
than 5% fragmented so that you can see which indices need to be
defragged. The final result set shows ALL indices on your database so that you can check to make sure defragmentation has occurred
correctly. If it has and an index has been defragged to below 5%
then it would disappear from your results set if we didn't open it up
to show all indices.

Cheers,
Amanda