SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

One File Per Core?

For a long time in my career I heard various advice on how many files you should create for a database. Since SQL Server 7, when it we had filegroups and multiple files, I've heard various pieces of advice, and honestly I'm not sure what the truth is and what is urban legend.  Over the years I've heard

  • One file per disk array
  • Use multiple files for performane since each gets its own thread
  • One file per CPU
  • One file per core.

I've seen a few postings recently where people were asking questions and even some of the very technically savvy MVPs have been confused by this. While reading through a few of the posts, I found this entry from Paul Randal on Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?

The entry is great in that it tries to explain some of the reasoning behind the advice and debunk some of the advice from various sources, including MS and hardware vendors.

The bottom line is that multiple files per CPU (or core now) really applies to tempdb, when you have allocations occurring from multiple threads. And then it's like 1 file per 2 or 4 cores, something you'd want to test.

For your database files, you separate things out when it makes sense. When you can affect performance by either moving heavily used tables/indexes to separate arrays (not just separate files). Or when you can move very lightly loaded, or needed data, like archive data, out of the way (think partitioning here) to ensure that it doesn't need to be scanned when it's not needed.


The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.