Warning: Undefined array key "HTTP_REFERER" in
/www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line
58
sys.dm_db_index_usage_stats
动态管理视图 sys.dm_db_index_usage_stats 该视图返回不同类型索引操作的计数以及 上次执行每种操作的时间。
只要启动 SQL Server (MSSQLSERVER) 服务，计数器就初始化为空。而且，当分离或关闭数据库时（例如， 由于 AUTO_CLOSE 设置为 ON），便会删除与该数据库关联的所有行。
这个视图最关键的就是最关键的就是这4个字段：
user_seeks 通过用户查询执行的搜索次数,就是利用聚集索引的次数。
user_scans 通过用户查询执行的扫描次数，就是没有利用任何索引的次数，逐行扫描最慢的那种。
user_lookups 通过用户查询执行的查找次数，就是利用非聚集索引的次数。
user_updates 通过用户查询执行的更新次数，就是更新前找到这条数据时利用的索引的次数 。因为更新是先查询到需要更新的数据，然后执行更新命令所以更新的时候这个user_updates字段和 user_seeks、user_lookups、user_scans字段中的一个都会加一。
--查询从未被使用的索引
SELECT t.name,ix.name,ddius.user_seeks,ddius.user_scans,ddius.user_lookups,ddius.user_updates
FROM sys.dm_db_index_usage_stats ddius
JOIN sys.tables t ON ddius.[object_id] = t.[object_id]
JOIN sys.indexes ix ON ix.[object_id] = ddius.[object_id] AND ix.index_id = ddius.index_id
WHERE ddius.database_id = DB_ID('brm_lvjian')
and last_user_seek is null
and last_user_scan is null
and last_user_lookup is null
and last_user_update is not null
索引与页关系
--查询索引使用的页数，每页是8K,页数乘以8，即为空间使用数
SELECT t.name,i.name,sp.in_row_data_page_count,sp.in_row_used_page_count,sp.in_row_reserved_page_count
FROM sys.tables t
JOIN sys.indexes i ON t.[object_id] = i.[object_id]
JOIN sys.dm_db_partition_stats sp ON sp.[object_id] = i.[object_id] AND sp.index_id = i.index_id
ORDER BY t.name,i.name
查询表占用空间，涉及的索引使用空间
SELECT a3.name AS [Schema 名称],
a2.name AS [表名称],
a1.rows as 记录条数,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [保留空间(K)],
a1.data * 8 AS [数据使用空间(k)],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data
THEN (a1.used + ISNULL(a4.used,0)) - a1.data
ELSE 0 END) * 8 AS [索引使用空间(k)],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used
THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used
ELSE 0 END) * 8 AS [未用空间(k)],
a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) 平均每条记录长度
FROM
(
SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN
(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(
SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id
) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY [保留空间(K)] DESC
--打开3604，信息发送到控制台
DBCC TRACEON(3604)
--查看表索引对应的页数明细
DBCC IND ('brm_lvjian_0817', 'frmuser', -1);
--查看页上的数据信息
DBCC PAGE (brm_lvjian_0817, 1, 409259, 3);
注：
DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file
print option = [0|1|2|3] -- display option; each option provides differing levels of information
)
DBCC IND
(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)
参考：http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx