Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Storage Checklist FAQ

27 August,2011 by Jack Vamvas

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.

 

 Read More

SQL Server Storage and IO performance - SQL Server DBA

SQL Disk storage performance overview - SQL Server DBA

SQL Server - Disk IO performance and SQLIO - SQL Server DBA

Calculate disk IO throughput and MB per second - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer