SQL语句实现查询Index使用状况

SELECT
   sch.name + '.' + t.name AS [Table Name],
   i.name AS[Index Name],
   i.type_desc,
   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]
赞 (0)

相关推荐

  • SQL语句实现查询Index使用状况

    SELECT sch.name + '.' + t.name AS [Table Name], i.name AS[Index Name], i.type_desc, 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, I

  • PHP获取MySQL执行sql语句的查询时间方法

    如下所示: //计时开始 runtime(); //执行查询 mysql_query($sql); //计时结束. echo runtime(1); //计时函数 function runtime($mode=0) { static $t; if(!$mode) { $t = microtime(); return; } $t1 = microtime(); list($m0,$s0) = explode(" ",$t); list($m1,$s1) = explode("

  • java执行SQL语句实现查询的通用方法详解

    完成SQL查询 并将查询结果放入Vector容器,以便其他程序使用 /* * 执行sql查询语句 */ public static <T> Vector<T> executeQuery(Class<T> clazz, String sql, Object... args) { Connection conn = null; PreparedStatement preparedstatement = null; ResultSet rs = null; Vector<

  • 使用SQL语句实现查询排序,顺序和倒序

    目录 SQL语句查询排序,顺序和倒序 SQL查询结果排序 SQL语句查询排序,顺序和倒序 SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC #ASC升序,DESC倒序 SQL查询结果排序 1.以指定顺序返回查询结果(order by的asc为升序,desc为降序) select ename,job,sal from emp where deptno = 10 order by sal as

  • vb.net 数组参与SQL语句的查询范例

    首先定义数组,我是同事session传值的 复制代码 代码如下: Dim struser As New List(Of String)struser = Session("chooseuser")strruser = "" For a = 0 To struser.Count - 1If a = 0 Thenstrruser = "'" & struser.Item(a).ToString.Trim & "'"

  • SQL语句实现查询并自动创建Missing Index

    SELECT migs.avg_total_user_cost*(migs.avg_user_impact/ 100.0) *(migs.user_seeks + migs.user_scans) ASimprovement_measure, 'CREATE INDEX[missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) + '_' + LEFT

  • SQL语句实现查询SQL Server内存使用状况

    SELECT type,--Clerk的类型 sum(virtual_memory_reserved_kb)as vm_Reserved_kb,--保留的内存 sum(virtual_memory_committed_kb)as vm_Committed_kb,--提交的内存 sum(awe_allocated_kb)as awe_Allocated_kb,--开启AWE后使用的内存 sum(shared_memory_reserved_kb)as sm_Reserved_kb,--共享的保留内

  • SQL语句实现查询当前数据库IO等待状况

    sys.dm_io_pending_io_requests可以返回当前IO Pending的状态,对于SQL Server 中每个挂起的I/O 请求,返回与其对应的一行,跟sys.dm_io_virtual_file_stats配合可以看到具体是哪个数据库IO出现问题. select DB_NAME(database_id) as DBNAME, database_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address from

  • SQL语句实现查询SQL Server服务器名称和IP地址

    获取服务器名称: SELECT SERVERPROPERTY('MachineName') select @@SERVERNAME select HOST_NAME() 获取IP地址可以使用xp_cmdshell执行ipconfig命令: --开启xp_cmdshell exec sp_configure'show advanced options', 1 reconfigure with override exec sp_configure'xp_cmdshell', 1 reconfigur

  • 查找MySQL中查询慢的SQL语句方法

    查找MySQL中查询慢的SQL语句方法

    如何在mysql查找效率慢的SQL语句呢?这可能是困然很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL .下面介绍MySQL中如何查询慢的SQL语句 一.MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句 1,slow_query_log 这

随机推荐