March 11, 2013 at 9:53 am
Hi Folks,
what ist the proper placement of files?
In an article I red : LDF and MDF on separate drive, TempDB on another separate drive, hot spot tables on separate filegroup (and on separate disk), etc.
At the moment I got following setup :
Drive D :
Database.mdf - 450 GB
Change Data Capture.mdf - 7 GB
Drive E :
Database.ldf
Drive F :
TempDB.mdf
Drive G:
TempDB.LOG
Drive T:
Indexes.mdf - 136 GB
Already took a short look a accidental dba - chapter 2 :
If I think logical, then I would put as many things as possible in own filgroups on seperate drives. But I want to know, what is your practice in real world? How about big or hotspot tables, do you put them on different drives? Is it good to put for example all the indexes on a seperate drive, or has it also a downside?
Greetz
Query Shepherd
March 11, 2013 at 10:40 am
there's no magic answer - you should split as you suggest, possibly with the exception of indexes - but then you should monitor the activity and find out what's active.
It also depends upon your underlying storage too. For example if your array is a set of disks then split into luns then you won't actually gain other than for being tidy.
Hopefully most of your data will be in cache so disk access is immaterial.
You must monitor to establish trends etc.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 11, 2013 at 10:54 am
SQL Pizza (3/11/2013)
Drive T:Indexes.mdf - 136 GB
Out of interest, are the indexes on your T: drive both non-clustered and clustered or just the clustered indexes?
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
March 12, 2013 at 3:25 am
WWDMark (3/11/2013)
SQL Pizza (3/11/2013)
Drive T:Indexes.mdf - 136 GB
Out of interest, are the indexes on your T: drive both non-clustered and clustered or just the clustered indexes?
They would be non clustered wouldn't they? If it included clustered indexes then it would also include the data and that would mean having the tables in seperate filegroups to be able to do this.
March 12, 2013 at 3:53 am
If you're splitting into files and filegroups, the first thing you need to do is decide whether you're splitting for IO load or splitting for DR and recovery purposes. The two have completely different strategies for what goes where.
If splitting for IO load, then you'd start by confirming that your IO subsystem is not handling the current load (or getting close to that) and ensuring that you have completely separate IO paths (all the way to the drives) to split the database among (as well as ensuring that simpler fixes like faster HBAs/switches aren't an option)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply