转载地址:https://segmentfault.com/a/1190000018022330
查看表的索引情况:
use [数据库名] sp_helpindex 表名;
查看索引使用情况:
user_seeks和user_scans字段都为0的,考虑是否为垃圾索引
另外last_user_seek,last_user_scan如果是一个很早的时间,则考虑是否应用变化导致该索引不被使用了
SELECT i.name indexname, user_seeks, user_scans, last_user_seek, last_user_scan FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id WHERE database_id = db_id('ClntMgr') AND s.object_id = object_id('IDVerifyTbl');
返回指定数据库、表、索引的碎片
对于索引类型为HEAP,一般情况下碎片比例会较大
原因:
1.没有聚集索引的表称为堆,意思是其中存储的数据没有特定的顺序。
2.在索引重建或者重新组织时,聚集索引依照聚集键和它重排序的数据页进行排序。
3.但是堆不会在索引重建或重新组织期间被重新生成,所以会脱离控制的增长,占用的数据页比必要的多很多。
SELECT OBJECT_NAME(f.object_id) 表名, i.name 索引名, f.index_type_desc 索引类型, f.avg_fragmentation_in_percent 碎片比例 FROM sys.dm_db_index_physical_stats(DB_ID('库名'), OBJECT_ID('表名'), NULL, NULL, 'limited') f INNER JOIN sys.indexes i ON i.object_id = f.object_id AND i.index_id = f.index_id ORDER BY f.avg_fragmentation_in_percent DESC;
在线重新生成表的所有索引
alter index all on 库名.dbo.表名 rebuild with (online = on);
重新组织表的所有索引
alter index all on 库名.dbo.表名 reorganize;
查看表、索引占用磁盘空间情况
SELECT name '表名', convert(char(11), row_Count) as '数据条数', (reservedpages * 8) '已用空间(KB)', (pages * 8) '数据占用空间(KB)', (CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '索引占用空间(KB)', (CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '未用空间(KB)', LTRIM(STR(reservedpages * 8 / 1024 / 1024, 15, 0) + ' GB') as '已用空间(GB)' from (SELECT name, SUM(reserved_page_count) as reservedpages, SUM(used_page_count) as usedpages, SUM(CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END) as pages, SUM(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END) as row_Count FROM sys.dm_db_partition_stats inner join sys.objects on sys.dm_db_partition_stats.object_id = sys.objects.object_id where type = 'U' group by sys.objects.name union SELECT sys.objects.name, sum(reserved_page_count) as reservedpages, sum(used_page_count) as usedpages, 0 as pages, 0 as row_count from sys.objects inner join sys.internal_tables on sys.objects.object_id = sys.internal_tables.parent_id inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id = sys.internal_tables.object_id where sys.internal_tables.internal_type IN (202, 204, 211, 212, 213, 214, 215, 216) group by sys.objects.name) t order by '已用空间(KB)' desc
查看表缺失的索引信息
SELECT DatabaseName = DB_NAME(database_id), [ Number Indexes Missing ] = count(*) FROM sys.dm_db_missing_index_details GROUP BY DB_NAME(database_id) ORDER BY [ Number Indexes Missing ] DESC;
确定开销最高的缺失索引
column_usage的取值有如下几种情况:
1.EqualityUsage代表在该列上做了相等运算;
2.InequalityUsage代表在该列上做了不等运算;
3.Include Cloumns代表包含性列
此查询的结果(按"总开销"排序)显示最重要缺失索引的成本以及有关数据库/架构/表和缺失索引中所需列的信息。特别是,此脚本可确定哪些列在相等和不相等 SQL 语句中使用。另外,它还报告应将哪些其他列用作缺失索引中的包含性列。
使用包含性列可以在不从基础页获取数据的情况下满足更多的覆盖查询,因而使用的 I/O 操作更少,从而提高性能。
SELECT TOP 100 [ Total Cost ] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0), avg_user_impact, TableName = statement, [ EqualityUsage ] = equality_columns, [ InequalityUsage ] = inequality_columns, [ Include Cloumns ] = included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY [ Total Cost ] DESC;