浅谈SQL Server性能问题

zyypjc

于 2022-11-04 18:49:20 发布

阅读量1.7k

点赞数 1

分类专栏： SQL SERVER调优文章系列 SQL SERVER管理(SQL SERVER 2016) SQL SERVER/AZURE SQL性能优化实践文章标签：数据库运维 sqlserver 性能优化数据库管理

本文链接：https://blog.csdn.net/zyypjc/article/details/127692518

版权

SQL SERVER调优文章系列同时被 3 个专栏收录

17 篇文章 18 订阅

订阅专栏

SQL SERVER管理(SQL SERVER 2016)

17 篇文章 3 订阅

订阅专栏

SQL SERVER/AZURE SQL性能优化实践

12 篇文章 5 订阅

订阅专栏

3. 理解任务（tasks）、工作者（works）、调度（schedulers）之间的关系

（零）前言

当你作为DBA时，很多人会向你抱怨：“这个程序数据加载和蜗牛一样，你看看是不是服务器出问题了？”造成这个问题的原因有很多。可能是程序应用服务器问题，网络问题，程序实现方式问题，数据库服务器负荷过重。不管是哪个问题，数据库总是第一个被抱怨的。我们DBA的职责就是找出问题所在，并解决它们。

（一）服务器概况

1. 使用脚本

问题解决第一步，诊断分析：

SELECT
parent_node_id                  AS Node_Id,
COUNT(*)                        AS [No.of CPU In the NUMA],
SUM(COUNT(*)) OVER()            AS [Total No. of CPU],
SUM(runnable_tasks_count )      AS [Runnable Task Count],
SUM(pending_disk_io_count)                    AS [Pending disk I/O count],
SUM(work_queue_count)          AS  [Work queue count]
FROM sys.dm_os_schedulers WHERE status='VISIBLE ONLINE' GROUP BY parent_node_id

2. 返回结果说明

如果返回的是一条记录，说明服务器不支持NUMA架构，否则记录数就是NUMA架构的节点数（NUMA：非均匀访存模型
）。
Node_id：NUMA节点id。
No.of CPU in the NUMA：分配给NUMA节点的CPU数，或调度数（number of schedulers）。
Total No. of CPU：服务器上可用CPU总数。
Runnable Task Count：在可运行队列里，等待被重现调度的，用于分配任务（tasks）的工作者（workers）数。即，可运行队列里请求数。
Pending disk I/O count：等待被完成的等待IO数。每个调度都有一个等待IO清单，用于判断它们在上下文切换时是否完成。当请求被插入时，这个数字会增加。请求完成后，数字会减少。
Work queue count：等待队列里的任务数。这些任务等待工作者拿走。

我会把这个脚本的输出结果存到一张表，并设置为计划任务每10分钟运行一次，收集运行2天。这样我们对服务器的运行状况就有了基本的了解。在我测试的服务器上，当Runnable Task Count一直在10的时候，用户就是抱怨服务器慢！正常情况，每个节点的这个数字应该低于10。这就给了我们当前系统运行的大致情况。如果这一步的输出结果是正常的，我们就可以排除数据库服务器的问题了，响应慢的问题可能是我们不能控制的阻塞造成的，或者只是部分会话响应慢，而不是整个服务器慢。

3. 理解任务（tasks）、工作者（works）、调度（schedulers）之间的关系

对于每个CPU，SQLSERVER都会有一个scheduler与之对应。在每个scheduler里，会有若干个worker，对应于每个线程。在客户端发过来请求之后，SQL会将其分解成一个或多个task。根据每个scheduler的繁忙程度，task会被分配到某个scheduler上。如果scheduler里有空闲的worker，task就会被分配到某个worker上。如果没有，scheduler会创建新的worker，供task使用。如果scheduler里的worker已经到了他的上限值，而他们都有task要运行，那么新的task只好进入等待worker的状态。

（二）列出等待资源的会话

上一节中，我们知道了如何快速检查服务器实例上正运行的任务数和IO等待的任务数。这个是轻量级的脚本，不会给服务器造成任何压力，即使服务器在高负荷下，也可以正常获得结果。

1. 获取在进行任何资源等待的会话脚本

问题检测的第2步是获取在进行任何资源等待的会话。下面的脚本会帮助我们获得这些信息。这个查询需要预建立一个函数，如果会话是由SQL Server代理启动的话，会显示具体的作业名。

