首页 > 数据库 >Sql Server 索引使用情况

Sql Server 索引使用情况

时间:2023-01-06 11:46:58浏览次数:43  
标签:index NAME object Server sys 索引 user Sql id

--查询数据库索引碎片状况 use dbname; SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,  ind.name AS IndexName, indexstats.index_type_desc AS IndexType,  indexstats.avg_fragmentation_in_percent  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats  INNER JOIN sys.indexes ind   ON ind.object_id = indexstats.object_id  AND ind.index_id = indexstats.index_id  WHERE indexstats.avg_fragmentation_in_percent > 50  ORDER BY indexstats.avg_fragmentation_in_percent DESC ;
--查询数据库索引使用状况 select db_name(database_id) as N'数据库名称',           object_name(a.object_id) as N'表名称',           b.name N'索引名称',         user_seeks N'索引查找次数',         user_scans N'索引扫描次数',         last_user_seek N'索引最后查找时间',        last_user_scan N'索引最后扫描时间',         rows as N'索引所属表行数' from sys.dm_db_index_usage_stats a join       sys.indexes b       on a.index_id = b.index_id      and a.object_id = b.object_id      join sysindexes c       on c.id = b.object_id --where database_id=db_id()   ---当前数据库 -- and object_name(a.object_id)   like 'test%' ---待检索表名  order by user_seeks,user_scans,object_name(a.object_id)

--查看那些被大量更新却很少被使用的索引,这类索引应该被移除 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT                                                       DB_NAME() AS DatabaseName     , SCHEMA_NAME(o.Schema_ID) AS SchemaName     , OBJECT_NAME(s.[object_id]) AS TableName     , i.name AS IndexName     , s.user_updates     , s.system_seeks + s.system_scans + s.system_lookups                           AS [System usage] INTO #TempUnusedIndexes 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 INNER JOIN sys.objects o ON i.object_id = O.object_id   WHERE 1=2 EXEC sp_MSForEachDB 'USE [?];                           INSERT INTO #TempUnusedIndexes SELECT TOP 20     DB_NAME() AS DatabaseName     , SCHEMA_NAME(o.Schema_ID) AS SchemaName     , OBJECT_NAME(s.[object_id]) AS TableName     , i.name AS IndexName     , s.user_updates     , s.system_seeks + s.system_scans + s.system_lookups                                          AS [System usage] 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 INNER JOIN sys.objects o ON i.object_id = O.object_id   WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND s.user_seeks = 0     AND s.user_scans = 0     AND s.user_lookups = 0 AND i.name IS NOT NULL ORDER BY s.user_updates DESC'                           SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC DROP TABLE #TempUnusedIndexes
--最高维护代价的索引,Maintenance cost高的应该被移除
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT                                                         DB_NAME() AS DatabaseName     , SCHEMA_NAME(o.Schema_ID) AS SchemaName     , OBJECT_NAME(s.[object_id]) AS TableName     , i.name AS IndexName     , (s.user_updates ) AS [update usage]     , (s.user_seeks + s.user_scans + s.user_lookups) AS [Retrieval usage]     , (s.user_updates) -       (s.user_seeks + s.user_scans + s.user_lookups) AS [Maintenance cost]     , s.system_seeks + s.system_scans + s.system_lookups AS [System usage]     , s.last_user_seek     , s.last_user_scan     , s.last_user_lookup INTO #TempMaintenanceCost 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 INNER JOIN sys.objects o ON i.object_id = O.object_id   WHERE 1=2 EXEC sp_MSForEachDB 'USE [?];                             INSERT INTO #TempMaintenanceCost SELECT TOP 20     DB_NAME() AS DatabaseName     , SCHEMA_NAME(o.Schema_ID) AS SchemaName     , OBJECT_NAME(s.[object_id]) AS TableName     , i.name AS IndexName     , (s.user_updates ) AS [update usage]     , (s.user_seeks + s.user_scans + s.user_lookups)                     AS [Retrieval usage]     , (s.user_updates) - (s.user_seeks + user_scans +                          s.user_lookups) AS [Maintenance cost]     , s.system_seeks + s.system_scans + s.system_lookups AS [System usage]     , s.last_user_seek     , s.last_user_scan     , s.last_user_lookup 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 INNER JOIN sys.objects o ON i.object_id = O.object_id   WHERE s.database_id = DB_ID()     AND i.name IS NOT NULL     AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0     AND (s.user_seeks + s.user_scans + s.user_lookups) > 0 ORDER BY [Maintenance cost] DESC'                       SELECT top 20 * FROM #TempMaintenanceCost ORDER BY [Maintenance cost] DESC DROP TABLE #TempMaintenanceCost

