There are several new features in SQL Server 2005. There are a few features to help find missing indexes, which are some of the very good ones. How great it will be if you know what indexes you need to create based on your workload? In SQL Server 2000, we had to use SQL Profiler trace files and Index tuning wizard. But with SQL Server 2005 DMVs, we can easily figure out what indexes we need to create which would benefit our application.
The following are the missing index DMVs ( From SQL Server 2005 BOL)
| sys.dm_db_missing_index_group_stats | Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes. |
| sys.dm_db_missing_index_groups | Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group. |
| sys.dm_db_missing_index_details | Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index. |
| sys.dm_db_missing_index_columns | Returns information about the database table columns that are missing an index. |
Let’s see what indexes are there for table [Person.Address] table in AdventureWorks database by running this code:
use
AdventureWorks;
exec
sp_helpindex
[
Person.Address
]
Fig:1
I don’t see an index for ModifiedDate column for [Person.Address] table. So, to get a entry in the “sys.dm_db_missing_index_details” DMV, lets run a query like this:
Query: 1
select
*
from
Person.Address
where
ModifiedDate
=
'
01/01/2008
'
You may not see any results for the query above, but SQL Server internally recorded that a query was run and a index on “ModifiedDate” column would have been very useful.
Query: 2
select
*
from
sys.dm_db_missing_index_details:
Fig: 2
In Fig: 2, see the “equality_columns” field, which implies that a index on the [Modified Date] column is missing ( or might be helpful)
Query: 3:
select
db_name
(d.database_id) dbname,
object_name
(d.
object_id
) tablename, d.index_handle,
d.equality_columns, d.inequality_columns, d.included_columns, d.statement
as
fully_qualified_object, gs.
*
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
where
d.database_id
=
d.database_id
and
d.
object_id
=
d.
object_id
and
object_name
(d.
object_id
)
=
'
Address
'
Run Query 1 several times. Now, run Query: 3,
Fig: 3
In Fig 3, notice the “user_seeks” column. So every time a query is run, for which an index might be useful, SQL Server keeps updating the missing index DMVs. This is very valuable information, based on this you can create indexes to support those queries. Isn’t this cool! Yes, SQL Server 2005 rocks!
The DMVs for missing indexes are great new features. I work with a Siebel CRM database where queries are built dynamically. So it is hard to design indexes in advance. The missing index feature helps to me create indexes for those queries that have high “user_seeks” for a particular column in a table.
For more information see “About the Missing Indexes Feature” in SQL Server 2005 Books Online.
from： http://www.sqlservercentral.com/articles/Indexing/64134/
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