/*****************************************************************************************
           PREREQUISITE FUNCTION
******************************************************************************************/
USE MASTER
GO
CREATE FUNCTION ConvertStringToBinary  ( @hexstring  VARCHAR(100)
)  RETURNS BINARY(34)  AS
BEGIN

   RETURN(SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
   FROM (SELECT CASE SUBSTRING(@hexstring, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos))
END
/***************************************************************************************
STEP 2: List the session which are currently waiting for resource
****************************************************************************************/
SELECT node.parent_node_id AS Node_id,
es.HOST_NAME,
es.Login_name,
CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN
         (
          SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=
          MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))
          )
    ELSE es.program_name END  AS [Program Name] ,
DB_NAME(er.database_id) AS DatabaseName,
er.session_id,
wt.blocking_session_id,
wt.wait_duration_ms,
wt.wait_type,
wt.NoThread ,
er.command,
er.status,
er.wait_resource,
er.open_transaction_count,
er.cpu_time,
er.total_elapsed_time AS ElapsedTime_ms,
er.percent_complete ,
er.reads,
er.writes,
er.logical_reads,
wlgrp.name AS ResoursePool              ,
SUBSTRING   (sqltxt.TEXT,(er.statement_start_offset/2) + 1,
            ((CASE WHEN er.statement_end_offset = -1
             THEN LEN(CONVERT(NVARCHAR(MAX), sqltxt.TEXT)) * 2
             ELSE er.statement_end_offset
            END - er.statement_start_offset)/2) + 1) AS [Individual Query],
sqltxt.TEXT AS [Batch Query]
FROM (SELECT session_id, SUM(wait_duration_ms) AS
wait_duration_ms,wait_type,blocking_session_id,COUNT(*) AS NoThread
FROM  SYS.DM_OS_WAITING_TASKS  GROUP BY session_id, wait_type,blocking_session_id) wt
INNER JOIN SYS.DM_EXEC_REQUESTS  er ON wt.session_id=er.session_id INNER JOIN SYS.DM_EXEC_SESSIONS es ON es.session_id= er.session_id
INNER JOIN SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS wlgrp ON wlgrp.group_id=er.group_id
INNER JOIN  (SELECT  os.parent_node_id ,task_address FROM SYS.DM_OS_SCHEDULERS  OS INNER JOIN
SYS.DM_OS_WORKERS  OSW ON OS.scheduler_address=OSW.scheduler_address
WHERE os.status='VISIBLE ONLINE' GROUP BY os.parent_node_id ,task_address ) node
ON node.task_address=er.task_address
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) AS sqltxt
WHERE sql_handle IS NOT NULL AND wt.wait_type NOT IN ('WAITFOR','BROKER_RECEIVE_WAITFOR')
GO

2. 输出结果的每列说明

Node_id NUMA节点ID。可以被调度者查询的节点映射。
HOST_NAME 建立连接的计算机名。
Login_name 连接到数据库服务器的会话用户名。
Program Name 使用会话的对应程序名。在连接字符串里可以设置程序名。如果会话是SQL Server代理的一部分，则显示作业名。
DatabaseName 会话的当前数据库名。
session_id 会话ID。
blocking_session_id 阻塞语句的会话ID。
wait_duration_ms 等待时间，单位为毫秒。这个时间不包括信号等待时间（signal wait time ）。
wait_type 等待类型名称，例如：SLEEP_TASK,CXPACKET等。
NoThread 当前会话的线程数，如果当前会话是并行执行（parallel execution）的话。
command 标识当前类型的命令，即T-SQL语句，例如Select,insert,update,delete等。
status 请求状态：Background,Running,Runnable,Sleeping 和 Suspended。
wait_resource 请求当前等待的资源。
open_transaction_count 当前会话打开的事务数。
cpu_time 请求使用的CPU时间，单位毫秒。
ElapsedTime_ms 自请求到达后，占用的CPU时间，单位毫秒。
percent_complete 指定操作的工作完成进度，例如备份、还原、回滚等。
reads 请求执行的读数。
writes 请求执行的写数。
logical_reads 请求执行的逻辑读数。
ResoursePool 资源管理池名称。
Individual Query 在会话里运行的批处理SQL语句。
Batch Query 在会话里运行的批处理(存储过程/一系列的语句）。

上述查询多次执行后，输出结果有很长wait_duration_ms的会话，这个会话不被其他会话阻塞，且一直在输出结果里。我们就要看看这个会话的程序名，主机名，登录用户名，还有对应的执行语句，具体进行什么操作造成的。根据这些信息，我们可以选择性的去终止这个会话，然后分析下具体的执行语句。如果会话是被阻塞的，我们要用另外的语句来找出阻塞的会话。

3. 列出服务器上正运行的会话清单

/***************************************************************************************
STEP 3: List the session which are currently waiting/running
****************************************************************************************/
SELECT node.parent_node_id AS Node_id,
es.HOST_NAME,
es.login_name,
CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN
(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))
)ELSE es.program_name END  AS program_name ,
DB_NAME(er.database_id) AS DatabaseName,
er.session_id,
wt.blocking_session_id,
wt.wait_duration_ms,
wt.wait_type,
wt.NoThread ,
er.command,
er.status,
er.wait_resource,
er.open_transaction_count,
er.cpu_time,
er.total_elapsed_time AS ElapsedTime_ms,
er.percent_complete ,
er.reads,er.writes,er.logical_reads,
wlgrp.name AS ResoursePool              ,
SUBSTRING (sqltxt.TEXT,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), sqltxt.TEXT)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Individual Query],
sqltxt.TEXT AS [Batch Query]
FROM
SYS.DM_EXEC_REQUESTS  er INNER JOIN SYS.DM_EXEC_SESSIONS es ON es.session_id= er.session_id
INNER JOIN SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS wlgrp ON wlgrp.group_id=er.group_id
INNER JOIN  (SELECT  os.parent_node_id ,task_address FROM SYS.DM_OS_SCHEDULERS  OS
INNER JOIN SYS.DM_OS_WORKERS  OSW ON OS.scheduler_address=OSW.scheduler_address
WHERE os.status='VISIBLE ONLINE' GROUP BY os.parent_node_id ,task_address ) node ON node.task_address=er.task_address
LEFT JOIN
(SELECT session_id, SUM(wait_duration_ms) AS
wait_duration_ms,wait_type,blocking_session_id,COUNT(*) AS NoThread
FROM  SYS.DM_OS_WAITING_TASKS  GROUP BY session_id, wait_type,blocking_session_id) wt
ON wt.session_id=er.session_id
CROSS apply SYS.DM_EXEC_SQL_TEXT(er.sql_handle) AS sqltxt
WHERE sql_handle IS NOT NULL AND ISNULL(wt.wait_type ,'') NOT IN
('WAITFOR','BROKER_RECEIVE_WAITFOR')
ORDER BY er.total_elapsed_time DESC

