White Papers

Deploying Microsoft SQL Server 2008 based Decision Support Systems using Dell EqualLogic 10GbE iSCSI Storage 18
4.5.2 MatchRAIDLevelstoDSSI/OComponents
Different database types can generate different I/O patterns in a DSS environment. I/O performance
can be optimized based on which RAID level is used. See Table 1 below for recommendations.
VolumeType: Recommendation:
DatabaseVolumes DSSworkloadsarereadintensive,thereforeRAID50isrecommended
6
to
optimizeforbothcapacityandperformance.
TempDBVolumes RAID50isrecommendedtooptimizeforbothcapacityandperformanceif
writeactivityislow.Ifwriteactivityishigh,RAID10isrecommended.
LogVolumes DSSworkloadshaverelativelylow logactivity,exceptduringdatabase
refreshes.Therefore,RAID50isrecommendedtooptimizeforbothcapacity
andperformance.IfDSSDBrefreshesincludeverylargebulkupdatesthen
RAID10isrecommended.
Table1:MatchingRAIDlevelstoDSSI/Ocomponents
4.5.3 UseSQLServerTablePartitioningtoImproveQueryProcessingPerformance
We used table partitioning in our test configurations so that SQL Server could most efficiently fetch
data. We partitioned the largest table in our test into four subsets. The subset data was spread across
four different volumes residing in their own file groups. The rest of the database tables were hosted on
the fifth volume. We based this partitioning scheme on a particular table column that contained the
most common field in the user queries.
Table partitioning is a recommended best practice to improve query processing so that SQL Server
can efficiently fetch relevant data
7
. SQL Query efficiency and I/O throughput improvements depend
on the partitioning scheme chosen, which in turn is heavily dependent on the data and workload
characteristics. Typically, partitioning will offer benefits when implemented on frequently accessed
6
For more information, see “How to Select the Correct RAID for an EqualLogic SAN”, available at
http://www.equallogic.com/resourcecenter/assetview.aspx?id=8071
7
See: MSDN SQL Server Development Center, “Partitioned Table and Index Strategies Using SQL
Server 2008”: http://msdn.microsoft.com/en-us/library/dd578580.aspx
Note: For more information on tempdb sizing and best practices, see the following
Microsoft Knowledgebase articles:
 Capacity Planning for tempdb: http://msdn.microsoft.com/en-
us/library/ms345368.aspx
 Optimizing tempdb Performance: http://msdn.microsoft.com/en-
us/library/ms175527.aspx
 Troubleshooting Insufficient Disk Space in tempdb: http://msdn.microsoft.com/en-
us/library/ms176029.aspx