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