sqlServer

SQLSERVER数据库查询执行效率、锁表等情况

执行效率、锁表

Posted by Sunfy on 2022-06-02
Words 1.7k and Reading Time 8 Minutes
Viewed Times
Viewed Times
Visitors In Total
sql执行效率
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT TOP 50 qs.total_worker_time / qs.execution_count as [Avg CPU Time],
    SUBSTRING(qt.text, qs.statement_start_offset / 2,
              (case
                   when qs.statement_end_offset = -1
                       then len(convert(nvarchar(max), qt.text)) * 2
                   else qs.statement_end_offset end - qs.statement_start_offset) / 2)
                                                        as query_text,
    qt.dbid,
              dbname=db_name(qt.dbid),
    qt.objectid
FROM sys.dm_exec_query_stats qs
         cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Avg CPU Time] DESC
SQLServer 查看耗时较多的SQL语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT TOP 20 
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
   qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],
    max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (CASE WHEN qs.statement_end_offset = -1 
            THEN DATALENGTH(qt.text) 
            ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
        AS [使用CPU的语法], qt.text [完整语法],
              dbname=db_name(qt.dbid),
              object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC
查询 某个时间段内比较耗时 的SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT TOP 1000 
       ST.text AS '执行的SQL语句',
       QS.execution_count AS '执行次数',
       QS.total_elapsed_time/1000 AS '耗时',--ms
       QS.last_worker_time AS '12',
       QS.total_logical_reads AS '逻辑读取次数',
       QS.total_logical_writes AS '逻辑写入次数',
       QS.total_physical_reads AS '物理读取次数',       
       QS.creation_time AS '执行时间' ,  
       QS.*
FROM   sys.dm_exec_query_stats QS
       CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE  QS.creation_time BETWEEN '2017-05-08 12:50:00' AND '2017-05-09 17:10:00' 
ORDER BY  QS.total_elapsed_time DESC
执行最慢的SQL语句（全部sql执行时间列表）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT (total_elapsed_time / execution_count) / 1000        N'平均时间ms'
     , total_elapsed_time / 1000                            N'总花费时间ms'
     , total_worker_time / 1000                             N'所用的CPU总时间ms'
     , total_physical_reads                                 N'物理读取总次数'
     , total_logical_reads / execution_count                N'每次逻辑读次数'
     , total_logical_reads                                  N'逻辑读取总次数'
     , total_logical_writes                                 N'逻辑写入总次数'
     , execution_count                                      N'执行次数'
     , SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
                 ((CASE statement_end_offset
                       WHEN -1 THEN DATALENGTH(st.text)
                       ELSE qs.statement_end_offset END
                     - qs.statement_start_offset) / 2) + 1) N'执行语句'
     , creation_time                                        N'语句编译时间'
     , last_execution_time                                  N'上次执行时间'
FROM sys.dm_exec_query_stats AS qs
         CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
                ((CASE statement_end_offset
                      WHEN -1 THEN DATALENGTH(st.text)
                      ELSE qs.statement_end_offset END
                    - qs.statement_start_offset) / 2) + 1) not like '�tch%'
ORDER BY total_elapsed_time / execution_count DESC;
查找逻辑读取最高的查询(存储过程)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT TOP ( 25 )
        P.name AS [SP Name] ,
        Deps.total_logical_reads AS [TotalLogicalReads] ,
        deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] ,
        deps.execution_count ,
        ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time,
                                               GETDATE()), 0) AS [Calls/Second] ,
        deps.total_elapsed_time ,
        deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] ,
        deps.cached_time
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[Object_id] = deps.[Object_id]
WHERE   deps.Database_id = DB_ID()
ORDER BY deps.total_logical_reads DESC;
查询CPU最高的10条SQL
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
SELECT TOP 10 TEXT                                                                                  AS 'SQL Statement'
            , last_execution_time                                                                   AS 'Last Execution Time'
            , (total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
            , (total_worker_time / execution_count) / 1000000.0                                     AS [Average CPU Time (sec)]
            , (total_elapsed_time / execution_count) / 1000000.0                                    AS [Average Elapsed Time (sec)]
            , execution_count                                                                       AS "Execution Count"
            , qs.total_physical_reads
            , qs.total_logical_writes
            , qp.query_plan                                                                         AS "Query Plan"
FROM sys.dm_exec_query_stats qs
         CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
         CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

-------------------------方式二：有中文列名称-------------------------------------
SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (CASE WHEN qs.statement_end_offset = -1
        THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC
找出执行频繁的语句的SQL语句
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
31
32
33
34
35
36
37
38
39
40
with aa as (
    SELECT
--执行次数 
QS.execution_count,
--查询语句 
SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1,
          ((CASE QS.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.text)
                ELSE QS.statement_end_offset END - QS.statement_start_offset) / 2) + 1
    ) AS statement_text,
--执行文本 
ST.text,
--执行计划 
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time,
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
    FROM sys.dm_exec_query_stats QS
--关键字 
             CROSS APPLY
         sys.dm_exec_sql_text(QS.sql_handle) ST
    WHERE QS.last_execution_time > '2016-02-14 00:00:00'
      and execution_count > 500

-- AND ST.text LIKE '%%' 
--ORDER BY 
--QS.execution_count DESC

)
select text,
       max(execution_count) execution_count --,last_elapsed_time,min_elapsed_time,max_elapsed_time 
