应用实例
sys.dm_exec_query_stats 和sysprocesses表
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区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来貌似不行，求大哥来解决下。
相关推荐
sql最全的常用命令语句
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
SQL server 2008 数据库优化常用脚本
–查询某个数据库的连接数 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
监控 SQL Server 的运行状况
监控 SQL Server 的运行状况 觉得值得一看和收藏的好东东哦
八大案例，带你参透SQLServer优化
工欲善其事，必先利其器。常见的分析性能问题的工具有三种：诊断硬件资源，等待类型，性能语句。硬件资源通常有四个方面判断：CPU监控，性能计数器主要包括%ProcessorTime、Processor QueueLength、Batchrequest/sec、Transactions/sec( total_)。这些参数能够判断CPU的处理能力，或者说一个实例的吞吐量。Memory监控，性能计数器主要为：BufferCacheHit
SQL Server2008常用优化脚本.txt
SQL Server常用优化脚本，包含查询某个数据库的连接数，前10名其他等待类型，CPU的压力，表现最差的前10名使用查询
