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.
| 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 OS2 (stripe) for the LOGS8 (single disks) for the datawould 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. |
 |
|
|
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/indexingquote:
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! |
|
|
|
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. |
|
|
|
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? |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-04-05 : 13:37:46
|
| By Core, up to 8.-Chad |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2013-04-06 : 00:59:23
|
| and anything as for opinions on the other databases? |
|
|
|
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 |
|
|
|
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. |
|
|
|
|
|
|
|
|
DB Files Setup