Page 86 - DLIS405_INFORMATION_STORAGE_AND_RETRIEVAL

Unit 8: Indexing

add new indexes to your environment, don’t forget to review all of your indexes to make sure you Notes
have not included any new duplicate indexes.
Production Phase:
Once you have promoted your code and your database to the production environment, are you
done developing indexes? No! There is always index work to consider once you are in the production
phase.
Now that your application code is finalized and in production, you will be able to examine real life
usage of your application to determine how those indexes are really being used and updated.
Therefore, you should start gathering index usage statistics. You need to consider keeping the index
usage statistics over time, as well as the missing index information. Keeping your index statistics
and missing index information over time will allow you to be able to determine how useful those
indexes really are, and how often they have been updated.
After you have tracked your index usage information for a week, a month, and/or a year you will
really be able to tell which indexes really have not been used. After you have gathered a sufficient
amount of index usage and missing index information, you can use this information to manage
your indexes. This collected index usage information will help identify those indexes that are being
updated frequently or rarely, if ever used, as well as those missing indexes that are frequently
missing. You can then use this information to tweak your indexes. Lastly don’t forget to look for
duplicate indexes once and while, especially after you have modified your indexes and/or added
new indexes.
Maintenance Phase:
The last phase of the index development lifecycle is maintenance. Of course, you have been doing
index maintenance all along the way by added, dropping and modifying indexes.
As your indexes are updated, they will become fragmented. The more fragmented your indexes the
more pages they take up, the more I/O it takes to traverse the index, and the slower they perform.
Periodically you need to review your index fragmentation to determine how fragmented your indexes
are.
By reviewing the fragmentation information, you will be able to determine if you should identify a
new FILL FACTOR for an index. Based on how fragmented your indexes are you might want to
perform an index rebuild verses an index organization operation. The Microsoft recommendation
is to rebuild an index if the index fragmentation is greater than 30% and reorganize it if the index
fragment is between 5% and 30%. Here is some code that will help you identify the index fragment
information for your indexes:
SELECT DB_NAME(ps.[database_id]) AS [database_name],
OBJECT_NAME(ps.[object_id], DB_ID()) AS [object_name],
si.[name] AS [index_name], ps.partition_number,
ps.index_type_desc, ps.alloc_unit_type_desc,
ps.index_level,
ps.[avg_fragmentation_in_percent],
ps.[page_count]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’)
ps
JOIN sys.sysindexes si
ON ps.OBJECT_ID = si.id
AND ps.index_id = si.indid
WHERE index_type_desc <> ‘HEAP’

LOVELY PROFESSIONAL UNIVERSITY 81