Category: Partitioning

Derik Hammer shows an example of “old-style” partitioning across servers:

SQL Server has a feature for partitioning tables and indexes. Partitioning can be implemented at many levels, however. You can create multiple tables for one logical data set, you can split the set into multiple databases, and you can even split it among different servers. Sharding is another term. It refers to partitioning data to horizontally scale out compute resources and storage.

There are different methods of handling sharding. Each of them need a central point of connection to handle querying the data on the other shards. This is typically called the control node. The method I am to discuss today is one where linked servers are used to connect the various shards.

This is useful for something like offloading old invoices which you rarely need to a separate server.  Derik also shows that the optimizer can, if it knows your partitioning rules, avoid unnecessary cross-server hits.

Marlon Ribunal has a script to split partitioned tables automatically:

So, let’s pretend it’s the month of April 2017 and this is the partition currently populated. Based on the query above, aside from the current partition bucket, we also have another available bucket month for May.

Say we want to maintain 3 available buckets at any given time. The next available bucket is May, so that means we need 2 more partitions to cover for June and July.

Read on for more, including some scripts that you can automate.

Erik Darling looks at the confusing mess that is SQL Server partitioning:

In the Chicago perf class last month, we had a student ask if partition level locks would ever escalate to a table level lock. I wrote up a demo and everything, but we ran out of time before I could go over it.

Not that I’m complaining — partitioning, and especially partition level locking, can be pretty confusing to look at.

If you really wanna learn about it, you should talk to Kendra — after all, this post is where I usually send folks who don’t believe me about the performance stuff.

Click through for that demo and explanation.

Eleni Markou shows how to partition tables in Postgres, SQL Server, and Google’s BigQuery:

When it comes to Microsoft SQL Server things are a bit different as this database system does not support dynamic partitions and so partitioning the table manually can be a huge maintenance issue.

That being said, in order to create a partitioned table a similar procedure to the one previously presented must be followed. This time we will create a monthly partition.

Read on for scripts for each.

Ken Kaufman explains a major performance problem when trying to get maximum (or minimum) values from a partitioned table:

Now that I rambled a bit you want to know why when using a partitioned table does grabbing the min and max of the primary key take sooooo long, and how do you fix it.  Theoretically you would expect SQL to perform the following steps in grabbing the Max Id

1.         Grab the Max Id from each partition using a seek
2.         Hold the results in  temp storage
3.         Get the Max ID from the  temp storage, and return that result.

 

However SQL doesn’t do that, it actually scans each partition and finds the max id after it has examined every record in each partition.  This is very inefficient, and could kill a query that depends on this value, as well as impact a busy server low on physical resources.    So what we need to do, is manually write the code to perform the steps that SQL Server should actually be doing.

Read on for one workaround Ken uses to deal with this inefficiency.

Kennie Nybo Pontoppidan shows us how to find the partition scheme for a particular table:

I needed to query SQL Servers metadata about partitioned tables, especially the column and the partition scheme used partitioning. The former is quite nicely documented in the SQL Server documentation (see link below), but the latter is not (yet). I have written the team about this, hopefully the documentation will be updated. Until then, I wrote this blog post to help others searching for an answer to this.

Click through for the script.