sqlskills-logo-2015-white.png

Survey: most prevalent latch waits (code to run)

I first started blogging about latches and some of the deeper parts of SQL Server internals last year (see Advanced performance troubleshooting: waits, latches, spinlocks) and now I’d like to pick up that thread (no scheduling pun intended :-)) and blog some more about some of the common latches that could be a performance bottleneck.

To that end, I’ve got some code below (plus example output) that will show the most common latch waits that have occurred on your system.

WITH Latches AS
(SELECT
latch_class,
wait_time_ms / 1000.0 AS WaitS,
waiting_requests_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_latch_stats
WHERE latch_class NOT IN (
‘BUFFER’)
AND wait_time_ms > 0
)
SELECT
W1.latch_class AS LatchClass,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(14, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S
FROM Latches AS W1
INNER JOIN Latches AS W2
ON W2.RowNum <= W1.RowNum
WHERE W1.WaitCount > 0
GROUP BY W1.RowNum, W1.latch_class, W1.WaitS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) – W1.Percentage < 95; — percentage threshold
GO

LatchClass                        Wait_S  WaitCount  Percentage  AvgWait_S
——————————— ——- ———- ———– ———-
LOG_MANAGER                       221.43  4659       45.81       0.0475
ACCESS_METHODS_HOBT_VIRTUAL_ROOT  199.56  7017       41.28       0.0284
FGCB_ADD_REMOVE                   35.17   1047       7.27        0.0336
DBCC_OBJECT_METADATA              26.85   256490     5.55        0.0001

I’d like you to run the code and send me the output (either as a comment or in email). I’ll collate all your output and do some blogging for your enjoyment. [Update: I don’t need any more data – thanks!]

Thanks!

62 thoughts on “Survey: most prevalent latch waits (code to run)”

  1. LatchClass|Wait_S|WaitCount|Percentage|AvgWait_S
    ACCESS_METHODS_DATASET_PARENT|3609.54|232903|26.61|0.0155
    DBCC_MULTIOBJECT_SCANNER|3474.60|896275|25.61|0.0039
    DBCC_PFS_STATUS|3360.49|1683543|24.77|0.0020
    FGCB_ADD_REMOVE|1106.13|130|8.15|8.5087
    DBCC_FILE_CHECK_OBJECT|974.11|481683|7.18|0.0020
    DBCC_OBJECT_METADATA|572.03|27024116|4.22|0.0000

    Reply

  2. LatchClass Wait_S WaitCount Percentage AvgWait_S
    LOG_MANAGER 682.30 865 92.64 0.7888
    ACCESS_METHODS_DATASET_PARENT 44.01 383 5.98 0.1149

    Reply

  3. Server A1 (Test):
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_MULTIOBJECT_SCANNER 1661.27 5509546 39.47 0.0003
    DBCC_OBJECT_METADATA 1308.37 3108014 31.09 0.0004
    FGCB_ADD_REMOVE 610.29 85 14.50 7.1798
    DBCC_PFS_STATUS 251.75 509930 5.98 0.0005
    ACCESS_METHODS_DATASET_PARENT 173.80 484086 4.13 0.0004

    Server A2 (Prod):
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 21096.44 15049167 82.63 0.0014
    DBCC_OBJECT_METADATA 2899.63 4191164 11.36 0.0007
    DBCC_MULTIOBJECT_SCANNER 923.67 5581847 3.62 0.0002

    Server B1 (Dev):
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 6186.55 9629096 76.51 0.0006
    NESTING_TRANSACTION_FULL 1562.85 1288631 19.33 0.0012

    Server B2 (Test):
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_HOBT_COUNT 0.02 35 100.00 0.0005

    Server C1 (Prod):
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 314.31 491170 95.92 0.0006

    Reply

  5. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 33006.85 14370060 43.79 0.0023
    DBCC_MULTIOBJECT_SCANNER 19819.49 15273937 26.29 0.0013
    DBCC_CHECK_AGGREGATE 9870.83 4258697 13.09 0.0023
    DBCC_FILE_CHECK_OBJECT 7209.26 7320825 9.56 0.0010
    DBCC_PFS_STATUS 4061.77 5541133 5.39 0.0007

    Reply

  6. LatchClass|Wait_S|WaitCount|Percentage|AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR|9284.35|5401116|90.24|0.0017
    NESTING_TRANSACTION_FULL|649.34|185574|6.31|0.0035

    Reply

  7. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 5.38 4722 58.93 0.0011
    ACCESS_METHODS_HOBT_VIRTUAL_ROOT 1.23 587 13.46 0.0021
    LOG_MANAGER 1.03 6 11.31 0.1720
    ACCESS_METHODS_HOBT_COUNT 0.61 278 6.66 0.0022
    ACCESS_METHODS_DATASET_PARENT 0.42 563 4.64 0.0008

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 854.88 2966965 98.26 0.0003

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 7663819.29 6203680697 82.01 0.0012
    DBCC_MULTIOBJECT_SCANNER 1090368.95 598033317 11.67 0.0018
    DBCC_PFS_STATUS 484297.13 281297457 5.18 0.0017

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 138304.15 1006663457 99.86 0.0001

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 2957.20 670180 99.42 0.0044

    Reply

  9. LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_PFS_STATUS 5572.55 1123767 67.40 0.0050
    DBCC_MULTIOBJECT_SCANNER 1451.89 1316737 17.56 0.0011
    DBCC_FILE_CHECK_OBJECT 881.87 756680 10.67 0.0012

    Reply

  10. LatchClass Wait_S WaitCount Percentage AvgWait_S
    DATABASE_MIRRORING_CONNECTION 67487.17 5074076 99.76 0.0133

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 501398.00 157807971 99.19 0.0032

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 142.02 38224 94.93 0.0037
    LOG_MANAGER 2.75 12 1.83 0.2288

    Reply

  11. LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_PFS_STATUS 13375.67 20279254 27.21 0.0007
    DBCC_MULTIOBJECT_SCANNER 12789.88 17695945 26.01 0.0007
    ACCESS_METHODS_DATASET_PARENT 12513.72 9058350 25.45 0.0014
    DBCC_FILE_CHECK_OBJECT 7421.10 10705453 15.09 0.0007
    DBCC_CHECK_AGGREGATE 1594.22 2388500 3.24 0.0007

    Reply

  12. –Server 1–
    ACCESS_METHODS_HOBT_COUNT 0.28 1746 49.65 0.0002
    ACCESS_METHODS_DATASET_PARENT 0.25 401 44.88 0.0006
    TRACE_CONTROLLER 0.03 1 5.48 0.0310

    –Server 2–
    ACCESS_METHODS_DATASET_PARENT 285736.67 269821798 99.80 0.0011

    Reply

  14. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 24924.16 10740152 84.82 0.0023
    DBCC_PFS_STATUS 1685.55 402708 5.74 0.0042
    NESTING_TRANSACTION_FULL 1466.76 547537 4.99 0.0027

    Reply

  15. Paul,
    the CTE gives me ‘Divide by zero’ error on two machines, all latches have WaitS = 0. I added "and wait_time_ms > 0" to the condition inside CTE.
    Here’s the data from ~30 servers:
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    DATABASE_MIRRORING_CONNECTION 7.16 150933 74.96 0.0000
    DATABASE_MIRRORING_SERVER 1.84 45 19.31 0.0410
    ACCESS_METHODS_HOBT_COUNT 0.28 7413 2.95 0.0000
    FCB 0.25 5 100.00 0.0500
    ACCESS_METHODS_DATASET_PARENT 24.12 3360 97.86 0.0072
    FCB 2.45 1 98.75 2.4530
    ACCESS_METHODS_DATASET_PARENT 166.43 163997 98.68 0.0010
    TRACE_CONTROLLER 0.02 1 100.00 0.0160
    ACCESS_METHODS_DATASET_PARENT 71806.92 21908940 99.83 0.0033
    ACCESS_METHODS_DATASET_PARENT 895619.12 72397235 94.01 0.0124
    NESTING_TRANSACTION_FULL 47308.03 6831904 4.97 0.0069
    ACCESS_METHODS_DATASET_PARENT 361370.72 161832730 99.18 0.0022
    ACCESS_METHODS_DATASET_PARENT 1662.26 396271 98.11 0.0042
    ACCESS_METHODS_DATASET_PARENT 944.71 324857 99.72 0.0029
    DATABASE_MIRRORING_CONNECTION 193.09 718276 95.69 0.0003
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 12.51 2005 98.27 0.0062
    LOG_MANAGER 7.77 6 99.59 1.2950
    ACCESS_METHODS_DATASET_PARENT 3.85 3801 89.39 0.0010
    LOG_MANAGER 0.27 11 6.15 0.0241
    ACCESS_METHODS_DATASET_PARENT 2.73 139 54.69 0.0197
    LOG_MANAGER 2.17 26 43.43 0.0835
    TRACE_CONTROLLER 0.02 1 100.00 0.0150
    ACCESS_METHODS_HOBT_VIRTUAL_ROOT 0.05 10 75.00 0.0048
    ACCESS_METHODS_HOBT_COUNT 0.02 52 25.00 0.0003
    ACCESS_METHODS_BULK_ALLOC 217.86 1719 80.90 0.1267
    NESTING_TRANSACTION_FULL 43.24 66078 16.06 0.0007
    ACCESS_METHODS_DATASET_PARENT 5838.77 8181902 98.82 0.0007
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 2972.93 10572151 76.75 0.0003
    ACCESS_METHODS_DATASET_PARENT 756.76 1227057 19.54 0.0006
    ACCESS_METHODS_DATASET_PARENT 5.63 1101 44.39 0.0051
    LOG_MANAGER 4.42 8 34.88 0.5526
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 2.41 6517 19.01 0.0004
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 7719.67 1644459 85.80 0.0047
    ACCESS_METHODS_DATASET_PARENT 1116.30 50291 12.41 0.0222
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 1.21 6208 45.18 0.0002
    LOG_MANAGER 0.74 22 27.60 0.0335
    ACCESS_METHODS_ACCESSOR_CACHE 0.37 3301 13.84 0.0001
    NESTING_TRANSACTION_FULL 0.18 1499 6.90 0.0001
    FCB 0.11 7 4.12 0.0157
    TRACE_CONTROLLER 0.06 1 100.00 0.0630
    LOG_MANAGER 0.11 15 53.92 0.0073
    TRACE_CONTROLLER 0.09 2 46.08 0.0470
    LOG_MANAGER 0.89 4 61.38 0.2225
    ACCESS_METHODS_HOBT_COUNT 0.44 126 30.07 0.0035
    ACCESS_METHODS_DATASET_PARENT 0.08 279 5.31 0.0003
    SERVICE_BROKER_TRANSMISSION_WORKTABLE 0.16 3 100.00 0.0520
    ACCESS_METHODS_HOBT_VIRTUAL_ROOT 0.02 1 51.61 0.0160
    TRACE_CONTROLLER 0.02 1 48.39 0.0150
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 148.42 350956 53.54 0.0004
    ACCESS_METHODS_DATASET_PARENT 128.23 18270 46.26 0.0070
    ACCESS_METHODS_DATASET_PARENT 1042.40 202541 99.10 0.0051

    Reply

  16. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 46967.71 3005327 57.93 0.0156
    NESTING_TRANSACTION_FULL 32349.08 1982002 39.90 0.0163

    Reply

  17. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 290587.58 22077528 93.74 0.0132
    DBCC_MULTIOBJECT_SCANNER 10448.03 2473325 3.37 0.0042

    Reply

  18. SQL1
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_PFS_STATUS 3723.78 10737860 59.97 0.0003
    DBCC_MULTIOBJECT_SCANNER 1429.92 4600940 23.03 0.0003
    DBCC_CHECK_TABLE_INIT 760.96 4093 12.26 0.1859

    SQL2
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_PFS_STATUS 476.06 1828955 39.29 0.0003
    DBCC_OBJECT_METADATA 452.40 500869 37.34 0.0009
    DBCC_MULTIOBJECT_SCANNER 196.99 774492 16.26 0.0003
    DBCC_CHECK_TABLE_INIT 42.52 700 3.51 0.0607

    Reply

  19. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 8265337.51 1746168136 50.30 0.0047
    DBCC_MULTIOBJECT_SCANNER 4120688.76 2357557420 25.08 0.0017
    DBCC_FILE_CHECK_OBJECT 3173230.95 2302805896 19.31 0.0014
    DBCC_CHECK_AGGREGATE 454578.13 214910589 2.77 0.0021

    Reply

  20. several sql 2005 servers

    Server Name LatchClass Wait_S WaitCount Percentage AvgWait_S
    server1 ACCESS_METHODS_SCAN_RANGE_GENERATOR 231551.09 40575897 99.02 0.0057
    server2 DATABASE_MIRRORING_CONNECTION 26.39 1349374 41.24 0
    server2 ACCESS_METHODS_DATASET_PARENT 19.62 4032 30.66 0.0049
    server2 ACCESS_METHODS_SCAN_RANGE_GENERATOR 16.48 23010 25.76 0.0007
    server3 ACCESS_METHODS_SCAN_RANGE_GENERATOR 1859.16 6643333 98.35 0.0003
    server4 ACCESS_METHODS_DATASET_PARENT 97.99 11842 65.51 0.0083
    server4 ACCESS_METHODS_SCAN_RANGE_GENERATOR 46.69 200161 31.21 0.0002

    Reply

  21. Several 2008 servers

    Server Name LatchClass Wait_S WaitCount Percentage AvgWait_S
    server5 ACCESS_METHODS_DATASET_PARENT 17684.09 4593625 99.77 0.0038
    server6 ACCESS_METHODS_DATASET_PARENT 13651.02 1719986 99.91 0.0079
    server7 ACCESS_METHODS_DATASET_PARENT 339600.99 256375845 93.21 0.0013
    server7 DBCC_MULTIOBJECT_SCANNER 10683.94 26882841 2.93 0.0004
    server8 DATABASE_MIRRORING_CONNECTION 2079.77 15159270 90.43 0.0001
    server8 DATABASE_MIRRORING_STREAM 82.38 996376 3.58 0.0001
    server8 ACCESS_METHODS_DATASET_PARENT 54.67 1989 2.38 0.0275

    Reply

  22. LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_PFS_STATUS 4976920.05 1140575069 64.87 0.0044
    ACCESS_METHODS_DATASET_PARENT 1536017.41 552931803 20.02 0.0028
    DBCC_MULTIOBJECT_SCANNER 869214.19 194523459 11.33 0.0045

    Reply

  24. Highly active prod OLTP DB..

    ACCESS_METHODS_SCAN_RANGE_GENERATOR 16231.37 100385386 65.26 0.0002
    ACCESS_METHODS_DATASET_PARENT 4350.33 1223080 17.49 0.0036
    ACCESS_METHODS_HOBT_VIRTUAL_ROOT 2906.96 83471 11.69 0.0348
    DATABASE_CHECKPOINT 947.52 5 3.81 189.5038

    Reply

  25. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ———————————————————— ————————————— ——————– ————————————— —————————————
    ACCESS_METHODS_DATASET_PARENT 259617.43 46537620 97.23 0.0056

    (1 row(s) affected)

    Reply

  26. LatchClass Wait_S WaitCount Percentage Avg_Wait_S

    ACCESS_METHODS_DATASET_PARENT 204.43 162274 59.22 0.0013
    LOG_MANAGER 140.19 99 40.61 1.4160

    Reply

  28. Hi Paul,

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 57293.83 7383546 75.25 0.0078
    NESTING_TRANSACTION_FULL 18702.86 34530193 24.56 0.0005

    Thanks.

    Reply

  29. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 9049.24 8265580 99.83 0.0011

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 17042.08 4609279 99.81 0.0037

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 235429.40 56947264 99.84 0.0041

    Reply

  30. Hi Paul,

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 2772.67 1780978 90.18 0.0016
    LOG_MANAGER 264.58 39 8.61 6.7841

    Thanks.

    Reply

  31. Hi Paul

    Server is pretty quiet at the moment, will try to pull up the busy evening load later.

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ALLOC_IAM_PAGE_RANGE_CACHE 309.38 42 46.42 7.3662
    TRACE_CONTROLLER 238.12 5576 35.73 0.0427
    ACCESS_METHODS_HOBT_VIRTUAL_ROOT 116.57 12747 17.49 0.0091

    Reply

  32. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 22506.99 7373964 80.88 0.0031
    FGCB_ADD_REMOVE 3956.33 397 14.22 9.9656

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_PFS_STATUS 59872.38 453172738 58.75 0.0001
    DBCC_MULTIOBJECT_SCANNER 28814.40 385797440 28.28 0.0001
    DBCC_FILE_CHECK_OBJECT 9488.44 205416442 9.31 0.0000

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_HOBT_COUNT 0.02 6 100.00 0.0025

    Reply

  33. prod 2008er:
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_BULK_ALLOC 252.62 167 47.48 1.5127
    DBCC_OBJECT_METADATA 157.07 5708962 29.53 0.0000
    FGCB_ADD_REMOVE 91.01 21 17.11 4.3338
    LOG_MANAGER 15.32 74 2.88 0.2070

    prod 2005er:
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_HOBT_VIRTUAL_ROOT 45.09 144097 48.04 0.0003
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 20.80 76216 22.16 0.0003
    ALLOC_IAM_PAGE_RANGE_CACHE 16.35 200 17.42 0.0818
    ACCESS_METHODS_DATASET_PARENT 5.10 2218 5.44 0.0023
    ACCESS_METHODS_ACCESSOR_CACHE 3.30 13343 3.52 0.0002

    Reply

  34. Here you go!

    –Server1
    ACCESS_METHODS_HOBT_VIRTUAL_ROOT 578938.85 8185224 77.10 0.0707
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 115961.84 126460221 15.44 0.0009
    DBCC_MULTIOBJECT_SCANNER 18220.93 51606069 2.43 0.0004
    DBCC_PFS_STATUS 14330.46 59301101 1.91 0.0002

    –Server2
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 132605.40 162857971 59.47 0.0008
    DBCC_MULTIOBJECT_SCANNER 57339.91 99607814 25.72 0.0006
    ACCESS_METHODS_DATASET_PARENT 12125.15 2990636 5.44 0.0041
    DBCC_FILE_CHECK_OBJECT 10873.49 38018673 4.88 0.0003

    –Server3
    TRACE_CONTROLLER 4734.51 89762 81.28 0.0527
    TRACE 563.76 137261 9.68 0.0041
    ALLOC_IAM_PAGE_RANGE_CACHE 257.86 15 4.43 17.1907

    –Server4
    ACCESS_METHODS_HOBT_COUNT 54.25 514 64.20 0.1055
    FGCB_ADD_REMOVE 13.22 300 15.65 0.0441
    ALLOC_IAM_PAGE_RANGE_CACHE 11.14 5 13.18 2.2280
    TRACE_CONTROLLER 3.72 411 4.40 0.0090

    Reply

  35. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 906935.48 1145583027 81.77 0.0008
    DATABASE_MIRRORING_CONNECTION 198248.46 181058839 17.87 0.0011

    Reply

  36. ACCESS_METHODS_DATASET_PARENT 83830.38 47662522 83.48 0.0018
    DBCC_PFS_STATUS 9615.97 3211002 9.58 0.0030
    DBCC_MULTIOBJECT_SCANNER 4974.50 1500510 4.95 0.0033

    Reply

  38. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 11703.52 1696560 59.03 0.0069
    ACCESS_METHODS_DATASET_PARENT 7901.91 206612 39.85 0.0382

    Reply

  39. This is a very old server whose hardware is due for an upgrade.
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    FGCB_ADD_REMOVE 1.58 7 60.94 0.2253
    LOG_MANAGER 0.62 20 24.03 0.0311
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 0.24 41 9.08 0.0057
    ACCESS_METHODS_DATASET_PARENT 0.15 24 5.95 0.0064

    Reply

  40. LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_MULTIOBJECT_SCANNER 49.21 38010 71.82 0.0013
    NESTING_TRANSACTION_FULL 10.60 14601 15.47 0.0007
    DBCC_PFS_STATUS 4.13 34244 6.02 0.0001
    DBCC_FILE_CHECK_OBJECT 1.59 17780 2.32 0.0001

    Reply

  41. LatchClass Wait_S WaitCount Percentage AvgWait_S
    NESTING_TRANSACTION_READONOY 2858.52 51965125 77.76 0.0001
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 694.76 1633821 18.90 0.0004

    Reply

  42. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 104983.19 38338751 99.26 0.0027

    Reply

  43. 2005 server 1
    ————-
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    VERSIONING_TRANSACTION_LIST 1.18 19692 85.43 0.0001
    ACCESS_METHODS_HOBT_COUNT 0.12 71 8.95 0.0017
    BUFFER_POOL_GROW 0.08 13 5.63 0.0060

    2005 server 2
    ————-
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    DBCC_MULTIOBJECT_SCANNER 110128.49 23241956 40.40 0.0047
    DBCC_PFS_STATUS 86800.08 17329749 31.84 0.0050
    DBCC_FILE_CHECK_OBJECT 37734.69 9991840 13.84 0.0038
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 25241.51 1604378 9.26 0.0157

    2005 server 2
    ————-
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 418781.01 89592359 79.35 0.0047
    NESTING_TRANSACTION_READONOY 35470.55 8632698 6.72 0.0041
    ACCESS_METHODS_DATASET_PARENT 24397.46 5428532 4.62 0.0045
    DBCC_MULTIOBJECT_SCANNER 20777.67 8928410 3.94 0.0023
    NESTING_TRANSACTION_FULL 16814.14 3577874 3.19 0.0047

    Reply

  44. All SQL Server 2008R2

    Server 1:
    ACCESS_METHODS_DATASET_PARENT 269087.19 127628774 97.88 0.0021

    Server 2:
    DBCC_MULTIOBJECT_SCANNER 101152.71 43254776 80.65 0.0023
    DBCC_OBJECT_METADATA 12848.97 11243163 10.24 0.0011
    DBCC_CHECK_AGGREGATE 6052.33 2860508 4.83 0.0021

    Server 3:
    DBCC_MULTIOBJECT_SCANNER 15777.35 14272923 49.84 0.0011
    DBCC_OBJECT_METADATA 7642.47 5462631 24.14 0.0014
    ACCESS_METHODS_DATASET_PARENT 6733.31 8343623 21.27 0.0008

    Reply

  45. Here is wat i get

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ———————————————————— ————————————— ——————– ————————————— —————————————
    ACCESS_METHODS_DATASET_PARENT 537445.56 272115024 99.95 0.0020

    Reply

  46. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ———————————————————— ————————————— ——————– ————————————— —————————————
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 47015.30 22157692 94.99 0.0021
    ACCESS_METHODS_DATASET_PARENT 2111.21 351550 4.27 0.0060

    Reply

  47. ACCESS_METHODS_DATASET_PARENT 2759.98 939665 83.60 0.0029
    DBCC_MULTIOBJECT_SCANNER 351.28 656398 10.64 0.0005
    LOG_MANAGER 164.74 101 4.99 1.6311

    Reply

  48. server 1 (OLTP)
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 3.43 15611 81.34 0.0002
    ACCESS_METHODS_DATASET_PARENT 0.73 1510 17.22 0.0005

    server 2 (OLTP)
    ACCESS_METHODS_DATASET_PARENT 0.73 1510 17.22 0.0005

    server 3 (DW)
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 1857.75 590292 83.90 0.0031
    ACCESS_METHODS_DATASET_PARENT 261.51 40306 11.81 0.0065

    server 4 (SharePoint)
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 1192.42 2756304 93.63 0.0004
    ACCESS_METHODS_HOBT_VIRTUAL_ROOT 26.33 9106 2.07 0.0029

    Reply

  49. DBCC_MULTIOBJECT_SCANNER 275713.86 68878691 43.11 0.0040
    ACCESS_METHODS_DATASET_PARENT 263432.50 13534652 41.19 0.0195
    DBCC_PFS_STATUS 64031.66 115153692 10.01 0.0006
    ACCESS_METHODS_BULK_ALLOC 17991.90 17022 2.81 1.0570

    Reply

  50. Server 1:
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    LOG_MANAGER 0.38 1 53.34 0.3750
    ACCESS_METHODS_DATASET_PARENT 0.31 56 44.38 0.0056

    Server 2:
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    FGCB_ADD_REMOVE 4.69 15 47.86 0.3125
    ACCESS_METHODS_DATASET_PARENT 4.54 3168 46.40 0.0014
    LOG_MANAGER 0.52 6 5.26 0.0858

    Reply

  51. DBCC_PERF 25.02 1879 98.16 0.0133
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 20998.38 14089870 83.56 0.0015
    DBCC_OBJECT_METADATA 1389.65 40725098 5.53 0
    ACCESS_METHODS_DATASET_PARENT 1243.79 3052406 4.95 0.0004
    DBCC_MULTIOBJECT_SCANNER 808.77 2695332 3.22 0.0003
    DBCC_OBJECT_METADATA 124.12 4330620 52.18 0
    ACCESS_METHODS_DATASET_PARENT 66.6 4515 28 0.0148
    LOG_MANAGER 19.66 66 8.27 0.2979
    NESTING_TRANSACTION_FULL 13.36 16008 5.61 0.0008
    DBCC_MULTIOBJECT_SCANNER 5.72 22008 2.4 0.0003
    TRACE_CONTROLLER 0.02 2 100 0.0075
    ACCESS_METHODS_DATASET_PARENT 0.11 18 43.78 0.0061
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 0.09 87 37.75 0.0011
    TRACE_CONTROLLER 0.05 2 18.47 0.023
    ACCESS_METHODS_DATASET_PARENT 47.94 12583 90.33 0.0038
    FGCB_ADD_REMOVE 2.76 4 5.2 0.69
    DBCC_OBJECT_METADATA 419.05 7658429 81.47 0.0001
    DBCC_MULTIOBJECT_SCANNER 55.95 162006 10.88 0.0003
    ACCESS_METHODS_DATASET_PARENT 15.13 13489 2.94 0.0011

    Below is identical data, but separated by server. I didn’t kn ow which format would make your extracti on job easier.

    DBCC_PERF 25.02 1879 98.16 0.0133

    ACCESS_METHODS_SCAN_RANGE_GENERATOR 20998.38 14089870 83.56 0.0015
    DBCC_OBJECT_METADATA 1389.65 40725098 5.53 0
    ACCESS_METHODS_DATASET_PARENT 1243.79 3052406 4.95 0.0004
    DBCC_MULTIOBJECT_SCANNER 808.77 2695332 3.22 0.0003

    DBCC_OBJECT_METADATA 124.12 4330620 52.18 0
    ACCESS_METHODS_DATASET_PARENT 66.6 4515 28 0.0148
    LOG_MANAGER 19.66 66 8.27 0.2979
    NESTING_TRANSACTION_FULL 13.36 16008 5.61 0.0008
    DBCC_MULTIOBJECT_SCANNER 5.72 22008 2.4 0.0003

    TRACE_CONTROLLER 0.02 2 100 0.0075

    ACCESS_METHODS_DATASET_PARENT 0.11 18 43.78 0.0061
    ACCESS_METHODS_SCAN_RANGE_GENERATOR 0.09 87 37.75 0.0011
    TRACE_CONTROLLER 0.05 2 18.47 0.023

    ACCESS_METHODS_DATASET_PARENT 47.94 12583 90.33 0.0038
    FGCB_ADD_REMOVE 2.76 4 5.2 0.69

    DBCC_OBJECT_METADATA 419.05 7658429 81.47 0.0001
    DBCC_MULTIOBJECT_SCANNER 55.95 162006 10.88 0.0003
    ACCESS_METHODS_DATASET_PARENT 15.13 13489 2.94 0.0011

    Reply

  52. Server 1

    ACCESS_METHODS_SCAN_RANGE_GENERATOR 2733782.68 345731770 82.80 0.0079
    NESTING_TRANSACTION_READONOY 552623.39 91863096 16.74 0.0060

    Server 2

    ACCESS_METHODS_DATASET_PARENT 6682708.17 1002354507 99.39 0.0067

    Reply

  53. From data warehouse:-

    DBCC_MULTIOBJECT_SCANNER 45389.41 103051415 37.36 0.0004
    DBCC_OBJECT_METADATA 44832.51 188171404 36.90 0.0002
    DBCC_FILE_CHECK_OBJECT 13267.66 36167202 10.92 0.0004
    DBCC_PFS_STATUS 12428.79 27247062 10.23 0.0005

    Reply

  54. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 70136.62 30051028 95.28 0.0023

    Reply

  56. LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_HOBT_COUNT 67.30 5941 84.26 0.0113
    ALLOC_IAM_PAGE_RANGE_CACHE 6.11 21 7.65 0.2911
    FGCB_ADD_REMOVE 4.76 9 5.96 0.5287

    Reply

  58. ¿que significa que solo me de este registro?
    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 802994.60 5229439 99.28 0.1536

    Reply

  59. SQL2008r2 standard

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    —————————— ——– ———– ———– ———–
    ACCESS_METHODS_DATASET_PARENT 150.46 92700 50.05 0.0016
    LOG_MANAGER 133.72 186 44.48 0.7189
    ACCESS_METHODS_HOBT_COUNT 16.12 49936 5.36 0.0003

    Reply

  60. SQL2008R2 Standard

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ———————————————————————-
    ACCESS_METHODS_DATASET_PARENT 1049409.64 729466939 99.98 0.0014

    Reply

  62. Getting timeout alerts for DBCC_CHECK_TABLE_INIT. below is out for query provided in blog. Please check and suggest solution to avoid the timeout alerts.

    LatchClass Wait_S WaitCount Percentage AvgWait_S
    ACCESS_METHODS_DATASET_PARENT 6629705.01 1013525767 80.15 0.0065
    DBCC_MULTIOBJECT_SCANNER 552256.09 80050759 6.68 0.0069
    DBCC_CHECK_TABLE_INIT 467233.77 7180 5.65 65.0743
    DBCC_CHECK_AGGREGATE 217677.28 34249966 2.63 0.0064

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course