--使用频繁的索引,这类索引不应被移除 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT                                               DB_NAME() AS DatabaseName         , SCHEMA_NAME(o.Schema_ID) AS SchemaName     , OBJECT_NAME(s.[object_id]) AS TableName     , i.name AS IndexName     , (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage]     , s.user_updates     , i.fill_factor INTO #TempUsage 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 INNER JOIN sys.objects o ON i.object_id = O.object_id   WHERE 1=2 EXEC sp_MSForEachDB 'USE [?];                               INSERT INTO #TempUsage SELECT TOP 20     DB_NAME() AS DatabaseName     , SCHEMA_NAME(o.Schema_ID) AS SchemaName     , OBJECT_NAME(s.[object_id]) AS TableName     , i.name AS IndexName     , (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage]     , s.user_updates     , i.fill_factor 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 INNER JOIN sys.objects o ON i.object_id = O.object_id   WHERE s.database_id = DB_ID()     AND i.name IS NOT NULL     AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 ORDER BY [Usage] DESC'                                   SELECT TOP 20 * FROM #TempUsage ORDER BY [Usage] DESC DROP TABLE #TempUsage

--碎片最多的索引,这类索引应该rebuild,否则会严重拖累数据库的性能
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT                                                         DB_NAME() AS DatbaseName     , SCHEMA_NAME(o.Schema_ID) AS SchemaName     , OBJECT_NAME(s.[object_id]) AS TableName     , i.name AS IndexName     , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] INTO #TempFragmentation FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]     AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id   WHERE 1=2 EXEC sp_MSForEachDB 'USE [?];                               INSERT INTO #TempFragmentation SELECT TOP 20     DB_NAME() AS DatbaseName     , SCHEMA_NAME(o.Schema_ID) AS SchemaName     , OBJECT_NAME(s.[object_id]) AS TableName     , i.name AS IndexName     , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]     AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id   WHERE s.database_id = DB_ID()   AND i.name IS NOT NULL   AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 ORDER BY [Fragmentation %] DESC'                         SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC DROP TABLE #TempFragmentation


--自上次SQL Server重启后,找出完全没有使用的索引,这类索引应该小心对待,不能一概而论,要看是什么原因导致这种问题
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT                                                     DB_NAME() AS DatbaseName     , SCHEMA_NAME(O.Schema_ID) AS SchemaName     , OBJECT_NAME(I.object_id) AS TableName     , I.name AS IndexName INTO #TempNeverUsedIndexes FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?];                           INSERT INTO #TempNeverUsedIndexes SELECT     DB_NAME() AS DatbaseName     , SCHEMA_NAME(O.Schema_ID) AS SchemaName     , OBJECT_NAME(I.object_id) AS TableName     , I.NAME AS IndexName FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id         AND I.index_id = S.index_id         AND DATABASE_ID = DB_ID() WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0   AND I.name IS NOT NULL   AND S.object_id IS NULL' SELECT * FROM #TempNeverUsedIndexes                         ORDER BY DatbaseName, SchemaName, TableName, IndexName DROP TABLE #TempNeverUsedIndexes
  --查看索引统计的相关信息
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT     ss.name AS SchemaName     , st.name AS TableName     , s.name AS IndexName     , STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'     , s.rowcnt AS 'Row Count'     , s.rowmodctr AS 'Number Of Changes'     , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0)                              AS DECIMAL(28,2)) AS '% Rows Changed' FROM sys.sysindexes s INNER JOIN sys.tables st ON st.[object_id] = s.[id] INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] WHERE s.id > 100   AND s.indid > 0   AND s.rowcnt >= 500 ORDER BY SchemaName, TableName, IndexName /* 因为查询计划是根据统计信息来的,索引的选择同样取决于统计信息, 所以根据统计信息更新的多寡可以看出数据库的大体状况,20%的自动更新对于大表来说非常慢。 */

 --查询数据库缺失索引状况
 SELECT  avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,           last_user_seek ,           last_user_scan ,           [statement] AS [Object] ,           'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'           + CONVERT(VARCHAR(32), D.index_handle) + '_'           + REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')           + ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')           + CASE WHEN equality_columns IS NOT NULL                       AND inequality_columns IS NOT NULL THEN ','                  ELSE ''             END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('                                                                 + included_columns                                                                 + ')', '') AS Create_Index_Syntax   FROM    sys.dm_db_missing_index_groups AS G           INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle           INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle   ORDER BY statement,PossibleImprovement desc
