locked
Multiple MDF files? RRS feed

  • Question

  • Very quick question.

    I have a server with 5 LUNS for database files. (ignoring backups for now)

    If I create the database as below:

    CREATE DATABASE [AndyTest] ON  PRIMARY 
    ( NAME = N'AndyTest', FILENAME = N'd:\AndyTest.mdf' , SIZE = 12072KB , FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'AndyTest_log', FILENAME = N'l:\AndyTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
    GO


    Without doing anything special with assigning particular tables/indexes to different filegroups, would it perform the same as this configuration?

    CREATE DATABASE [AndyTest] ON  PRIMARY 
    ( NAME = N'AndyTest', FILENAME = N'd:\AndyTest.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
    ( NAME = N'AndyTest2', FILENAME = N'e:\AndyTest2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
    ( NAME = N'AndyTest3', FILENAME = N'f:\AndyTest3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
    ( NAME = N'AndyTest4', FILENAME = N'g:\AndyTest4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'AndyTest_log', FILENAME = N'l:\AndyTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
    GO

    I know that I can spend a lot of time creating filegroups, moving indexes to different LUNS etc etc.. but as a strictly basic configuration, do the above perform the same?  (lets assume that the table sizes will be over 8MB in the above instance)

    Regards,

    Andy

    Tuesday, October 25, 2016 10:17 AM

Answers

  • Apologies if the size was confusing. 

    If it makes it easier, then replace the MB to GB.

    So, 3GB files for the NDF's and an 8GB table.

    I've answered this myself now as some new hardware dropped in my lap so i just built it!

    • Marked as answer by AndyB1978 Wednesday, October 26, 2016 8:34 AM
    Wednesday, October 26, 2016 8:33 AM

All replies

  • Hello Andy,

    I am a bit confused; you create NDF file of size 3 MB and talk about table of size around 8 MB; and you worry about performance?

    If your database reaches size of TB then you can start making some thought's of splitting your database over several secondary files; but with that low sizes it don't make sense.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 25, 2016 4:55 PM
    Answerer
  • Apologies if the size was confusing. 

    If it makes it easier, then replace the MB to GB.

    So, 3GB files for the NDF's and an 8GB table.

    I've answered this myself now as some new hardware dropped in my lap so i just built it!

    • Marked as answer by AndyB1978 Wednesday, October 26, 2016 8:34 AM
    Wednesday, October 26, 2016 8:33 AM