SQL MCM Training – Day 3

Nothing to say about today, Jonathan did really a great job describing Extended Events and Baselining/Benchmarking to us, and his boss – Paul – did a quick really great overview of Resource Governor. I’ve done a lot in these areas in the past, so that was a more easy one to me than yesterday J. Here are my study notes for today.

Module 7: Extended Events

• SQL Trace will be replaced by Extended Events some time
  
• SQL Denali CTP3 has more than 500 Extended Events
  
• Event Sessions with events with no targets are possible
  
• Event Sessions with targets and no events are possible
  
• The Event Engine has no idea of Extended Events
  
  • It only provides an operational environment
    
• Event Sessions have memory buffers attached
  
• Dispatcher Pool is a worker pool
  
• Package is a container about events with metadata
  
• package0 is a container about generic things like targets
  
• Any event can work with any action with any taraget from any packet
  
• ETW (Event Tracing for Windows)
  
  • Gets information from the Windows Kernel
    
  • ETW & Extended Events are integrated to each other
    
• Fastest first false value should be placed in the first place in a predicate
  
  • Support for short circuit evaluation
    
  • First false evaluation prevents events from firing
    
• You can bind to Global State Data through Actions
  
  • Like SPID
    
• Predicates can store state
  
  • You want only to process a event when it is fired the 5th time
    
• Actions are accessing Global State Data
  
• Actions are executed after a predicate is evaluated
  
  • When an event will not be fired, the action will be not executed
    
• Actions are executed synchronously
  
  • Can impact performance!
    
• By default the Ring Buffer Target uses 4 MB
  
  • ring_buffer target (DMV) can only return 2 MB, therefore mailformed XML can be returned
    
  • Limit therefore the Ring Buffer Target to 2 MB
    
• Denali only provides an asynchronous Bucketizer
  
  • Synchronous should not be used anymore in SQL Server 2008 (R2)
    
• Event Counter Target can be used to check how often an event is fired
  
  • For planning more specific Event Sessions
    
  • With that information you can also redefine your predicates to reduce the events needed to process
    
• Event Pairing Target is used to match events
  
  • Bad example from BOL: Lock Acquired and Lock Released
    
    • Because of Lock Escalation, Lock Released is not fired anymore
      
  • Can be used for finding orphant transactions
    
• Predicates are working on map keys, and not map values
  
• Whitepaper “Using SQL Server 2008 Extended Events” by Jonathan Kehayias
  
  • http://msdn.microsoft.com/en-us/library/dd822788(SQL.100).aspx
    
    
• Shred XML data in a table and then do analysis from the table
  
  • It’s much faster
    
• Event Sessions are isolated from each other
  
• It takes 2 microseconds to fire an event, everything else is done asynchronously
  
• Dispatch of async targets occurs under 2 conditions
  
  • The memory buffer becomes full
    
  • Dispatch Latency exceeds
    
• sys.dm_xe_packages.capabilities is a bitmap mask
  
• Extended Events uses Multi Page Allocations outside of the Buffer Pool
  
  • On x32 this is VAS address space
    
  • This can lead to allocation problems
    
• Metadata file in depricated in Denali (Async File Target)
  
• greater_than_max_float64
  
• less_than_min_float64
  
• NETWORK_IO = ASYNC_NETWORK_IO in sys.dm_os_wait_stats
  
• MSSQL\Install.u_tables.sql
  
  • Contains the System Health Event Session
    
• Events can’t be changed
  
  • They must be dropped from the Event Sessions and added with the changed definition
    
• Actions are executed syncronously
  
• Causality tracking should be used for event correlation
  
  • Don’t add actions for event correlation
    
• Memory Dumps are placed into the LOG folder
  
• Event Sesions can be automatically started when SQL Server is started
  
  • STARTUP_STATE
    
• asynchronous_file_target can be read outside of SQL Server in SQL Server Denai CTP3
  
• There can be only one ETW session on the entire Windows Server
  
• Troubleshooting Recompiles with Extended Events
  
  • In the first step trace the Recompiles on the server level
    
  • In the second step trace the Recompiles in the database, where the recompiles were highest
    
  • Use the Bucketizer Target for that
    
• sys.dm_xe_session_targets is the Ring Buffer target
  
• Parameter Sniffing can be troubleshooted through Extended Events
  
  • use greater_than_max_int64
    
  • Events gets only captured when the stored procedure execution get’s slower
    
    • When a “bad” parameter value was provided
      

Module 8: Resource Governor

• Enterprise Edition only!
  
• Can be used to limit MAXDOP on a more granular level
  
• MAXDOP
  
  • Workload Group MAXDOP 4, Server MAXDOP 1
    
    • You can do control MAXDOP through Query Hints from 1 to 4
      
    • But you can’t do anything more than MAXDOP > 4 through a query hint, because the workload group throtted it down to a maximum MAXDOP 4
      
• Resource Governor will override the MAXDOP query hint
  
  • Query hint
    
• It governs per scheduler
  
• Resource Governor can’t limit existing connections
  
• Connections can’t be migrated between workload groups
  
  • Connection must be reset, or disconnect/reconnect
    
• I/O, TempDb can’t be controlled through Resource Governor
  
• You can limit the number of active connections to a workload group/database
  
• CPU/IO affinity masks are depricated in Denali
  
• Throtteling is only done when there is contention
  
  • Otherwise CPU limit are ignored
    
• The internal pool can be changed
  
  • The classifier function and login triggers are running in the internal pool
    
• Classifier function must return a SYSNAME
  
  • It can’t be a SQLCLR function, it must be a T-SQL function
    
  • But you can call a SQLCLR inside the T-SQL function
    
• CPU for Backup Compression can be limited through Resource Governor
  
• Perfmon Counters
  
  • SQLServer:Resource Pool Stats
    
  • SQLServer:Workload Group Stats
    
• You can’t tell if a specific query was throttled
  
• Connections are grouped into workload groups/buckets
  
• Whitepaper on Resource Governor: http://msdn.microsoft.com/en-us/library/ee151608.aspx
  
  
• Parallel Index Rebuilds/Parallel DBCC CHECKDBs are Enterprise Edition only!
  
• You can only have up to 20 resource pools, 18 user defined
  
  • But the max doesn’t really make sense
    
• GROUP_MAX_REQUESTS
  
  • Limits the number of concurrent connections (0: unlimited connections)
    
  • Configured on the workload group
    
  • Perfmon Counters
    
    • Workload Group Stats: Active Requests
      
    • Workload Group Stats: Queued Requests
      
• Extended Events have a resource pool id as action
  
• Only for one SQL Server Instance!
  
  • Governing across SQL Server Instances doesn’t work
    

Module 9: Baselining & Benchmarking

• Memory Ballooned
  
  • Because of Balloon drivers
    
• Ring Buffer provides the CPU time in sqlservr.exe
  
• Memory pressure problems in the Buffer Pool
  
  • Low PLE
    
  • Low or no Free Pages
    
  • High Lazy Writes/sec
    
  • High Fee List Stalls/sec
    
• SQL Load Generator: http://sqlloadgenerator.codeplex.com
  
• SQL Trace
  
  • The traces itself filter out unrelevant events
    
  • That’s a big difference to Extended Events, where events are not fired, if they are not needed
    
  • When an event is registered inside the system, it is send to EVERY trace, and the trace itself has to filter out, if it is interested in the event, or not
    
• SQL Alerts can be used to automatically start a perfmon trace through logman.exe or to start a SQL Trace
  

Thanks for reading

-Klaus