Mark Horton-381871 (5/26/2011)
Ok, so a LUN for data and a LUN for Logs, etc. What about a LUN per data file? My Db has 5 data files. Should I put each data file on it own dedicated LUN or is it a moot point and I should get back to 1 data file on a single LUN. I have read that your should have a LUN per processor. I know this is a challenge to administer, but I am trying to plan to extreme growth.Same question about tempdb. Should tempdb be consolidated back to 1 data file instead of spread across multiple LUNS?
Mark,
I have not read any advice about a LUN per processor.
What is most important from my experience and training is:
1. To have your database data files separated from your database log files. Database files are primarily accessed in random seeks based upon the requirements of the various queries that are executed. Database log files are primarily accessed in a linear, sequential record fashion. The 2 methods of access are executed most efficiently if they are separated so place a single (or multiple) database data file(s) on one LUN, its corresponding database log file on another LUN.
2. An exception to that would be the Master, Msdb, and Model database files. Accesses of these database are typically low, mostly reads. I would place all of their data and log files on the same LUN. This has worked very well for me in several different production environments.
3. The other exception is the TempDb database data and log files. I recommend that you place your TempDb database data files on one LUN. Place your TempDb database log file on another LUN. As a general rule, you should have one TempDb database data file per CPU core but there are practical limits to the efficiency of this. It's a debated subject but from what I've read, the practical limit is probably 8 different TempDb data files (less if you have fewer than 8 CPU cores). Would putting each TempDb database data file on a different LUN improve system performance? Maybe. You'll have to experiment with your server. It all depends on the volume of and types of access of your databases and how much your TempDb database is utilized.
4. A LUN per application database data file? I would consider doing this for your application database consisting of 5 physical files. Anything you can do to spread I/O across more physical spindles will speed up your system. I'm not saying for certain that it will speed up your system, but it might, depending on what parts of your database are being accessed concurrently with other accesses of the same database. There is no way to know without experimentation.
Here is a script to create multiple TempDb data files that so you don't have to research how to do it. Please make sure you understand what the query is going to do you before execute it. You'll need to execute it, then restart SQL Server for the changes to become effective:
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME='templog' ,FILENAME='H:\templog.ldf' ,SIZE=20GB);
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME='tempdev' ,FILENAME='G:\tempdb.mdf' ,SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev1',FILENAME='G:\tempdb1.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev2',FILENAME='G:\tempdb2.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev3',FILENAME='G:\tempdb3.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev4',FILENAME='G:\tempdb4.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev5',FILENAME='G:\tempdb5.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev6',FILENAME='G:\tempdb6.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev7',FILENAME='G:\tempdb7.mdf',SIZE=20GB);
GO
Hope that helps,
LC