The Accidental DBA:Troubleshooting

你将得到类似下图结果

这个结果并没有太大的用处，我们需要过滤掉那些不相干的等待。我使用Paul的代码

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N‘BROKER_EVENTHANDLER‘, N‘BROKER_RECEIVE_WAITFOR‘,
        N‘BROKER_TASK_STOP‘, N‘BROKER_TO_FLUSH‘,
        N‘BROKER_TRANSMITTER‘, N‘CHECKPOINT_QUEUE‘,
        N‘CHKPT‘, N‘CLR_AUTO_EVENT‘,
        N‘CLR_MANUAL_EVENT‘, N‘CLR_SEMAPHORE‘,
 
        -- Maybe uncomment these four if you have mirroring issues
        N‘DBMIRROR_DBM_EVENT‘, N‘DBMIRROR_EVENTS_QUEUE‘,
        N‘DBMIRROR_WORKER_QUEUE‘, N‘DBMIRRORING_CMD‘,
 
        N‘DIRTY_PAGE_POLL‘, N‘DISPATCHER_QUEUE_SEMAPHORE‘,
        N‘EXECSYNC‘, N‘FSAGENT‘,
        N‘FT_IFTS_SCHEDULER_IDLE_WAIT‘, N‘FT_IFTSHC_MUTEX‘,
 
        -- Maybe uncomment these six if you have AG issues
        N‘HADR_CLUSAPI_CALL‘, N‘HADR_FILESTREAM_IOMGR_IOCOMPLETION‘,
        N‘HADR_LOGCAPTURE_WAIT‘, N‘HADR_NOTIFICATION_DEQUEUE‘,
        N‘HADR_TIMER_TASK‘, N‘HADR_WORK_QUEUE‘,
 
        N‘KSOURCE_WAKEUP‘, N‘LAZYWRITER_SLEEP‘,
        N‘LOGMGR_QUEUE‘, N‘MEMORY_ALLOCATION_EXT‘,
        N‘ONDEMAND_TASK_QUEUE‘,
        N‘PREEMPTIVE_XE_GETTARGETSTATE‘,
        N‘PWAIT_ALL_COMPONENTS_INITIALIZED‘,
        N‘PWAIT_DIRECTLOGCONSUMER_GETNEXT‘,
        N‘QDS_PERSIST_TASK_MAIN_LOOP_SLEEP‘, N‘QDS_ASYNC_QUEUE‘,
        N‘QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP‘,
        N‘QDS_SHUTDOWN_QUEUE‘, N‘REDO_THREAD_PENDING_WORK‘,
        N‘REQUEST_FOR_DEADLOCK_SEARCH‘, N‘RESOURCE_QUEUE‘,
        N‘SERVER_IDLE_CHECK‘, N‘SLEEP_BPOOL_FLUSH‘,
        N‘SLEEP_DBSTARTUP‘, N‘SLEEP_DCOMSTARTUP‘,
        N‘SLEEP_MASTERDBREADY‘, N‘SLEEP_MASTERMDREADY‘,
        N‘SLEEP_MASTERUPGRADED‘, N‘SLEEP_MSDBSTARTUP‘,
        N‘SLEEP_SYSTEMTASK‘, N‘SLEEP_TASK‘,
        N‘SLEEP_TEMPDBSTARTUP‘, N‘SNI_HTTP_ACCEPT‘,
        N‘SP_SERVER_DIAGNOSTICS_SLEEP‘, N‘SQLTRACE_BUFFER_FLUSH‘,
        N‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP‘,
        N‘SQLTRACE_WAIT_ENTRIES‘, N‘WAIT_FOR_RESULTS‘,
        N‘WAITFOR‘, N‘WAITFOR_TASKSHUTDOWN‘,
        N‘WAIT_XTP_RECOVERY‘,
        N‘WAIT_XTP_HOST_WAIT‘, N‘WAIT_XTP_OFFLINE_CKPT_NEW_LOG‘,
        N‘WAIT_XTP_CKPT_CLOSE‘, N‘XE_DISPATCHER_JOIN‘,
        N‘XE_DISPATCHER_WAIT‘, N‘XE_TIMER_EVENT‘)
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST (‘https://www.sqlskills.com/help/waits/‘ + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO

等待类型可以参考MSDN条目中的sys.dm_os_wait_stats
注意百分比的计算，它是使用各等待类型的wait_time_ms除以"总的"wait_time_ms，而这个"总的"是指查询语句中没有过滤掉的那些等待。因此，如果你修改过滤类型，百分比会跟着变动。
1.2、Capturing Wait Statistics
到目前为止，我已经讨论过在某个时间点查看等待统计。作为一名DBA，你需要知道每个实例上哪些等待是正常，并且在系统性能不佳的时候使用这些值。
捕获等待统计信息的最简单的方法是定期将快照数据保存到数据表中；你可以在文章Capturing Baselines for SQL Server: Wait Statistics中找到对应的语句。一旦你有方法来捕获数据，定期回顾它以了解典型等待，并找出潜在的问题。当你发现问题时，你可以使用等待统计信息来帮助你进行故障排除。
1.3、Using the Data
当你发现问题时，一个推荐的做法是，运行你的等待统计查询，然后将结果和你的基线对比。如果你看出什么不一样，你就知道从哪里开始你的调查。但是，等待统计信息仅仅告诉你从哪里开始查找你的答案。Do not assume that your highest wait is the problem, or even that it’s a problem at all.
例如，一个常见的等待CXPACKET，CXPACKET意味着使用并行。如果它是靠前的等待类型，是不是意味着你应该马上调整实例的MAXDOP设置？不是，你应该先去分析为什么它是最高的等待，如果缺少索引并且有大量的表扫描，那么将会有CXPACKET等待。因此你应该优化语句，而不是调整MAXDOP。
另一个例子是WRITELOG等待，当SQL Server等待日志刷新时会产生WRITELOG等待，当信息需要被写入到数据库事务日志时会产生日志刷新。日志刷新应该迅速完成，because when there is a delay in a log write, then the task that initiated the modification has to wait, and tasks may be waiting behind that。但是，日志刷新并不是每一次都发生，所以你会有WRITELOG等待。如果你看到WRITELOG等待是靠前的，不要认为你需要新的存储，你需要进一步去调查。你可以去检查读写情况。
你可以从SQL Server Wait Types and Latch Classes library得到各类等待的详细信息。
正如你所看到的两个例子，等待统计信息是一个起点。它们非常有价值，很容易把它们当作"答案"，但它们不是。Wait statistics do not tell you the entire story about a SQL Server implementation.There is no one “thing” that tells you the entire story, which is why troubleshooting can be incredibly frustrating, yet wonderfully satisfying when you find the root of a problem.
成功地解决SQL Server中的性能问题需要了解所有可用的数据，以帮助你发现和调查，了解从哪里开始，以及捕获哪些信息。
Wait Statistics以往文章：http://www.cnblogs.com/Uest/p/5763100.html#waitstats
二、Monitoring Disk I/O
数据库存储看起来像是一个黑盒子，DBA负责管理数据库，而这些数据库通常位于SAN的某个地方，space simply presented to a DBA as a drive letter representing some amount of space.But storage is about more than a drive letter and a few hundred GBs.拥有足够的空间对数据库很重要，但我经常看到客户计划的是容量而不是性能，这可能会成为一个问题。作为一名DBA，你不仅需要向存储管理员询问存储空间，还需要询问吞吐量，and the best way to back up your request is with data.
2.1、I/O Data in SQL Server
在Accidental DBA系列，我已经提到了不少DMV，今天也不例外。如果你想查看SQL Server中的I/O情况，你可以使用sys.dm_io_virtual_file_stats动态管理视图。2005版本之前的你可以使用fn_virtualfilestats函数得到相同的信息。Paul的文章中有一个我经常用来获取文件信息的查询How to examine IO subsystem latencies from within SQL Server
使用下面语句返回

SELECT
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

数据看起来好像存在严惩的磁盘问题——写延迟超过1s，尤其是我折笔记本电脑使用的SSD。我使用这张截图是为了说明这些数据是累积的，只有当实例重启才会重置。你可以启动一个大IO操作(例如重建索引)来影响sys.dm_io_virtual_file_stats中的数值，并且它可能需要点时间才能恢复正常。Joe有一篇文章详细讨论这个问题，Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data.
sys.dm_io_virtual_file_stats动态管理视图不仅显示延迟，它还会告诉你哪些文件有最高的读和写以及读写的量。你可以确定哪些数据库(和文件)是重点访问的，并随着时间的推移来确定它(们)是否发生了变化。
2.2、I/O Data in Windows
如果你想收集Windows的I/O数据，性能监视器是最好的选择，我喜欢查看每个磁盘的以下计数器信息：
Avg. Disk sec/Read
Avg. Disk Bytes/Read
Avg. Disk sec/Write
Avg. Disk Bytes/Write
Jon谈论过性能计数器，上面提到的计数器会告诉你延迟和吞吐量。延迟是指一个I/O请求需要花费多长时间，但是对于不同的人员它的测量方法会有所不同。通常我们关心的是来自SQL Server的延迟。对于Windows，延迟是从初始I/O请求到完成请求的这段时间。正如，Joe在他的文章中提到，你可能会在从SQL Server与Windows的延迟之间看到一些不同。
当我们使用性能监视器测量延迟，我们查看Avg Disk sec/Read和Avg Disk sec/Write.磁盘缓存、磁盘、控制器卡或存储系统，影响读取和写入值。写入通常被写入到缓存中，并且应该很快完成。读取，当不在缓存中，必须到磁盘获取(这通常会花费更长的时间)。很容易认为延迟和磁盘完全相关，但事实并非如此。记住，我们在讨论I/O子系统，这包括从服务器本身到磁盘并且返回的整个路径。这个路径包括服务器中的HBAs、交换机、SAN中的控制器、SAN中的缓存和磁盘本身。You can never assume that latency is high because the disks can’t keep up. 有时，HBAs的队列深度设置过低，或者你可能与一个失败的组件(比如GBIC)或一个坏的端口卡有间歇性的连接。你必须将你所拥有的延迟信息与存储团队共享，并请求他们进行调查。希望你有一个精明的存储团队，知道如何调查所有的路径。在复杂的环境中，一张图片胜过千言万语。最好是和存储管理员绘制出从操作系统到SAN LUN或卷的映射。这将会有关于服务器、到SAN的路径和SAN本身的讨论。
Remember what matters is getting the I/O to the application. If the IO leaves the disk but gets stuck along the way, that adds to latency. There could be an alternate path available (multi-pathing), but maybe not.
使用Avg. Disk Bytes/Read和Avg. Disk Bytes/Write测量吞吐量，会告诉我们服务器和存储之间转移了多少数据。这是有价值的，而且常常比计算I/Os更有用，因为我们可以用它来了解我们的磁盘需要读写多少数据才能满足需求。理想情况下，当系统进行优化时，你会捕获这些信息(比如添加索引来减少全表扫描，这样会影响I/O的数量)，but often times you will need to just work within the current configuration.
2.3、Capturing Baselines
在讨论sys.dm_os_virtual_file_stats动态管理视图时，我提到了基线。如果你认为我将离开它，那么你一定不知道我对基线的爱！
你希望从SQL Server和Windows获取数据，以向存储管理员提供吞吐量数据。你需要这些数据在SAN上进行存储，这不仅可以给你足够的空间来容纳预期的数据库增长，而且还将为你提供数据库所需的IOPs和MB/sec.
除了单次I/O和延迟数据查看，你应该设立一个进程定期收集这些数据，那样你就能识别是否有变更。You will want to know if a database suddenly starts issuing more IOs (did someone drop an index?) or if the change is I/Os is gradual.你需要确保I/Os在你期望的时间内完成。记住，SAN是一个共享存储，你并不可能总是知道谁和你共享那个存储。如果另一个具有高I/O需求的应用程序被放置在相同的磁盘组上，并且你的延迟增加，那么你希望能够精确地指出这种变化并向你的SAN管理员提供支持，从而支持在你的数据库中性能的变化。
2.4、总结
作为一名DBA，你需要了解你的数据库在读写方面的表现，并且最好了解你的存储团队。同样，了解数据库真正"生存"，和哪些应用共享相同的存储。当一个性能问题出现时，使用基线数据作为开始，并且不要犹豫让你的SAN管理员获取更多信息。虽然有很多数据可以供DBA使用，但你无法自己获取完整的图片。给存储团队买些披萨或甜甜圈，交一些新朋友，这可能没什么坏处。最后如果你有兴趣深入挖掘SQL Server I/O的细节，你可以阅读下面链接：
How It Works: Bob Dorr’s SQL Server I/O Presentation
SQL Server 2000 I/O Basics (still relevant)
SQL Server I/O Basics, Chapter 2
三、Tempdb Contention
Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.
考虑这样的场景，数百个并发查询，这些查询创建、使用、然后删除临时表。每次创建一个临时表，必须分配一个数据页，再加上一个分配元数据页来跟踪分配给该表的数据页。这就需要在PFS页上做标记以表明那两页已被分配。当临时表被删除时，这些页被收回，它们要再次在PFS页上标记。每次只有一个线程可以更改分配页面，这样会使其成为热点，并降低总体工作负载。
但真正有趣的是许多人没有意识到他们有这个问题——甚至是经验丰富的DBA。使用dm_os_waiting_tasks动态管理视图很容易得出你是否有这样的问题。如果你运行下面的查询，你将了解服务器上的各种线程正在等待什么

SELECT
    [owt].[session_id],
    [owt].[exec_context_id],
    [owt].[wait_duration_ms],
    [owt].[wait_type],
    [owt].[blocking_session_id],
    [owt].[resource_description],
    CASE [owt].[wait_type]
        WHEN N‘CXPACKET‘ THEN
            RIGHT ([owt].[resource_description],
            CHARINDEX (N‘=‘, REVERSE ([owt].[resource_description])) - 1)
        ELSE NULL
    END AS [Node ID],
    [es].[program_name],
    [est].text,
    [er].[database_id],
    [eqp].[query_plan],
    [er].[cpu_time]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es] ON
    [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
    [es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
    [es].[is_user_process] = 1
ORDER BY
    [owt].[session_id],
    [owt].[exec_context_id]
GO

如果你看到很多wait_type列是PAGELATCH_UP或者PAGELATCH_EX，并且resource_description是2:1:1，那么这是PFS页(dbid=2,fileid=1,pageid=1)，如果resource_description是2:1:3，它是SGAM页。
本节后续部分与原文不同，使用下述脚本模拟tempdb争用

--step1：存储过程创建、使用、删除临时表
CREATE DATABASE demo ;
GO
-- Create stored procedure that creates a temp table, a clustered index and populates with 10 rows
-- The script expects a database called Demo to exist
USE demo ;
GO
CREATE PROCEDURE [dbo].[usp_temp_table]
AS 
    CREATE TABLE #tmpTable
        (
          c1 INT,
          c2 INT,
          c3 CHAR(5000)
        ) ;
    CREATE UNIQUE CLUSTERED INDEX cix_c1 ON #tmptable ( c1 ) ;
    DECLARE @i INT = 0 ;
    WHILE ( @i < 10 ) 
        BEGIN
            INSERT  INTO #tmpTable ( c1, c2, c3 )
            VALUES  ( @i, @i + 100, ‘coeo‘ ) ;
            SET  @i += 1 ;
        END ;
    DROP TABLE #tmpTable;
GO
-- Create stored procedure that runs usp_temp_table 50 times
CREATE PROCEDURE [dbo].[usp_loop_temp_table]
AS 
    SET nocount ON ;
    DECLARE @i INT = 0 ;
    WHILE ( @i < 100 )
        BEGIN
            EXEC demo.dbo.usp_temp_table ;
            SET  @i += 1 ;
        END ;
GO        

--step2：清除sys.dm_os_wait_stats信息
DBCC sqlperf(‘sys.dm_os_wait_stats‘,clear);

--step3：cmd命令行中执行，Ostress测试300个线程并发，每个线程执行1次
shell> d:
shell> cd D:\Program Files\Microsoft Corporation\RMLUtilsshell> Ostress -S"127.0.0.1,7777" -E -Q"EXEC demo.dbo.usp_loop_temp_table;" -o"E:\Ostress\out" -n300 -r1

--step4：step3完成马上执行


                  

	 	
                    

                        

                            

                                
< 上一篇
sql  所有数据表中 插入字段
                            
                            

                                 
下一篇 >
mysql5.7新特性