Back to basics – Using another Default Filegroup

SQL Server uses the file system on a computer, just like all database platforms. Whenever you create a database, there are always at least two files that are created – one for the data (MDF files) and another for the logging function (LDF files). But you can create more files if you want, and it’s a best practice to do that.

It goes a bit deeper than that. Files aren’t just stored alone, but grouped into a FileGroup. This is just a name that SQL Server uses to refer to sets of files. You can have multiple files in a single FileGroup, or just one file in a FileGroup.

One of those filegroups will be designated as the "Default". That means that if you don’t use the ON clause when you’re creating an object like a table or index, it will automatically go to the one marked as Default.

So to recap – if you just create a database and then start adding objects to it, you’ll have one filegroup called PRIMARY (which is the Default) and two files, one of the data and the other for the log. And that brings us to today’s entry.

When a database is created, it isn’t empty. Lots of tables, views, and other objects are created. In addition, SQL Server has a few databases of it’s own (called system databases) that are on the same system, and potentially in the same drives that you’re using for other data. This can cause the system to wait on the hard drive more than it should have to.

So what you can do is the minute you create a database, create another FileGroup right away, and then designate it as the Default. Then you can place files on other drives in your system to balance the IO a little better.

More about all this is here: