{"id":888,"date":"2009-04-23T21:19:00","date_gmt":"2009-04-23T21:19:00","guid":{"rendered":"\/blogs\/paul\/post\/Misconceptions-around-TF-1118.aspx"},"modified":"2017-04-13T09:54:10","modified_gmt":"2017-04-13T16:54:10","slug":"misconceptions-around-tf-1118","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/","title":{"rendered":"Misconceptions around TF 1118"},"content":{"rendered":"

[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer required and has no effect.]<\/strong><\/p>\n

There’s a lot of\u00a0confusion and misconceptions about trace flag 1118. This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion. <\/span><\/p>\n

There are multiple points of confusion, which I’ll address in turn. Then I’ll prove that the trace flag still works in all versions of SQL Server\u00a0up to and including SQL Server 2014.<\/span><\/p>\n

1)<\/strong> Why was the trace flag usually required in 2000?<\/strong> In SQL 2000, whenever a temp table is\u00a0created\u00a0in tempdb and a row inserted, an IAM page must be allocated and a single data page must be allocated. These two pages are both ‘single-page’ allocations, from a mixed extent (see <\/span><\/span>Inside The Storage Engine: Anatomy of an extent for more info<\/span><\/a>). This means that an SGAM allocation bitmap page must be accessed, and a PFS page must be accessed (see <\/span>Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps<\/span><\/a>\u00a0for more info).<\/span><\/p>\n

With lots of very small temp tables being created, this means the very first SGAM page and the very first PFS page in the data file are accessed\/changed by all the threads, leading to latch contention problems on these two pages. When the temp tables are deleted again, the various pages are deallocated, which again needs to access and change the PFS page, and potentially the SGAM page.<\/span><\/p>\n

There are two ways to alleviate this problem. Firstly, create multiple data files in tempdb – which splits the latch contention over multiple allocation bitmaps (from having allocations come from multiple files) and thus reduces the contention. The general rule of thumb was one tempdb data file for each processor core. Secondly, turn on TF1118, which makes the first 8 data pages in the temp table come from a dedicated extent. This means one extent is allocated from the GAM page, rather than 8 single pages (and potentially 8 accesses to the SGAM page). The pages within the extent are reserved and allocated singly from this extent, as needed. This also cuts down on contention and is\u00a0documented in <\/span>KB 328551<\/span><\/a>. <\/span><\/p>\n

2)<\/strong> What does reserved vs. allocated mean?<\/strong> When an extent is allocated to a table, the 8 pages in the extent are not immediately allocated as well. Allocating an extent means those 8 pages are reserved exclusively for subsequent allocation to that table. The pages are allocated individually as needed, but no other table can allocate them. This is why such extents are called ‘dedicated’ extents (see my blog post link above for more details). You can see the counters of reserved pages vs. allocated pages in the output from sp_spaceused. <\/span><\/span><\/p>\n

3)<\/strong> Why is tempdb allocation contention maybe not so\u00a0much in 2005-2014?<\/strong> In SQL Server 2005, my team changed the allocation system for tempdb to reduce the possibility of contention. There is now a cache of temp tables. When a new temp table is created on a cold system (just after startup) it uses the same mechanism as for SQL 2000. When it is dropped though, instead of all the pages being deallocated completely, one IAM page and one data page are left allocated, and the temp table is put into a special cache. Subsequent temp table creations will look in the cache to see if they can just grab a pre-created temp table ‘off the shelf’. If so, this avoids accessing the allocation bitmaps completely.\u00a0This can lead to a big drop in contention, as long as the temp tables can be cached, and the query plan being dropped from memory doesn’t cause the cached temp table to be removed. Paul\u00a0White (blog<\/a>|twitter<\/a>) blogged a really in-depth\u00a0explanation of the cache – see here<\/a>.\u00a0<\/span><\/span><\/p>\n

4)<\/strong> Does the trace flag still exist in 2005-2014?<\/strong>\u00a0Yes it does – KB 328551 clearly states: <\/span><\/span><\/p>\n

Note<\/strong> Trace flag -T1118<\/strong> is also available and supported in Microsoft SQL Server 2005 and SQL Server 2008. However, if you are running SQL Server 2005 or SQL Server 2008, you do not have to apply any hotfix. <\/span><\/span><\/p><\/blockquote>\n

Just to make extra-sure (as I’m always paranoid about saying absolutes), I checked with my good friend Ryan Stonecipher, who’s the dev lead for the team that owns allocation (and a bunch of other stuff, including DBCC). He confirmed the code is exactly the same in 2008 as it was in 2005. And I prove it to you below too. There have been no changes in 2008R2, 2012, or 2014 that would negate the need for trace flag 1118.<\/span><\/p>\n

[Edit 2012:] 4a) What is Paul’s recommendation for using trace flag 1118? <\/strong>Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on.<\/span><\/span><\/p>\n

