11/20/2022 0 Comments Tsql option recompile![]() The reason SQL Server caches query plans is to reduce latency on repeated runs of the same query, when possible, by not having to recompile and re-decide on what execution plan to use to serve your queries. In fact it'll only slow your queries down, possibly by an insignificant amount of time, but perhaps not when you have the same query recompiling over and over at a high frequency. Until you determine you have an otherwise unsolvable parameter sniffing issue with your Dynamic SQL, then there's no need to use the OPTION (RECOMPILE) query hint. OPTION (RECOMPILE) (and RECOMPILE hints) is useful in edge case parameter sniffing issues where there's no alternative fix, as discussed in this Kendra Little article. In other words, don't proactively use them, only reactively use them after you've measurably determined your query's use case will benefit from them. Now that I got that out of the way, □, there's no need to use query hints until there is a need to use them. Thank you beforehand for any tips and recommendations. At most it will be dozens of objects, in average it should be around 10 (this should loosely corelate with the 1/4 "high-frequency" queries) At minimum 2 objects (tables, views, joins etc.) will come into play in every query.There is also the question of input variables that come into play. joining the same tables etc.) as the one before it. Even if a query should be "composed" basically the same way (ie.The rest will be used sporadically, might be a few times per day, might be a few times over a week.Let's say several hundred times a day (that's several hundred executions of 1 specific query per day). About 1/4 of them will be used repeatedly with "higher-frequency".There will be hundreds (maybe even thousands) of different dynamic queries created.I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries? ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |