SQL Server Performance Forum – Threads Archive

Configuting a DB – queries

I am always confused with onething.Can someone give a solution iwth following scenario I have two drives , M: (for .MDF ) and N

for .NDF) files. Now i have to create a new database, initial estimated size is 10GB . Can some one guide me now, how to create datafiles to get good perfmomance,(initial size,whether need a .NDF file now etc … ) please let me knwo your opinions, so that i can shoot my other questions [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] . Thanks in advance . rajiv

HI Rajiv, This is not an easy task to give it in one line or in some time.
The reason is, its involved like the amount of transaction which may happen with in ur database, Hardware configuration and RAID levels which are applied like so many factors are to be studied.
About the database allocation in disks and its plans please check this
http://sql-server-performance.com/rc_hardware_planning.asp
You may get some idea. You can set the initial size of your database through T-SQL or EM
Check this: USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = ‘c

rogram filesmicrosoft sql servermssqldatasaledat.mdf’,
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = ‘Sales_log’,
FILENAME = ‘c

rogram filesmicrosoft sql servermssqldatasalelog.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO —- Check more about this in BOL
-Johnson

True the optimal database size allocation depends on the database growth and how frequently the database is used. I will refer the same which was posted on other thread. If the filegroup comprises multiple files spread across various physical disks, each with its own disk controller, then queries for data from the table will be spread across the disks, thereby improving performance. The same effect can be accomplished by creating a single file on a RAID (redundant array of independent disks) level 0, 1, or 5 device. It is advantageous to get as much data spread across as many physical drives as possible in order to improve throughput through parallel data access. To spread data evenly across all disks, you can place a single file across striped disks or maintain each disk separately and place a file on each disk.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Rajiv,
It is easy to create a database using Enterprise Manager. Just right click any user database and go to New Database . Here, You will get a Data Files & Log files tab in which you can set the location, Initial size and Filegroup. To get good performance, I feel it depends upon your hardware Configuration. If you have sorted it out, reply through the same forum. N.Babu Godson Samuel

Thanks Satya,johnson,samuel. Let me study it once again. Samuel,its not a qusetion of how to createa DB, but how to organise files in my scenario. Couple of possibilities are … 1)create a .MDF file in M: drive of size around .5GB and create a .NDF file of size 9.5GB to N<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />rive 2)create a .MDF file of 10GB in M:drive, when it reaches limit,later i will add one .NDF to N<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />rive(may be after 1 year) etc … Still hoping to hear more from you.DB organisation seems to be a tough talk <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> .Satya thanks ur reply

Now your question could be whether M: and N: and just logical drives or seperate physical disks.Even i dont know how to identify this. i checked device manager and could see many entries like
DGC RAID 5 SCISI Disk DRive
DGC RAID 5 SCISI Disk DRive (around 30 entries liek this ). Sorry i am not much familiar with RAID .
I hope above info would help, atlease someone to identify the correct soln :

i will add that normally log file are Write Intensive so always place log file on a RAID system for writing e.x RAID 1
And your datafile on a RAID 5.
And Also recommended to use a secoudary filegroup for all user objects (ndf)
For better performance , place each file on a physically different disk. AKTHAR

Here my pbm is i donno how to check if M: and N: drive are same disk or seperate physical disks <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> . Anyway Great Akthar, it showed some light .thanks

You can get a very good estimate based on array configuration utility installed in your server ( if disks were ever RAID’ed ) . IN explorer Check capcaity for M and N drives, and then check logical / physical views thru ACU and you should be close to your answer.

Hi, thanks a lot, yes both M: and N: are two physical disks .finally, I have decided to create a .MDF(M

and one .NDF file(N

. Now i have few more queries . Suppose i have created two files one .MDF(512 MB ) and .NDF (10GB ) , how SQL server write datas to files. will it wait to fillup .MDF to go to .NDF .???? actually i want system datas in .MDF and user datas in .NDF <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> .

yes i got the solution for this .One article says … "Create a user-defined filegroup and make it the default filegroup.
It’s a good decision in most cases to store and manage system and user objects separately from one another, so the user objects will not compete with system objects for space in the primary filegroup. Usually, a user-defined filegroup is not created for small databases, for example, if the database is less than 100Mb." Now i have made it like … M:drive
Primary –
data.MDF 512MB N:drive
Secondary- DEFAULT Group
data2.NDF 10GB Now my question is , Suppose there is a new object getting created in Master,MSDB,or Model then in which file it gets created. is it Primary or secondary filegroup ???? I hope this is a good question , anyone can discuss over .Please wordout ur thoughts . rajiv

Well normally every System objects is created in the Primary FileGroup AKTHAR

Dear Rajiv, Normaly we don’t paly with the system databases. Even if we do it will be in the PRIMARY filegroup, that is within your .MDF file only. MSDB is one which will comparitivly grow considering other system database, so you should have sufficient place for that. Better option is, keep your system databases in seperate drive, don’t club with your user-databases. File group idea is excellent if you are in clueless about the database growth. The reason is, one fine day, you find the disk size if consiumed almost full, add diskspace and create onemore filegroup in the new disk and assign the tables into the new filegroup. -Johnson

Hi, please read "CREATE DATABASE" -> "F. Create a database with filegroups" which tell you how to assign FILEGROUP while creating database. similarly you can modify file groups using ALTER DATABASE. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

True and by default the database and its objects will be created in PRIMARY filegroup which is a default one, until unless you specify the location of filegroup it will not be created. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>