THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

T-SQL Tuesday #040: Files, Filegroups and Visualizing Interleaved Objects

Early in my career as a DBA, I have to admit I didn’t quite “get” what all the fuss was about with the multiple file and multiple filegroup capability in SQL Server. Over the years, though, as I learned more about partitioning, backup strategies for large databases and, most importantly, storage internals I’ve started to catch on. For today’s T-SQL Tuesday, I thought I would share an early lesson, using the newly released SQL File Layout Viewer utility available here.

tsql2sday

Adam Machanic started T-SQL Tuesday several years ago and it’s been a great way to motivate bloggers and readers to share ideas around a topic. This month’s even is hosted by Jen McCown / MidnightDBA. An awesome idea, as ever.

What I hope to illustrate today is a simple but vital concept about files and file groups: files, on their own, use a proportional fill algorithm in SQL Server and will receive new data from any object that needs more space. If an object needs additional storage, an allocation will be made in an empty area of whichever file has the most space available. Under the multi-file feature alone, there is no attempt whatsoever to separate objects in the database logically into different files. The result is that objects can appear to “round robin” through the files as more space is allocated – it’s not really a round-robin algorithm, but if the files are all about equally full, it looks similar.

Filegroups, on the other hand, allow logically grouping objects in a way that specifically directs them into separate files, and so controls the physical layout of those objects on disk. Using filegroups it’s possible to direct an object into a file (or set of files) and physically separate it from the rest of the database.

Because of this distinction, it’s been my experience that filegroups are far and away the more useful of the two features, and if I have a database that merits splitting up, I go straight for filegroups, with one physical file per group. (There are some edge case exceptions like TempDB allocation contention that are outside the scope of this post.)

Interleaving

There are a few reasons we’d want to physically separate objects, but, because it so clearly illustrates the difference, I want to show interleaving today. I made a demo database using the following script:

USE [master]
GO

:setvar datapath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA"
:setvar logpath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA"

