By: Updated: 2016-07-06 Related: ProblemHave you ever thought about how SQL Server stores data in its data files?As you know, data in tables is stored in row and column format at the logical level,but physically it stores data in data pages which are allocated from the data files ofthe database.In this tip I will show how pages are allocated to data files andwhat happens when there are multiple data files for a SQL Server database. SolutionEvery SQL Server database has at least two operating system files: a data file and a logfile. Data files can be of two types: Primary or Secondary.
The Primary data file contains startupinformation for the database and points to other files in the database. Userdata and objects can be stored in this file and every databasehas one primary data file.
Sql Server Page Files Sizes
Secondary data files are optional andcan be used to spread data across multiple files/disks by putting each file on a differentdisk drive. SQL Server databases can have multiple data and log files, but only oneprimary data file. Above these operating system files, there are Filegroups. Filegroupswork as a logical container for the data files and a filegroup can have multiple datafiles.The disk space allocated to a data file is logically divided into pages whichis the fundamental unit of data storage in SQL Server. A database page is an 8 KBchunk of data. When you insert any data into a SQL Server database, it saves thedata to a series of 8 KB pages inside the data file. If multiple data filesexist within a filegroup, SQL Server allocates pages to all data files basedon a round-robin mechanism.
So if we insert data into a table, SQL Server allocatespages first to data file 1, then allocates to data file 2, and so on, thenback to data file 1 again. SQL Server achieves this by an algorithm known as ProportionalFill.The proportional fill algorithm is used when allocating pages, so all data files allocate space around the same time. This algorithm determines the amount of informationthat should be written to each of the data files in a multi-file filegroup based on theproportion of free space within each file, which allows the files to become fullat approximately the same time. Proportional fill works based on the free spacewithin a file. Analyzing How SQL Server Data is StoredStep 1: First we will create a database named 'Manvendra' with three datafiles (1 primary and 2 secondary data files) and one log file by running the below T-SQLcode. You can change the name of the database, file path, file names, size and filegrowth according to your needs.
USE ManvendraGODBCC showfilestatsWith this command we can see the number of Extents for each data file. As youmay know, the size of each data page is 8KB and eight continuous pages equalsone extent, so the size ofan extent would be approximately 64KB. We created each data file with a size of 5 MB, sothe totalnumber of available extents would be 80 which is shown in columnTotalExtents, we can get this by (5.1024)/64.UsedExtents is the number of extents allocated withdata. As I mentioned above, the primary data file includes system informationabout the database, so this is why this file has a higher number ofUsedExtents.
![Sql Sql](https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/media/create-database.png)
USE ManvendraGoSelect DBNAME AS DatabaseName, Name, fileid, physicalname,(size. 8.0/1024) as Size,((size. 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed'). 8.0/1024)) As FreeSpaceFrom sys.databasefilesYou can see the difference between the screenshot below and the abovescreenshot. Free space in each data file has been reduced and the same amount ofspace has been allocated from both of the secondary data files, because bothfiles have the same amount of free space and proportional fill works based onthe free space within a file. USE ManvendraGOINSERT INTO TestDATA DEFAULT VALUES;GO 10000Select DBNAME AS DatabaseName, Name, fileid, physicalname,(size.
![File File](https://vmexpo.files.wordpress.com/2015/09/sqldatadiskalignment-02.jpg)
8.0/1024) as Size,((size. 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed'). 8.0/1024)) As FreeSpaceFrom sys.databasefilesWe can see again both secondary data files have the same amount of free space andsimilar amount of space has been allocated from the primary data file as well.Thismeans SQL Server uses a proportional fill algorithm to fill data in to the datafiles. Monday, May 20, 2019 - 3:19:26 AM - Shreyas MoolyaThanks Manvendra for this post. I was new to MS SQL server 2017 and this helped me visualize how data gets stored inside the database.Also, it would be nice if you could understand and post an article on how python as a service interacts with the MySQL server as there are very less good posts to find good knowledge like the above.Friday, March 08, 2019 - 6:57:29 PM - KavishI have a VLDB (2TB) with 3 primary files. There is a one time activity that will update about 80 percent records in all of the tables.My question is?If I add 2 more files to the database before the update activity, will the update write the data to the new files i.e.
Will data be taken out of the 3 files and use the proportional fill algorithm to spread the data across all five filesORThe update only update the exisiting page and the new files will only be used if the updated data is more than the existing data and it needs new pages to write to?Sunday, March 04, 2018 - 4:55:28 AM - TrungThanksFriday, January 26, 2018 - 9:10:46 PM - MelcHi,Just want to ask if you have 2tb database, then you add secondary file, will the size of the mdf will not grow anymore hence the ndf? I have a dillema right now because of our large db size.Thursday, October 12, 2017 - 8:18:00 AM - SuperchargedNext to database performance benefits another important reason may be valid for your environment.From recoverability point of view you may not prefer one large MDF-file.
Let's say you have a 2TB database all fysically located in one MDF-fie. Recovering this file (from your backup) may slow down your whole recovery process for following 2 reasons:1.
Sql Server Page File Size Recommendation
When file restore fails, you have to start all over again with this large file.2. No parallellism is used by SQL Server during restore operation having just one file. Having multiple files within your filegroup will enables parallell file restore by defaut enhancing you whole restore job.Just my 2cents, hope it helps.Thursday, May 25, 2017 - 4:27:09 PM - Eric in SacramentoVery simple, helpful, and informative, thank you!Thursday, September 01, 2016 - 12:26:25 PM - SepidehJust to let you know this top helped, Thank you!Monday, July 11, 2016 - 9:14:19 PM - TomThanks Greg and Manvendra. That makes sense.Monday, July 11, 2016 - 8:46:59 AM - Greg RobidouxHi Tom,to add to Manvendra's response, here are some reasons why you would create additional data files:.
Page File For Sql Server
the current disk is out of space, so you create a new data file on a different disk drive. performance reasons, if you put the files on different drives you might be able to get an IO performance improvement-GregSunday, July 10, 2016 - 10:44:56 AM - ManvendraHi Tom,As I mentioned in 'Solution Section' about secondary files in above tip. One of the main reason to have multiple data files are as given ' Secondary data files are optional and can be used to spread data across multiple files/disks by putting each file on a different disk drive'Friday, July 08, 2016 - 8:52:15 AM - TomI am not a DBA and I am relatively new to the concepts of how data is stored.
Can you give a few real-life examples of why one might want to have more than one file (.NDF)?