BIDA0001 – SQL Server Manual Partitioning

0
203

If you are not familiar with the benefits of manually partitioning a data warehouse when using SQL Server, then this post is intended to give you a brief introduction as to the benefits of manual partitioning.

Because we were building sql server data warehouses before the database supported partitioning feature was introduced by Microsoft in SQL Server 2005 we had to implement manual partitioning.

When microsoft implemented database supported partitioning in SQL Server 2005 everyone moved to the database supported partitioning because there was very little extra cost involved.

Enterprise Edition could be purchased as a Client Access License and this was suitable for data warehouses.

All this changed in SQL Server 2012 when Enterprise Edition, the only edition with the partitioning feature, was changed to only be available as a per core license and only for USD17,000+.

USD17,000+ per core for the data warehouse database license is not affordable for a lot of Romanian companies.

So for those Romanian companies who wanted to use SQL Server for your data warehouse and did not know how to implement manual partitioning you were faced with a decision:

  • Buy SQL Server Enterprise Edition for USD17,000+ per core.
  • Implement your data warehouse on Standard Edition without partitioning the large tables.

In talking with a number of Romanian companies who implemented SQL Server as their data warehouse we have found that many of them have implemented Standard Edition with no partitioning of the largest fact tables.

Further, we have often found that the tables are stored in a single file group, often “Primary”, and often in tables with clustered indexes.

These three things all serve to slow down the performance of your data warehouse.

In SQL Server it is best to implement the large fact tables as follows:

  • Partition the tables, usually by month.
  • Place the different partitions for both indexes and data on different disks to spread the workload of queries for recent data over a number of disk drives.
  • To avoid the use of clustered indexes and place the data in to separate files using file groups to direct the placement of the data.

Though this does take some time and effort to set up correctly and to maintain over the years. The major benefits are:

  • Superior performance.
  • Ease of maintenance of the much smaller tables and indexes.
  • The much lower cost of the Standard Edition License either as a per core purchase or a Client Access License purchase.

Obviously, here in Romania, most companies would like to be able to pay the license fee for Standard Edition if they can get “nearly the same” performance as they could get from the Enterprise Edition.

Table partitioning is the single largest performance improvement feature available in Enterprise Edition that is not available in Standard Edition.

Partitioning also improves the ability to manage the growth of the data warehouse over time. As years and years of data builds up in the data warehouse partitioning means only the newer tables are updated. Tables and files are smaller. Indexes are smaller.

The benefits of implementing partitioning are widely documented.

What is not widely documented in how to implement manual partitioning on SQL Server. Certainly MicroSoft has no interest in explaining to you how this is done!

We have written a very detailed white paper to show you how to manually partition tables in SQL Server Standard Edition to get “almost the same” performance as the Enterprise Edition partitioning feature provides.

If you would like a copy of this detailed white paper please just put your best email address in to the form below and we will send you a link where you can download this document.