CREATE DATABASE [VizDemo3] ON  PRIMARY 
( 
    NAME = N'VizDemo3', FILENAME = N'$(datapath)\VizDemo3.mdf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
FILEGROUP [MultiFileFG] 
( 
    NAME = N'VizDemoMFGF1', FILENAME = N'$(datapath)\VizDemoMFGF1.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
( 
    NAME = N'VizDemoMFGF2', FILENAME = N'$(datapath)\VizDemoMFGF2.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
( 
    NAME = N'VizDemoMFGF3', FILENAME = N'$(datapath)\VizDemoMFGF3.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
FILEGROUP [SingleFileFG1] 
( 
    NAME = N'VizDemoSFG1', FILENAME = N'$(datapath)\VizDemoSFG1.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
FILEGROUP [SingleFileFG2] 
( 
    NAME = N'VizDemoSFG2', FILENAME = N'$(datapath)\VizDemoSFG2.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
)
LOG ON 
( 
    NAME = N'VizDemo3_log', FILENAME = N'$(logpath)\VizDemo3_log.ldf' , 
    SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB 
)
GO

ALTER DATABASE [VizDemo3] SET RECOVERY SIMPLE 
GO

USE VizDemo3
GO

CREATE TABLE dbo.SampleCustomers_on_Primary  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON [PRIMARY] ;
GO

CREATE TABLE dbo.SampleOrders_on_Primary  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON [PRIMARY] ;
GO

INSERT dbo.SampleCustomers_on_Primary DEFAULT VALUES;
INSERT dbo.SampleOrders_on_Primary DEFAULT VALUES;
GO 20000


CREATE TABLE dbo.SampleCustomers_on_Files  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON MultiFileFG ;
GO

CREATE TABLE dbo.SampleOrders_on_Files  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON MultiFileFG ;
GO

INSERT dbo.SampleCustomers_on_Files DEFAULT VALUES;
INSERT dbo.SampleOrders_on_Files DEFAULT VALUES;
GO 20000


CREATE TABLE dbo.SampleCustomers_on_FileGroups  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON SingleFileFG1 ;
GO

CREATE TABLE dbo.SampleOrders_on_FileGroups  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON SingleFileFG2 ;
GO

INSERT dbo.SampleCustomers_on_FileGroups DEFAULT VALUES;
INSERT dbo.SampleOrders_on_FileGroups DEFAULT VALUES;
GO 20000

This is a really simple example showing several possible file layouts, and what those do with the physical storage of the data. Starting from the top, note that the database create statement makes three different file setups:

  1. Our old friend Primary – one MDF file to rule them all!
  2. A file group with three physical files: MultiFileFG. These files will be populated with SQL Server’s proportional fill.
  3. Two file groups with one physical file apiece: SingleFileFG1 and SingleFileFG2. This allows the DBA to direct objects into specific files on disk.

Next I make some pairs of sample tables, and locate a pair in Primary, a pair in MultiFileFG, and a pair (one table each) in SingleFileFG1 and 2. The tables are each populated with 20,000 rows of sample data, arbitrarily called Customers and Orders.

It’s important to note that the tables are filled with data in an alternating fashion – one table gets a row, then the other table gets a row – and that means that the allocations for space for those objects will also alternate. One table grows, then the other, then the first again.

If the tables are in the same filegroup, even with separate physical files, the data will be interleaved. There will be a block of pages for one table allocated, then from the other table. Under default settings in SQL Server (not using the –E parameter, for example) the blocks of pages will each be a 64k extent of 8 data pages.

So, let’s have a look at what happens inside the files, and see if it supports this logic.

One File

InterleaveScreenCap1

My goodness it does! But you probably suspected that, as I’m the guy writing this post.

First let’s look at the blue area – the stack-of-bricks looking light blue and purple region is the Primary filegroup / MDF file. What we are seeing is the alternating, interleaved extents from the two tables, one in blue and one in lavender/purple. This isn’t terrible, but there are two things worth noting:

  1. There’s a decent amount of fragmentation. Not harmful, necessarily, but these two tables definitely are not contiguous. If we have an application that wants to do a lot of scans, and we have spinning media, we might have some performance degradation. Read-ahead probably doesn’t work. This might not be ideal for a data warehouse.
  2. If we re-index one of those objects the other one will remain full of “holes.” We could re-index both, but it might take a few passes before this interleaving works itself out.

I want to emphasize here that this isn’t necessarily a terrible situation, but it’s illuminating as far as how the data is physically written out.

Three Files in a Group

The next reasonable question to ask is whether making multiple files, alone, might solve this issue. So the next section of the visualizer, scrolling down a bit, shows the one filegroup/multiple file scenario from the demo. This is filegroup MultiFileFG, shown in alternating pink and purple:

InterleaveScreenCap2

Notice, we got three files, but the two tables we map into them are still interleaved, now across all three files. (Each file in the visualizer has a signature of orange-gray-orage system pages right at the beginning.)

This pattern is because of the proportional fill applied to the physical files inside one filegroup. An extent is allocated for an object from one file, then the next file has the most available space, so the next file used for the next extent, and so on. This means our tables are still cut up into 8-page extents spread across the physical media.

Two Groups with One File Each

The last section of the demo writes each table into a separate filegroup, where each filegroup is backed by a single physical file. The results here are represented by the solid pink area in the graphic:

InterleaveScreenCap3

In this case, the alternating inserts in the script don’t result in alternating allocations inside the files, because one table is specifically isolated to one filegroup and the other table to the other. In this case, the data in the files is practically contiguous.

Conclusion

Does this mean you should go out and make 1,000 file groups, one for every table or index? Please don’t! There’s a balance when planning files and filegroups between manageability, imagined performance impact of fragmentation (or real), complexity, and many other factors. SQL Server actually does a fine job, on smaller databases, working with one or a handful of files. But if you are planning a larger warehouse project, or you find the hardware you have struggles with the volume of data you need to manage, or you are looking at partitioning, then understanding how these storage internals behave, along with many other design considerations, will help.

Published Monday, March 11, 2013 11:08 PM by merrillaldrich
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

George Lapin said:

Could you relate this filegroup planning to use of a SAN?  SAN manufacturers tend to say their SAN solves all storage and retrieval issues.  Could you please comment?

gllapin@yahoo.com

March 15, 2013 1:47 PM
 

merrillaldrich said:

George, it's true that some SAN salespeople want you to believe their products solve all your pain (it's part of the pitch). However, there are still good reasons to use filegroups for a larger DB no matter what the storage looks like. These include index maintenance/fragmentation, partial restore capability, management of files related to table partitioning, read-ahead, online migration, probably others.

March 25, 2013 1:17 PM
 

Lonny Niederstadt said:

Good stuff.  I think its really helpful to be able to visualize these things.  People say all kinds of things about fragmentation and contiguity when they are trying to sell something :)  I rarely hear talked about in SQL Server circles (more often for Oracle) is the penalty for exceeding disk or adapter maximum outstanding IO queue depth, and the associated performance penalty.  Sure there's some inherent benefit to performing one 1 mb or 512k multi-block read instead of individual database block reads.  But the big benefit is the lower liability of qfull/scsibusy conditions by driving IO size up and IO count down.

Every component along the IO path - Windows disk driver, HBA driver, SAN front end port, SAN processor, SAN back end port, physical disk - has a maximum queue depth and/or maximum IOPs rate.

Assuming that the developers have the largest role in writing the queries and designing the data structures, the database administrator gets to participate in the adventure by designing the database file and filegroup layout, and by implementing index maintenance (hopefully without inducing an unreasonable amount of unneeded index rebuild or reorg traffic).

For large data warehouse and analytics workflows, I think there will be emerging talk about SQL Server average IO size. Why? If queue saturation is a significant concern for the humongo Oracle and DB2 databases out there... and the same SANs are being sold to provide SQL Server storage...

April 16, 2013 5:35 PM
 

merrillaldrich said:

Thanks, Lonny

April 16, 2013 6:33 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement