SQL Server Hints ---------------- What are hints? Simply to influence query execution plans to retrieve data faster. syscacheobjects ---------------- SQL server after compiling a query creates the execution plan and keeps it on syscacheobjects object. You can query the object to see the existing query plans as follows: select * from sys.syscacheobjects where cacheobjtype='compiled plan' Hints ----- When you specify hints in your queries the query execution plans are biased toward your hints rather than using default strategies. Types of Hints -------------- Join Hints: Affects the joins [join types] Query Hints: Affects overall execution plan Table: Affects to table access Join Hints ---------- Loops: Uses nesting for join. Each row of the inner table is checked for condition satisfaction with the corresponding outer row. Hash: One table is treated as a hash table and the other is scanned one row at a time and the hash function is used to find equalities Merge: Each table is sorted first. Then they are compared row by row [corresponding] Remote: In this case, at least one table is remote. The join is done at the right table side. Example: select * ----- ---- --- option (merge join) or select * from x inner merge join y on... Query Hints: ------------ Specified at the end of the query with keyword 'Option' select * from x ------------ ------------ option (recompile, fast 80) Hash Group or Order groups: Aggregations (group by, compute, distinct) are done by hashing or ordering Concat Union, Hash Union, Merge Union: All unions will be done with the specified union type Loop Join, Hash Join, Merge Join: Joins will be done with the specified join types FAST number_rows: Force Order: MaxDop: Optimize For: Recompile: Keep Plan: Expand Views: Table Hints: ------------ Affects how the table will be locked or which index will be used for the query. NoEXPAND INDEX fastfirstrow nowait rowlockpaglock tablock nolock holdlock ignore_triggers Plan Guides ----------- Sometimes queries are originated from a third part application and you have no control to change the query. You can use plan guides to influence such queries. For example: Using plan guides you can specify the nature of the select, update, insert queries. You can use built in stored procedures to create or drop plan guides. sp_create_plan_guide sp_control_plan_guide
From: http://sitestree.com/?p=4800
Categories:38
Tags:
Post Data:2007-12-13 01:33:56
Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
(Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a>
In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
<a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>