--索引碎片整理
1)删除索引并重建
2)使用 DROP_EXISTING 语句重建索引
3)使用 ALTER INDEX REBUILD 语句重建索引(avg_fragmentation_in_percent>30%)
4)使用 ALTER INDEX REORGANIZE 重新组织索引(5%<avg_fragmentation_in_percent<=30%)

标签:index,NAME,object,Server,sys,索引,user,Sql,id
From: https://www.cnblogs.com/oradba/p/17029973.html

相关文章

  • 在sqlite数据库使用sql查询显示表头列和自动对齐列的方法
    在sqlite数据库使用sql查询的结果中是不带表头列的。如下: 上面已经显示出了SCORE表中的所有数据,但似乎显示的不太明白,因为看不出来各个数据的含义。显示表头(.headero......
  • PG SQL 日期函数
    POSTGRESQL日期函数大全 一:各个数据中的连接符SqlServer的连接符:+(加号)Sqlite的连接符:.(点)PostgreSQL的连接符:||(或)二:PostgreSQL部分的日期函数及变量to_char()、Da......
  • sql日志:获得数据库报错信息
    原文链接:https://jingyan.baidu.com/article/25648fc1a6d0d5d090fd0024.html1、选择资源管理器下的“管理”选项卡,点击“sqlserver”日志 2、选择对话框中,双击相应的......
  • (四)elasticsearch 源码之索引流程分析
    1.概览前面我们讨论了es是如何启动,本文研究下es是如何索引文档的。下面是启动流程图,我们按照流程图的顺序依次描述。  其中主要类的关系如下:  2.索引流程......
  • SQLSERVER 的复合索引和包含索引到底有啥区别?
    一:背景1.讲故事在SQLSERVER中有非常多的索引,比如:聚集索引,非聚集索引,唯一索引,复合索引,Include索引,交叉索引,连接索引,奇葩索引等等,当索引多了之后很容易傻傻的分不清,比如......
  • MSSQL链接错误___证书链是由不受信任的颁发机构颁发的
    provider:SSLProvider,error:0-证书链是由不受信任的颁发机构颁发的Aconnectionwassuccessfullyestablishedwiththeserver,butthenanerroroccurreddu......
  • MySQL优化(超完整版)(一)
    一、 MySQL的优化  前言:  MySQL数据库的优化模块:   -数据库的设计—三大范式   -数据库的索引:唯一索引、主键索引、聚合索引、复合索引、默认索引 ......
  • MySQL优化(超完整版)(二)
    7. MySQL分库分表(1)分库分表概念介绍  MySQL的分库分表有两种方式:垂直拆分和水平拆分。  垂直拆分:垂直拆分就是要把表按模块划分到不同数据库表中(当然原则还是不破......
  • 阿里一面 | 说说你对 MySQL 死锁的理解
    1、什么是死锁?死锁指的是在两个或两个以上不同的进程或线程中,由于存在共同资源的竞争或进程(或线程)间的通讯而导致各个线程间相互挂起等待,如果没有外力作用,最终会引发整个系......
  • 限制mysql临时表ibtmp1文件大小
    限制mysql临时表ibtmp1文件大小:vi/etc/my.cnf(只能改my.cnf文件重启生效,不支持mysql会话里setglobal操作)innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500G:......