SSWUGtv:
Avoiding surprises with your SQL Server configuration… who is changing what? Raj Sabhlok of ManageEngine on the show to talk about more holistic monitoring of your servers, plus Laura Rose with time management tips.
[Watch the Show]
Raid with DB
In response to Accidental DBA editorial Adam asks,” Could you please send some article about RAID, main problems of RAID….”
The best advice I have seen on RAID is not available online. Books on SQL Server management and administration generally provide a more thorough coverage on the topic, because the answer, like all things database is, “it depends.”
With that caveat, there are number of things to do or avoid. So, providing a few topics, you should be able to run
some Google Searches to point you in the right direction.
If you have access to a high performing SAN, put your stuff on the SAN. Generally raid 10 is best for database volumes. However, I have experienced high performance on a large SAN built on over 100 Raid 5 drives. However, that particular SAN has a huge amount of Cache available such that disk access is highly optimized, and thus, Raid 5 drive configuration works efficiently.
I have also worked with lower end SAN configurations where it made sense to have multiple Raid 10 partitions because the physical disks were hit much more frequently on large volume queries.
Most often, when connecting to a SAN, you really don’t have a choice. Your SAN administrator will provide you LUNs and you do what you want with them. You don’t know who/what other processes share the physical disks supporting your physical devices.
TempDB is another device in SQL Server you gain great performance benefit from if it is on separate physical disks. On my massive SAN example above, TempDB was also on the same physical disks as everything else; yet my TempDB performance was faster than any other configuration I could afford.
If you are using Direct Attached Storage, then the rule of thumb is to separate
1) Operating System/Swap File
2) TempDB (one file per CPU/Core)
3) TempDB Transaction Log (one file)
4) Data Files
5) Transaction Logs
This assumes a production box with multiple disk drives available. With the reduced price of a SAN today, I don’t think I would go with a drive optimized direct attached storage configuration (lots of drives that may be configured many different ways). Usually with direct attached storage I am keeping costs down, so performance isn’t quite an issue.
Assuming 5 drives I would consider the following…
1) Drive 1, 2 – Mirrored Set Operating System
2) Drive 1, 2 – Mirrored Set Transaction Logs
3) Drive 3 – TempDB Files
4) Drive 4, 5 – Mirrored Set Data Files
Notice that TempDB doesn’t have a mirror. That is by design since TempDB can be temporarily moved to another drive in order to get SQL Server running again.
Anything with fewer drives really doesn’t matter. At that point you either mirror or make sure you have good backups.
The goal is to isolate processes that compete for disk utilization. Since windows uses a swap file to handle virtual memory, this is good to isolate. If you machine has adequate RAM, the issue is less important; especially when the server is only hosting SQL Server.
Transaction logs are written and read and may be accessed concurrently to data files; for this reason, separate (if possible) transaction logs from data files.
The principles apply when working with any OS and/or database engine. The less contention you have on the disk subsystem, the better your database performance will be.
If you have a blog on this topic, or would like to provide direction for any database engine, please send your comments to btaylor@sswug.org.
Cheers,
Ben
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)
Featured Script
sync_dbids.sql
Script for sql used to update a profiler trace table(s) so that it can be replayed on a different server…. @tablename is t… (read more)