SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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


No comments.

Leave a Comment

Please register or log in to leave a comment.