The following script helps you to find the top 10 missing indexes using SQL 2005 DMV.
SELECT TOP 10 migs_adv.index_advantage,
mid.statement as table_name,
mig.index_handle as index_handle
FROM (select (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
from sys.dm_db_missing_index_group_stats migs) as migs_adv,
WHERE migs_adv.group_handle = mig.index_group_handle
AND mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage DESC
Now take the index handle of the top missing index and apply below to determine which columns should be included in the newly created index:
SELECT * FROM sys.dm_db_missing_index_details where index_handle = 45
Once you’ve got your columns, create your new index: (we tend to put indexes in their own fileGroups so they may reside on their own disk spindle for increased performance)
CREATE INDEX missingIndex_xxx
ON [DB].[dbo].[TBL]([col1], [col2], [col3])
INCLUDE ([col4], [col5])
SELECT o.name AS object_name, i.name AS index_name
, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
AND i.index_id = u.index_id
AND u.database_id = DB_ID()
WHERE o.type <> 'S'
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY (convert(decimal(19,4),ISNULL(u.user_seeks, 0))
+ ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, o.name, i.name
The results of this will show you how many times each index has been used, and how often it’s been updated. If you have 0 seeks, scans, and lookups but a ton of updates, it’s a good bet that the index in question is a waste of time and can be deleted. Conversly, if you have an index that has lots of seeks/scans and minimal updates – it’s really earning it’s paycheck.
The query even includes the DROP command as part of the results, so all you need to do is cut, paste, and execute.
Macworld — Apple on Tuesday released a public beta of Safari 4, the next generation of its Internet Web browser.
Other new features in Safari 4 include Top Sites, giving users a visual preview of frequently visited pages; Full History Search, to search through titles, web addresses and the complete text of recently viewed pages; Cover Flow, to easily flip through web history or bookmarks; and Tabs on Top, to make tabbed browsing easier.
Safari 4 includes HTML 5 support for offline technologies so web-based applications can store information locally without an Internet connection. The browser also supports advanced CSS effects.
Safari 4 public beta is free and available now for download from Apple’s Web site.