Início / dba / Perguntas / 91118
Accepted
swasheck
Asked: 2015-02-05 14:09:42 +0800 CST

stats_column_id e index_column_id não são atualizados com a ordem física do índice clusterizado alterada

  • 772

A menos que eu esteja entendendo mal a finalidade da coluna, o código a seguir indica que uma alteração na estrutura do índice clusterizado não altera a posição ordinal ( stats_column_id) da coluna no sys.stats_columns DMV. (Testado em 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;

No entanto, os vetores de densidade indicam uma alteração na coluna inicial do objeto de índice/estatística. Isso é um mal-entendido fundamental da minha parte? Em caso afirmativo, como encontraria a coluna inicial de um objeto de estatísticas usando DMVs?

Versões testadas do SQL Server: 2008R2, 2014

sql-server statistics

2 respostas

  1. Best Answer

    Eu estava tendo o mesmo problema ao tentar reproduzir a maneira como os outros recuperam informações de índice das exibições sys.dm no SQL Server. Eu simplesmente não consegui descobrir a ordem das colunas no índice.

    A seguir está um script que criei para determinar a ordem das colunas em qualquer índice para uma determinada tabela:

    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

    A coluna key_ordinalna tabela sys.index_columns é a ordem na qual as colunas são armazenadas no índice.

    Não há uma key_ordinalcoluna para a sys.stats_columnstabela. A coluna stats_column_idapenas replica a index_column_idcoluna do objeto que ela referencia.

    Há uma pequena diferença no texto do artigo sys.stats_columns (Transact-SQL) para a coluna stats_column_id:

    Ordinal baseado em 1 dentro do conjunto de colunas de estatísticas.

    ...e no artigo sys.index_columns (Transact-SQL) para a key_ordinalcoluna:

    Ordinal (baseado em 1) dentro do conjunto de colunas -chave .

    Eu acho que o index_column_id(sys.index_columns) e stats_column_id(sys.stats_columns) são equivalentes um ao outro e que apenas a tabela sys.index_columns tem uma coluna de ordenação, ou seja, key_ordinal.

    • 2

  2. Por todas as contas, este pode ser um comportamento com bug no sys.stats_columns DMV. Isso parece estar causando problemas quando uma estatística é atualizada por meio do índice pai. Acredito que isso se deva ao mecanismo com que as estatísticas estão sendo atualizadas em uma mudança de restrição.

    Caso você crie uma estatística manualmente e depois deseje alterar as colunas, primeiro você deve descartar e recriar, o que força os metadados a serem atualizados no DMV em questão. Na operação que você demonstrou, parece haver uma situação em que os metadados não são atualizados em nenhuma circunstância (DBCC *, CHECKPOINT, reinicialização do servidor, atualização de estatísticas por meio da alteração do índice pai etc.) após a alteração. Do meu teste inicial, posso encontrar apenas um caso em que os metadados são atualizados corretamente, que é o cenário de soltar e recriar.

    Você pode dar uma olhada no item Connect sobre o problema e votar conforme apropriado. Há uma consulta alternativa postada lá, mas seu mecanismo é baseado na correspondência do nome do índice com o nome da estatística e na utilização dos metadados do índice.

    • 1

relate perguntas