This one of the very interesting article I read regarding SQL Server 2005 Storage. Please refer original article at MS TechNet here.
- Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application.
- More / faster spindles are better for performance.
- Try not to “over” optimize the design of the storage; simpler designs generally offer good performance and more flexibility.
- Validate configurations prior to deployment.
- Always place log files on RAID 1+0 (or RAID 1) disks.
- Isolate log from data at the physical disk level.
- Consider configuration of TEMPDB database.
- Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.
- Don’t overlook some of SQL Server basics.
- Don’t overlook storage configuration bases.
Reference : Pinal Dave (https://blog.sqlauthority.com) , MS TechNet .
Question for you. What’s the best way to exploit MAXDOP on hyper-threaded server (4 CPUs)? I want to use all the resources available without worrying about deadlocks. I am processing millions of records (job executing a stored procedure).
Please share any comments you have …