{"id":4698,"date":"2017-03-13T09:21:40","date_gmt":"2017-03-13T16:21:40","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4698"},"modified":"2017-03-28T17:04:28","modified_gmt":"2017-03-29T00:04:28","slug":"sqlskills-sql101-temporary-table-misuse","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/","title":{"rendered":"SQLskills SQL101: Temporary table misuse"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>As <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/sqlskills-sql101-stored-procedures\/\" target=\"_blank\">Kimberly blogged about recently<\/a>, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our <strong>SQLskills SQL101<\/strong> blog posts, check out\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/SQL101\" target=\"_blank\">SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n<p style=\"text-align: justify;\">After I left Microsoft in 2007, one of the first clients I worked with (who\u00a0we&#8217;re still working with today) threw an interesting problem at me: <em>&#8220;We can&#8217;t run stored proc X any more because it causes tempdb to fill the drive and then it fails.&#8221;<\/em> Game on. I built some monitoring infrastructure into the proc using the DMV\u00a0<em>sys.dm_db_task_space_usage<\/em> to figure out how much tempdb space was being used at various points and find the problem area.<\/p>\n<p style=\"text-align: justify;\">It turned out to be problem *areas*, and in fact the proc was loaded with temporary table (I&#8217;ll just use the common contraction &#8216;temp table&#8217; from now on) misuse, illustrating all three of the common temp table problems I&#8217;m going to describe below. Once I fixed that proc, (reducing the tempdb usage from more than 60GB down to under 1GB, and the run time from many minutes to a few tens of seconds) I implemented some automated monitoring built around the <em>sys.dm_db_task_space_usage<\/em> DMV to identify procedures and ad hoc queries that were misusing temp tables. We&#8217;ve since used this monitoring at many other clients to identify temp table misuse.<\/p>\n<p style=\"text-align: justify;\">In this post I\u2019d like to describe the three main ways that temp table are misused:<\/p>\n<ul>\n<li>Over-population of temp tables<\/li>\n<li>Incorrect indexing on temp tables<\/li>\n<li>Using a temp table where none are required<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Don\u2019t get me wrong though \u2013 temp tables are great \u2013 when they\u2019re used efficiently.<\/p>\n<p style=\"text-align: justify;\"><strong>Over-Population of a Temp Table<\/strong><\/p>\n<p style=\"text-align: justify;\">This problem involves creating a temp table using something like a <em>SELECT \u2026 INTO #temptable<\/em> construct and pulling far more data into the temp table than is necessary.<\/p>\n<p style=\"text-align: justify;\">The most common thing we see is pulling lots of user table columns into the temp table, where some of the columns are not used ever again in subsequent code. This is a HUGE waste of I\/O and CPU resources (extracting the columns from the user table in the first place &#8211; and imagine the extra CPU involved if the source data is compressed!) and a big waste of tempdb space (storing the columns in the temp table). I\u2019ve seen code pulling large <em>varchar<\/em> columns\u00a0into a temp table that aren&#8217;t used, and with multi-million row datasets\u2026<\/p>\n<p style=\"text-align: justify;\">The other facet of over-population of temp tables is pulling in too many rows. For instance, if your code is interested in what happened over the last 12 months, you don\u2019t need to pull in all the data from the last ten years. Not only will it be bloating the temp table, it will also drastically slow down the query operations. This was one of the biggest problems in the client scenario I described above.<\/p>\n<p style=\"text-align: justify;\">The key to better performance is making sure your selection\/projection is as focused as possible. To limit your selection, use an effective <em>WHERE<\/em> clause. To limit your projection, list only the necessary columns in your select list.<\/p>\n<p style=\"text-align: justify;\"><strong>Incorrect Indexing on a Temp Table<\/strong><\/p>\n<p style=\"text-align: justify;\">This problem involves either creating indexes before populating the table (so that no statistics are generated) or creating a bunch of inappropriate indexes that are not used.<\/p>\n<p style=\"text-align: justify;\">The most common example we see is creating a single-column nonclustered index for each of the temp table columns. Those are usually just taking up space for no use whatsoever. Temp tables *DO* need indexes (preferably after load) but as with any form of query tuning \u2013 <em>only<\/em> the RIGHT indexes. Consider creating permanent tables that mimic what\u2019s going on in your temporary objects and then using the Database Tuning Advisor (DTA)\u00a0to see if it has recommendations. While DTA\u2019s not perfect, it\u2019s often WAY better than guessing. Kimberly has a great post in our Accidental DBA series that discusses indexing strategies &#8211; start <a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/the-accidental-dba-day-20-of-30-are-your-indexing-strategies-working-aka-indexing-dmvs\/\" target=\"_blank\">there<\/a>.<\/p>\n<p style=\"text-align: justify;\">Also, don&#8217;t create any nonclustered indexes until the temp table has been populated, otherwise they won&#8217;t have any statistics, which will slow down query performance, possibly drastically.<\/p>\n<p style=\"text-align: justify;\">Oh yes, and, don&#8217;t create a clustered index for the temp table before populating it unless you know that the data being entered is already sorted to exactly match the cluster key you&#8217;ve chosen. If not, inserts into the temp table are going to cause index fragmentation which will really slow down the time it takes to populate the temp table. If you know the data is sorted and you create the clustered index first, there&#8217;s still no guarantee that the Storage Engine will feed the data into the temp table in the right order, so be careful. And if you go that route, remember that you&#8217;ll need to update the statistics of the clustered index after the temp table creation.<\/p>\n<p style=\"text-align: justify;\">You need to be careful here because in some versions of SQL Server, changing the schema of a temp table in a stored proc can cause recompilation issues. Do some testing and pick the sequence of events that makes the most sense for performance in your situation.<\/p>\n<p style=\"text-align: justify;\"><strong>Using a Temp Table Where None is Required<\/strong><\/p>\n<p style=\"text-align: justify;\">The SQL Server Query Optimizer is a fabulous beast and is very good at figuring out the most efficient way to execute most queries. If you choose to take some of the query operation and pre-calculate it into a temp table, sometimes you\u2019re causing more harm than good. Any time you populate a temp table you\u2019re forcing SQL Server to materialize the complete set of results of whatever query you ran to populate the temp table. This can really limit SQL Server\u2019s ability to produce a pipeline of data flowing efficiently through a query plan and making use of parallelism and collapsing data flows when possible.<\/p>\n<p style=\"text-align: justify;\">While it\u2019s true that you might be able to do better than the optimizer sometimes, don\u2019t expect that it\u2019s the case all the time. Don\u2019t just go straight to using temp tables, give the optimizer a chance \u2013 and, make sure to retest your code\/expectations around Service Packs and hot fixes as these may have eliminated the need for temp tables as well.<\/p>\n<p style=\"text-align: justify;\">A good way to test whether a temp table is actually a hindrance to performance is to take the tempdb-creation code, embed it as a derived table in the main query, and see if query performance improves.<\/p>\n<p style=\"text-align: justify;\">It&#8217;s quite often the case that temp tables because an architectural standard in an environment when they proved useful long ago and now everyone used them, without ever checking if they&#8217;re *really* good for all cases.<\/p>\n<p style=\"text-align: justify;\">One other thing you can\u00a0consider\u00a0is replacing temp tables with In-Memory OLTP \u00a0memory-optimized tables, in all Editions of SQL Server 2016 SP1 and later, and in Enterprise Edition of SQL Server 2014. That&#8217;s beyond the scope of this post, but you can read about it in this <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/mt718711.aspx\" target=\"_blank\">Books Online page on MSDN<\/a>.<\/p>\n<p style=\"text-align: justify;\"><strong>Summary<\/strong><\/p>\n<p style=\"text-align: justify;\">Always try to follow these guidelines when using a temp table:<\/p>\n<ul>\n<li style=\"text-align: justify;\">Determine if a temp table is the most efficient way to achieve the goal of the code you&#8217;re writing<\/li>\n<li style=\"text-align: justify;\">Limit the number of columns being pulled into the temp table<\/li>\n<li style=\"text-align: justify;\">Limit the number of rows being pulled into the temp table<\/li>\n<li style=\"text-align: justify;\">Create appropriate indexes for the temp table<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Take a look at your current temp table usage. You may be surprised to find a lot of tempdb space and CPU resources being consumed by inappropriate temp table usage, population, and indexing.<\/p>\n<p style=\"text-align: justify;\">Hope you found this helpful!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[73,108,93],"tags":[],"class_list":["post-4698","post","type-post","status-publish","format-standard","hentry","category-query-tuning","category-sql101","category-tempdb"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: Temporary table misuse - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Temporary table misuse - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-13T16:21:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-03-29T00:04:28+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/\",\"name\":\"SQLskills SQL101: Temporary table misuse - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-03-13T16:21:40+00:00\",\"dateModified\":\"2017-03-29T00:04:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Temporary table misuse\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLskills SQL101: Temporary table misuse - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Temporary table misuse - Paul S. Randal","og_description":"As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/","og_site_name":"Paul S. Randal","article_published_time":"2017-03-13T16:21:40+00:00","article_modified_time":"2017-03-29T00:04:28+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/","name":"SQLskills SQL101: Temporary table misuse - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-03-13T16:21:40+00:00","dateModified":"2017-03-29T00:04:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-temporary-table-misuse\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Temporary table misuse"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4698","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4698"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4698\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4698"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4698"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4698"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}