THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: file fragmentation and SAN – Part V

SQL Server workloads

 

So far, the discussions in all the previous posts (1, 2, 3, and 4) on the performance impact of file fragmentation on a drive presented from a high-end enterprise-class disk array are related to disk I/O workloads. Ultimately, you want to know how file fragmentation may impact your SQL Server workloads.

 

In this post, I share with you some results from running three SQL Server workloads: (1) database backups, (2) checkpoints, and (3) table scans. These SQL Server workloads were run in the same three test scenarios as used in all the previous four posts:

 

·      Non-fragmented. The database files were created on an empty drive,

·      2MB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 2MB in size,

·      128KB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 128KB in size,

 

The same database was used in all the tests. The database was about 9.5GB in size, and the table on which table scan was performed had 10 million rows and was about 4GB in size. DBCC DROPCLEANBUFFERS was run prior to each table scan.

 

The following chart is a summary of the SQL Server workload test results:

 

Clearly, file fragmentation did not have any significant performance impact on these SQL Server workloads. Because the performance of these workloads is dependent on the disk I/O throughput, we could have predicted this from the results of our disk I/O tests as reported in the previous four posts. However, it’s still comforting to see the prediction validated with real data points.

 

This concludes this series of posts on the performance impact of file fragmentation on a drive that is presented from a high-end enterprise class disk array. Overall, the impact was significantly less than what we have seen on a traditional directly attached disk drive. For I/O throughput, there was little to no impact. For I/O latency (or response time), file fragmentation can cause some I/O request to take much longer to complete.

 

In a future post(s), I’ll explore whether the same holds true with a drive presented from a lower end disk array.

 

Published Monday, December 29, 2008 5:36 PM by Linchi Shea

Attachment(s): image002.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

In case this is your first visit to SQLBlog.com, Linchi Shea does very methodical analysis of the performance

December 29, 2008 6:19 PM
 

aspiringgeek said:

Excellent work, Linchi.  I'm eager to see the results of the low-end disk array experiments.  In them meantime, could you share the details of the hardware used for this experiment, including server & SAN?  Thanks much.

December 30, 2008 9:19 AM
 

Linchi Shea said:

Jimmy;

The server was a standard DL580 with 4 single-core sockets (2.0GHz Xeon) with 4GB of RAM. The disk array was a DMX2 with 10K rpm 146GB FC drives in a RAID10 config. The test LUN was 96GB in size (with 8GB disk slices from 24 spindles). Two Emulex LP8000 HBAs were load balanced with PowerPath. The tests were done more than a year ago. This is not a state of the art config by today's standard.

December 30, 2008 4:10 PM
 

John Sansom said:

Excellent series of posts! The information is clearly presented and very valuable knowledge.

I appreciate the time and effort it took to prodcue, so thank you.

April 24, 2009 4:29 AM
 

Query said:

How much cache did that DMX2 have, i.e. did the DMX2's cache size substantially exceed the size of data being accessed, or even represent a large fraction of the data?

March 21, 2012 12:05 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement