租户ID上每个表的SQL Server分区-使用的磁盘空间 [英] SQL Server Partition per table on Tenant ID - disk space used

问题描述

我们目前正在开发多租户Web应用程序。
此应用程序将所有数据存储在一个数据库中。

We are currently developing a Multi Tenant web application. This application stores all it's data in 1 single database.

租户的数据行始终带有租户ID。

Datarows for a tenant are always with the tenant id.

目前，我们正在考虑创建表分区，以租户ID作为分区键。这是有道理的，因为所有查询中的99.9％将把租户ID包括在where子句条件中。如果我理解正确，SQL Server查询将通过消除不包含分区键的表分区（在我的情况下为租户ID）来优化所有此类查询。

At the moment we are considering creating table partitions, with the tenant id as partitioning key. This makes sense because 99.9% of all queries will include the tenant id as a where clause criterium. If I understand correctly, SQL Server query optimize all such queries by eliminating the table partitions that not contain the partition key (in my case the tenant id).

对

但是我主要的问题是：$这种策略（严重的缺点，不使用文件组等，每个表允许的最大分区数...）将不胜感激。 b $ b我们要计算每个租户的磁盘使用情况。 SQL Server支持有关每个分区的磁盘使用情况的报告（标准报告）。
有人知道T-SQL可以使我获得给定租户ID的所有表分区的磁盘使用情况吗？

But my main question is this: We want to calculate the disk usage per tenant. SQL Server supports reporting on disk usage per partition (standard report). does anybody know of T-SQL that would allow me to get the disk usage of all table partitions for a given tenant ID?

推荐答案

这是Kendra Little进行分区的一个很好的入门。它应该可以帮助您回答是否要分区的问题。 http：//www.brentozar。 com / archive / 2012/03 / how-decide-if-should-use-table-partitioning /

Here's a good primer on partitioning by Kendra Little. It should help you answer the question of whether or not to partition. http://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/

我的一个建议是确保每个命中该表的查询都会在谓词中使用分区消除。

One recommendation I have is to make sure that every query hitting the table uses partition elimination in the predicate.

对于文件组，请记住分区方案会将分区映射到文件组。如果您想为每个租户做一个文件组，这可能会变得很复杂。

As for file groups, keep in mind that the partition scheme maps the partition to the filegroup. This could get complicated if you wanted to do 1 file group per tenant.

对于SQL Server 2005-2008 R2，一个表最多可以包含1,000个分区。在2012年，他们将限制增加到15,000个分区。如果您需要的还不止这些，请对分区值进行空间分配，然后由范围确定数据将移至哪个分区。

With SQL Server 2005 - 2008 R2, 1,000 partitions are the maximum that a table may contain. With 2012, they increased the limit to 15,000 partitions. If you need more than that, space the partition values out and let the range determine which partition the data will go.

这是一个表值函数，您可以用来导出空间分区使用情况：

Here's a table valued function you can use to derive space usage by partition:

CREATE FUNCTION tvfPartitionAllocationDetails (@schema_name sysname, @table_name sysname) 
RETURNS TABLE 
AS 
RETURN

select  f.data_space_id,
        f.NAME AS file_group_name,
        SCHEMA_NAME(t.schema_id) AS table_schema,
        t.name AS table_name,
        [HOBT?] = CASE pst.index_id WHEN 0 THEN 'HEAP' WHEN 1 THEN 'B-TREE' END,
        p.partition_number,
        ps.name AS partition_scheme_name,
        pf.name AS partition_function_name,
        partition_function_range = CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' WHEN 0 THEN 'LEFT' END,
        left_prv.value AS left_range,
        right_prv.value AS right_value,
        ISNULL(STR(CAST(left_prv.value AS BIGINT)), '-INF')
        + CASE WHEN pf.boundary_value_on_right = 0 THEN ' < '
               ELSE ' <= '
          END + 'X' + CASE WHEN pf.boundary_value_on_right = 0 THEN ' <= '
                           ELSE ' < '
                      END + ISNULL(STR(CAST(right_prv.value AS BIGINT)), 'INF') AS range_desc
        ,SUM(used_page_count) * 8 [TableSpaceUsed(KB)]
        ,(SELECT SUM(ISNULL(used_page_count,0)) * 8 FROM sys.dm_db_partition_stats  WHERE object_id = p.OBJECT_ID AND partition_number = p.partition_number AND index_id > 1) [NCIndexSpaceUsed(KB)]
        ,SUM(used_page_count) used_page_count
        ,row_count
from sys.dm_db_partition_stats pst
INNER JOIN sys.partitions p ON pst.partition_id = p.partition_id
JOIN    sys.tables t
        ON p.object_id = t.object_id
JOIN    sys.indexes i
        ON p.object_id = i.object_id
        AND p.index_id = i.index_id
JOIN    sys.allocation_units au
        ON p.hobt_id = au.container_id
JOIN    sys.filegroups f
        ON au.data_space_id = f.data_space_id
LEFT JOIN    sys.partition_schemes ps
        ON ps.data_space_id = i.data_space_id
LEFT JOIN    sys.partition_functions pf
        ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values left_prv
        ON left_prv.function_id = ps.function_id
           AND left_prv.boundary_id + 1 = p.partition_number
LEFT JOIN sys.partition_range_values right_prv
        ON right_prv.function_id = ps.function_id
           AND right_prv.boundary_id = p.partition_number
where pst.object_id = object_id(quotename(@schema_name) + '.' + quotename(@table_name)) 
    AND used_page_count > 0 
    AND pst.index_id IN (0,1)/*Remove Nonclustered index counts*/

GROUP BY f.data_space_id,
        f.NAME,
        t.schema_id,
        t.name,
        p.partition_number,
        ps.name,
        pf.name,
        pf.boundary_value_on_right,
        left_prv.value,
        right_prv.value,
        ISNULL(STR(CAST(left_prv.value AS BIGINT)), '-INF')
        + CASE WHEN pf.boundary_value_on_right = 0 THEN ' < '
               ELSE ' <= '
          END + 'X' + CASE WHEN pf.boundary_value_on_right = 0 THEN ' <= '
                           ELSE ' < '
                      END + ISNULL(STR(CAST(right_prv.value AS BIGINT)), 'INF') ,
        row_count,
        p.OBJECT_ID,
        pst.index_id;

然后您可以查询表值函数，如下所示：

Then you can query the table valued function like so:

SELECT * FROM dbo.tvfPartitionAllocationDetails('dbo','mytablename');

这假定不存在任何行或外页。如果您有它们，并且想要显示它们，可以轻松地将它们添加到函数中。

This assumes no out of row or lob pages. If you have those, and want to display them, they can easily be added to the function.

这篇关于租户ID上每个表的SQL Server分区-使用的磁盘空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！