Share via

Deploying SQL Server 2005 with SAN #3

  • Article

Prem Mehra and Mike Ruthruff

 

We recommend that prior to deploying SAN in a SQL Server production environment you conduct a bench-mark to establish a performance base-line. There are several benefits: 1) Establish a base-line so that you know ahead of time the maximum throughput and response time to expect 2) Identify any performance issues quickly and resolve them. This process is quicker because the issues are only due to the I/O subsystem since the DBMS has not been deployed yet.

 

Tools such as SQLIO.exe or IOMeter can be used for this base lining. SQLIO.exe is an unsupported tool provided by Microsoft to accomplish this and is available for download from here. https://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO.msi.

IOMeter is a tool available at: https://sourceforge.net/projects/iometer/

 

The name - SQLIO.exe - implies that the tool is designed specifically for SQL Server. That is not the case; you as a user have to design your tests to simulate the type of I/Os that SQL Server is designed to issue. That said, keep in mind that the purpose of running SQLIO.exe is not to accurately simulate SQL Server IO workload but rather test the limits of the IO subsystem for different basic types of IO in order to determine if performance of the chosen configuration is acceptable.

 

Characteristics for most general types of SQL Server IO are given in the below table.

 

Operation

Random / Sequential

Read / Write

Size Range

OLTP – Log

Sequential

Write

512 bytes – 64KB

OLTP – Data

Random

Read/Write

8K

Bulk Insert

Sequential

Write

Any multiple of 8K up to 128K

Read Ahead (DSS, Index Scans)

Sequential

Read

Any multiple of 8KB up to 256K

 

 

Operation

Random / Sequential

Read / Write

Size Range

CREATE DATABASE

Sequential

Write

512KB

(Only log file is initialized in SQL Server 2005)

Backup

Sequential

Read/Write

1 MB

Restore

Sequential

Read/Write

64K

DBCC – CHECKDB

Sequential

Read

8K – 64K

DBCC – DBREINDEX (Read Phase)

Sequential

Read

(see Read Ahead)

DBCC – DBREINDEX (Write Phase)

Sequential

Write

Any multiple of 8K up to 128K

DBCC – SHOWCONTIG

Sequential

Read

8K – 64K

 

Note these values may change as optimizations are made to take advantage of modern storage enhancements.

 

The SQLIO.msi download for SQLIO tool contains an .rtf file which has some details about the considerations one should make when benchmarking a disk subsystem and how to interpret the results. We would recommend reviewing this file before running tests. Some of the key points to keep

Comments

  • Anonymous
    November 17, 2005
    Did I miss Deploying SQL Server 2005 with SAN #2?

  • Anonymous
    November 17, 2005
    Please post a link to #1 and #2.

  • Anonymous
    November 21, 2005
    Sorry. The other two are posted under the "Performance and Scalability" category. I will post this one also there.

  • Anonymous
    November 22, 2005
    Are you going to address sector alignment? I have done testing and found the best settings for me but I am curious about your recommendations. Does WinSQL x64 have any impact when designing the SAN? Particually, is there an optimal stripe sizes, sector alignment, AU for SQL on GPT partitions.

  • Anonymous
    December 16, 2005
    The summary table that you list above is the IO patterns that I typically see. I do however have a question about Read-Ahead. In the SQL Server 2000 I/O Basics whitepaper

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

    "When a read-ahead is posted, it can be from 1 to 1,024 pages. SQL Server limits a single read-ahead request depth to 128 pages on most editions. However, Microsoft SQL Server Enterprise Edition raises the limit to 1,024 pages."

    Using these page numbers IO size would be

    128 pages would be 1024KB
    1024 pages would be 8192KB

    So why do we not see Read IO of 1024 or 8192?

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2233543-sql-performance-bench-marks

  • Anonymous
    August 31, 2010
    Hello: Sequential IO’s of 2K – 64K in size (log). is very confusing. every thing goes from Log Cache to Log file. so there should be definite page size in Log Cache which gets FLUSH to log file. What size will be that? Thanks