Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.