{"id":527,"date":"2011-04-18T13:24:46","date_gmt":"2011-04-18T13:24:46","guid":{"rendered":"\/blogs\/jonathan\/post\/Looking-at-multiple-data-files-and-proportional-fill-with-Extended-Events.aspx"},"modified":"2017-04-13T12:18:21","modified_gmt":"2017-04-13T16:18:21","slug":"looking-at-multiple-data-files-and-proportional-fill-with-extended-events","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/looking-at-multiple-data-files-and-proportional-fill-with-extended-events\/","title":{"rendered":"Looking at multiple data files and proportional fill with Extended Events"},"content":{"rendered":"

At SQL Connections, I presented a session titled \u201cLearn SQL Server Internals with Extended Events\u201d where I demonstrated a number ways to use Extended Events to learn about the internal workings of the database engine for SQL Server.  The morning of the session I was chatting with someone about a problem they had seen and the topic of proportional fill came up and how the database engine stripes data across multiple files in a user database.  From this discussion, I got the idea to play around with multiple database files and built a demo using Extended Events that showed how proportional fill worked inside of the database engine.  This wasn\u2019t a planned demo for my presentation, and I had plenty of other demo\u2019s that showed various SQL Server Internals, but it became a really good demo that I decided to throw into the mix, which put me way over budget for time.  I decided to leave it up the audience which demo they wanted to see for the last demo of the session, an originally planned one, or the one I wrote that morning for proportional fill; the majority wanted to see the one on proportional fill and it turned out to be the best demo of the entire session based on the crowd interest and feedback.  What was most interesting to me was the number of people attending the session that had never heard of the concept of proportional fill with SQL Server.  Proportional fill is the algorithm used by SQL Server to determine how much information is written to each of the files in a multi-file filegroup based on the proportion of free space within each file; which allows the files to become full at approximately the same time.  Proportional fill has nothing to do with the actual file sizes, it is strictly based on the free space within a file.  <\/p>\n

To demonstrate proportional fill in SQL Server, we\u2019ll take a look at how the page writes and I\/O operations are distributed across varying configurations for a test database using the same test and event session for each configuration<\/p>\n

Basic Example<\/h1>\n

To look at how proportional fill functions, we\u2019ll start with a basic example, using a database with a separate filegroup for user objects that has been marked as the default filegroup for the database.  The UserObjects filegroup will have four data files, each 32MB in size.<\/p>\n

\n
\n
-- Create a multi-file database with evenly sized files<\/span><\/pre>\n
CREATE<\/span> DATABASE<\/span> [MultipleDataFiles] ON<\/span>  PRIMARY<\/span> <\/pre>\n
( NAME = N'MultipleDataFiles'<\/span>, <\/pre>\n
    FILENAME = N'H:\\SQLData\\MultipleDataFiles.mdf'<\/span> , <\/pre>\n
    SIZE<\/span> = 32768KB , <\/pre>\n
    FILEGROWTH = 32768KB ), <\/pre>\n
FILEGROUP [UserObjects] <\/pre>\n
( NAME = N'MultipleDataFiles_UserObjects1'<\/span>, <\/pre>\n
    FILENAME = N'H:\\SQLData\\MultipleDataFiles_UserObjects1.ndf'<\/span> , <\/pre>\n
    SIZE<\/span> = 32768KB , <\/pre>\n
    FILEGROWTH = 32768KB ), <\/pre>\n
( NAME = N'MultipleDataFiles_UserObjects2'<\/span>, <\/pre>\n
    FILENAME = N'H:\\SQLData\\MultipleDataFiles_UserObjects2.ndf'<\/span> , <\/pre>\n
    SIZE<\/span> = 32768KB , <\/pre>\n
    FILEGROWTH = 32768KB ), <\/pre>\n
( NAME = N'MultipleDataFiles_UserObjects3'<\/span>, <\/pre>\n
    FILENAME = N'H:\\SQLData\\MultipleDataFiles_UserObjects3.ndf'<\/span> , <\/pre>\n
    SIZE<\/span> = 32768KB , <\/pre>\n
    FILEGROWTH = 32768KB ), <\/pre>\n
( NAME = N'MultipleDataFiles_UserObjects4'<\/span>, <\/pre>\n
    FILENAME = N'H:\\SQLData\\MultipleDataFiles_UserObjects4.ndf'<\/span> , <\/pre>\n
    SIZE<\/span> = 32768KB , <\/pre>\n
    FILEGROWTH = 32768KB )<\/pre>\n
 LOG ON<\/span> <\/pre>\n
( NAME = N'MultipleDataFiles_log'<\/span>, <\/pre>\n
    FILENAME = N'L:\\SQLLogs\\MultipleDataFiles_log.ldf'<\/span> , <\/pre>\n
    SIZE<\/span> = 131072KB , <\/pre>\n
    FILEGROWTH = 32768KB )<\/pre>\n
GO<\/span><\/pre>\n
ALTER<\/span> DATABASE<\/span> [MultipleDataFiles] <\/pre>\n
MODIFY<\/span> FILEGROUP [UserObjects] DEFAULT<\/span><\/pre>\n
GO<\/pre>\n<\/p><\/div>\n