Where does the law of diminishing returns come into play in regards to table partitions in SQL Server 2005?

Question

I'm wondering if anyone out there has run into a realistic max number of table partitions in sql server 2005. I know that the docs state that there is a limit of 1000 partitions per table, but I have a hard time believing that, without a lot of hacking and mucking about, that 1000 partitions would be all that usable.

Any help is appreciated.

Wayne E. Pfeffer

Answer 1

You would most likely script your operations, so if you NEEDED 1000 partitions, it wouldn't be a management problem. More partitions = smaller partitions. So if you have mega huge data, having more partitions could be useful.

However, when you start approaching these limits - like column # limits or other such maximums not related to hardware, you might want to start rethinking your design.

Answer 2

Start the other way - by asking if 2 partitions is too much. You want to find out if your queries will do partition elimination at all. SQL Server 2005's partition elimination doesn't perform too well, and if you don't have the partitioned fields as part of your WHERE clause, SQL Server often scans every partition anyway. For example, if I've got my SalesDetail table partitioned on CustomerID, this may not do partition elimination:

SELECT *
  FROM dbo.SalesReps sr
  INNER JOIN dbo.Customers c ON sr.SalesRepID = c.SalesRepID
  INNER JOIN dbo.Sales s ON c.CustomerID = s.CustomerID
  WHERE sr.SalesRepID = 10

Even though SalesRep 10 only has a couple of customers, the engine isn't smart enough to build the list of matching customers first, then only query the Sales partitions for those customers. It's gonna hit every partition. So I'd recommend making sure your partitioning scheme works at all for your SELECT queries, and then if so, run with it. The more tightly refined you can get your partitions, the faster your read queries will run when they eliminate partitions.