{"id":1126,"date":"2020-01-28T22:14:00","date_gmt":"2020-01-28T12:14:00","guid":{"rendered":"http:\/\/www.moneystock.net\/wp_e\/?p=1126"},"modified":"2020-01-21T22:15:17","modified_gmt":"2020-01-21T12:15:17","slug":"sql-query-performance-tuning","status":"publish","type":"post","link":"https:\/\/moneystock.net\/wp_e\/2020\/01\/28\/sql-query-performance-tuning\/","title":{"rendered":"SQL query performance tuning"},"content":{"rendered":"\n<p><strong>Tuning quick tips<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Getting the entire record before apply condition. -> Solution: Apply condition and paging before select. <\/li><li>Using sub-query -> Solution: Turn it into join <\/li><li>Using Table Variable being used with large data -> Solution: use Temp Table<\/li><\/ol>\n\n\n\n<p>When a query performs badly, 3 things above are the most common causes I came across. After fixed with the solutions above, it performs at an acceptable speed even with a relatively complex query and large data volumes. <\/p>\n\n\n\n<p><strong>Tools<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/www.sentryone.com\/plan-explorer\">https:\/\/www.sentryone.com\/plan-explorer<\/a><\/p>\n\n\n\n<p>Sentryone plan explore is freeware and helps to figure out the bottleneck from a complex query. I don&#8217;t understand why the SSMS plan presents the execution plan so badly. If you are not happy with SSMS&#8217;s execution plan like me, I strongly recommend Sentry One plan explorer. <\/p>\n\n\n\n<p><strong>Tip<\/strong><\/p>\n\n\n\n<p>From SQL\n2014, Execution plan assumptions changed which leads to better performance.<\/p>\n\n\n\n<p>This is\nreason to upgrade SQL Server and it&#8217;s compatibility level to &gt; 120.<\/p>\n\n\n\n<p><strong>Tuning Questions<\/strong><\/p>\n\n\n\n<p>These are the questions worth we should ask when tuning an SQL query. <\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Stats missing?<\/li><li>Stats stale?<\/li><li>Sampling adequate?<\/li><li>Multi-column stat help?<\/li><li>Parameter sniffing?<\/li><li>Table variable\/MSTVF?<\/li><li>Only querying the most recent rows?<\/li><li>Date type conversion issue?<\/li><li>Comparing columns from the same table? <\/li><li>Remote data sources?<\/li><li>Predicates being buried in complexity?<\/li><li>Too much in a single query?<\/li><\/ul>\n\n\n\n<p><\/p>\n\n\n\n<p><strong>About Parameter sniffing<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/stackoverflow.com\/questions\/20864934\/option-recompile-is-always-faster-why\">https:\/\/stackoverflow.com\/questions\/20864934\/option-recompile-is-always-faster-why<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/stackoverflow.com\/questions\/40432794\/what-are-the-main-differences-between-optionoptimize-for-unknown-and-optionre\/40437749#40437749\">https:\/\/stackoverflow.com\/questions\/40432794\/what-are-the-main-differences-between-optionoptimize-for-unknown-and-optionre\/40437749#40437749<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Tuning quick tips Getting the entire record before apply condition. -> Solution: Apply condition and paging before select. Using sub-query -> Solution: Turn it into join Using Table Variable being used with large data -> Solution: use Temp Table When a query performs badly, 3 things above are the most common causes I came across.&hellip; <a class=\"more-link\" href=\"https:\/\/moneystock.net\/wp_e\/2020\/01\/28\/sql-query-performance-tuning\/\">Continue reading <span class=\"screen-reader-text\">SQL query performance tuning<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[128],"tags":[143,563,562,567],"class_list":["post-1126","post","type-post","status-publish","format-standard","hentry","category-db","tag-mssql","tag-query","tag-sql","tag-tuning","entry"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/posts\/1126","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/comments?post=1126"}],"version-history":[{"count":2,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/posts\/1126\/revisions"}],"predecessor-version":[{"id":1128,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/posts\/1126\/revisions\/1128"}],"wp:attachment":[{"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/media?parent=1126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/categories?post=1126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/tags?post=1126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}