\n
\nSQL Server Hints\n----------------\nWhat are hints?\n\nSimply to influence query execution plans to retrieve data \nfaster.\n\nsyscacheobjects\n----------------\nSQL server after compiling a query creates the execution \nplan and keeps it on syscacheobjects object.\nYou can query the object to see the existing query plans as \nfollows:\n\nselect * from sys.syscacheobjects\nwhere cacheobjtype='compiled plan'\n\n\nHints\n-----\n\nWhen you specify hints in your queries the query execution \nplans are biased toward your hints rather than using default\n strategies.\n\nTypes of Hints\n--------------\nJoin Hints: Affects the joins [join types]\nQuery Hints: Affects overall execution plan\nTable: Affects to table access \n\n\nJoin Hints\n----------\nLoops: Uses nesting for join. Each row of the inner table is\n checked for condition satisfaction with the corresponding \nouter row.\nHash: One table is treated as a hash table and the other is \nscanned one row at a time and the hash function is used to \nfind equalities\n\nMerge: Each table is sorted first. Then they are compared \nrow by row [corresponding]\n\nRemote: In this case, at least one table is remote. The join\n is done at the right table side.\n\n\nExample:\n\nselect *\n-----\n----\n---\noption (merge join)\n\n\nor\n\nselect * \nfrom x\ninner merge join y on...\n\n\nQuery Hints:\n------------\nSpecified at the end of the query with keyword 'Option'\n\n\nselect * from x\n------------\n------------\noption (recompile, fast 80)\nHash Group or Order groups: Aggregations (group by, compute, distinct) are done by hashing or ordering\nConcat Union, Hash Union, Merge Union: All unions will be done with the specified union type\nLoop Join, Hash Join, Merge Join: Joins will be done with the specified join types\nFAST number_rows:\nForce Order:\nMaxDop:\nOptimize For:\nRecompile:\nKeep Plan:\nExpand Views:\n\n\n\nTable Hints:\n------------\nAffects how the table will be locked or which index will be \nused for the query.\nNoEXPAND\nINDEX\nfastfirstrow\nnowait\nrowlockpaglock\ntablock\nnolock\nholdlock\nignore_triggers\n\n\nPlan Guides\n-----------\nSometimes queries are originated from a third part application and you have no control to change the query.\nYou can use plan guides to influence such queries. For example: Using plan guides you can specify the nature of the\n select, update, insert queries.\nYou can use built in stored procedures to create or drop \nplan guides.\n\nsp_create_plan_guide\nsp_control_plan_guide\n<\/pre>\n<\/p>\nFrom: http:\/\/sitestree.com\/?p=4800
Categories:38
Tags:
Post Data:2007-12-13 01:33:56<\/p>\nShop Online: <a href='https:\/\/www.ShopForSoul.com\/' target='new' rel=\"noopener\">https:\/\/www.ShopForSoul.com\/<\/a>\n (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http:\/\/Training.SitesTree.com' target='new' rel=\"noopener\"> http:\/\/Training.SitesTree.com<\/a> \n In Bengali: <a href='http:\/\/Bangla.SaLearningSchool.com' target='new' rel=\"noopener\">http:\/\/Bangla.SaLearningSchool.com<\/a>\n <a href='http:\/\/SitesTree.com' target='new' rel=\"noopener\">http:\/\/SitesTree.com<\/a>\n 8112223 Canada Inc.\/JustEtc: <a href='http:\/\/JustEtc.net' target='new' rel=\"noopener\">http:\/\/JustEtc.net (Software\/Web\/Mobile\/Big-Data\/Machine Learning) <\/a>\n Shop Online: <a href='https:\/\/www.ShopForSoul.com'> https:\/\/www.ShopForSoul.com\/<\/a>\n Medium: <a href='https:\/\/medium.com\/@SayedAhmedCanada' target='new' rel=\"noopener\"> https:\/\/medium.com\/@SayedAhmedCanada <\/a>\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"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’ … <\/p>\n