5)<\/strong> And why is it still there in 2005-2014? <\/strong>It does the same thing in 2005-2014 as it did in 2000. If the temp table creation\/deletion\u00a0workload is high enough, you can still see latch contention, as the temp table cache won’t be enough to completely alleviate the need for creating actual new temp tables, rather than just being able to grab one ‘off the shelf’. In that case, using the trace flag to change to extent-based allocation (in *exactly* the same way as for 2000) can help, as can creating more tempdb data files.<\/span><\/span><\/p>\n

As far as data files go though, the number has changed. Instead of a 1-1 mapping between processor cores and tempdb data files (*IF* there’s latch contention), now you don’t need so many. [Edit 2012:] The advice I like to use now is that which Bob Ward from CSS gave out at PASS in 2011: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, start with 8 files and if there’s still latch contention, add in groups of 4 more files. This is documented Microsoft advice in KB article 2154845<\/a>.<\/strong><\/span><\/span><\/p>\n

6) Why does DBCC IND still show two pages, even with the\u00a0trace\u00a0flag\u00a0on? <\/strong>I’ve heard of some people being confused by the output of DBCC IND<\/span>\u00a0when the\u00a0trace flag\u00a0is turned on. Creating a single row temp table will only show two pages allocated in the DBCC output – one IAM page and one data page. Yes, that’s completely correct – as only two pages are allocated, but the data page comes from a dedicated extent, not a mixed extent. (IAM pages are *always* single-page allocations from mixed-extents).<\/span><\/span><\/p>\n

And now the\u00a0proof, on SQL 2014.<\/span><\/p>\n

SELECT @@VERSION;
\nGO <\/span><\/p>\n

Microsoft SQL Server 2014 – 12.0.4422.0 (X64)<\/span><\/p><\/blockquote>\n

First off, I’ll create a temp table without the trace flag enabled, and see what pages the table has allocated, by looking at the first IAM. I’ll use a temp table with an 8000+ byte row size, and insert two rows – so we have two data pages for clarity. <\/span><\/p>\n

DBCC TRACEOFF (1118, -1);<\/strong>
\nGO <\/span><\/p>\n

USE tempdb;
\nGO <\/span><\/p>\n

CREATE TABLE #temp (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT ‘a’);
\nGO
\nINSERT INTO #temp DEFAULT VALUES;
\nGO 2<\/span> <\/span><\/p><\/blockquote>\n

Now I’ll figure out what is the first IAM page, using my sp_AllocationMetadata<\/span> script (see <\/span>here<\/span><\/a> for the script and details), and dump it with DBCC PAGE<\/span> to see the single-page allocations it’s tracking, and which dedicated extents are allocated to the table:<\/span><\/p>\n

EXEC sp_AllocationMetadata ‘#temp’;
\nGO <\/span><\/p>\n

Object Name\u00a0\u00a0\u00a0\u00a0Index ID\u00a0 Alloc Unit ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Alloc Unit Type\u00a0\u00a0First Page\u00a0\u00a0Root Page\u00a0\u00a0First IAM Page
\n————–\u00a0——— ——————– —————- ———– ———- —————
\n#temp__<snip>\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1441151881544663040\u00a0 IN_ROW_DATA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(1:289)\u00a0\u00a0\u00a0\u00a0\u00a0(0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(9:10)<\/span><\/p>\n

DBCC TRACEON (3604);
\nGO
\nDBCC PAGE (‘tempdb’, 9, 10, 3);
\nGO <\/span><\/p>\n

<snip><\/span><\/p><\/blockquote>\n

IAM: Single Page Allocations @0x000000001447A08E<\/span><\/p>\n

Slot 0 = (1:289)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 1 = (8:16)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 2 = (0:0)
\nSlot 3 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 4 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 5 = (0:0)
\nSlot 6 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 7 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>
\nIAM: Extent Alloc Status Slot 1 @0x000000001447A0C2<\/span><\/p>\n

