SQL Server IO Subsystem
I am currently revising the IO setup in relation to our main SQL Server 2005 instance.
At the moment we have all data, logs and backups all on one LUN.
I have been allocated 4 LUNS which I was thinking of using as follows:
- LUN1 = Data Files
- LUN2 = Transaction Logs
- LUN3 = Backups
Now for LUN4 for I was thinking of either putting system databases here Master, Model, Temp or putting heavily used databases on here, which would be the most logical approach?
I guess I am just looking for suggestions as to how others would split this up.
Best Answer
I agree with Kev, TempDB is often under heavy load and should be separated from the rest of the files. One thing to remember with Luns and SAN, is that even if you have different Luns you cannot be sure that the different Luns are using their own set of harddrives, unless you divide your SAN into disk groups.
Make sure you have as many fast disks as possible in your diskgroup, do not focus on the space requirements for your datebase. Some companies buy few but large disks to their SAN to save money, but they loose performance.
Using LUN4 for TempDB is a good idea.
It's always useful to separate Tempdb from user db where possible.
The best thing to do is to TEST in your environment. Do a server-side trace on a typical production workload and then replay this in a test environment with and without the tempdb LUN and benchmark your results. Base your decision on real-world testing. Sometimes you will get unexpected surprising results.
Based on my own testing, I have placed tempdb on the same LUN as the data files because placing it on a separate LUN for my workload did not achieve anything except complicating the setup slightly.
