SQL MCM Training – Day 5

Today I’ve not taken too much notes, because the whole afternoon was about the Management Data Warehouse and Multi-Instance Management. But before that Paul and Jonathan have spoken a lot about troubleshooting with DMVs.

Module 12: DMVs – Part 2

  • sys.dm_io_virtual_file_stats
    • Spikes can’t be seen from this DMV, only through perfmon.exe
  • I/O Backup activity is tracked by sys.dm_io_virtual_file_stats
    • I/Os aren’t going through the Buffer Pool, but the I/Os are managed by SQLOS
  • There can be only a maximum of 32 outstanding I/Os for the log file per database
  • CHECKPOINT Throttling
    • CHECKPOINT process will throttle itself down, when the latency is more than 20ms
    • When CHECKPOINT occurs during shutdown, it will throttle itself down, when the latency is more than 100ms
    • SQLIO Basics Chapter 2, Page 48
  • Log Flushes
    • A VLF has several log blocks of size between 512 bytes to 60kb
    • When a transaction is committed, rollback or reaches a size of 60kb it is copied to the Log Cache Manager
    • Log Cache Manager has 128 buffers per database
    • Spinlock must be aquired when accessing the Log Cache Manager
    • Log Cache Manager flushes the buffer asynchronously out to the I/O sub system
      • Here is the limit of the 32 outstanding I/Os or 3840kb on x64
  • Lazywriter uses the BUF structure to know the last 2 times, when a page was accessed
  • sys.dm_os_buffer_descriptors can be used to check which database has the highest pressure in the Buffer Pool when memory pressure exists
  • DBCC CHECKDB disfavours pages read in the buffer pool
    • sys.dm_db_index_physical_stats also uses disfavouring
    • “bstat” in DBCC PAGE output in page header has one bit, that says if the page is disfavoured or not, but these bits are not documented
  • Backups don’t touch the buffer pool
  • When a scan reads data of more than 10% of the buffer pool, the pages are disfavoured immediately
  • sys.dm_db_partition_stats
    • Gives you page count, instead of using sys.dm_db_index_physical_stats which must touch the index
  • Version Store is completely no-logged
    • TempDb has a lot of different behavior regarding transaction log, therefore the Version Store was put into TempDb and not into the user database
  • sys.dm_exec_procedure_stats
    • Returns runtime statistics about executed stored procedures
  • Shared Locks are not acquired for master and TempDb when you set the database context to it
  • Lock list of the Lock Manager is a dynamic list
    • It can change during the reading of sys.dm_tran_locks
    • The output of sys.dm_tran_locks is not a precise output
  • Lock conversion will not wait indefinitely
  • sys.dm_os_ring_buffer
    • Written by System Health events
    • RING_BUFFER_CONNECTIVITY shows how long a Login Trigger or a Resource Governor classifier function takes for execution
      • SspiProcessingInMilliseconds: Authentifcation time in Active Directory
  • There is a memory clerk for each memory node (Hard- and Soft-NUMA)
  • DAC has it’s own memory node
  • sys.dm_os_memory_node_access_state
    • Cross NUMA node memory access statistics
    • Trace Flag 842 is needed
  • Implicit Conversions
  • Bookmark Lookups can lead to Deadlocks

Thanks for reading


1 thought on “SQL MCM Training – Day 5”

  1. Lonny Niederstadt

    Do dbcc checktable, checkalloc, checkcatalog all disfavor pages they read? If a page is already in the buffer pool cache, will it be read from disk by dbcc checktable, checkalloc, checkcatalog anyway?

Comments are closed.

Attend my Live Training on September 29, 2022:

SQL Server Availability Groups

EUR 590 incl. 20% VAT