需要帮助解决生产数据库中的SQL Server等待事件故障 [英] Needs help in Troubleshooting SQL server wait event in production database
问题描述
亲爱的所有人，
我是SQL服务器性能调优问题的新手。对于新构建的SQL服务器之一（SQL Server 2016标准版），我们遇到了很多缓慢的问题。报告的主要等待事件是LATCH_EX &安培;  CXPACKET群组。我改变了"成本
并行阈值"。例如10，但这确实使情况变得更糟。我经历了保罗关于这些等待事件的所有优秀文章，但仍然无法如何继续下去。
我们也没有微软的支持也帮助我，我是孤独的dba对于我的组织。
任何输入/建议都将受到高度赞赏。
最诚挚的问候，
Sandy
首先，改变这是一件好事 " Parallel Threshold for Parallelism"但是设置 它到 25
至少。
你能告诉我们吗？此查询的输出？
; WITH Waits AS
（
SELECT ; $
wait_type，
wait_time_ms / 1000.AS wait_time_s，
;   100 * wait_time_ms / SUM（wait_time_ms）OVER（）AS PCT，
      ROW_NUMBER（）OVER（ORDER BY wait_time_ms DESC）用作Rn
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN（N'CLR_SEMAPHORE 'N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'， &NBSP ; N'LAZYWRITER_SLEEP'，
N'RESOURCE_QUEUE'， N'SQLTRACE_BUFFER_FLUSH'，
N'SLEEP_TASK"，      N'SLEEP_SYSTEMTASK'，
      N'WAITFOR"，          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'，
      N'CHECKPOINT_QUEUE'，N'REQUEST_FOR_DEADLOCK_SEARCH'，
      N'XE_TIMER_EVENT"，  N'XE_DISPATCHER_JOIN'，
      N'LOGMGR_QUEUE"，    N'FT_IFTS_SCHEDULER_IDLE_WAIT'，
      N'BROKER_TASK_STOP'，N'CLR_MANUAL_EVENT'，
      N'CLR_AUTO_EVENT"，  N'DISPATCHER_QUEUE_SEMAPHORE'，
      N'TRACEWRITE"，      N'XE_DISPATCHER_WAIT'，
      N'BROKER_TO_FLUSH"，  N'BROKER_EVENTHANDLER'，
      N'FT_IFTSHC_MUTEX"，  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'，
      N'DIRTY_PAGE_POLL"，  N'SP_SERVER_DIAGNOSTICS_SLEEP '
的 N'ONDEMAND_TASK_QUEUE'，N'BROKER_RECEIVE_WAITFOR 'N'QDS_ASYNC_QUEUE'
）
）b
    - 过滤掉额外的无关等待
，cte1
AS
（
SELECT
W1.wait_type，
CAST（W1.wait_time_s AS DECIMAL（12,2））AS wait_time_s，
CAST（W1.pct作为DECIMAL（12,2））AS pct
，rn
--- CAST（SUM（W2.pct）AS DECIMAL（12,2） ）AS running_pct
FROM Waits AS W1
--- JOIN等待AS W2
--- ON W2.rn< = W1.rn
GROUP BY W1.rn，W1.wait_type，W1.wait_time_s，W1.pct
--- --HAVING SUM（W2.pct）-W1.pct< 90--百分比阈值
）SELECT * FROM
（SELECT wait_type，wait_time_s，pct，SUM （PCT）OVER（ORDER BY RN
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP BETWEEN UNBOUNDED PRECEDING
&NBSP ROWS; AND CURRENT ROW）AS runqty FROM cte1
）AS Der WHERE runqty< 95
订单B. Y runqty;
还运行以下查询以查看哪些查询使用并行性
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
$
WITH XMLNAMESPACES
（DEFAULT'http：//schemas.microsoft.com/sqlserver/2004/07/showplan')
$
SELECT
&NBSP; &NBSP; &NBSP; query_plan AS CompleteQueryPlan，
&NBSP; &NBSP; &NBSP; n.value（'（@ StatementText）[1]'，'VARCHAR（4000）'）AS StatementText，
&NBSP; &NBSP; &NBSP; n.value（'（@ StatementOptmLevel）[1]'，'VARCHAR（25）'）AS StatementOptimizationLevel，
&NBSP; &NBSP; &NBSP; n.value（'（@ StatementSubTreeCost）[1]'，'VARCHAR（128）'）AS StatementSubTreeCost，
&NBSP; &NBSP; &NBSP; n.query（'。'）AS ParallelSubTreeXML，
&NBSP; &NBSP; &NBSP; ecp.usecounts，
&NBSP; &NBSP; &NBSP; ecp.size_in_bytes&NBSP;
从sys.dm_exec_cached_plans作为ECP&NBSP;
剖面APPLY sys.dm_exec_query_plan两个（plan_handle）AS EQP&NBSP;
剖面APPLY query_plan。 nodes（'/ ShowPlanXML / BatchSequence / Batch / Statements / StmtSimple'）AS qn（n）
WHERE n.query（'。'）。exists（'// RelOp [@ PhysicalOp =" Parallelism"]'）= 1
Dear All,
I am very new to SQL server performance tuning issues . For one of the newly built SQL server(SQL server 2016 Standard Edition) we are getting lot of slowness . Major wait events reported are LATCH_EX & CXPACKET. I changed "Cost Threshold for Parallelism" to 10 for the instance but that did make the situation worse. I went through Paul's all excellent write- ups on these wait events but still clueless how to proceed on it.
We don't have support of Microsoft also to assist me and I am lone dba for my organisation .
Any inputs/suggestions will be highly appreciated .
Best Regards,
Sandy
First of all this is a good thing to change "Cost Threshold for Parallelism" but set it to 25 at least.
Can you show us the output of this query ?
;WITH Waits AS
(
SELECT
wait_type,
wait_time_ms /1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'ONDEMAND_TASK_QUEUE',N'BROKER_RECEIVE_WAITFOR',N'QDS_ASYNC_QUEUE'
)
)
--filter out additional irrelevant waits
,cte1
AS
(
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
CAST(W1.pct AS DECIMAL(12,2))AS pct
,rn
--- CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
FROM Waits AS W1
--- JOIN Waits AS W2
--- ON W2.rn<= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
-----HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
) SELECT * FROM
( SELECT wait_type,wait_time_s,pct,SUM(pct) OVER(ORDER BY rn
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runqty FROM cte1
) AS Der WHERE runqty <95
ORDER BY runqty;Also run the below query to see what queries are using parallelism
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
query_plan AS CompleteQueryPlan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
n.query('.') AS ParallelSubTreeXML,
ecp.usecounts,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
这篇关于需要帮助解决生产数据库中的SQL Server等待事件故障的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
