检测Sql Server服务器SQL语句执行情况
来源:互联网 发布:什么叫数据发票 编辑:程序博客网 时间:2024/05/16 10:56
1、查找目前SQL Server所执行的SQL语法,并展示资源情况:
2、展示耗时查询:
3、当前进程及其语句:
4、存储过程执行情况:
5、开销较大的查询:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
s2.dbid ,
DB_NAME(s2.dbid)
AS
[数据库名] ,
--s1.sql_handle ,
(
SELECT
TOP
1
SUBSTRING
(s2.text, statement_start_offset / 2 + 1,
( (
CASE
WHEN
statement_end_offset = -1
THEN
( LEN(
CONVERT
(NVARCHAR(
MAX
), s2.text))
* 2 )
ELSE
statement_end_offset
END
) - statement_start_offset ) / 2 + 1)
)
AS
[语句] ,
execution_count
AS
[执行次数] ,
last_execution_time
AS
[上次开始执行计划的时间] ,
total_worker_time
AS
[自编译以来执行所用的 CPU 时间总量(微秒)] ,
last_worker_time
AS
[上次执行计划所用的 CPU 时间(微秒)] ,
min_worker_time
AS
[单次执行期间曾占用的最小 CPU 时间(微秒)] ,
max_worker_time
AS
[单次执行期间曾占用的最大 CPU 时间(微秒)] ,
total_logical_reads
AS
[总逻辑读] ,
last_logical_reads
AS
[上次逻辑读] ,
min_logical_reads
AS
[最少逻辑读] ,
max_logical_reads
AS
[最大逻辑读] ,
total_logical_writes
AS
[总逻辑写] ,
last_logical_writes
AS
[上次逻辑写] ,
min_logical_writes
AS
[最小逻辑写] ,
max_logical_writes
AS
[最大逻辑写]
FROM
sys.dm_exec_query_stats
AS
s1
CROSS
APPLY sys.dm_exec_sql_text(sql_handle)
AS
s2
WHERE
s2.objectid
IS
NULL
ORDER
BY
last_worker_time
DESC
2、展示耗时查询:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- List expensive queries
DECLARE
@MinExecutions
int
;
SET
@MinExecutions = 5
SELECT
EQS.total_worker_time
AS
TotalWorkerTime
,EQS.total_logical_reads + EQS.total_logical_writes
AS
TotalLogicalIO
,EQS.execution_count
As
ExeCnt
,EQS.last_execution_time
AS
LastUsage
,EQS.total_worker_time / EQS.execution_count
as
AvgCPUTimeMiS
,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count
AS
AvgLogicalIO
,DB.
name
AS
DatabaseName
,
SUBSTRING
(EST.text
,1 + EQS.statement_start_offset / 2
,(
CASE
WHEN
EQS.statement_end_offset = -1
THEN
LEN(
convert
(nvarchar(
max
), EST.text)) * 2
ELSE
EQS.statement_end_offset
END
- EQS.statement_start_offset) / 2
)
AS
SqlStatement
-- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!!
--,EQP.[query_plan] AS [QueryPlan]
FROM
sys.dm_exec_query_stats
AS
EQS
CROSS
APPLY sys.dm_exec_sql_text(EQS.sql_handle)
AS
EST
CROSS
APPLY sys.dm_exec_query_plan(EQS.plan_handle)
AS
EQP
LEFT
JOIN
sys.databases
AS
DB
ON
EST.dbid = DB.database_id
WHERE
EQS.execution_count > @MinExecutions
AND
EQS.last_execution_time > DATEDIFF(
MONTH
, -1, GETDATE())
ORDER
BY
AvgLogicalIo
DESC
,AvgCPUTimeMiS
DESC
3、当前进程及其语句:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Current processes and their SQL statements
SELECT
PRO.loginame
AS
LoginName
,DB.
name
AS
DatabaseName
,PRO.[status]
as
ProcessStatus
,PRO.cmd
AS
Command
,PRO.last_batch
AS
LastBatch
,PRO.cpu
AS
Cpu
,PRO.physical_io
AS
PhysicalIo
,SES.row_count
AS
[RowCount]
,STM.[text]
AS
SQLStatement
FROM
sys.sysprocesses
AS
PRO
INNER
JOIN
sys.databases
AS
DB
ON
PRO.dbid = DB.database_id
INNER
JOIN
sys.dm_exec_sessions
AS
SES
ON
PRO.spid = SES.session_id
CROSS
APPLY sys.dm_exec_sql_text(PRO.sql_handle)
AS
STM
WHERE
PRO.spid >= 50
-- Exclude system processes
ORDER
BY
PRO.physical_io
DESC
,PRO.cpu
DESC
;
4、存储过程执行情况:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Stored Procedure Execution Statistics
SELECT
ISNULL
(DBS.
name
,
''
)
AS
DatabaseName
,OBJECT_NAME(EPS.object_id, EPS.database_id)
AS
ObjectName
,EPS.cached_time
AS
CachedTime
,EPS.last_elapsed_time
AS
LastElapsedTime
,EPS.execution_count
AS
ExecutionCount
,EPS.total_worker_time / EPS.execution_count
AS
AvgWorkerTime
,EPS.total_elapsed_time / EPS.execution_count
AS
AvgElapsedTime
,(EPS.total_logical_reads + EPS.total_logical_writes)
/ EPS.execution_count
AS
AvgLogicalIO
FROM
sys.dm_exec_procedure_stats
AS
EPS
LEFT
JOIN
sys.databases
AS
DBS
ON
EPS.database_id = DBS.database_id
ORDER
BY
AvgWorkerTime
DESC
;
5、开销较大的查询:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
开销较大的查询
*/
SELECT
ss.SUM_execution_count ,
t.text ,
ss.SUM_total_elapsed_time ,
ss.sum_total_worker_time ,
ss.sum_total_logical_reads ,
ss.sum_total_logical_writes
FROM
(
SELECT
S.plan_handle ,
SUM
(s.execution_count) SUM_Execution_count ,
SUM
(s.total_elapsed_time) SUM_total_elapsed_time ,
SUM
(s.total_worker_time) SUM_total_worker_time ,
SUM
(s.total_logical_reads) SUM_total_logical_reads ,
SUM
(s.total_logical_writes) SUM_total_logical_writes
FROM
sys.dm_exec_query_stats s
GROUP
BY
S.plan_handle
)
AS
ss
CROSS
APPLY sys.dm_exec_sql_text(ss.plan_handle) t
ORDER
BY
sum_total_logical_reads
DESC
6, 查询某个SPID,session_id对应的执行sql.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select er.session_id, cast(csql.text AS varchar(255)) AS CallingSQL
from master.sys.dm_exec_requests er WITH (NOLOCK)
CROSS APPLY fn_get_sql (er.sql_handle) csql
where er.session_id =309
0 0
- 检测Sql Server服务器SQL语句执行情况
- Sql Server 监控 Job 执行情况
- Sql Server 监控 Job 执行情况
- SQL: 跟踪SQL语句的执行情况
- 使用SQL Server Profiler跟踪“金蝶K3ERP“后台sql语句执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- sqlserver 检查表索引及sql语句执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 应用Druid监控SQL语句的执行情况
- 【Android基础】listview控件的使用(1)------最简单的listview的使用
- IOS UIWebView学习
- Redmine经验分享
- 在键值表中寻找最大值的键
- Messenger:使用消息的跨进程通信
- 检测Sql Server服务器SQL语句执行情况
- 在.NET中用Request.QueryString传递中文问题
- Gas Station
- “char *”类型的实参与“LPCTSTR”类型的形参不兼容
- JAVA获取本机信息
- 广州传智播客.Net培训教学特色
- Codeforces Round #230 (Div. 1)B,C
- cocos2dx + eclipse + win7 环境搭建
- 用setw( )设置字段宽