sys.partitions 行數嚴重錯誤 - 如何糾正這個問題？

查詢 sys.partitions 可以返回表的近似行數。

我注意到無論實際內容如何（即使對於空分區），這都會為所有分區返回相同的行數。

該表有一個聚集列儲存索引，並且幾乎所有列都創建了統計資訊。每次數據載入後，每天都會更新統計資訊。該表按日期分區。

sys.partitions 查詢：

   SELECT   convert(date, convert(varchar,rv.[value])) as partitionDate, p.rows as syspartitions_RowCount
       FROM       sys.tables t     
       join       sys.schemas  sc on sc.schema_id = t.schema_id        
       JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
       JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                                   AND     i.[index_id]          = p.[index_id]
       JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
       LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
       LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
       LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                                   AND     rv.[boundary_id]+1      = p.[partition_number]
       WHERE   p.[index_id] <=1
               and t.[name] ='tbl'
               and sc.name = 'temp'
               and convert(date, convert(varchar,rv.[value])) > '2016-05-31'
               order by convert(date, convert(varchar,rv.[value])), 
               t.[name]

表查詢：

                   select date, count_big(*) as real_count
               from temp.tbl
               where date > '2016-05-31'
               group by date
               order by date 

兩個查詢的範例結果：

嘗試使用sys.dm_db_partition_stats代替sys.partitions，如：

SELECT ObjectName = QUOTENAME(sc.name) + '.' + QUOTENAME(t.name)
   , RangeValue = rv.value
   , sys_partitions_RowCount = p.rows
   , sys_dm_db_partition_stats_row_count = ddps.row_count
FROM sys.tables t 
   INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id 
   INNER JOIN sys.partitions p ON t.object_id = p.object_id
   INNER JOIN sys.indexes i ON t.object_id = i.object_id
       AND p.index_id = i.index_id
   INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
   INNER JOIN sys.partition_schemes ps ON ps.data_space_id = ds.data_space_id
   INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
   INNER JOIN sys.partition_range_values rv ON rv.function_id = pf.function_id
       AND (rv.boundary_id + 1) = p.partition_number
   INNER JOIN sys.dm_db_partition_stats ddps ON t.object_id = ddps.object_id 
       AND p.partition_id = ddps.partition_id
WHERE p.index_id <= 1
   and t.name ='tbl'
   and sc.name = 'temp'
ORDER BY sc.name
   , t.name
   , rv.value;

對於 Azure SQL 數據倉庫，您需要使用sys.dm_pdw_nodes_db_partition_stats而不是sys.dm_db_partition_stats，即使它們包含相同的詳細資訊。

請注意，我刪除了該CONVERT(date,...)功能，因此此程式碼與所有分區方案兼容，而不僅僅是那些具有日期範圍值的分區方案。

在 SQL Server 的本地版本中，sys.partitions從內部表中獲取其行數，ALUCOUNT或者sys.sysrowsets，如果ALUCOUNT.rows為 NULL。的定義sys.partitions是：

CREATE VIEW sys.partitions AS
   SELECT rs.rowsetid AS partition_id
       , rs.idmajor AS object_id
       , rs.idminor AS index_id
       , rs.numpart AS partition_number
       , rs.rowsetid AS hobt_id
       , isnull(ct.rows, rs.rcrows) AS rows
       , rs.fgidfs AS filestream_filegroup_id
       , cmprlevel AS data_compression
       , cl.name AS data_compression_desc
   FROM sys.sysrowsets rs OUTER APPLY OpenRowset(TABLE ALUCOUNT, rs.rowsetid, 0, 0) ct
   LEFT JOIN sys.syspalvalues cl ON cl.class = 'CMPL' AND cl.value = cmprlevel

本地版本的sys.dm_db_partition_stats行數與內部表不同PARTITIONCOUNTS：

CREATE VIEW sys.dm_db_partition_stats AS
   SELECT c.partition_id
       , i.object_id
       , i.index_id
       , c.partition_number
       , c.in_row_data_page_count
       , c.in_row_used_page_count
       , c.in_row_reserved_page_count
       , c.lob_used_page_count
       , c.lob_reserved_page_count
       , c.row_overflow_used_page_count
       , c.row_overflow_reserved_page_count
       , c.used_page_count
       , c.reserved_page_count
       , c.row_count
FROM sys.indexes$ i 
CROSS APPLY OpenRowSet(TABLE PARTITIONCOUNTS, i.object_id, i.index_id, i.rowset) c

雖然兩者都sys.partitions應該sys.dm_db_partition_stats 有正確的行數，但我會更加信任PARTITIONCOUNTS內部表。

我遇到了同樣的問題，遇到了這個寶石。

在這篇文章中找到：https ://www.red-gate.com/simple-talk/sql/bi/azure-sql-data-warehouse-explaining-architecture-system-views/

SELECT pnp.partition_number,t.name,nps.[row_count],nps.[used_page_count]*8.0/1024 as usedSpaceMB,nt.distribution_id
FROM
  sys.tables t
INNER JOIN sys.indexes i
   ON  t.[object_id] = i.[object_id]
   AND i.[index_id] <= 1 /* HEAP = 0, CLUSTERED or CLUSTERED_COLUMNSTORE =1 */
INNER JOIN sys.pdw_table_mappings tm
   ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
   ON tm.[physical_name] = nt.[name]
INNER JOIN sys.pdw_nodes_partitions pnp 
   ON nt.[object_id]=pnp.[object_id] 
   AND nt.[pdw_node_id]=pnp.[pdw_node_id] 
   AND nt.[distribution_id] = pnp.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
   ON nt.[object_id] = nps.[object_id]
   AND nt.[pdw_node_id] = nps.[pdw_node_id]
   AND nt.[distribution_id] = nps.[distribution_id]
   AND pnp.[partition_id]=nps.[partition_id]
WHERE t.name='FactProductInventory'
ORDER BY usedSpaceMB DESC;

引用自：https://dba.stackexchange.com/questions/181740