{"id":886,"date":"2009-04-27T00:21:00","date_gmt":"2009-04-27T00:21:00","guid":{"rendered":"\/blogs\/paul\/post\/physical-database-layout-vs-database-size.aspx"},"modified":"2017-04-13T09:51:45","modified_gmt":"2017-04-13T16:51:45","slug":"physical-database-layout-vs-database-size","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/","title":{"rendered":"Physical database layout vs. database size"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/weekly-survey-does-size-really-matter-or-is-it-what-you-do-with-it\/\"><font face=\"verdana,geneva\" size=\"2\">here<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> for the survey). I let the survey run for a while to get a good sampling, and I wasn&#39;t disappointed, with over 1000 responses! Here are the results as of 4\/27\/2009. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\"><img fetchpriority=\"high\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg\" alt=\"\" width=\"576\" height=\"290\" \/> <img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout2.jpg\" alt=\"\" width=\"576\" height=\"306\" \/> <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout3.jpg\" alt=\"\" width=\"575\" height=\"305\" \/> <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout4.jpg\" alt=\"\" width=\"576\" height=\"290\" \/> <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Just like any other &#39;best practice&#39; kind of topic, the question of how to&nbsp;design the physical layout of a database&nbsp;provokes a lot of (sometimes heated) discussion. There are lots of options and there are even more factors to consider &#8211; so the best answer is my perennial favorite &quot;it depends&quot;! In this post, I don&#39;t want to tell you how I think you should layout your database &#8211; instead I want to discuss some of the options and let you make up your own mind,&nbsp;with the added benefit of data on what your peers are doing with their databases. The main point of this survey was to&nbsp;see what people are doing, rather than as a driver for an editorial blog post. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">As you can clearly see from the results above, and predictably, the distribution of layout types shifts as the database size increases &#8211; but I was very surprised by the number of single file databases over 10GB. Rather than go through each option in the survey, I&#39;m going to talk a bit about some of the things to consider when planning a layout. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\"><font size=\"2\"><strong>Underlying I\/O subsystem<\/strong> <\/font><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">This could be the most important factor to consider. If you only have a single physical drive, for instance, there&#39;s arguably not much point creating multiple data files, as that will force the disk heads to bounce back and forth to the different file locations on the disk. On the other hand, if you have a SAN with several thousand drives grouped together into multiple LUNs, your possibilities are a lot wider (and maybe much harder to come up with the optimal layout). Several people asked if I&#39;d go into depth around having multiple controllers, and different drive layouts in a SAN &#8211; and my answer is no. I&#39;m not an expert at storage design, which, like indexing, is both an art and a science. There&#39;s a good whitepaper that discusses some of this: <\/font><a href=\"https:\/\/technet.microsoft.com\/library\/Cc966414\"><font face=\"verdana,geneva\" size=\"2\">Physical Database Storage Design<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">, which I helped review back in my MS days. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\"><font size=\"2\"><strong>Performance, recoverability, manageability<\/strong> <\/font><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Having multiple files with separate storage for each allows reads and writes to be parallelized for increased performance, lowering the amount of disk head contention. When a checkpoint occurs, and pages are written to disk, spreading the I\/O load over multiples files can speed up the checkpoint and reduce the IOPS spikes that you may see. It can also lead to reduced contention for the various allocation bitmaps &#8211; in the same way as I&#39;ve described for tempdb. In user databases with a very high rate of allocations, contention can arise on the GAM pages &#8211; but it&#39;s not common. Some people also advocate having separate filegroups for tables and indexes, and although this can sometimes be more trouble than it&#39;s worth, and often turns into a religious debate, I have heard of people getting a perf boost from this. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">One of the most convincing reasons (I find) for having multiple filegroups is the ability to do much more targeted recovery. With a single file database, if it gets corrupted or lost, you have to restore the whole database, no matter how large the file is &#8211; and this can seriously affect your ability to recover within the RTO (Recovery Time Objective) agreement. By splitting the database into multiple filegroups, you can make use of partial database availability and online piecemeal restores (in Enterprise Edition) to allow the database to be online as soon as the primary filegroup is online, and then restore the remaining filegroups in priority order &#8211; bringing the application online as soon as the relevant filegroups are online. You can even use this layout to spread your backup workload &#8211; moving to filegroup-based backups instead of database backups, although this isn&#39;t very common. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">As far as manageability is concerned, there are a few reasons to have multiple filegroups. Firstly, you can isolate a table that requires a lot of I\/O (e.g. in terms of index maintenance) on separate storage from other tables, so that maintenance operations (and the I\/O overhead of doing them) doesn&#39;t interfere with the I\/O of the other tables. Also, you can provision different kinds of storage for different tables &#8211; in terms of disk speed and RAID level (redundancy), for instance. If you want to be able to move data around, you can do it much more easily if the database is split up, than if it&#39;s a single file. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\"><font size=\"2\"><strong>Summary<\/strong>&nbsp; <\/font><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Ok &#8211; so I lied. I *am* going to offer advice &#8211; <em>against<\/em> one of the options: single filegroup, single file. For smaller databases, this is fine &#8211; but as the database size gets larger, say, over tens of GB, then having a single file can become a serious liability. With a single file database (or even a single <em>filegroup<\/em> database), you lose most of the benefits mentioned above.<\/font>\n<\/p>\n<p>\n<font size=\"2\">Bottom line &#8211; as your databases get larger, you&#39;re going to need to think more carefully about their layout, otherwise you could run into big problems as your workload increases or when disaster strikes. As the survey results show, this is what your peers are doing.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Next post &#8211; this week&#39;s survey! <\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn&#39;t disappointed, with over 1000 responses! Here are [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,35,46,64,65,66,91],"tags":[],"class_list":["post-886","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-disaster-recovery","category-high-availability","category-partial-database-availability","category-partitioning","category-performance-tuning","category-surveys"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Physical database layout vs. database size - 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\/physical-database-layout-vs-database-size\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Physical database layout vs. database size - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn&#039;t disappointed, with over 1000 responses! Here are [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-04-27T00:21:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:51:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg\" \/>\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=\"5 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\/physical-database-layout-vs-database-size\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/\",\"name\":\"Physical database layout vs. database size - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg\",\"datePublished\":\"2009-04-27T00:21:00+00:00\",\"dateModified\":\"2017-04-13T16:51:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Physical database layout vs. database size\"}]},{\"@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":"Physical database layout vs. database size - 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\/physical-database-layout-vs-database-size\/","og_locale":"en_US","og_type":"article","og_title":"Physical database layout vs. database size - Paul S. Randal","og_description":"A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn&#39;t disappointed, with over 1000 responses! Here are [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/","og_site_name":"Paul S. Randal","article_published_time":"2009-04-27T00:21:00+00:00","article_modified_time":"2017-04-13T16:51:45+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/","name":"Physical database layout vs. database size - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg","datePublished":"2009-04-27T00:21:00+00:00","dateModified":"2017-04-13T16:51:45+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/4\/disklayout1.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/physical-database-layout-vs-database-size\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Physical database layout vs. database size"}]},{"@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\/886","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=886"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/886\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=886"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=886"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}