열의 목적을 이해하지 않는 한 다음 코드는 클러스터형 인덱스의 구조를 변경해도 sys.stats_columns DMV stats_column_id에서 열의 서수 위치 ( )가 변경되지 않음을 나타냅니다 . (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
답변
모든 계정에서 이것은 sys.stats_columns DMV에서 버그가있는 동작 일 수 있습니다. 통계가 상위 인덱스를 통해 업데이트 될 때 문제를 일으키는 것으로 보입니다. 나는 이것이 제약 조건 변경에서 통계가 업데이트되는 메커니즘 때문이라고 생각합니다.
통계를 수동으로 작성한 다음 열을 변경하려면 먼저 메타 데이터를 해당 DMV에서 업데이트하도록 삭제하고 다시 작성해야합니다. 작업에서 변경이 이루어진 후 메타 데이터가 어떤 상황 (DBCC *, CHECKPOINT, 서버 다시 시작, 상위 인덱스 변경을 통한 통계 업데이트 등)에서 업데이트되지 않는 상황 인 것으로 나타났습니다. 초기 테스트에서 메타 데이터가 올바르게 업데이트되어 삭제 및 다시 작성 시나리오 인 경우가 하나만 발견되었습니다.
문제에 대한 연결 항목 을 보고 적절하게 투표 할 수 있습니다. 쿼리에 대한 해결 방법이 있지만 그 메커니즘은 인덱스 이름을 통계 이름과 일치시키고 인덱스 메타 데이터를 활용하는 것을 기반으로합니다.
답변
다른 사람들이 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열 key_ordinalsys.index_columns 테이블은 컬럼 인덱스에 저장되는 순서이다.
테이블에 대한 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 기반)
I가 있다고 생각한다 index_column_id(sys.index_columns) 및 stats_column_id(sys.stats_columns)가 서로 동등하고만이 sys.index_columns 테이블, 즉 오더링 열을 갖는다 key_ordinal.