Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 DB Files Setup

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-04-03 : 09:23:43
We're in the process of ordering new servers, and i just wanted to see if i can get some feedback on how you guys might setup the data files for the server.

the database servers are mainly data warehouses, we house probably in the neighborhood of about 3 billion rows, some tables only 10-20 columns wide, some of the larger tables (between the 150-200 million range) can be as wide as 200 columns.

right now, our current server is basically a jbod array, single disks (16 of them), with one data file on each disk. then we have a Raid-0 stripe of 4 disks that the log file sits on. heres an example create script of the DB.


CREATE DATABASE [ADDRESS]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'ADDRESS_01', FILENAME = N'D:\Drives\SATA_DISK_001\ADDRESS_20120706_01.mdf' , SIZE = 8556992KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_02', FILENAME = N'D:\Drives\SATA_DISK_002\ADDRESS_20120706_02.ndf' , SIZE = 2523264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_03', FILENAME = N'D:\Drives\SATA_DISK_003\ADDRESS_20120706_03.ndf' , SIZE = 2614848KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_04', FILENAME = N'D:\Drives\SATA_DISK_004\ADDRESS_20120706_04.ndf' , SIZE = 2612416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_05', FILENAME = N'D:\Drives\SATA_DISK_005\ADDRESS_20120706_05.ndf' , SIZE = 2614848KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_06', FILENAME = N'D:\Drives\SATA_DISK_006\ADDRESS_20120706_06.ndf' , SIZE = 2614848KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_07', FILENAME = N'D:\Drives\SATA_DISK_007\ADDRESS_20120706_07.ndf' , SIZE = 2614848KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_08', FILENAME = N'D:\Drives\SATA_DISK_008\ADDRESS_20120706_08.ndf' , SIZE = 2614848KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_09', FILENAME = N'D:\Drives\SATA_DISK_009\ADDRESS_20120706_09.ndf' , SIZE = 2614848KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_10', FILENAME = N'D:\Drives\SATA_DISK_010\ADDRESS_20120706_10.ndf' , SIZE = 2614848KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_11', FILENAME = N'D:\Drives\SATA_DISK_011\ADDRESS_20120706_11.ndf' , SIZE = 2756480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_12', FILENAME = N'D:\Drives\SATA_DISK_012\ADDRESS_20120706_12.ndf' , SIZE = 2876352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_13', FILENAME = N'D:\Drives\SATA_DISK_013\ADDRESS_20120706_13.ndf' , SIZE = 2876352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_14', FILENAME = N'D:\Drives\SATA_DISK_014\ADDRESS_20120706_14.ndf' , SIZE = 3480448KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_15', FILENAME = N'D:\Drives\SATA_DISK_015\ADDRESS_20120706_15.ndf' , SIZE = 4060608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'ADDRESS_16', FILENAME = N'D:\Drives\SATA_DISK_016\ADDRESS_20120706_16.ndf' , SIZE = 5186304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'ADDRESS_log', FILENAME = N'D:\Drives\SATA_RAID_001\DBLOGS\ADDRESS_20120706_log.ldf' , SIZE = 32768KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
GO


the current disks are all 300GB 15.7k SAS Drives.



the new server we're getting, will have 12 total drives (expandable up to 30), all high-performance SSD 400GB SAS Drives.

so my question to you is this, how should i lay out the 12 drives?
i'm thinking:
2 (stripe) for the OS
2 (stripe) for the LOGS
8 (single disks) for the data

would you guys recommend a different configuration? backups are not a concern as all data is replicated to another server, if this one goes down, i can take all week if i wanted to for restore w/o any interruption of service.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-03 : 10:18:10
This is the only database on the server?

What about system dbs?
What about the OS and SQL Binaries?
What about tempdb?
Why are you creating the 16 files with different sizes?

I would almost certainly create fewer files.
Have you considered using file groups?
Consider RAID 10 for data drives.

Also, I don't care about rowcounts, I care about database file size when considering configuration.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-04-03 : 11:03:53
quote:
Originally posted by russell

This is the only database on the server?

What about system dbs? (and tempdb)

-System DBS and TempDB i usually sit on the same Disk Set as the Log DBs. that can easily be re-arranged though.

quote:
What about the OS and SQL Binaries?

-the OS/SQL Install would be the 2 (striped drives i mentioned above. so they would be their own disk set.

quote:
Why are you creating the 16 files with different sizes?

-the 16 files @ different size all has to do with how the DB has grown, they initially started out at 2GB and have grown to where they are now. the firs tone i'm assuming is so large because of keying/indexing

quote:
I would almost certainly create fewer files.
Have you considered using file groups?

-I've used file groups in the past w/ partitioning, but it seemed to be a pain to maintain, and that's the only benefit that I've found for it. i'm sure there's more reasons to use it though, do you have any recommendations, or know of any good reasons why i might want to?

quote:
Consider RAID 10 for data drives.

-we don't have enough disk space, or number of disks to do a 10 raid (at least imho) like i said though, the redundancy isn't really an issue, so should i maybe consider striping? if so, how many files (or groups) would you setup?

quote:
Also, I don't care about rowcounts, I care about database file size when considering configuration.

-here's a dump of the db sizes (just to give you an idea)





Thank you in advance!
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-03 : 12:59:15
8 disks is enough for TWO RAID 10s.

So are all these databases on the new disks, or are the new disks (the 8 you propose for data) for just the address db?

Advantages of multiple file groups mostly for managing backups when the databases get very large.

I always split tempdb out onto its' own disks when possible.

I can't think of much advantage of having 16 files in the same filegroup on 8 disks.

Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-04-04 : 01:10:19
i know i would have enough drives to do 2 raid 10's, but i dont think i would have enough disk space. the only gain of raid 10 vs 0, is that it is mirror as well for redundancy right?

all of those databases would be on this new server, so yes, all of the dbs would be on these drives. any advantage of trying to segment the dbs so that for example the two hardest hit dbs are on separate disks all together?

most of these databases are data warehouses, not much rights, all reads.

how many files would you do if you had 8 drives to play with (for data only), and you can config the raid however you wanted?
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-04-04 : 08:18:19
Try to create multiple files for tempdb based on the available processors.

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-04-04 : 10:36:13
should each file sit on it's on disk (or aaray)? or just separate files
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-04-05 : 01:49:42
you can create them on the same disk.
But make sure that all the files are of same size.
this will help in avoid page latches.

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-04-05 : 13:31:48
how many per processor? and do you go by per-core? or per CPU?
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-05 : 13:37:46
By Core, up to 8.

-Chad
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-04-06 : 00:59:23
and anything as for opinions on the other databases?
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-06 : 19:54:40
Basically agree with Russell. I don't see much benefit in multiple files in the same group, on the same disk. And RAID 0 provides no tolerance for losing a disk. If you get a bad disk, you are likely to end up with a corrupt log.

-Chad
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-04-06 : 22:11:37
the multiple files would be on separate disks though. so say in this case, i have eight 400GB drives, how would you configure them? i was thinking of doing 4 stripes (so 4 files, one file on each array, and i'd have 4 arrays. then when i get more drives, i can simply add to each aaray, and the O.S. would be none the wiser.

but that's my idea, i have no performance knowledge to back up what i wanna do, i really want the fastes read io's i can get.
Go to Top of Page
   

- Advertisement -