--總覽
select db_name(a.dbid) as 'Database',loginame,
a.spid 'PID',
a.cpu 'CPU Time',a.physical_io 'I/O',
a.memusage 'MEM usage',
--login_time,
last_batch,a.status,hostname,program_name,
nt_domain,nt_username,
sql.text
from sys.sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) AS sql
where 1=1
and substring(convert(char(6),last_batch,12),3,4) = substring(convert(char(6),getdate(),12),3,4)
order by 5 desc
--CPU使用
DECLARE @total BIGINT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
SELECT top 5 sb.name 'Database', @total 'System CPU', SUM(cpu) 'Database CPU', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc
--I/O使用
DECLARE @total2 INT
SELECT @total2=sum(physical_io) FROM sys.sysprocesses sp (NOLOCK)
join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
SELECT top 5 sb.name 'Database', @total2 'Total Physical I/O', SUM(physical_io) 'Physical I/O', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(physical_io)) / CONVERT(DECIMAL(17,2),@total2)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(physical_io)) / CONVERT(DECIMAL(17,2),@total2)*100) desc
--Runing process I/O
SELECT
db_name(req.database_id) AS 'Database',
sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
--History Top 10
SELECT top 10
total_logical_reads as 'Total Read',
total_logical_writes as 'Total Writes',
total_logical_reads+total_logical_writes as 'Total I/O',
--execution_count as 'Counts',
total_elapsed_time as 'Total Time',
b.text AS 'SQL_TEXT',
CASE WHEN b.dbid IS NULL THEN 'Ad Hoc Query'
WHEN dbid = 32767 THEN 'Resource Database'
ELSE DB_NAME(dbid)
END AS 'Database'
FROM sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(a.sql_handle) b
WHERE total_logical_reads+total_logical_writes > 0
and convert(char(8),last_execution_time, 112) > '20130526' --避免撈全部
order by 3 desc;
