SQL2k5 의 update statistics 문서화 되지 않은 옵션
SQL Server 2005의 update statistics에 문서화 되지 않은 추가적인 설정 값(pagecount,rowcount)이 추가 되었습니
다.
문서화 되지 않은 옵션이기 때문에 일반적인 서비스를 제공하는 환경에서는 사용하시면 다른 문제가 발생될 가능성
이 충분이 있습니다.
그러기에 테스트 용도로만 사용하시기 바랍니다.
아래는 SQL Server 2000 및 2005에 대해서 통계 정보에 대해서 쿼리의 실행계획이 어떻게 변경이 되는지 알아보고 있
습니다.
여기서 말하는 통계정보는 행수 와 페이지수를 말합니다.
/*==================SQL Sever 2005
Select @@version
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)
Mar 13 2006 11:20:51
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
*/
Use Tempdb
--Drop Table t
Create table t(a int identity , b int )
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
select id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
id indid dpages rowcnt rows
----------- ------ ----------- -------------------- -----------
1255675521 0 1 5 5
*/
SET STATISTICS PROFILE ON
--행이 많이 있지 않기때문에 일반적인 SORT작업으로 DISTINCT를 수행 하였음.
select distinct(b) from t
5 1 select distinct(b) from t
5 1 |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[t].[b] ASC))
5 1 |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
EstimateRows
5.0
5.0
5.0
--문서화 되지 않는 rowcount,pagecount 옵션을 사용해서 많은 행,페이지수를 설정함.
update statistics t with rowcount = 1000000000, pagecount = 90000;
--추가적인 시스템 통계정보가 생성됨.
select name,id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
name id indid dpages rowcnt rows
----------------------- ------ ----------- -------------------- -----------
NULL 1255675521 0 90000 1000000000 1000000000
_WA_Sys_00000002_4AD81681 1255675521 2 0 0 0
*/
--추가된 통계 정보 제거
DROP STATISTICS t._WA_Sys_00000002_4AD81681
--통계 정보에 많은 행이 있기 때문에 SORT가 아닌 hash로 연산 작업을 함.
select distinct(b) from t --option(recompile)
5 1 select distinct(b) from t option(recompile)
5 1 |--Hash Match(Aggregate, HASH:([tempdb].[dbo].[t].[b]), RESIDUAL:([tempdb].
[dbo].[t].[b] = [tempdb].[dbo].[t].[b]))
5 1 |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
--예상행 수
EstimateRows
31622.777
31622.777
31622.777
63245.555
63245.555
1.0E+9
/*==================SQL Sever 2000
Select @@version
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)
Mar 9 2006 11:38:51
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
*/
Use Tempdb
--Drop Table t
Create table t(a int identity , b int )
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
select id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
id indid dpages rowcnt rows
----------- ------ ----------- -------------------- -----------
811149935 0 1 5 5
*/
SET STATISTICS PROFILE ON
--역시 SORT로 DISTINCT작업을 수행함.
select distinct(b) from t
/*
5 1 select distinct(b) from t
5 1 |--Sort(DISTINCT ORDER BY:([t].[b] ASC))
5 1 |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
EstimateRows
5.0
5.0
5.0
*/
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
--UPDATE STATISTICS의 rowcount/pagecount 가 지원이 되지 않아 바로 업데이트
update sysindexes SET
dpages = 90000,
Rowcnt = 1000000000
Where id = object_id('t') AND Indid = 0
--해당 값이 변한 것을 확인
select name,id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
name id indid dpages rowcnt rows
----------------------- ------ ----------- -------------------- -----------
NULL 1255675521 0 90000 1000000000 1000000000
_WA_Sys_b_30592A6F 11149935 2 0 0 0
*/
--추가된 통계 정보 제거
DROP STATISTICS t._WA_Sys_b_30592A6F
--역시 처음과 같은 실행계획으로 풀리지 않았음.
select distinct(b) from t
/*
5 1 select distinct(b) from t
5 1 |--Parallelism(Gather Streams)
5 4 |--Sort(DISTINCT ORDER BY:([t].[b] ASC))
5 4 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([t].[b]))
5 4 |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
EstimateRows(예상 행)
2.721337E+8
2.721337E+8
2.721337E+8
1.0E+9
1.0E+9
*/
결론 :
아시다시피 SQL Server 2000, 2005는 통계정보 및 기반 정보를 기반으로 실행계획을 만드는 CBO를 가지고 있습니다.
이러한 통계 정보가 잘못 또는 부족할 경우 실행계획이 변경되는 것을 보다 직관적으로 볼 수 있으며, 이 통계정보가
얼마나 중요한지 확인 할 수 있습니다.
참고)
테스트 머신은 일반 1CPU를 가지고 있는 머신이며 위 Parallelism연산자는 SQL서버의 시작 옵션 중 -P4를 추가 하
여 논리적인 UMS를 4개로 만들어서 보여지고 있습니다.
물리적인 프로세스로 인해 병렬처리를 테스트 하지 못할때 필요한 옵션입니다. 그러나 성능에 대한 문제는...
참고 자료)
UPDATE STATISTICS undocumented options
http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx
SQL Server 2005의 update statistics에 문서화 되지 않은 추가적인 설정 값(pagecount,rowcount)이 추가 되었습니
다.
문서화 되지 않은 옵션이기 때문에 일반적인 서비스를 제공하는 환경에서는 사용하시면 다른 문제가 발생될 가능성
이 충분이 있습니다.
그러기에 테스트 용도로만 사용하시기 바랍니다.
아래는 SQL Server 2000 및 2005에 대해서 통계 정보에 대해서 쿼리의 실행계획이 어떻게 변경이 되는지 알아보고 있
습니다.
여기서 말하는 통계정보는 행수 와 페이지수를 말합니다.
/*==================SQL Sever 2005
Select @@version
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)
Mar 13 2006 11:20:51
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
*/
Use Tempdb
--Drop Table t
Create table t(a int identity , b int )
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
select id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
id indid dpages rowcnt rows
----------- ------ ----------- -------------------- -----------
1255675521 0 1 5 5
*/
SET STATISTICS PROFILE ON
--행이 많이 있지 않기때문에 일반적인 SORT작업으로 DISTINCT를 수행 하였음.
select distinct(b) from t
5 1 select distinct(b) from t
5 1 |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[t].[b] ASC))
5 1 |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
EstimateRows
5.0
5.0
5.0
--문서화 되지 않는 rowcount,pagecount 옵션을 사용해서 많은 행,페이지수를 설정함.
update statistics t with rowcount = 1000000000, pagecount = 90000;
--추가적인 시스템 통계정보가 생성됨.
select name,id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
name id indid dpages rowcnt rows
----------------------- ------ ----------- -------------------- -----------
NULL 1255675521 0 90000 1000000000 1000000000
_WA_Sys_00000002_4AD81681 1255675521 2 0 0 0
*/
--추가된 통계 정보 제거
DROP STATISTICS t._WA_Sys_00000002_4AD81681
--통계 정보에 많은 행이 있기 때문에 SORT가 아닌 hash로 연산 작업을 함.
select distinct(b) from t --option(recompile)
5 1 select distinct(b) from t option(recompile)
5 1 |--Hash Match(Aggregate, HASH:([tempdb].[dbo].[t].[b]), RESIDUAL:([tempdb].
[dbo].[t].[b] = [tempdb].[dbo].[t].[b]))
5 1 |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
--예상행 수
EstimateRows
31622.777
31622.777
31622.777
63245.555
63245.555
1.0E+9
/*==================SQL Sever 2000
Select @@version
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)
Mar 9 2006 11:38:51
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
*/
Use Tempdb
--Drop Table t
Create table t(a int identity , b int )
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
Insert into t valueS(@@identity)
select id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
id indid dpages rowcnt rows
----------- ------ ----------- -------------------- -----------
811149935 0 1 5 5
*/
SET STATISTICS PROFILE ON
--역시 SORT로 DISTINCT작업을 수행함.
select distinct(b) from t
/*
5 1 select distinct(b) from t
5 1 |--Sort(DISTINCT ORDER BY:([t].[b] ASC))
5 1 |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
EstimateRows
5.0
5.0
5.0
*/
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
--UPDATE STATISTICS의 rowcount/pagecount 가 지원이 되지 않아 바로 업데이트
update sysindexes SET
dpages = 90000,
Rowcnt = 1000000000
Where id = object_id('t') AND Indid = 0
--해당 값이 변한 것을 확인
select name,id,indid,dpages,rowcnt,rows from sysindexes where id = object_id('t')
/*
name id indid dpages rowcnt rows
----------------------- ------ ----------- -------------------- -----------
NULL 1255675521 0 90000 1000000000 1000000000
_WA_Sys_b_30592A6F 11149935 2 0 0 0
*/
--추가된 통계 정보 제거
DROP STATISTICS t._WA_Sys_b_30592A6F
--역시 처음과 같은 실행계획으로 풀리지 않았음.
select distinct(b) from t
/*
5 1 select distinct(b) from t
5 1 |--Parallelism(Gather Streams)
5 4 |--Sort(DISTINCT ORDER BY:([t].[b] ASC))
5 4 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([t].[b]))
5 4 |--Table Scan(OBJECT:([tempdb].[dbo].[t]))
EstimateRows(예상 행)
2.721337E+8
2.721337E+8
2.721337E+8
1.0E+9
1.0E+9
*/
결론 :
아시다시피 SQL Server 2000, 2005는 통계정보 및 기반 정보를 기반으로 실행계획을 만드는 CBO를 가지고 있습니다.
이러한 통계 정보가 잘못 또는 부족할 경우 실행계획이 변경되는 것을 보다 직관적으로 볼 수 있으며, 이 통계정보가
얼마나 중요한지 확인 할 수 있습니다.
참고)
테스트 머신은 일반 1CPU를 가지고 있는 머신이며 위 Parallelism연산자는 SQL서버의 시작 옵션 중 -P4를 추가 하
여 논리적인 UMS를 4개로 만들어서 보여지고 있습니다.
물리적인 프로세스로 인해 병렬처리를 테스트 하지 못할때 필요한 옵션입니다. 그러나 성능에 대한 문제는...
참고 자료)
UPDATE STATISTICS undocumented options
http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx