In a new data centre build, storage system or new equipment, the DBA should have an input into the architecture and configuration.
This is an FAQ on Storage. Typical questions I might ask the storage team, or the storage team may ask me .
Do you have documentation from the SAN vendor?
Ask for SAN documentation and SQL Server performance guidelines.
Look for the recommendations the SAN vendor will outline for OLTP \ DSS . Documentation will normally include information about file locations and other configurables.
What if the SAN administrator wants to do things differently from the documentation?
Ask SAN administrator to justify why they are doing things differently. Why are they using a particular stripe size? Why are they using a particular RAID level?
Do a cost versus benefit analysis . Often there isn’t an open cheque book. This means compromises are required. Produce explanations for every decision
What RAID level should the DBA recommend?
Check post on RAID levels - SQL Server – RAID levels overview
RAID10 is generally faster . It is also more expensive. Analyse carefully the workload requirements. Define the IOPS requirements per application. How does this compare with the capacity?
Consider defining different drives with different RAID levels
Weigh up costs versus performance versus storage space required. Produce a document outlining the relationships between performance, storage space and cost.
How does this fit into the budget?
If a RAID level compromise needs to occur , which IO activity types should be compromised and in what order?
Explain carefully the consequences to users.
1) Tempdb data files .
2) Tempdb log files
3) User database files
4) User database log files
What Cache size and cache settings should be used?
Create a write activity IO profile of the database servers. Define the optimal speeds . Work with the storage guy to find the optimal configuration
Should database server drives use Dedicated Arrays or Shared Arrays?
Normally, I would say Dedicated Arrays. But based on experience – this is not always the best advice. You need to ask some hard questions , such as:
Is the SAN team skilled to deal with Dedicated Arrays ?
Can they monitor the SAN performance and present to DBA ?
Are VLDBs used ?
If the answer is no to most of these questions , I’ve found Shared Arrays easier to implement. The Shared Arrays should be architected in a way to allow growth.
Should SAN snapshots be used?
Yes. If you have very large drives with large databases, the benefits of SAN snapshots are fantastic. Consider the benefit of copying all the files at a block level and then backing up to tape overnight. Automatically the Production databases are available for longer period over a 24 hr period.
Some vendors offer logical grouping of drives , which allows you to maintain data and log files on separate drives and still snapshot.
Where should the TempDB be stored?
Always separate LUN if possible. TempDB doesn’t require replication , consider SSD attached.Tempdb and solid state drives
What stripe size should be used?
SQL Server allocates 1 extent at a time – 64KB. Therefore a stripe size 64KB or bigger is optimal. Sequential is good for datawarehouses. Listen to advice from your vendor , test the workload and come up with a figure.
I was involved recently in a large SAN installation. The decision was made by the SAN expert to size at 256kb striping , on the basis it would assist in reads. He’s reasoning was that the segments are larger therefore much more likely the relevant data would be nearby.
Should Data Compression be used?
Data Compression – read Data compression in SQL Server 2008 . For data sets that have few changes Data Compression is good. For OLTP it’s slower.
Be specific as possible , when creating a storage Checklist. Read all the documentation is read . If during performance testing expectations are not met, speak to the storage expert in your organisation and the vendor.
Analyse the performance stack – before you make a conclusion about storage. If you’re testing a new storage system , many factors influence the performance.
Author: Jack Vamvas (http://www.sqlserver-dba.com)