from aa
where [text] not like '%sp_MSupd_%'
  and [text] not like '%sp_MSins_%'
  and [text] not like '%sp_MSdel_%'
group by text
order by 2 desc
查询当前锁定的表(锁等待)
1
2
3
select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks
where resource_type = 'OBJECT'
查询到锁表后进行杀死
1
2
3
4
5
6
7
declare
    @spid int
Set @spid = 51 --锁表进程
declare
    @sql varchar(1000)
set @sql = 'kill ' + cast(@spid as varchar)
exec (@sql)
查询SqlServer总体的内存使用情况
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- 查询SqlServer总体的内存使用情况
select      type
        , sum(virtual_memory_reserved_kb) VM_Reserved
        , sum(virtual_memory_committed_kb) VM_Commited
        , sum(awe_allocated_kb) AWE_Allocated
        , sum(shared_memory_reserved_kb) Shared_Reserved
        , sum(shared_memory_committed_kb) Shared_Commited
        --, sum(single_pages_kb)    --SQL2005、2008
        --, sum(multi_pages_kb)        --SQL2005、2008
from    sys.dm_os_memory_clerks
group by type
order by type


-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
-- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) 
from sys.allocation_units a, 
    sys.dm_os_buffer_descriptors b, 
    sys.partitions p 
where a.allocation_unit_id=b.allocation_unit_id 
    and a.container_id=p.hobt_id 
    and b.database_id=db_id()
group by p.object_id,p.index_id 
order by buffer_pages desc 


-- 查询缓存的各类执行计划，及分别占了多少内存
-- 可以对比动态查询与参数化SQL（预定义语句）的缓存量
select    cacheobjtype
        , objtype
        , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
        , count(bucketid) as cache_count
from    sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype


-- 查询缓存中具体的执行计划，及对应的SQL
-- 将此结果按照数据表或SQL进行统计，可以作为基线，调整索引时考虑
-- 查询结果会很大，注意将结果集输出到表或文件中
SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO
使用SQL语句设置SQLserver内存分配
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
-- 1.使用SQL语句设置SQLserver内存分配：
-- a.配置最小内存
--将最小内存设置为0MB
exec sp_configure N'min server memory (MB) ',16
--b.配置最大内存
--将最大内存设置为256MB
exec sp_configure N'max server memory (MB)',266 

--执行结果：
--配置选项 'min server memory (MB)' 已从 14 更改为 16。请运行 RECONFIGURE 语句进行安装。
--配置选项 'max server memory (MB)' 已从 444 更改为 266。请运行 RECONFIGURE 语句进行安装。

--最后执行下面语句进行安装
reconfigure with override
--执行结果：
命令已成功完成。

--系统服务日志：
配置选项 'min server memory (MB)' 已从 14 更改为 16。请运行 RECONFIGURE 语句进行安装。
配置选项 'max server memory (MB)' 已从 444 更改为 266。请运行 RECONFIGURE 语句进行安装。

--2.手动操作设置
--右击本地服务器-->服务器属性-->内存 设置完，单击确定即可。


一般设置完后，最好将该SQL服务重启下。
查询SQLSERVER执行过的SQL记录
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
仅支持SQL SERVER2008及以上版本

SELECT TOP 1000
--创建时间
QS.creation_time,
--查询语句
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
--执行文本
ST.text,
--执行计划
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
FROM
sys.dm_exec_query_stats QS
--关键字
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE
QS.creation_time BETWEEN '2018-05-08 09:00:00' AND '2018-05-08 18:00:00'
–AND ST.text LIKE '%%'
ORDER BY
QS.creation_time DESC


SELECT TOP 1000
--创建时间
QS.creation_time,
--执行文本
ST.text
FROM
sys.dm_exec_query_stats QS
--关键字
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE
QS.creation_time BETWEEN '2018-05-08 09:00:00' AND '2018-05-08 18:00:00'
AND ST.text NOT LIKE '%SELECT * FROM T_LOCATIONINFO WHERE STRCLIPLOGICID in(%'
ORDER BY
QS.creation_time DESC

Copyright 2021 sunfy.top ALL Rights Reserved

...

...

00:00
00:00
FEATURED TAGS
sqlServer
FRIENDS