sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

Before You Upgrade to SQL Server Enterprise Edition

Issue 3 Number 4 April 2012

How to Save $20,000

The Enterprise Edition of SQL Server is much more expensive than the Standard Edition, about $20,000 more expensive per license. Microsoft can get this price because Enterprise Edition contains features that are essential to 24/7, enterprise-scale databases that are not available in Standard Edition.

Database applications that do not need these features normally use the less expensive Standard Edition. In my experience I have found that there are two enterprise features that most often drive the decision to buy the more expensive edition. They are on-line indexing and table partitioning.

If you are considering upgrading from Standard to Enterprise Edition because you need either of these features, you might want to read this article before spending the money. It could save you $20.000 or more.

While on-line indexing and table partitioning offer huge improvements in the management and performance of Enterprise-scale databases, there are some limitations that must be understood before committing to the greater expense of Enterprise Edition. Users who do not research these limitations sometimes find that after spending a lot of money, they do not get the expected benefits.

On-Line Indexing

In Standard Edition, tables are locked while being rebuilt and can’t be accessed by users. On-line indexing, available only in Enterprise Edition, allows you to rebuild indexes while users are still able to use the database. That means that truly 24/7 applications must use the Enterprise Edition to maintain their indexes to preserve good performance while still allowing users to access the database at any time.

However, there are a number of limitations to on-line indexing that must be understood before buying.

For example, XML indexes and spatial indexes cannot be rebuilt on-line. More importantly, the clustered index of any table that contains columns of xml, spatial or LOB datatypes cannot be rebuilt on-line either. These indexes must be rebuilt by locking the tables, as in Standard Edition.

Since a relatively small number of tables contain any of these special datatypes, this limitation will not be a significant problem for most databases. However, if you have large, active tables with these datatypes, this could be a show-stopper for you. You might want to think about this before you spend a lot of money on Enterprise Edition licenses.

Here is Microsoft’s complete list of indexes that CANNOT be rebuilt on-line. That means that the clustered index on tables with these datatypes cannot be rebuilt on-line either.

• XML indexes

• Spatial indexes

• Indexes on local temp tables

• A subset of a partitioned index (Only the entire partitioned index can be rebuilt online.)

• Clustered indexes if the underlying table contains LOB data types

• Nonclustered indexes that are defined with LOB data type columns

Nonclustered indexes can be rebuilt online if the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey columns.

Consider carefully whether any of these limitations will be a show-stopper for you.

Table Partitioning

Table Partitioning can work wonders in managing large tables. It can also improve query performance on large tables. However, the conditions under which partitioning will improve performance must be clearly understood.

A Quick Look at Partitioning

We have to explain a bit about partitioning in general before we can describe how performance gains are dependent on partitioning criteria. This will not be a complete description of a very complex subject and will not enable you to set up partitioning. But it will allow you to understand whether you will get a significant performance improvement by implementing table partitioning.

A large table can be separated into partitions by designating one of the table columns as the partition column. The indexes on the table will normally by partitioned (or aligned) on the same column.

Often the partition column is a date value. (Let’s use OrderDate as our example). For example, you might want to partition the table by months or years to separate the new, active data from the less active older data.

You want to do this because you know (or think you know) that the query engine understands which partitions contain the data requested by a query and will ignore the other partitions that do not contain any of the requested data. This is called Partition Elimination and is one of the principal ways that partitioning can improve performance.

However, Partition Elimination can only occur when your indexes are aligned on the partition column AND the query contains a filter on the partition column. e.g. “WHERE OrderDate BETWEEN ‘1/1/2012’ AND ‘3/1/2012’. If the query does not filter on the partition column, then the query engine might have to look in all the partitions and Partition Elimination will not occur.

You can see that you must understand your data and your code very well to be able to choose the right partition column for your table and your indexes. In some cases you may not find a key that will allow a lot of partition elimination to take place.

There are many more advantages to table partitioning than just partition elimination. But if you are counting on a significant performance improvement through partition elimination, it is a good idea to determine if that can happen in your case.

It makes a great deal of sense to identify the possible partition columns before you spend a lot of money on Enterprise licenses.

This has been a very brief glimpse at a very complex subject. If you would like to understand more about table partitioning, I recommend reading the Microsoft whitepaper at this URL.

Partitioning Whitepaper

Conclusion

It can sometimes be a great disappointment to discover the limitations of Enterprise Edition AFTER you have bought the licenses. If you are uncertain about achieving the results you want by upgrading to Enterprise Edition, send me an email or give me a call at 503.342.3422 ext 1.

There is no charge or obligation if you have a quick question that I can help you with.

As always, if you have questions or comments, give me a call or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

Limits of On-Line Indexing and Table Partitioning

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator