SQL Server Blog

Based on my last post we now know how to create incremental statistics on a partitioned table and I mentioned that it was a massive improvement for performance of maintenance, but just how much?

I did a post a while ago about why you need to be careful with Sampled Statistics and therefore why a FULLSCAN is really the only reliable way to go… BUT there has always been the problem that a FULLSCAN update can take a VERY long time.



Oh, and yes… this is also to do with partitioning!!!

Yet more partitioning stuff… this time it’s the fact that we can rebuild an index on just a single partition of a table… and online. Now that’s handy!!!



We know that we can rebuild an index online but that’s a whole table and, if they’re big enough to be partitioned, then we really don’t want to incur the cost of effectively creating a copy of the table as this will hurt our disks and our transaction log.

To continue with partitioning there’s another good feature to consider which involves combining it with another Enterprise feature… compression.



What partitioning can offer is the ability to offer compression at a partition level and therefore allow you to get the best from your SQL Server from all angles in regard to disk space and performance.

This is something I struggled to put together the first time I needed it because partitioning uses internal tables with some very strange IDs and even stranger links between tables.

Partition Switching is an incredibly fast way to insert or delete data from a partitioned table and works very well in data warehousing environments, for example in which large overnight loads take place.

There are many reasons to use partitioning in order to make maintenance easier (stats and reindexing) and allowing for partition switching (fast loading and deleting of data), but there is another which is Partition Elimination.



Partition Elimination isn’t a reason for using partitioning, but it is a pleasant after-effect of the process. In a nutshell it can help with query performance.

Partitioning is an Enterprise only feature and, if you happen to be lucky enough to be using Enterprise, it can be an incredibly powerful and useful feature.



It’s also pretty simple to implement once you know what you’re doing… and therefore this is a quick guide.

This is actually an addendum to my last post as I referred to using SQL compression and provided a script in which you could compress a set of tables.



However, the problem is knowing which tables you should compress?



For example, which tables are good for compression and which are bad?