sys.dm_exec_query_stats 和sysprocesses表

足球不是方的 2017-06-07 02:59:05
下面的sql是提取top10 最耗时的
sqlselect top 10
DB_Name(qp.dbid) as dbname,
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qt.text,
qs.sql_handle
from sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
--WHERE last_execution_time between @dtBeginDate and @dtEndDate
group by qt.text, DB_Name(qp.dbid),qs.sql_handle
order by sum(qs.total_worker_time) desc

想加上执行对应sql的
loginame as [登录名],
login_time as [登录时间],
last_batch as [最后执行时间],
hostname as [客户端计算机名称],
program_name [应用程序名称],
先是这些字段在表 master.dbo.sysprocesses

现在怎么关联上去,根据sql_handle来貌似不行,求大哥来解决下。
...全文
576 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
足球不是方的 2017-06-08
  • 打赏
  • 举报
回复
大概明白了,看了下您的博客,问啥会监控sp命令? 我现在有做了阻塞,死锁,top10(cpu,io,内存)sql语句,作业执行状态,磁盘空间,数据库空间的监控。 请问还有说明内容需要监控的? 我现在还想监控2个数据 1、连接到数据库的程序(ip,客户端,登录名),想做一个白名单出来 2、想汇总一个登录名的状态(权限,状态,最后一次登录时间等信息) 以上2个有什么好的方法吗?
中国风 2017-06-07
  • 打赏
  • 举报
回复
DBA-常用到的动态视图分析语句 http://blog.csdn.net/roy_88/article/details/42303869 sysprocesses 这是当前进程,你的语句去显示当前进程的信息,结果集是没有意义的 你应该查出对应的存储或语句,再执行优化处理,参照上面连接
中国风 2017-06-07
  • 打赏
  • 举报
回复
有结果集时,再把你要显示的栏位加上
中国风 2017-06-07
  • 打赏
  • 举报
回复
首先要进程里有对应的T-SQL才会显示,用以下方法试试 e.g.
SELECT TOP 10
        DB_NAME(qp.dbid) AS dbname ,
        SUM(qs.total_worker_time) AS total_cpu_time ,
        SUM(qs.execution_count) AS total_execution_count ,
        COUNT(*) AS number_of_statements ,
        qt.text ,
        qs.sql_handle
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
		INNER JOIN sys.dm_exec_requests AS rq ON rq.sql_handle=qs.sql_handle
		INNER JOIN master.sys.sysprocesses AS pc ON pc.spid=rq.session_id
 --WHERE  last_execution_time between @dtBeginDate and @dtEndDate
GROUP BY qt.text ,
        DB_NAME(qp.dbid) ,
        qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC;
sql最全的常用命令语句 询某个数据库的连接数 select count(*) from Master.dbo.SysProcesses where dbid=db_id() --前10名其他等待类型 SELECT TOP 10 * from sys.dm_os_wait_stats ORDER BY wait_time_ms DESC SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%' OR wait_type like 'LAZYWRITER_SLEEP%' --CPU的压力 SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id 500 begin select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses a end select text,a.* from master.sys.sysprocesses a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) where a.spid = '51' dbcc inputbuffer(53) with tb as ( select blocking_session_id, session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) ), tb1 as ( select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)', total_scheduled_time,reads,writes,logical_reads from tb a inner join master.sys.dm_exec_sessions b on a.session_id=b.session_id ) select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id --当前进程数 select * from master.dbo.sysprocesses order by cpu desc --查看当前活动的进程数 sp_who active --查询是否由于连接没有释放引起CPU过高 select * from master.dbo.sysprocesses where spid> 50 and waittype = 0x0000 and waittime = 0 and status = 'sleeping ' and last_batch < dateadd(minute, -10, getdate()) and login_time 50 and waittype = 0x0000 and waittime = 0 and status = 'sleeping ' and last_batch < dateadd(minute, -60, getdate()) and login_time 1 ORDER BY qs.plan_generation_num SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time, SUM(qs.execution_count) AS total_execution_count, SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time, COUNT(*) AS number_of_statements FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt GROUP BY qt.text ORDER BY total_cpu_time DESC --统计总的CPU时间 --ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间 -- 计算可运行状态下的工作进程数量 SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id FROM sys.dm_os_workers AS o INNER JOIN sys.dm_os_schedulers AS s ON o.scheduler_address=s.scheduler_address AND s.scheduler_id<255 WHERE o.state='RUNNABLE' GROUP BY s.scheduler_id

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