Max And Min Partition Values

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.

Related Posts

Rotating Out Partitions

Kendra Little explains that there are a couple of models available for partitioned table management: I recently received a terrific question about table partitioning: I’m writing up a proposal for my company to start partitioning a 2.5TB table. The idea/need is to control the size of the table while saving the old data. The old […]

Read More

Switching Partitions And Table Structure

Andrew Pruski demonstrates a gotcha when switching partitions between tables: When working with partitioning the SWITCH operation has to be my favourite. The ability to move a large amount of data from one table to another as a META DATA ONLY operation is absolutely fantastic. What’s also cool is that we can switch data into a non-partitioned table. Makes […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930