索引组成、产生原理、解决方法、优化方法
- 索引组成、产生原理、解决方法：http://wenku.baidu.com/view/a45a827d27284b73f2425071.html
- 优化方法（国外著名Ola Hallengren数据库专家，微软官方推荐）：http://download.csdn.net/detail/daiyueqiang/5272815
显示指定的表或视图的数据和索引的碎片信息
方法：DBCC SHOWCONTIG
语法：
DBCC SHOWCONTIG
[ (
{ table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
) ]
[
WITH
{
[ , [ ALL_INDEXES ] ]
[ , [ TABLERESULTS ] ]
[ , [ FAST ] ]
[ , [ ALL_LEVELS ] ]
[ NO_INFOMSGS ]
}
]
|
举例：
declare
@table_id
int
set
@table_id=object_id(
'表名'
)
dbcc showcontig(@table_id)
|
查找缺失索引
-- =============================================
-- Author: daiyueqiang -- Create date: 2012-12-31
-- Description: 查询当前数据库中缺失的索引，知道你进行优化的参考。
-- =============================================
SELECT
user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
AS
[index_advantage] ,
migs.last_user_seek ,
--上一次访问时间
mid.[statement]
AS
[
Database
.
Schema
.
Table
] ,
--表
mid.equality_columns ,
--等式判断列
mid.inequality_columns ,
--不等式判断列
mid.included_columns ,
--于查询的涵盖列的逗号分隔列表。有关涵盖列或包含列的详细信息
migs.unique_compiles ,
--将从该缺失索引组受益的编译和重新编译数。许多不同查询的编译和重新编译可影响该列值
migs.user_seeks ,
--由可能使用了组中建议索引的用户查询所导致的查找次数
migs.avg_total_user_cost ,
-- 可通过组中的索引减少的用户查询的平均成本
migs.avg_user_impact
--实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。
FROM
sys.dm_db_missing_index_group_stats
AS
migs
WITH
( NOLOCK )
INNER
JOIN
sys.dm_db_missing_index_groups
AS
mig
WITH
( NOLOCK )
ON
migs.group_handle = mig.index_group_handle
INNER
JOIN
sys.dm_db_missing_index_details
AS
mid
WITH
( NOLOCK )
ON
mig.index_handle = mid.index_handle
WHERE
mid.database_id = DB_ID()
--默认当前数据库。如果自己定义的数据库则使用DB_ID ( [ 'database_name' ] )
ORDER
BY
index_advantage
DESC
|
后续说明：
具有较高的 index_advantage 的索引那些 SQL 服务器认为会产生最大的积极影响，减少工作量，基于查询的成本和预期他们会使用索引的次数减少。
查看现有索引的使用情况
-- =============================================
-- Author: daiyueqiang
-- Create date: 2012-12-31
-- Description: 查询当前数据库中所有堆表、 聚集的索引和非聚集索引、 读取、 写入和每个索引的填充因子的数量，知道你进行优化的参考。
--Index Read/Write stats (all tables in current DB)
-- =============================================
SELECT
OBJECT_NAME(s.[object_id])
AS
[ObjectName] ,
i.
name
AS
[IndexName] , i.index_id ,
user_seeks + user_scans + user_lookups
AS
[Reads] ,
user_updates
AS
[Writes] ,
i.type_desc
AS
[IndexType] ,
i.fill_factor
AS
[FillFactor]
--填充因子
FROM
sys.dm_db_index_usage_stats
AS
s
INNER
JOIN
sys.indexes
AS
i
ON
s.[object_id] = i.[object_id]
WHERE
OBJECTPROPERTY(s.[object_id],
'IsUserTable'
) = 1
AND
i.index_id = s.index_id
AND
s.database_id = DB_ID()
ORDER
BY
OBJECT_NAME(s.[object_id]) ,
writes
DESC
,
reads
DESC
;
|
这是一个有用的查询，为更好地了解数据库的工作负荷。它可以帮助您确定某个特定的索引的波动性和写入数据的读取的比率。这可以帮助您改进和优化您的索引策略。例如，如果您有一个表，是相当静态 （很少写入任何索引），你可能会更有信心有关添加更多的索引在你失踪的索引查询中列中。
如果您使用的是 SQL Server 2008 企业版，此查询可以帮助您决定是否会启用数据压缩 （页或行） 的好主意。具有很少写活动的索引很可能是更合适数据压缩比波动性更大的索引。
查询未使用的索引
-- =============================================
-- Author: daiyueqiang
-- Create date: 2012-12-31
-- Description: 查询当前数据库中所有未使用的索引，知道你进行优化的参考。本sql的意思是，表的索引在数据库中未被使用，作为你进行下一步删除的依据。其中也可以加入时间判断
--List unused indexes
-- =============================================
SELECT
OBJECT_NAME(i.[object_id])
AS
[
Table
Name
] ,
i.
name
FROM
sys.indexes
AS
i
INNER
JOIN
sys.objects
AS
o
ON
i.[object_id] = o.[object_id]
WHERE
i.index_id
NOT
IN
(
SELECT
s.index_id
FROM
sys.dm_db_index_usage_stats
AS
s
WHERE
s.[object_id] = i.[object_id]
AND
i.index_id = s.index_id
AND
database_id = DB_ID()
--下列条件作为时间判断，查看在某个时间之后未使用的索引列表，如果不需要可删除
AND
(
last_user_seek>=
'@DateTime'
or
--用户上次执行搜索时间
last_user_scan>=
'@DateTime'
or
--用户上次执行扫描时间
last_system_seek>=
'@DateTime'
or
--系统上次执行搜索的时间
last_system_scan>=
'@DateTime'
--系统上次执行扫描的时间
)
)
AND
o.[type] =
'U'
ORDER
BY
OBJECT_NAME(i.[object_id])
ASC
|
查询当前数据库中使用较少的索引或者写入次数大于读取次数的索引
-- =============================================
-- Author: daiyueqiang
-- Create date: 2012-12-31
-- Description: 查询当前数据库中使用较少的索引或者写入次数大于读取次数的索引，
--此查询会寻找有大量的零的读取和写入的任何索引。任何属于此类别的索引是删除 （在充分调查) 的合适选择,指导你进行优化的参考。
--Possible Bad NC Indexes (writes > reads)
-- =============================================
SELECT
OBJECT_NAME(s.[object_id])
AS
[
Table
Name
] ,
i.
name
AS
[
Index
Name
] ,
--索引名称
i.index_id ,
user_updates
AS
[Total Writes] ,
--写入次数
user_seeks + user_scans + user_lookups
AS
[Total Reads] ,
--读取次数
user_updates - ( user_seeks + user_scans + user_lookups )
AS
[Difference]
--写入与读取只差
FROM
sys.dm_db_index_usage_stats
AS
s
WITH
( NOLOCK )
INNER
JOIN
sys.indexes
AS
i
WITH
( NOLOCK )
ON
s.[object_id] = i.[object_id]
AND
i.index_id = s.index_id
WHERE
OBJECTPROPERTY(s.[object_id],
'IsUserTable'
) = 1
AND
s.database_id = DB_ID()
AND
user_updates > (user_seeks + user_scans + user_lookups )
AND
i.index_id > 1
--聚集索引和非聚集索引
ORDER
BY
[Difference]
DESC
,
[Total Writes]
DESC
,
[Total Reads]
ASC
;
|
