출처 : 블로그
원문 : 그는 무슨생각을 하는가?
실제 생성한 인덱스 대 자동 생성되는 인덱스
[출처]: Windows & .NET MAGAZINE
2003년 06월호/ Id : 366
제가 인덱스를 생성하지 않았는데도 sysindexes 테이블에는 인덱스에 대한 많은 값이 입력된다는 것을 알았습니다. 그것들은 실제 인덱스가 아니라 SQL 서버의 옵티마이저가 자동적으로 생성한 통계라는 것을 알았습니다. 그런데 인덱스가 실제로 만들어 진 것인지 혹은 SQL 서버에 의해 자동적으로 생성된 것인지를 어떻게 알 수 있을까요?
초심자들은 종종 이러한 통계들이 sysindexes 테이블에 들어있기 때문에 인덱스라고 생각합니다. 또, 어떻게 그 통계값들이 그곳에 생성되었는지 궁금해 합니다. 기본적으로 SQL 서버는 컬럼에 인덱스가 없다면 그 컬럼에 대해 통계값을 생성합니다. 그래서 옵티마이저가 컬럼 내의 데이터 분산에 대한 통계적인 정보를 바탕으로 해서 좀 더 효과적인 쿼리 실행 계획을 선택할 수 있게 합니다. 실제 인덱스에서 자동으로 생성된 통계값을 구분하기는 쉽습니다. 자동 생성된 통계값은 접두사 _WA_Sys로 시작됩니다. (SQL 서버 2000과 7.0의 경우)
뿐만 아니라 INDEXPROPERTY() 함수의 IsAutoStatistics 속성값을 사용해서 실제 인덱스인지 자동 생성된 통계값 인지를 알아낼 수도 있습니다. SQL 서버 옵티마이저가 어떤 통계라도 생성해 낼 수 있게 두는 것이 바람직한 선택입니다. 저는 항상 데이터베이스를 위해 통계 자동 생성 옵션을 활성화 해두고 있습니다.
많은 사람들은 뻔히 보이는 결말을 잊어버립니다. 자동 생성된 통계의 현존은 실제 인덱스를 유리하게 합니다. [리스트 1]의 결과를 생각해 보십시오. Tempdb에서 Northwind의Orders 테이블을 복사해 오고 있습니다. 한 행을 가져온 다음 SQL 서버는 통계를 추가할 것인지를 확인합니다. 너무나 당연히도 이 테이블은 OrderId 컬럼에 인덱스가 없어서 SQL 서버는 자동으로 _WA_Sys_OrderID_58D1301D 이름의 통계를 생성했습니다. OrderId 컬럼의 통계의 현존은 Orders 테이블에 인덱스를 추가함으로 오는 이익을 알 수 있는 실마리가 됩니다.
[리스트 1] 자동 생성된 통계를 보여주는 코드
|
USE tempdb GO IF OBJECTPROPERTY(OBJECT_ID('dbo.orders'), 'IsUserTable')=1 DROP TABLE dbo.orders GO SELECT * INTO tempdb..orders FROM northwind..orders GO SELECT * FROM tempdb..orders WHERE orderid = 10248 GO SELECT * FROM tempdb..sysindexes WHERE id = object_id('orders') AND name LIKE '_wa_sys%' GO |
[리스트 2]의 쿼리는 적어도 하나 이상 자동 생성된 통계를 갖고 있는 데이터베이스 안의 모든 사용자 테이블의 자동 생성된 통계들을 카운트 해오고 있습니다. 함부로 지워버리지는 마십시오. 모든 통계가 실제 인덱스로 대체되어야 하는 것은 아닙니다. 저는 SQL 서버가 하나의 테이블에 50개 이상의 통계를 자동적으로 생성한 경우도 본 적이 있습니다. 말할 필요도 없이 이런 테이블들은 형편없는 인덱싱 전략은 갖고 있었습니다. 테이블을 빠르게 카운트 하는 것과 자동 생성된 통계의 숫자는 테이블이 필요로 하는 인덱스 작업을 알 수 있게 하는데 관련이 있는 것입니다.
[리스트 2] 테이블에 자동 생성된 통계 개수를 보여주는 코드
|
SELECT object_name(id) TableName ,count(*) NumberOfAutoStats FROM sysindexes WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 AND INDEXPROPERTY ( id , name , 'IsAutoStatistics' ) = 1 GROUP BY object_name(id) ORDER BY count(*) DESC |