Disk / File Tuning
-
I have a server that is going to be working as a reporting server, very few writes (once a day data load) and many, many reads. I want to improve performance for the reads and so have the databases set up so that the logs are on one drive, data on another, indexes on another and tempdb on another.
The question is this: which of the above should be on the fastest drive? I have 3 partitions on a SAN and 1 on local disk RAID, so I have to decide which to place where to get the best performance. I was thinking about keeping the MDF's local and the rest on the SAN, however as there are few writes would it be an improvement to keep the LDF's local and have the MDF's on the SAN. Or should I keep the tempdb loca and have everything else on the SAN.
Any thioughts?
-
tempdb and log file(s) on the faster driver.
-
RAID 5 is best for RAID as just reads and still have a good redundancy, if not worried about down time lengths then gain space with RAID 0, just make sure you have good backups available thou.
Placing log elsewhere won't buy a whole lot as few writes.
TempDB gets used in operations like Order By, Group By and others so off to the next fastest drive would be good.
Seperate index from data to another drive or multiple drives would be a huge bennifit.
Depending on the data might also split tables into seperate file groups and place on different drives. For instance small support tables that don't change often and have just a sinlge clustered index and takes up 3 or fewer pages would be good to seperate from the main data tables file group especially if they are used in a lot of queires.
More info on your structure and system would be helpfull.
-
The hardware is made up of a SAN, running RAID 7 with 3 LUNS and 3 logical drives. There is also a local internal raid array configured to RAID 10. We have already seperated out the largest indexes to their own filegroups, we just wanted to confirm that the tempdb should be on the faster drive and the log files on the slower.
-
In your case of writing once a day you will see more action in the tempdb than in the logs. So yes tempdb is priority here. Selects are not logged and that is what will be happening most often.
-
That's what I figured, thanks for the confirmation.
-
I know this is an old topic, and it's been even longer since I did this type of tuning work, but I have a question about disk layout, especially in the era of virtual disks.
Back in the day when I worked on high performance tp systems (and all you had available was racks of disks), it was important to spread the i/o's over not just the physical drive, but also over the i/o channels. Now, with virtual disks, is that still the case? For example, do you want to put a table on a virtual disk, and it's indices on others, such that they do not share the same cable (whether it be copper or fiber)?
Thanks,
Tom
-
Please rather post new questions in new threads. Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok. Moved here http://qa.sqlservercentral.com/Forums/Topic850733-65-1.aspx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply