Monthly Archives: September 2012

Capacity Specifications for SQL Server

Here is a useful link for all those who ever wondered about the maximum capacity of SQL Server. Here you’ll find a table specifying things like the number of partitions a table can have, the number of bytes of code a single stored procdure can have, but also fairly basic (and most important) stuff like the maximum row size, maximum index key size, maximum columns per table and so on.

So, add the link to your boookmarks. You’ll need it over and over again:
Maximum Capacity Specifications for SQL Server

Get number of rows per partition

How can you get the number of rows in each partition of your partitioned tables?
Here is a simple query to retrieve that data, including the boundary value for each partition:

     p.partition_id                             as PartitionID
    ,                                     as [Table]
    ,                                    as PartitionScheme
    ,                                    as PartitionFunction
    ,p.partition_number                         as [Partition]
    ,p.rows                                     as [Rows]
    ,prv.value                                  as Boundary
    --,pf.type_desc                               as BoundaryType
    ,case when pf.boundary_value_on_right = 1 
        then 'Right'
        else 'Left'
        end                                     as BoundarySide

    sys.tables t
    inner join sys.partitions p 
        on t.object_id = p.object_id 
        and p.index_id = 1
    inner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
    inner join sys.data_spaces ds on i.data_space_id = ds.data_space_id
    inner join sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
    inner join sys.partition_functions pf on ps.function_id = pf.function_id
    left outer join sys.partition_range_values prv 
        on pf.function_id = prv.function_id
        and p.partition_number = prv.boundary_id
    is_ms_shipped = 0   
    and exists (select 1 from sys.partitions p2
                where t.object_id = p2.object_id
                and partition_number > 1)
order by 
    ,p.partition_number desc

This will show you

  • Tables that have at least two partitions. If you also want to see the number of rows for all the other tables, remove the … and exits (…) from the where-clause.
  • Only one entry per partition. If you want to see an entry per partitioned index, remove and p.index_id = 1 from the join-condition.
  • %d bloggers like this: