主页 / dba / 问题 / 91118
Accepted
swasheck
Asked: 2015-02-05 14:09:42 +0800 CST

stats_column_id 和 index_column_id 不随聚集索引的物理顺序变化而更新

  • 772

除非我误解了该列的用途，否则以下代码表明聚集索引结构的更改不会更改stats_column_id列在sys.stats_columns DMV 中的序号位置 ( )。（在 AdventureWorks2014、AdventureWorks2008R2 测试）

select i.name, c.name, ic.column_id, ic.index_column_id
from sys.indexes i 
join sys.index_columns ic
    on i.object_id = ic.object_id
    and i.index_id = ic.index_id
join sys.columns c 
    on i.object_id = c.object_id
    and ic.column_id = c.column_id
where i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by ic.key_ordinal;

select sh.name,s.name, c.name, c.column_id, sc.column_id, sc.stats_column_id
from sys.stats s 
join sys.stats_columns sc
    on s.object_id = sc.object_id
    and s.stats_id = sc.stats_id
join sys.columns c 
    on s.object_id = c.object_id
    and sc.column_id = c.column_id
join sys.tables t 
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where s.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by sc.stats_column_id;

dbcc show_statistics('[Person].[BusinessEntityAddress]','PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID') with density_vector;

ALTER TABLE [Person].[BusinessEntityAddress] DROP CONSTRAINT [PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID]
GO

ALTER TABLE [Person].[BusinessEntityAddress] ADD  CONSTRAINT [PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID] PRIMARY KEY CLUSTERED 
(
    AddressID ASC,
    [BusinessEntityID] ASC, 
    [AddressTypeID] ASC
)
GO


select i.name, c.name, ic.column_id, ic.index_column_id
from sys.indexes i 
join sys.index_columns ic
    on i.object_id = ic.object_id
    and i.index_id = ic.index_id
join sys.columns c 
    on i.object_id = c.object_id
    and ic.column_id = c.column_id
where i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by ic.key_ordinal;

select sh.name,s.name, c.name, c.column_id, sc.column_id, sc.stats_column_id
from sys.stats s 
join sys.stats_columns sc
    on s.object_id = sc.object_id
    and s.stats_id = sc.stats_id
join sys.columns c 
    on s.object_id = c.object_id
    and sc.column_id = c.column_id
join sys.tables t 
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where s.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by sc.stats_column_id;

dbcc show_statistics('[Person].[BusinessEntityAddress]','PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID') with density_vector;

但是，密度向量表示索引/统计对象的前导列发生了变化。这是我的根本误解吗？如果是这样，我如何使用 DMV 找到统计对象的前导列？

经过测试的 SQL Server 版本：2008R2、2014

sql-server statistics

2 个回答

  1. Best Answer

    我在尝试重现其他人从 SQL Server 中的 sys.dm 视图检索索引信息的方式时遇到了同样的问题。我只是无法弄清楚索引中列的顺序。

    以下是我创建的脚本，用于确定给定表的任何给定索引中列的顺序：

    SELECT s.name                  AS Schema_name,
       o.name                  AS Table_Name,
       i.type_desc             AS Index_Type,
       i.name                  AS Index_Name,
       c.name           AS Table_Column,
       i.fill_factor           AS Indx_Fill_Factor,
       ic.key_ordinal          AS [Key_ordinal (IDX Column_Order)],
       ic.index_column_id      AS Index_column_id,
       stc.stats_column_id     AS Stats_Col_ID,
       -- Additional info for each joined table
       -- comment out what you don't need
       -- 2 lines at a time
       --
       -- '| table object -->', -- column seperator
       -- o.*,
       -- '| schema object-->', -- column seperator
       -- s.*,
       '| index info-->', -- column seperator
       i.*,
       '| sys index info -->', -- column seperator
       si.*,
       '| indx cols info -->', -- column seperator
       ic.*,
       '| tab cols info -->', -- column seperator
       c.*,
       '| idx stats info -->', -- column seperator
       st.*,
       '| idx stats columns info -->', -- column seperator
       stc.*
FROM   sys.objects             AS o
       JOIN sys.schemas        AS s
            ON  s.schema_id = o.schema_id
       JOIN sys.indexes        AS i
            ON  i.object_id = o.object_id
       JOIN sys.sysindexes as si
            ON  si.[id] = i.object_id
            AND si.indid = i.index_id
       JOIN sys.index_columns  AS ic
            ON  ic.object_id = i.object_id
            AND ic.index_id = i.index_id
       JOIN sys.columns        AS c
            ON  c.object_id = ic.object_id
            AND c.column_id = ic.column_id
       JOIN sys.stats          AS st
            ON  st.object_id = i.object_id
            and st.stats_id = i.index_id 
      JOIN sys.stats_columns  AS stc
      ON c.column_id = stc.column_id
      AND stc.stats_id = st.stats_id
      AND stc.[object_id] = o.[object_id]
WHERE  1=1 
     --and i.type <> 1 -- Exclude Clustered Indexes. 0 = Heap; 1 = Clustered Index, 2 = Non-Clustered Index
       AND s.name != 'sys' -- Exclude sys items
       and o.name = 'BusinessEntityAddress'
       AND i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
ORDER BY
       o.object_id,
       i.index_id,
       ic.key_ordinal

    sys.index_columns 表中的列key_ordinal是列在索引中的存储顺序。

    该表没有key_ordinal专栏sys.stats_columns。该列stats_column_id只是复制index_column_id它引用的对象的列。

    列的文章sys.stats_columns (Transact-SQL)的措辞略有不同stats_column_id：

    一组统计列中基于 1 的序号。

    ...并在列的文章sys.index_columns (Transact-SQL)中key_ordinal：

    键列集中的序数（从 1 开始）。

    我认为index_column_id(sys.index_columns) 和stats_column_id(sys.stats_columns) 是等价的，只有 sys.index_columns 表有一个排序列，即key_ordinal.

    • 2

  2. 从各方面来看，这可能是 sys.stats_columns DMV 中的错误行为。当通过父索引更新统计信息时，这似乎会导致问题。我认为这是由于在约束更改中更新统计信息的机制造成的。

    如果您手动创建统计信息，然后希望更改列，您必须先删除并重新创建，这会强制在相关的 DMV 中更新元数据。在您演示的操作中，似乎存在这样一种情况，即一旦发生更改，元数据在任何情况下（DBCC *、CHECKPOINT、服务器重启、通过父索引更改的统计信息更新等）都不会更新。从我的初始测试中，我只能找到一种情况，即元数据已正确更新，即删除和重新创建场景。

    您可以查看有关该问题的Connect 项目并酌情投赞成票。那里发布了一个解决查询的方法，但其机制基于将索引名称与统计名称匹配并利用索引元数据。

    • 1

相关问题