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
      • LOGCACHE_ACCESS
    • 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

-Klaus

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.

Enjoy one of my newest Online Trainings:

Design, Deploy, and Optimize SQL Server on VMware

EUR 699 incl. 20% VAT