GO

这里的输出列和第2步完全相同，我会分析total_elapsed_time占用时间较长的会话，酌情考虑是否终止这些会话，并分析下对应的执行SQL语句。大多数情况下（服务器一致运行稳定，突然卡住了），使用上述步骤就可以解决问题。

（三）列出阻塞的会话

在上一篇里，我们讨论了列出等待资源或正运行的会话脚本。这篇文章我们会看看如何列出包含具体信息的话阻塞会话清单。

1. 使用脚本

/******************************************************************************************/
CREATE FUNCTION [dbo].dba_GetStatementForSpid
(
   @spid SMALLINT
)
RETURNS NVARCHAR(4000)
BEGIN
   DECLARE @SqlHandle BINARY(20)
   DECLARE @SqlText NVARCHAR(4000)
   SELECT @SqlHandle = sql_handle
      FROM sys.sysprocesses WITH (nolock) WHERE   spid = @spid
   SELECT @SqlText = [text] FROM
      sys.dm_exec_sql_text(@SqlHandle)
   RETURN @SqlText
END
GO

/*****************************************************************************************
STEP 4: List the current blocking session information
****************************************************************************************/

SELECT
es.session_id,
es.HOST_NAME,
DB_NAME(es.database_id) AS DatabaseName,
CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN (SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35))))))
ELSE es.program_name END  AS program_name ,
es.login_name ,
bes.session_id AS Blocking_session_id,
MASTER.DBO.dba_GetStatementForSpid(es.session_id) AS [Statement],
bes.HOST_NAME AS Blocking_hostname,
CASE WHEN Bes.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN
(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=
MASTER.DBO.ConvertStringToBinary
(LTRIM(RTRIM((SUBSTRING(Bes.program_name,CHARINDEX('(job',es.program_name,0)+4,35))))))
ELSE Bes.program_name END  AS Blocking_program_name,
bes.login_name AS Blocking_login_name,
  MASTER.DBO.dba_GetStatementForSpid(bes.session_id ) AS [Blocking Statement]
FROM sys.dm_exec_requests S
INNER JOIN sys.dm_exec_sessions  es ON es.session_id=s.session_id
INNER JOIN sys.dm_exec_sessions  bes ON bes.session_id=s.blocking_session_id

2. 列出已经打开事务但未活动的会话

这个脚本会列出被阻塞和正阻塞的语句信息，帮助我们进行问题分析。下面的脚本会帮助我们列出已经打开事务但未活动的会话，即打开事务，但上30秒内都没执行任何语句的会话。

