August 15, 2017 at 9:45 am
Setting up a new server.
For the data, I have 2 TB SSD RAID1 as drive D: and 12 TB SATA RAID10 as drive E:
The plan for D: is:
- tempdb
- transaction log
- indexes
I still have a lot of room on D:. I will place some data tables there too. Which option sounds better:
1) To place large frequently used tables and their indexes on D:
2) To place large frequently used tables on D: and their indexes on E:
3) To place large frequently used tables on E: and their indexes on D:
The considerations I take into account are:
a) the speed of the drives
b) recommendation to have the transaction log on a separate drive
c) recommendation to have data and indexes on separate drives
August 17, 2017 at 1:57 pm
I personaly found better performance placing log on separate drive from both tempdb and indexes. In your case , why do you want to place tempdb and log and some indexes on same drive? As for consideration I would definitely want tempdb and indexes\tables on separate drives from each other AND from log, if you have a lot of writes... if your datavase read heavy (which doesn't affect log but might affect tempdb for sort spills and other fun things) you want tempdb separate from index and data (I usually end up with 4 drives : Tempdb, tLog , Data and index drives).
August 17, 2017 at 2:48 pm
It also depends on whether the physical drives are going to be separated into logical drives in Windows. For example, you could carve out 100 GB on the D:\ for TempDB, another 200 GB for the Logs, and ~500 GB for indexes. That still leaves ~200 GB free on the physical allocation.
If you cannot split it up like that, then it really matters a lot less.
I would be concerned with the RAID 1 config on the D:\ physical drive. Any opportunity to change that to RAID 10?
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 22, 2017 at 3:27 am
it all depends on your workload....if you use tempdb a lot then splitting that is advisable. however, if your database is ready-heavy i would avoid putting indexes alongside tlog and tempdb.
assuming these are all local disks....and @alan: what is the issue with RAID1?
August 22, 2017 at 5:35 am
Eliyahu - Tuesday, August 15, 2017 9:45 AMI still have a lot of room on D:. I will place some data tables there too. Which option sounds better:
1) To place large frequently used tables and their indexes on D:
2) To place large frequently used tables on D: and their indexes on E:
3) To place large frequently used tables on E: and their indexes on D:
Why are you planning to split data and indexes apart? What's the goal?
I really don't like the idea of tempDB and log files on the same drive. If TempDB grows unexpectedly, you could end up with the log out of space.
You should have space free on the drives, to account for future growth.
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
August 22, 2017 at 6:50 am
Thanks for the answers.
I have only C, D and E. C is system, D and E are for data. From a separate research, I concluded that logical drives don't matter, physical do. So I have only 2 drives to play with.
On the existing server, the log is about 25 GB and the tempdb may grow to 40+ GB, still far from the 2 TB capacity of D: If I don't place tempdb next to the transaction log, I put it on the slower drive with data. Is it really a better option? Unlikely.
Splitting data and indexes apart is one of the old performing-tuning advices. Since it is disputed if it is still a valid advice or not, I am fine with dropping this idea for something reliably better.
So, the question is what is the best use of about 1.8 TB of SSD RAID1 and 12 TB SATA RAID10 with 0.2 TB of SSD reserved for the transaction log and tempdb?
The database is for both reading and writing intensive..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply