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
  • http://sqlcat.com/technicalnotes/archive/2008/12/09/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager.aspx
• 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
  • NVARCHAR => VARCHAR leads to Table/Index Scans
  • Sympthom: Table/Index Scan + Implicit Conversion
  • http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx
  • http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/16/unexpected-side-effects-problems-from-implicit-conversions.aspx
• Bookmark Lookups can lead to Deadlocks

Thanks for reading

-Klaus