SQL语句实现查询Index使用状况
SQL 实现 使用 查询 语句 index 状况
2023-06-13 09:19:43 时间
ISNULL(user_updates,0) AS [Total Writes],
ISNULL(user_seeks +user_scans + user_lookups,0) AS [Total Reads],
s.last_user_seek,
s.last_user_scan ,
s.last_user_lookup,
ISNULL(user_updates,0) ISNULL((user_seeks+ user_scans +user_lookups),0)AS [Difference],
p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTERJOIN sys.dm_db_index_usage_statsAS s WITH (NOLOCK) ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id=db_id() AND objectproperty(s.object_id, IsUserTable ) = 1
INNER JOIN sys.tables AS t WITH (NOLOCK) ON i.object_id = t.object_id
INNER JOIN sys.schemas AS sch WITH (NOLOCK) ON t.schema_id = sch.schema_id
LEFT OUTERJOIN sys.dm_db_partition_stats AS p WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
AND ISNULL(user_updates,0) =ISNULL((user_seeks + user_scans + user_lookups),0) shows all indexesincluding those that have not been used
AND ISNULL(user_updates,0) -ISNULL((user_seeks + user_scans + user_lookups),0) 0 only shows thoseindexes which have been used
AND i.index_id 1 Only non-first indexes (I.E.non-primary key)
AND i.is_primary_key 1 Only those that are not defined asa Primary Key)
AND i.is_unique_constraint 1 Only those that are not classed as UniqueConstraints .
ORDER BY [Table Name],[index name]
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 SQL语句实现查询Index使用状况
ISNULL(user_seeks +user_scans + user_lookups,0) AS [Total Reads],
s.last_user_seek,
s.last_user_scan ,
s.last_user_lookup,
ISNULL(user_updates,0) ISNULL((user_seeks+ user_scans +user_lookups),0)AS [Difference],
p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTERJOIN sys.dm_db_index_usage_statsAS s WITH (NOLOCK) ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id=db_id() AND objectproperty(s.object_id, IsUserTable ) = 1
INNER JOIN sys.tables AS t WITH (NOLOCK) ON i.object_id = t.object_id
INNER JOIN sys.schemas AS sch WITH (NOLOCK) ON t.schema_id = sch.schema_id
LEFT OUTERJOIN sys.dm_db_partition_stats AS p WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
AND ISNULL(user_updates,0) =ISNULL((user_seeks + user_scans + user_lookups),0) shows all indexesincluding those that have not been used
AND ISNULL(user_updates,0) -ISNULL((user_seeks + user_scans + user_lookups),0) 0 only shows thoseindexes which have been used
AND i.index_id 1 Only non-first indexes (I.E.non-primary key)
AND i.is_primary_key 1 Only those that are not defined asa Primary Key)
AND i.is_unique_constraint 1 Only those that are not classed as UniqueConstraints .
ORDER BY [Table Name],[index name]
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 SQL语句实现查询Index使用状况
相关文章
- 使用MySQL与SQL IF实现数据库动态操作(mysqlsqlif)
- MySQL Status Mysqlx_stmt_execute_sql 数据库状态作用意思及如何正确
- Oracle中使用SQL创建数据表(oracle创建表sql)
- Exploring the Power of Oracle SQL(oracle.sql)
- 清理使用SQL Server数据库:实现高效的日志清理(sqlserver数据库日志)
- 安卓手机实现连接SQL Server数据库的简易方法(安卓连sqlserver)
- 使用SQL Server实现轮训:一种有效解决方案(sqlserver轮训)
- SQL Server 字符串的左填充实现(sqlserver左填充)
- SQL Server实现记录关联的技巧(sqlserver做关联)
- MS SQL可以用来实现企业级数据库管理功能(mssql能用来做什么)
- SQL Server:实现更高的可用性(高可用sqlserver)
- 使用SQL Server视图实现数据管理(视图 sqlserver)
- MySQL 中 SQL 比较详解常用语句及其实现方法(mysql中sql比较)
- MySQL SQL语句的写法详解(mysql中sql怎么写)
- sql学习使用LR分析Oracle SQL语句的完美指南(lr 写oracle)
- MySQL 数据库 SQL 优化技巧(mysql下sql优化)
- 借助Oracle SQL轻松实现除法运算(oracle sql除以)
- Oracle SQL实现转置-简单快捷(oracle sql转置)
- 使用Oracle SQL进行批处理分析(oracle sql跑批)
- 认真学习,精通 Oracle SQL 编写技巧(oracle sql编写)
- Oracle SQL实现数据累加的巧妙方式(oracle sql累加)
- Oracle SQL实现多表联查,挑战极限(oracle sql多表)
- Oracle SQL中使用别名的优势(oracle sql别名)
- Oracle8i与SQL有何不同(oracle8i和sql)