number of database files

  • samirca007


    Points: 412


    I read in  book of sql  server administration

    the number of files used for the same database must be equivalent to the level of parallelism defined for the cpu cores (maxdop) is a question included in the transaction log file or not?

    So if I have maxdop 4, I make 1 mdf and 3 ndf + next to the ldf or I have 1 mdf, 2 ndf and 1 ldf included in the 4?

    Thank you in advance,


  • frederico_fonseca


    Points: 14636

    could you post a link to that book - that used to be something that applied only to tempdb files, and even that is really a myth.


    for most cases you do not need to split the files - and not based solely on that info you got and on a per CPU.

  • Grant Fritchey

    SSC Guru

    Points: 396476

    Nope, nope, nope. That book is either wildly wrong, or you may have misread a section of it. A database only has to have two files, data & log. You can add additional files as a way to manage storage, or, if you have additional disks and additional disk controllers, as a way to help I/O performance. But I wouldn't add files to a database because of parallel execution. As was already noted, that prescription for tempdb is junk.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA (Mike Vessey)


    Points: 21757

    filegroups in a database can be used for a few things (filestream, memory optimised tables etc etc) but splitting your storage is your main gain.. (perhaps a tiny improvement if you are on separate spindles due to seek times)

    I really wouldn't bother splitting, grant is correct - plus it will make your life a misery when you come to do scripting for restores

    files in tempdb …. I stick with cpu count -1 (up to 8) mainly due to reading brent ozar's articles about locks and latches...

    we fixed a lot of stuff by doing that, but tempdb is different to any other db, it is used by a lot more things (unindexed tables, bad joins etc)


Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply