 |
Return Create A Forum - Home |
 |
--------------------------------------------------------- |
|
ExcelSoft Database Professionals |
 |
https://esdbp.createaforum.com |
|
--------------------------------------------------------- |
|
***************************************************** |
|
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 |
|
◾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 indicates 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 |
|
◾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 |
|
◾Query may be is slow from an application server or other |
|
user, compared to SSMS a new execution plan, because ARITABORT |
|
is different |
|
◾Therefore the execution plan is compiled, and execution |
|
plan created for the current supplied parameters |
|
◾Therefore parameter sniffing can�t be reproduced 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 updating a Clustered Index 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 |
|
◾One 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 |
|
***************************************************** |
