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

Discovering Partition Schemes

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 […]

Read More

Switching In Identity Columns

Kenneth Fisher shows a way of working around the difficulty of adding an identity column to an existing table: A friend had an interesting problem today. A really big table (multiple millions of rows) and no primary key. He then ran into an issue where he had to have one. The easiest thing is to […]

Read More

Categories

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