SQLMCM Training – Day 15

The whole last 3 days I’ve spent almost every minute with reviewing the material from the last 2 weeks (around 38 hours in total…). Today Bob Beauchemin
joined us, and talks the whole week about SQL Server Development, Security, PowerShell, and some esoteric technologies like SQLCLR, Service Broker, and CDC/Change Tracking.

Don’t expect too much notes from this week, because I know a lot of those things already J

Module 1: Optimizing Procedural Code

• Implicit Conversation leads to Clustered Index Scan
  
  • Seen in the predicate of the Scan Operator in the Execution Plan
    
  • Unicode <> Non Unique Conversion
    
  • Entity Framework v1 needs Unicode for that reason in the database
    
• Constant Scan
  
  • One Column, One Row RowSet
    
• Index over a computed column leads to a persisted computed column
  
• Query Compilation
  
  • Phase 0
    
    • Initializes the Query Optimizer
      
    • Query cost <= 0.2 will take the plan for execution
      
  • Phase 1
    
  • Phase 2
    
    • Clock runs until timeout is occurred
      
      • See “Timeout” in Execution Plan
        
• Hash Join is used when you have no indexes/keys on the table
  
• When you have a key/index on at least one table, a nested loop join is used
  
• Each query operator is COM object
  
• Bitmap IN ROW optimization
  
• Correlated sub query implies Nested Loop operator
  
• Samples are used when Auto Create and Auto Update Statistics is used
  
  • Depends on the number of pages, around 20% – 30% are used
    
• Service Broker queues have no statistics
  
  • The queues are changing too frequently, so it doesn’t make sense to maintain statistics on them
    
• Density Vector returns the number of distinct rows
  
• Filtered Indexes/Filtered Statistics are rebuild based on ALL rows, not the filtered rows
  
• sys.dm_os_memory_cache_entries
  
  • original_cost
    
  • current_cost
    
• DBCC FLUSHPROCINDB(db_id)
  
• DBCC USEROPTIONS shows the SET and all the other session options
  
  • user_id = default schema id
    
• Disable SET ARITHABORT option in SSMS
  
  • Every client that connects to SQL Server has this option also disabled
    
  • If you try to find out, why a query is slow from an application server or other user, you will get within SSMS a new execution plan, because ARITABORT is different
    
  • Therefore the execution plan is compiled, and you get an execution plan for the current supplied parameters
    
  • Therefore you can’t reproduce a parameter sniffing problem within SSMS when ARITABORT is enabled!
    
• GROUP BY/HAVING clause
  
  • Query will never get parametrized
    
  • Also not, when FORCED parameterization is enabled on the database
    
• When you need FORCED parameterization on query level
  
  • Use Plan Guides
    
• Halloween Protection
  
  • Eager Spool is a Stop-And-Go Operator
    
  • Lazy Spool is a Pass-Through Operator
    
  • E.g. When you’re updating a CI in a table
    
• Table Valued Function
  
• Inline Statement Table Valued Function
  
  • Gets a Parse Tree (cacheobjtype), View (objtype) in sys.dm_exec_cached_plans
    
  • Get no record in sys.dm_exec_query_stats
    
  • You get only a record in sys.dm_exec_query_stats for the statement, that is calling the Inline Statement Table Valued Function
    
• Multiple Statement Table Valued Function
  
  • Gets a Compiled Plan (cacheobjtype), Proc(objtype) in sys.dm_exec_cached_plans
    

Thanks for reading

-Klaus