MSSQL DMV查询

网上很多相关文章,整理一部分记录备用

 

 

1.查询当前IO高的查询

SELECT  TOP 10 qs.spid, waittime, cpu, physical_io,st.text,login_time, last_batch,status, cmd, loginame,
 hostname, program_name
 FROM sys.sysprocesses AS qs
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.physical_io DESC

 

 

--当前执行

SELECT  TOP 10 session_id, start_time, status, command, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
     (CASE WHEN qs.statement_end_offset = -1
      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
     ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),[Parent Query] = qt.text
    , cpu_time, total_elapsed_time,  reads, writes, logical_reads
    , database_id, user_id,  wait_type, wait_time, last_wait_type,
 open_transaction_count, open_resultset_count,  
 lock_timeout, deadlock_priority, row_count   
    FROM sys.dm_exec_requests AS qs
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY (qs.reads+qs.writes)
 DESC

 2.查找记录数过千万但未分区的表

SELECT b.name ,a.index_id,partition_number,rows,data_compression_desc
FROM sys.partitions  a ,sysobjects b
WHERE a.object_id=b.id AND b.type='U' AND
 a.index_id IN (0,1) 
 AND partition_number=1
 AND rows>10000000
 ORDER BY a.rows DESC