{"id":1095,"date":"2008-01-29T02:10:21","date_gmt":"2008-01-29T02:10:21","guid":{"rendered":"\/blogs\/paul\/post\/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx"},"modified":"2017-04-13T09:52:03","modified_gmt":"2017-04-13T16:52:03","slug":"search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/","title":{"rendered":"Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?"},"content":{"rendered":"

There’s been a very interesting discussion going on over at SQLServerCentral.com<\/a>\u00a0about whether to create multiple files for a user database because the server has multiple CPUs – see the thread here<\/a>. I wrote a couple of long responses during the thread that I wanted to duplicate here as I think it’s of broad interest.<\/p>\n

My first response was:<\/p>\n

Doesn’t make sense to split any database into multiple files for per-cpu performance, except tempdb which can suffer from multiple-CPUs all trying to modify the same allocation bitmaps under high load with small temp tables being created\/deleted (see http:\/\/www.microsoft.com\/technet\/prodtechnol\/sql\/2005\/workingwithtempdb.mspx<\/span><\/a> for details).<\/p>\n

Now, saying that, there’s an exception – and that’s when a non-tempdb database has the same allocation bitmap contention issues, but that only happens on the very high-end with thousands of single-row inserts per second happening on each CPU. It’s pretty rare. I’ve never seen it but Kimberly has.<\/p>\n

What you should be considering is splitting a database into filegroups to be able to isolate critical tables within separate filegroups, or splitting a partitioned table across multiple filegroups. For SS2005, these aren’t for performance but instead for manageability (e.g. being able to restore a single filegroup offline while the rest of the database remains online, or setting several partitions of a table to be read-only). For SS2008, there are improvements to query plan generation over multiple partitions that could allow for better parallelism, but its still not optimal compared to non-partitioned data.<\/p>\n

So – complex topic and these are simple guidelines. Hope they help.<\/p><\/blockquote>\n

This was followed by a discussion pointing out that various hardware vendors imply the opposite, as do several MS sources. So here was my latest response:<\/p>\n

\n

Hi folks,<\/p>\n

What’s really not made clear in the MS literature is that the multiple-files in a database really only applies to tempdb. Even on tempdb, on 2005 you don’t need one file per core – more like 1\/4 -1\/2 the number of files as there are cores.<\/p>\n

The tempdb problem is this – common workloads create and drop many worktables per second. The allocations that happen for a table are initially single-pages (as opposed to whole extents). This means that a search of the SGAM page for the 4GB interval needs to be done to find a mixed-extent with a free page to allocate. Multiple CPUs all hammering this page cause contention on it and performance problems. Then, a page needs to be allocated for the first IAM page – same thing happens. Then these pages need to be marked allocated in a PFS page – same thing happens. And then these pages need to inserted into the sysindexes row for the tabel – more contention. On 2000 this was particularly bad – so T1118 plus multiple files was the solution, where SQL Server would round-robin the single page allocations in the files in tempdb, alleviating the contention somewhat.<\/p>\n

In SQL Server 2005, we changed the temp table mechanism so that whenever a temp table is dropped, one data page, one IAM page, and the system table entries (no longer sysindexes, but instead is a ‘hidden’ table called sys.allocation_units) are cached. When a new temp table is allocated, if there’s a cached ‘template temp table’ it is picked up and used without so much contention on the various allocation bitmaps. On a heavily loaded system there can still be contention and so you still need multiple files for an SMP box, but just not so many. And you don’t need T1118 any more for user databases, but do still for tempdb.<\/p>\n

So – this is more prevalent on tempdb, but CAN happen on a user database under extreme load on monster hardware. Testing should show whether this is happening to you – if not, don’t create multiple files for performance.<\/p>\n

Now, in terms of what works for your particular vendors IO scalability – that’s beyond me and you may need to think about that if its something they recommend. However – I’d still take it with a pinch of salt and do your own testing. See the whitepaper at http:\/\/www.microsoft.com\/technet\/prodtechnol\/sql\/bestpractice\/pdpliobp.mspx<\/span><\/a> for some testing\/banchmarking info.<\/p>\n

Hope this helps clarify a bit more – great discussion!<\/p>\n<\/blockquote>\n

I’m interested to hear any other opinions on this – especially cases where you’ve found it necessary to create multiple files for performance.<\/p>\n

Thanks!<\/p>\n","protected":false},"excerpt":{"rendered":"

There’s been a very interesting discussion going on over at SQLServerCentral.com\u00a0about whether to create multiple files for a user database because the server has multiple CPUs – see the thread here. I wrote a couple of long responses during the thread that I wanted to duplicate here as I think it’s of broad interest. My […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[53,66,78],"tags":[],"class_list":["post-1095","post","type-post","status-publish","format-standard","hentry","category-io-subsystems","category-performance-tuning","category-search-engine-q-and-a"],"yoast_head":"\nSearch Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box? - 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\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"There’s been a very interesting discussion going on over at SQLServerCentral.com\u00a0about whether to create multiple files for a user database because the server has multiple CPUs – see the thread here. I wrote a couple of long responses during the thread that I wanted to duplicate here as I think it’s of broad interest. My […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-01-29T02:10:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:52:03+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=\"4 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\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/\",\"name\":\"Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-01-29T02:10:21+00:00\",\"dateModified\":\"2017-04-13T16:52:03+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?\"}]},{\"@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":"Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box? - 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\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/","og_locale":"en_US","og_type":"article","og_title":"Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box? - Paul S. Randal","og_description":"There’s been a very interesting discussion going on over at SQLServerCentral.com\u00a0about whether to create multiple files for a user database because the server has multiple CPUs – see the thread here. I wrote a couple of long responses during the thread that I wanted to duplicate here as I think it’s of broad interest. My […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/","og_site_name":"Paul S. Randal","article_published_time":"2008-01-29T02:10:21+00:00","article_modified_time":"2017-04-13T16:52:03+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/","name":"Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-01-29T02:10:21+00:00","dateModified":"2017-04-13T16:52:03+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-12-should-you-create-multiple-files-for-a-user-db-on-a-multi-core-box\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?"}]},{"@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\/1095","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=1095"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1095\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1095"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1095"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1095"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}