Page 85 - DLIS405_INFORMATION_STORAGE_AND_RETRIEVAL

Information Storage and Retrieval

Notes sys.dm_db_index_usage_stats AS s

ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE o.type = ‘U’
— Clustered and Non-Clustered indexes
AND i.type IN (1, 2)
AND (DB_NAME(s.database_id) = ‘SmarTPH’ or s.database_id IS NULL);
Another thing to look at is missing index statistics. As the database engine is processing queries, it
determines whether a query would perform better if an additional index were added to the database.
This information is called missing indexes. The missing index information can be exposed using
DMV’s. You can use this missing index information to determine which new indexes you might
need. Keep in mind there might be lots of missing indexes identified.

You should only consider adding those missing indexes that are identified to have a large number
user seeks and scans. These are the missing indexes that will be used quite frequently. Once again,
you need to keep a good balance of indexes, so don’t add every missing index. Here is a query that
identifies the missing indexes for the AdventureWorks DB, and orders them by user scans and
seeks:
Build Create Index Statements From Missing Indexes
d.statement AS [ObjectName],
gs.unique_compiles,
gs.user_seeks,
gs.user_scans,
gs.avg_total_user_cost,
gs.avg_user_impact,
‘CREATE INDEX MissingIndex_’ + rtrim(cast(d.index_handle AS char(100)))
+ ‘ ON ‘ + d.statement + ‘ (‘ +
CASE WHEN equality_columns IS NOT NULL THEN equality_columns ELSE ‘’
ND +
CASE WHEN equality_columns IS NOT NULL AND
inequality_columns IS NOT NULL THEN ‘, ‘ ELSE ‘’ END +
CASE WHEN inequality_columns IS NOT NULL THEN inequality_columns ELSE
‘’ END + ‘) ‘ +
CASE WHEN included_columns IS NOT NULL THEN ‘INCLUDE (‘ + included_columns
+ ‘)’ ELSE ‘’ END AS MissingIndex
FROM sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats gs ON gs.group_handle
= g.index_group_handle
join sys.dm_db_missing_index_details d ON g.index_handle
= d.index_handle
After you have reviewed your index usage statistic and your missing indexes, you need to determine
what index modification you need. Make any index modification you need first in your development
environment, and then promote them up to your acceptance testing environment. Every time you

80 LOVELY PROFESSIONAL UNIVERSITY