{"id":4160,"date":"2013-06-17T07:30:50","date_gmt":"2013-06-17T14:30:50","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4160"},"modified":"2017-04-13T09:54:28","modified_gmt":"2017-04-13T16:54:28","slug":"files-and-filegroups-survey-results","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/","title":{"rendered":"Files and filegroups survey results"},"content":{"rendered":"<p>Last month I kicked off a survey asking you to run some code to send me data on the size, number of files, and number of filegroups in your databases. I got back more than 17000 results, and I&#8217;m presenting a couple of ways of interpreting the data here. Interestingly, I only had a handful of results for databases above 100GB and for databases with more than 100 files, so to keep the graphs readable, I&#8217;ve chosen to exclude those.<\/p>\n<p><strong>Single Filegroup<\/strong><\/p>\n<p>First off, for databases with only a primary filegroup, how many files were there in that filegroup?<\/p>\n<ul>\n<li><span style=\"font-size: 13px; line-height: 19px;\">24 files: 3 databases<\/span><\/li>\n<li><span style=\"font-size: 13px; line-height: 19px;\">20 files: 1 database<\/span><\/li>\n<li>16 files: 3 databases<\/li>\n<li>15 files: 1 database<\/li>\n<li>12 files: 1 database<\/li>\n<li>10 files: 1 database<\/li>\n<li>8 \u00a0files: 10 databases<\/li>\n<li>7 files: 1 database<\/li>\n<li>6 files: 50 databases<\/li>\n<li>5 files: 6 databases<\/li>\n<li>4 files: 56 databases<\/li>\n<li>3 files: 27 databases<\/li>\n<li>2 files: 67 databases<\/li>\n<li><span style=\"text-decoration: underline;\"><em><strong>1 file: 16121 databases<\/strong><\/em><\/span><\/li>\n<\/ul>\n<p>Unsurprisingly, single file plus single filegroup is the most common physical layout. We see this over and over, regardless of the size of the database.<\/p>\n<p>As your databases start to increase in size, you need to consider splitting them up (note I&#8217;m not using the overloaded term &#8216;partitioning&#8217;) to allow you to do:<\/p>\n<ul>\n<li><span style=\"line-height: 12.997159004211426px;\">Targeted restores in the event of wholesale data loss, helping to reduce downtime. If you have multiple filegroups, you can potentially do a partial restore of only the data required to get the OLTP portion of your workload up and running, restoring the rest of the filegroups later.<\/span><\/li>\n<li>Targeted maintenance to reduce the time and resources necessary to manage fragmentation. If your indexes are split of multiple filegroups (using partitioning) you can rebuild or reorganize just the index portion that has fragmentation.<\/li>\n<li>Targeted performance management. If your workload uses\/affects multiple portions of \u00a0your database, it may be beneficial to place those different portions of the database on different sections of your I\/O subsystem.<\/li>\n<\/ul>\n<p>None of these things can be done with a single file plus single filegroup database.<\/p>\n<p><strong>Data Files vs. Filegroups<\/strong><\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg\"><img fetchpriority=\"high\" decoding=\"async\" class=\"alignnone size-full wp-image-4162\" alt=\"FilesFilegroups\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg\" width=\"900\" height=\"295\" \/><\/a><\/p>\n<p>This is really interesting. The majority of databases that had more than one filegroup had the number of data files equal to the number of filegroups. I was prepared for this, with quite a few respondents pointing this out in their data and explaining that the database is from a third-party vendor application.<\/p>\n<p>As a general rule of thumb, I recommend each filegroup having 2-4 data files, as this will give overall better I\/O performance than a single data file. I&#8217;ve seen this over and over on client systems and you can see quite a few data points on the graph above reflecting that too. I also have some empirical evidence from various performance tests I&#8217;ve done (narrow scenarios, but definite proof-points):<\/p>\n<ul>\n<li><span style=\"line-height: 12.997159004211426px;\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/benchmarking-multiple-data-files-on-ssds-plus-the-latest-fusion-io-driver\/\" target=\"_blank\">Benchmarking: Multiple data files on SSDs<\/a><br \/>\n<\/span><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/benchmarking-do-multiple-data-files-make-a-difference\/\" target=\"_blank\">Benchmarking: do multiple data files make a difference?<\/a><\/li>\n<\/ul>\n<p>Just to be clear, based on comments, this is because of parallelism at the I\/O subsystem level (not one thread per data file, as that&#8217;s a myth, but being able to write to multiple points on the array during checkpoints), and this does not apply if you only have a single physical drive on which to put your data (and I don&#8217;t mean what Windows sees as a single drive letter, I really mean just one actual drive). In fact, if you only have a single drive, you have other problems because that gives you no redundancy &#8211; you&#8217;re living on a knife-edge.<\/p>\n<p>Note that I didn&#8217;t include tempdb in this survey. Tempdb is a whole different kettle of fish (excellent British phrase!), where multiple data files can be required to alleviate in-memory contention for allocation bitmaps (classic PAGELATCH_UP\/EX contention). For tempdb guidelines see:\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/tempdb-configuration-survey-results-and-advice\/\" target=\"_blank\">Tempdb configuration survey results and advice<\/a>.<\/p>\n<p><strong>Data Files vs. Database Size<\/strong><\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesdbsize.jpg\"><img decoding=\"async\" class=\"alignnone size-full wp-image-4161\" alt=\"FilesDBSize\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesdbsize.jpg\" width=\"898\" height=\"295\" \/><\/a><\/p>\n<p>This is also really interesting. Without knowing what these databases are being used for, it seems that many of these databases have too many files for the size of the database (my gut feel, based on experience). My guess is that the file\/filegroup layout was chosen based on rules that don&#8217;t equate to the normal reasons for having multiple file\/filegroups, as I described above. What&#8217;s particularly surprising is the number of database less than 1-GB in size that have many, many data files.<\/p>\n<p><strong>Summary<\/strong><\/p>\n<p>So how many data files and filegroups should you have? There&#8217;s no right answer, and the sweet spot is going to vary for all of you.<\/p>\n<p>Some general rules of thumb:<\/p>\n<ul>\n<li><span style=\"font-size: 13px; line-height: 19px;\">As your databases get larger, it becomes more likely that you&#8217;re going to need multiple files and filegroups<\/span><\/li>\n<li><span style=\"line-height: 12.997159004211426px;\">Multiple filegroups give you enhanced possibilities for targeted disaster recovery, easier manageability, and I\/O subsystem placement<\/span><\/li>\n<li>Each filegroup should have 2-4 files at least, with tempdb being a special case<\/li>\n<\/ul>\n<p><span style=\"font-size: 13px; line-height: 19px;\">Whatever you do, don&#8217;t forget to test to figure out the optimal configuration for your workload.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last month I kicked off a survey asking you to run some code to send me data on the size, number of files, and number of filegroups in your databases. I got back more than 17000 results, and I&#8217;m presenting a couple of ways of interpreting the data here. Interestingly, I only had a handful [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[44],"tags":[],"class_list":["post-4160","post","type-post","status-publish","format-standard","hentry","category-general"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Files and filegroups survey results - 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\/files-and-filegroups-survey-results\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Files and filegroups survey results - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Last month I kicked off a survey asking you to run some code to send me data on the size, number of files, and number of filegroups in your databases. I got back more than 17000 results, and I&#8217;m presenting a couple of ways of interpreting the data here. Interestingly, I only had a handful [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2013-06-17T14:30:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:54:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.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=\"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\/files-and-filegroups-survey-results\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/\",\"name\":\"Files and filegroups survey results - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg\",\"datePublished\":\"2013-06-17T14:30:50+00:00\",\"dateModified\":\"2017-04-13T16:54:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Files and filegroups survey results\"}]},{\"@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":"Files and filegroups survey results - 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\/files-and-filegroups-survey-results\/","og_locale":"en_US","og_type":"article","og_title":"Files and filegroups survey results - Paul S. Randal","og_description":"Last month I kicked off a survey asking you to run some code to send me data on the size, number of files, and number of filegroups in your databases. I got back more than 17000 results, and I&#8217;m presenting a couple of ways of interpreting the data here. Interestingly, I only had a handful [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/","og_site_name":"Paul S. Randal","article_published_time":"2013-06-17T14:30:50+00:00","article_modified_time":"2017-04-13T16:54:28+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg","type":"","width":"","height":""}],"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\/files-and-filegroups-survey-results\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/","name":"Files and filegroups survey results - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg","datePublished":"2013-06-17T14:30:50+00:00","dateModified":"2017-04-13T16:54:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2013\/06\/filesfilegroups.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/files-and-filegroups-survey-results\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Files and filegroups survey results"}]},{"@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\/4160","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=4160"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4160\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}