(1:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 – (1:3192)\u00a0\u00a0\u00a0\u00a0 = NOT ALLOCATED<\/span>\u00a0\u00a0\u00a0 <\/span><\/p><\/blockquote>\n

As you can clearly see from the partial output of the dump of the IAM page, there are two single-page allocations and no extents allocated to the temp table. This is what should happen when the trace flag is not enabled.<\/span><\/p>\n

Now I’ll do the same thing with the trace flag 1118 enabled. <\/span><\/p>\n

USE tempdb;
\nGO <\/span><\/p>\n

DROP TABLE #temp;
\nGO <\/span><\/p>\n

DBCC TRACEON (1118, -1);
\n<\/strong>GO <\/span><\/p>\n

CREATE TABLE #temp (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT ‘a’);
\nGO
\nINSERT INTO #temp DEFAULT VALUES;
\nGO 2 <\/span><\/p>\n

EXEC sp_AllocationMetadata ‘#temp’;
\nGO <\/span><\/p>\n

Object Name\u00a0\u00a0\u00a0\u00a0Index ID\u00a0\u00a0Alloc Unit ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Alloc Unit Type\u00a0 First Page\u00a0\u00a0Root Page\u00a0\u00a0First IAM Page
\n————– ——— ——————– —————- ———– ———- —————
\n#temp__<snip>\u00a0 0 \u00a0 \u00a0 \u00a0 \u00a0 1513209475623550976 \u00a0IN_ROW_DATA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(8:96)\u00a0\u00a0\u00a0\u00a0\u00a0(0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(7:11) <\/span><\/p>\n

DBCC TRACEON (3604);
\nGO
\nDBCC PAGE (‘tempdb’, 7, 11, 3);
\nGO <\/span><\/p>\n

<snip><\/span><\/p>\n

IAM: Single Page Allocations @0x000000001A78A08E<\/span><\/p>\n

Slot 0 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 1 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 2 = (0:0)
\nSlot 3 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 4 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 5 = (0:0)
\nSlot 6 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 7 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>
\nIAM: Extent Alloc Status Slot 1 @0x000000001A78A0C2<\/span><\/p>\n

(8:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 – (8:88)\u00a0\u00a0\u00a0\u00a0\u00a0 = NOT ALLOCATED
\n(8:96)\u00a0\u00a0\u00a0\u00a0\u00a0 –\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 ALLOCATED
\n(8:104)\u00a0\u00a0\u00a0\u00a0\u00a0 – (8:3192)\u00a0\u00a0\u00a0\u00a0 = NOT ALLOCATED<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/p><\/blockquote>\n

Now as you can clearly see, there are no single-page allocations, and there’s a single extent allocated to the table. Proof that trace flag 1118 still does exactly what it should in SQL Server 2014. <\/span><\/p>\n

Now for a DBCC IND<\/span> on the table:\u00a0<\/span><\/p>\n

DBCC IND (‘tempdb’, ‘#temp’, -1);
\nGO
\n<\/span><\/p>\n

PageFID PagePID\u00a0\u00a0\u00a0\u00a0 IAMFID IAMPID\u00a0\u00a0\u00a0\u00a0\u00a0 ObjectID\u00a0\u00a0\u00a0 IndexID
\n——- ———– —— ———– ———– ———–
\n7\u00a0 \u00a0 \u00a0 \u00a011\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 NULL\u00a0\u00a0 NULL \u00a0 \u00a0 \u00a0 \u00a0-1354579385 0
\n8\u00a0 \u00a0 \u00a0 \u00a096 \u00a0 \u00a0 \u00a0 \u00a0 \u00a07\u00a0 \u00a0 \u00a0 11 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0-1354579385 0
\n8\u00a0 \u00a0 \u00a0 \u00a097\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 7\u00a0 \u00a0 \u00a0 11 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0-1354579385 0
\n<\/span><\/p><\/blockquote>\n

(I’ve removed some of the trailing columns for clarity.) We\u00a0see that it still only lists the two data pages (8:96, 8:97) and the IAM page (7:11) – although an entire extent was allocated to the temp table, only two pages from the extent were actually allocated and used – the rest are reserved for use by that table, but remain unallocated.\u00a0<\/span><\/p>\n

Hopefully this post has cleared up a lot of the confusion around this trace flag and what it does.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,48,61,62,66,86,93,96],"tags":[],"class_list":["post-888","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-inside-the-storage-engine","category-misconceptions","category-on-disk-structures","category-performance-tuning","category-sql-server-2008","category-tempdb","category-trace-flags"],"yoast_head":"\nMisconceptions around TF 1118 - 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\/misconceptions-around-tf-1118\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Misconceptions around TF 1118 - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-04-23T21:19:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:54:10+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=\"8 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\/misconceptions-around-tf-1118\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/\",\"name\":\"Misconceptions around TF 1118 - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-04-23T21:19:00+00:00\",\"dateModified\":\"2017-04-13T16:54:10+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Misconceptions around TF 1118\"}]},{\"@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":"Misconceptions around TF 1118 - 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\/misconceptions-around-tf-1118\/","og_locale":"en_US","og_type":"article","og_title":"Misconceptions around TF 1118 - Paul S. Randal","og_description":"[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/","og_site_name":"Paul S. Randal","article_published_time":"2009-04-23T21:19:00+00:00","article_modified_time":"2017-04-13T16:54:10+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/","name":"Misconceptions around TF 1118 - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-04-23T21:19:00+00:00","dateModified":"2017-04-13T16:54:10+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Misconceptions around TF 1118"}]},{"@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\/888","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=888"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/888\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=888"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=888"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=888"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}