/*****************************************************************************************
STEP 4: List the Open session with transaction which is not active
****************************************************************************************/
SELECT es.session_id,
es.login_name,
es.HOST_NAME,
DB_NAME(SP.dbid) AS DatabaseName,
sp.lastwaittype,
est.TEXT,cn.last_read,
cn.last_write,
CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=MASTER.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))
)ELSE es.program_name END  AS program_name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id                INNER JOIN sys.dm_exec_connections cn ON es.session_id = cn.session_id
INNER JOIN sys.sysprocesses SP ON SP.spid=es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er  ON st.session_id = er.session_id
AND er.session_id IS NULL
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est
WHERE (DATEDIFF(SS,cn.last_read,GETDATE())+DATEDIFF(SS,cn.last_write,GETDATE()))>30
AND lastwaittype NOT IN ('BROKER_RECEIVE_WAITFOR' ,'WAITFOR')
GO

（四）列出最耗资源的会话

本章节我们看下从计划缓存里列出执行状态。

1. 使用脚本

/*****************************************************************************************
List heavy query based on CPU/IO. Change the order by clause appropriately
******************************************************************************************/
SELECT TOP 20
DB_NAME(qt.dbid) AS DatabaseName
,DATEDIFF(MI,creation_time,GETDATE()) AS [Age of the Plan(Minutes)]
,last_execution_time AS [Last Execution Time]
,qs.execution_count AS [Total Execution Count]
,CAST((qs.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) [Total Elapsed Time(s)]
,CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average Execution time(s)]
,CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Total CPU time (s)]
,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU]
,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
,CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]
,CAST((qs.total_physical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Physical Read]
,CAST((qs.total_logical_reads) / qs.execution_count AS DECIMAL(28, 2))  AS [Avg Logical Reads]
,CAST((qs.total_logical_writes) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical Writes]
,max_physical_reads
,max_logical_reads
,max_logical_writes
, SUBSTRING (qt.TEXT,(qs.statement_start_offset/2) + 1,((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) + 1) AS [Individual Query]
, qt.TEXT AS [Batch Statement]
, qp.query_plan
FROM SYS.DM_EXEC_QUERY_STATS qs
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY
[Total CPU time (s)]
--[Avg Physical Read]
--[Avg Logical Reads]
--[Avg Logical Writes]
--[Total Elapsed Time(s)]
--[Total Execution Count]
DESC

2. 输出结果的每列说明

DatabaseName 执行计划的数据库环境（数据库名）。
Age of the Plan(Minutes) 计划缓存里计划的生存期，单位为分钟。
Last Execution Time 这个计划的上次执行日期和时间。
Total Execution Count 自上次编译后，总执行次数；在执行计划生存期内[Age of the Plan(Minutes)]，总执行次数（自上次编译后）。
Total Elapsed Time(s) 执行这个计划总执行次数后[Total Execution Count]的总占用时间，单位为秒。
Average Execution time(s) 这个计划每次执行的平均时间，单位为秒。
Total CPU time (s) 执行这个计划总执行次数后[Total Execution Count]的总CPU时间，单位为秒。
% CPU 与Total Elapsed Time(s)相比，CPU占用时间比。
% Waiting 与Total Elapsed Time(s)相比，等待资源占用时间比。
CPU time average (s) 每次执行的平均CPU时间，单位为秒。
Avg Physical Read 每次执行的平均物理读数。
Avg Logical Reads 每次执行的平均逻辑读数。
Avg Logical Writes 每次执行的平均逻辑写数。
max_physical_reads 每次执行的时候，出新最大物理读数。
max_logical_reads 每次执行的时候，出新最大逻辑读数。
max_logical_writes 每次执行的时候，出新最大逻辑写数。
Individual Query 批处理语句的部分信息。
Batch Statement 批处理查询。
query_plan XML格式的执行计划，点击后我们可以看图示执行计划。

一般我们可以分析前5条记录(通过修改排序规则)的具体语句信息。大多数情况，我们会发现问题出现在临时表的滥用，distinct语句，游标，不合适的表连接条件，不合适的索引等等。其他经常发生的问题是，存储过程对数据库的大量调用（CPU消耗和执行时间都很小）。这个需要和开发人员反馈，修改下具体的实现方式。如果数据经常被调用，可以在程序里使用缓存方法避免与服务器的多次交互。有些对数据库的调用只是检查结果数据是否有改变。有些对数据库的调用是为检查数据库表里是否有新记录，且必须马上处理的。为了完成这些操作，程序会在1秒内多次查询表来找出未处理的记录。这个可以通过程序的异步调用来往表里插入数据来解决，或可以使用.net框架里的sqlDependency来解决。（sqlDependency提供了这样一种能力:当被监测的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让系统自动更新数据(或缓存)的目的。）