Performance blog

ara3n

Hello

Saw this blog. That talks about performance increase up to 40 percent. I haven't tried it yet. Has anybody else heard of this?

http://sqlblog.com/blogs/kevin_kline/ar ... -step.aspx

Slawek_Guzek

Hi ara3n,

According to my experience aligning partitions gives a little, sometimes not noticeable performance increase.

But, as I said this is my personal experience. I haven't seen and play with many hundreds SQL installations to say arbitrarily - yes it works and gives you up to ....%, or no it doesn't work. Of course it is a good practice to follow, and I'm always creating aligned partitions when installing SQL server.

Theoretically it gives you the most noticeable improvement when SQL is trying to read or write single extent (which is 64k in size), and if the partition allocation units are equal in size to single extent. If they are are misaligned then reading/writting single extent requires to touch two underlying clusters

If you are creating partition and don't putting any extra considerations on this (I mean partitioning it and format using defaults) then default cluster size is 4kB and the partition is misaligned (default alignment of first partition is 31.5kB on W2k3 servers). But it doesn't hurt much because in such a scenario you SQL always reads at least 16 clusters to read 1 extent, so due to misalignment it may want to read at most one more cluster - so 16 vs 17 clusters is not a big deal. Why not big deal to me ? Or your database file is de-fragmented and everything will be read in sequence, so 1. it will be read very fast, 2 OS and disk read-ahead capabilities will seed everything up, or the database file is fragmented and then system may try to intensively seek for each of cluster - again 16 vs. 17 seeks per read is not dramatical increase (despite of fact that itself 16 seeks per one extend read it is a catastrophe for a database).

Of course the world is not black and white, and it is not likely that you will have such a horrible fragmentations. Instead it is much more likely that the database file will not be fragmented at all or very little, because normally when you create a database the file is preallocated and most of it is continuous (or at least partially continuous)

You may play with cluster sizes and tries to make them bigger to speed thing up. If you force OS to format partition with 64 kb cluster to make 1 to 1 OS to SQL read/write operation it actually might cause problems.
But if your SQL box has enough RAM and it is not forced to flush pages between checkpoints then it tries to write as big block at checkpoint as possible. It depends on number of database files - more files smaller block size as the tables are spearheaded across he files and it is harder to construct a big block to write). Writing more than one extent at a time relaxes the strees with wrintting one extra cluster due to misalignment.

When it comes to reads - in 'normal' SQL environment it could possibly have bigger influence. In NAV we have most of the the time SELECT * which reads a lot of unnecessary data (unless course you're using ISEMPTY, or GET/FINDFIRST/FINDLAST), so reading one extra cluster, especially in sequence doesn't hurt much.

That's my thought on the subject. I've made some tests with different cluster sizes/alignments some time ago, and didn't find any simple magic trick (as claimed by mentioned blog) which will boost my SQL by 40% just like that.

As I said it is worth to align the partition as it is worth to follow all good practices, but IMHO it is not something critical which will boost or kill your SQL.

Regards,
Slawek

David_Singleton

ara3n wrote:

Hello

Saw this blog. That talks about performance increase up to 40 percent. I haven't tried it yet. Has anybody else heard of this?

This is not just for SQL, it basically applies to all disk intensive programs. This has generally been recommended by Navision since the DOS days of Navision. I actually had a developer working for me, that was obsessed with Disk alignment, and when he went on site, one of the first things he did was backup the database, format the server drives, realign then and then restore. I am sure it helped, but the improvements were never really measurable.

The blog covers some good points. BUT the comment made by this guy of 30-40% performance increase with one change like this is total nonsense. Like most hardware issues in Navision, its not a case of one magic fix that suddenly fixes everything. Its a case of balancing the issues one at a time, measuring their results, and balancing them out to a workable solution.

So IMHO, yes you should be aligning partitions, but don't expect it to make some huge performance increase.

David_Singleton

Slawek Guzek wrote:

According to my experience aligning partitions gives a little, sometimes not noticeable performance increase.
...

Re-reading your post, I think you said it much better than I did.

Sorry if it looks like I was duplicating what you said.

Slawek_Guzek

David Singleton wrote:

Sorry if it looks like I was duplicating what you said.

It doesn't. The word 'nonsense' as the comment of the blog's claim is a good summary which is lacking in my post

To me it looks like the author of the blog has just learned about partition alignment and was so excited that had written the blog to show his expertise, without rethinking the advantages carefully, and without checking the results in real life.

But who knows... Maybe he is right, and we just haven't seen enough..

Regards,
Slawek