Muller Jannie

About myself and everyone else.

Data flow VS plumbing

In a sense a DBA is just a data plumber. Instead of water we make sure that data gets from point A to point B.

We are definitely more environmentally concerned than plumbers because if water leaks out along the way we end up with data integrity issues. We need to make sure people can drink the water, that would be analogous to querying the data. I’m struggling to find a creative plumbing expression to describe an index. Maybe the DBA VS plumber is not such as good idea but then again I don’t know too much about plumbing. 🙂

For the last couple of days I’ve been sitting and staring at the results of SQLIO. Note my comments on performance tips that this is used for testing your IO performance. SQLIO is relatively old and very well used and documented so you shouldn’t have any problems running it. You don’t need SQL installed so it’s great for a blank system.

The Setup.

The current storage unit I tested isn’t a particularly fast one and I would have done it differently but a point that I was trying to get to with my plumbing analogy is the fact that you VERY seldom get to build the infrastructure from scratch. I’ve had the opportunity to do that with another company and I can say that it was a great experience. It’s nice to apply theory, however the biggest lesson that I’ve learned from that is that limitation drives solutions.

It’s limitations of memory and CPU that was responsible for better and more optimal code. It’s the limitation of power that drives the goal for constant decrease of wattage. It’s also the limitation of Oxygen that drives the decrease of server sizes to the reduction of  total carbon footprint. Then again limitation is only challenged by the abundance of creative solutions!

I was testing a DS5100 IBM Storage unit. If you don’t have an idea about storage units yet, download and emulator to get familiar with your storage. This also helps you to understand your SANMAN and the work involved when making config changes.

I have a RAID1+0 with 8 disks in it. It’s nothing fancy. Out of this array I’ve carved 2 LUNs.

As per hints and tips, know what you want to test.

The Test.

I wanted to test the sector alignment issue. (Google Jimmy May) and the general throughput and impact of segment size and allocation unit size on Windows and the SAN.

Before I get to the results I want to go through a few moving parts which I was reminded of while testing.

Data – Data lands on a drive. SQLIO creates a file which is uses to read or write. Simple ? (Nope). Depending on the amount of drives and other systems on the same RAID a file on a disk is not just a file on a disk. When your drive is formatted you will specify the allocation unit before formatting you also have to align your partitions. If you have Win 2008 you don’t have to worry about this. I aligned my storage 64k and it’s also the segment size I’ll be using on the SAN. The definition of the segment size on the SAN specifies it’s the number of IOs that will be issued to one disk before moving to the next disk in the stripe. The bigger the segment the less disks will be touched by each IO. You can verify you alignment with msinfo32 (checkout the offset in the component – storage – disk) for the relevant disk.


Did the alignment make a difference.

From my tests the align partition constantly out performed the non aligned segment. (duh)

Then again the flow of data contains MANY moving parts, to name a few.

HBA queue depth. We have 2x 8GB HBA controllers. We run WM Ware which also means that WM virtualise the HBA. From reading several articles on HBA performance they suggest (you have guessed it) TESTING! The default of 32 queue is usually not enough for a busy SQL system. The only way I currently translate this from a SQL side is the possible error message saying SQL waiting for IO for more than 15 seconds. I also see some timeouts on .net type applications and web services. a result that can be seen on the SAN is that the disks aren’t busy but SQL is waiting on IO. This means there is a bottleneck between your RAID and SQL i.e. possibly the HBA queue depth. Alternating the same test between two controllers definitely made a difference. This could be due to the capacity (8gb=400mb/s) or queue depth.

SAN Cache(Read). Our systems a primarily order and task management systems. I would have thought given that new data accounts for a slight portion of the workload and updating or modifying old data is the majority that the read caching would be beneficial to SQL. At this stage I have no conclusive proof that this is the case , based on what I can see from the cache performance (cache hit on the SAN not the OS counter that’s a different cache.) We are hitting cache fairly good. However from the logic in the SQL engine , SQL might be more effective to get this as it has the query plan at hand and can benefit from the read ahead mechanism.

SAN Cache (Writing). Since read caching can be configured per LUN this is quite nice. I’m wondering if data on the SAN has to be retrieved via cache similar to buffer cache for SQL. If this is the case I think the cache hit counter would be useless and there would be an overhead so I think this is false. Since I remove the read caching I now have more cache available for writing. If you do this make sure that you have a battery backup. If not your data goes pear during a power failure.

Alignment – Make sure your disks are aligned where possible. I’ve found that with the 64k alignment and 64k allocation unit I get the best performance. Note that at this stage the SAN Segment size is set to 64.

Fragmentation – I don’t have a conclusive answer on this yet, some SAN vendors say it will improve performance or decrease performance. I am definitely not one to run defrag on my SQL drives. I’ve read one article about the guy who takes his DB offline and copy it to another disk and attach it, this is done to defrag the DB. I have to claim this is not true as the OS sees an MDF. It has very little or no idea about the offset of pages so this claim might be only related to physical file fragmentation on the SAN and not the DB itself. It’s a novel idea if you can afford the downtime .

Filters – I believe the article about IO stalls also notes that you should try and refrain from installing antivirus applications on your SQL server. This is because the virus filters might not be designed to cope with your SQL throughput. Personally I cringe if I see antivirus on a SQL server. This should be done on another layer not on your SQL box! If you inherited someone else’s plumbing in this regard at least make sure that you exclude all the files related to SQL and the network activity.

Dynamic VS Simple disk (Windows) – In some of my tests it would appear that the simple disk performs better. Unfortunately as I was not measuring this at the time I can’t say for sure. From the documentation on the two I can’t see a performance difference. However there could be a reservation on the SAN of some sort that is created for the dynamic disk . This could perhaps have an influence.

Other activity – Note that during all of the testing my LUN is isolated. The entire RAID is not, neither is the controllers.The other bits and bobs in between such as a WM Host (Don’t start) are all shared components you will need to accept some anomalies in your testing due to the shared environment.

Exhaustive testing happens only on Christmas, when you have a laboratory to yourself. I’ve been fortunate enough to meet with Thomas Kejser and he explained how he reach the 1tb SSIS load in 20 mins. It’s a story worth reading about but he tells it like only a person with an immense passion for IT can.

There are many moving parts in the data flow. Try in narrow down each cog in the system with results before and after.

Happy tuning.

Note: I’m busy writing something to generate graphs for SQLIO (This has been done before in powershell but I’d like to try it for SQL). It saves a lot of time.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


This entry was posted on September 29, 2011 by in SQL Server and tagged , , , , .
%d bloggers like this: