DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: General
       *****************************************************
       #Post#: 75--------------------------------------------------
       Optimizing Procedural Code
       By: srinivasma_exceldbp Date: June 11, 2014, 6:15 am
       ---------------------------------------------------------
       ◾Implicit Conversation leads to Clustered Index Scan
       ◾Seen in the predicate of the Scan Operator in the
       Execution Plan
       &#9726;Unicode <> Non Unique Conversion
       &#9726;Entity Framework v1 needs Unicode for that reason in the
       database
       &#9726;Constant Scan
       &#9726;One Column, One Row RowSet
       &#9726;Index over a computed column leads to a persisted
       computed column
       &#9726;Query Compilation
       &#9726;Phase 0
       &#9726;Initializes the Query Optimizer
       &#9726;Query cost <= 0.2 will take the plan for execution
       &#9726;Phase 1
       &#9726;Phase 2
       &#9726;Clock runs until timeout is occurred
       &#9726;See “Timeout” in Execution Plan
       &#9726;Hash Join is used when you have no indexes/keys on the
       table
       &#9726;When you have a key/index on at least one table, a nested
       loop join is used
       &#9726;Each query operator is COM object
       &#9726;Bitmap IN ROW optimization
       &#9726;Correlated sub query indicates Nested Loop operator
       &#9726;Samples are used when Auto Create and Auto Update
       Statistics is used
       &#9726;Depends on the number of pages, around 20% – 30% are used
       &#9726;Density Vector returns the number of distinct rows
       &#9726;Filtered Indexes/Filtered Statistics are rebuild based on
       ALL rows, not the filtered rows
       &#9726;sys.dm_os_memory_cache_entries
       &#9726;original_cost
       &#9726;current_cost
       &#9726;DBCC FLUSHPROCINDB(db_id)
       &#9726;DBCC USEROPTIONS shows the SET and all the other session
       options
       &#9726;user_id = default schema id
       &#9726;Disable SET ARITHABORT option in SSMS
       &#9726;Every client that connects to SQL Server has this option
       also disabled
       &#9726;Query may be is slow from an application server or other
       user, compared to  SSMS a new execution plan, because ARITABORT
       is different
       &#9726;Therefore the execution plan is compiled, and  execution
       plan created for the current supplied parameters
       &#9726;Therefore  parameter sniffing  can’t be reproduced within
       SSMS when ARITABORT is enabled!
       &#9726;GROUP BY/HAVING clause
       &#9726;Query will never get parametrized
       &#9726;Also not, when FORCED parameterization is enabled on the
       database
       &#9726;When you need FORCED parameterization on query level
       &#9726;Use Plan Guides
       &#9726;Halloween Protection
       &#9726;Eager Spool is a Stop-And-Go Operator
       &#9726;Lazy Spool is a Pass-Through Operator
       &#9726;E.g. When updating a Clustered Index  in a table
       &#9726;Table Valued Function
       &#9726;Inline Statement Table Valued Function
       &#9726;Gets a Parse Tree (cacheobjtype), View (objtype) in
       sys.dm_exec_cached_plans
       &#9726;Get no record in sys.dm_exec_query_stats
       &#9726;One record in sys.dm_exec_query_stats for the statement,
       that is calling the Inline Statement Table Valued Function
       &#9726;Multiple Statement Table Valued Function
       &#9726;Gets a Compiled Plan (cacheobjtype), Proc(objtype) in
       sys.dm_exec_cached_plans
       *****************************************************