sqlserver常用资源消耗sql

 --当前的数据库用户连接情况-- SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='db_name' SELECT * FROM [sys].[dm_exec_sessions] WHERE [session_id]>50  ---选取了前10个最耗CPU时间的会话---  SELECT TOP 10  [session_id],  [request_id],  [start_time] AS '开始时间',  [status] AS '状态',  [command] AS '命令',  dest.[text] AS 'sql语句',   DB_NAME([database_id]) AS '数据库名',  [blocking_session_id] AS '正在阻塞其他会话的会话ID',  [wait_type] AS '等待资源类型',  [wait_time] AS '等待时间',  [wait_resource] AS '等待的资源',  [reads] AS '物理读次数',  [writes] AS '写次数',  [logical_reads] AS '逻辑读次数',  [row_count] AS '返回结果行数'  FROM sys.[dm_exec_requests] AS der   CROSS APPLY   sys.[dm_exec_sql_text](der.[sql_handle]) AS dest   WHERE [session_id]>50 AND DB_NAME(der.[database_id])='db_name'    ORDER BY [cpu_time] DESC        ---选取了前10个最耗CPU时间的SQL---  SELECT TOP 10   dest.[text] AS 'sql语句'  FROM sys.[dm_exec_requests] AS der   CROSS APPLY   sys.[dm_exec_sql_text](der.[sql_handle]) AS dest   WHERE [session_id]>50    ORDER BY [cpu_time] DESC     SELECT TOP 10    total_worker_time/execution_count AS avg_cpu_cost, plan_handle,    execution_count,    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,       (CASE WHEN statement_end_offset = -1          THEN LEN(CONVERT(nvarchar(max), text)) * 2          ELSE statement_end_offset       END - statement_start_offset)/2)    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [avg_cpu_cost] 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 2 DESC;    SELECT  TOP 10          [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;