Welcome to EMC Consulting Blogs Sign in | Join | Help

Claypole's World - The SQL Server Side

Concerning development of bespoke applications and database administration.

SQL2K5 Enterprise Edition Mini Feature 1 : -E Startup Option

Are you using multiple files? Are you set up for proportional fill? Is it a warehouse?  Are you concerned about extent fragmentation? You should be.  Extent fragmentation upsets your lovely read-ahead and results in smaller I/O.

Let me explain.  When SQL Server allocates - it does so on a one extent at a time.  If multiple data files are involved in your database then this will be performed in a round-robin style to ensure you spread the data across the files proportionally (assuming you have met the rules for prop fill).  Now if you have multiple objects requesting extents then you can quickly see that it is unlikely that one table is going to be built with contiguous extents and is therefore likely to be spread out across the files. In an OLTP environment extent fragmentation is not an issue and can actually be a good thing as it can mean that the load is spread out across your disk subsystem.  However, in a data warehouse it is a right pain.  The reason being is that the read ahead can only operate against contiguous areas of disk and this fragmentation is almost certainly going to put the mockers on that.  More specifically it will impact the size of the read-ahead and so consequently the efficiency of the I/O. So what can you do?

Well if you have x64 Enterprise Edition (which if you are doing any warehousing of significance I hope you are) then help is at hand.  However, check that out - Enterprise Edition and 64 bit only - niche or what.  Nice start for the mini-series though :-).

-E (note the case sensitivity -e is the path for the error log) in your startup options changes the allocation from 1 extent i.e. 64kb to 4 extents i.e. 256kb.  Now this still isn't the biggest I/O you could have received (more on that later) but it's better than a poke in the eye with a stick coated in something brown and fragrant.

There is a support KB article on this which can be found here.

N.B. This option isn't currently documented in BOL.  However, I have fed this back to the documenters and they have assured me that it will be in the next release of BOL. 

Cheers, James

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

 

Claypole's World - The SQL Server Side said:

I thought it might be fun to do series of posts on features that are exclusive to enterprise edition

March 16, 2008 20:44
 

Claypole's World - The SQL Server Side said:

One of my focuses at the moment is the work that Microsoft have done with various hardware providers

April 25, 2010 07:16

Leave a Comment

(required) 
(optional)
(required) 
Submit

About James.Rowland-Jones

James is an Advisory Practice Consultant with EMC Consulting. He works primarily with SQL Server technologies in architecture, development and administration capacities. He also co-ordinates our Internal SQL Server Community.

View James Rowland-Jones's profile on LinkedIn

Powered by Community Server (Personal Edition), by Telligent Systems