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

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。