索引信息
来源:互联网 发布:java 路由器端口扫描 编辑:程序博客网 时间:2024/04/29 01:21
/* =================================================================================
OrchidCat http://blog.csdn.net/OrchidCat
2011-07-05
在日常的使用中,数据库管理员将会经常性的针对索引进行监控及相应的处理.
针对此应用,现将常用的几个关于索引状况的T-SQL列出,希望对正在使用MS SQL Server的朋友所有帮助.
========================================================================= */
--1. 查找当前数据库中索引对应的表名,列名以及类型信息
--All Index
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [架构], T.[name] AS [表名], I.[name] AS [索引名], AC.[name] AS [列名], I.[type_desc] AS [索引类型]FROM sys.[tables] AS T INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id] INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' ORDER BY T.[name], I.[index_id], IC.[key_ordinal]
--2. 查找当前数据库中索引的碎片信息
--Fragmentation
SELECT object_name(IPS.object_id) AS [表名], SI.name AS [索引名称], IPS.Index_type_desc AS [类型], IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, IPS.record_count AS [行数], IPS.ghost_record_count AS [幻影记录行数], IPS.fragment_count, IPS.avg_fragment_size_in_pagesFROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS JOIN sys.tables T WITH (nolock) ON IPS.object_id = T.object_id JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_idWHERE T.is_ms_shipped = 0order by IPS.avg_fragment_size_in_pages desc
--生成满足条件重建索引的T-SQL
DECLARE @页满度TINYINT = 80 --可修改DECLARE @索引页总数TINYINT = 80 --可修改SELECT 'alter index ' + i.name + ' on ' + OBJECT_NAME(i.id) + ' rebuild'FROM sys.sysindexes i , sys.dm_db_index_physical_stats(DB_ID('希望查找的数据库名称'), NULL, NULL, NULL, 'SAMPLED') oWHERE i.id = o.object_id AND i.indid = o.index_id AND o.page_count > @索引页总数 AND avg_page_space_used_in_percent < @页满度 AND i.name NOT LIKE '_WA%'
--3. 查找当前数据库中依照成本计算,应补充的索引
--missing index
SELECT [表名]=sys.objects.name, [累计平均成本]=(avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) ,[T_SQL]='CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';', [相等谓词列]=mid.equality_columns, [不等谓词列]=mid.inequality_columns, [查询涵盖列]=mid.included_columns FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID WHERE (migs.group_handle IN ( SELECT TOP (500) group_handle FROM sys.dm_db_missing_index_group_stats WITH (nolock) ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)) AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1 ORDER BY [表名] asc
--4. 查找未使用的索引
--Unused
SELECT [表名]=o.name, [索引名称]=i.name, [索引ID]=i.index_id, [读次数]=user_seeks + user_scans + user_lookups , [写次数] = user_updates , [行数] = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id), CASEWHEN s.user_updates < 1 THEN 100ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates END AS [读写比], 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'T_SQL'FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id INNER JOIN sys.objects o on s.object_id = o.object_idINNER JOIN sys.schemas c on o.schema_id = c.schema_idWHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1AND s.database_id = DB_ID() AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0AND i.is_unique_constraint = 0AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000ORDER BY 读次数
- 索引信息
- ETERM出错信息索引
- 信息检索资料索引
- 索引 信息 点点滴滴
- 重新整理索引信息
- 查看索引信息
- 数据库索引统计信息
- elasticsearch 获取索引信息
- 生成索引信息及索引创建脚本
- 生成索引信息及索引创建脚本
- 生成索引信息及索引创建脚本
- Lucene创建索引/检索信息
- [SQL Server] 查询索引信息.
- sphinx加载索引信息文件
- 信息检索笔记-索引构建
- 信息检索笔记-索引压缩
- 信息检索之索引构建
- Oracle收集索引统计信息
- 详解Controller之Filter
- Linux程序设计入门--时间概念
- C语言数组
- VS2008错误:error PRJ0050: 未能注册输出,请确保您有修改注册表的相应权限。
- 微分方程在建模中的应用(建立差分模型)
- 索引信息
- Linux守护进程的编程方法
- 部分代码
- Linux程序设计入门--信号处理
- 兆鹏带你读Watir——【第二篇】浏览器状态及线程等待
- C语言结构体
- linux下配置ip地址
- Linux程序设计入门--线程操作
- js多文件上传框