찐빵의 IT World

통계 - 선택도

통계 보기

DBCC SHOW_STATISTICS (테이블이름,인덱스이름) 

실행한 화면

2번째 표의 All Density : 전체 밀도

컬럼 전체의 밀도 즉 이것은  평균적인 밀도이며,  쿼리가 바인드변수 피킹이나, 파라메터 스니핑을 하지 못할 때 사용되는 밀도 입니다. 반면, 바인드변수 피킹이나 파라미터 스니핑이 가능한 상황이라면, histogram 을 이용하게 됩니다. 둘 중 어떤것을 이용 했으냐에 따라 내 쿼리의 선택도 판단은 달라 집니다. 데이터 선택도는 둘 중 무엇을 사용하고 내가 쿼리하는 데이터가 뭐냐에 따라 틀립니다.

밀도가 1에 가까워 지면 거의다 같은 데이터 이고, 밀도가 0에 가까워지면 전체 행에 같은 행이 거의 없다는 말입니다.

만약 밀도가 1이면, 모두 같은데이터이고, 밀도가 0.1 이고 전체행수가 10 이라면 같은 데이터가 (0.1 * 10) 1개 있다는 말입니다.

선택도가 0에 가까운 것이 밀도는 낮고 선택도는 좋다는 말입니다.

출처 및 더 자세한 정보 : http://www.sqler.com/558930

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = 'F:\TempDB\tempdb.mdf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = 'F:\TempDB\tempdb.ldf', SIZE = 1024MB, FILEGROWTH = 100MB)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = 'F:\TempDB\tempdb2.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = 'F:\TempDB\tempdb3.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = 'F:\TempDB\tempdb4.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = 'F:\TempDB\tempdb5.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = 'F:\TempDB\tempdb6.ndf', SIZE = 1024MB, FILEGROWTH = 100MB)
GO

이렇게 데이터파일 및 로그파일을 수정하면 원래 쓰던 파일은 남아있다. 잘 변경되었나 확인 후 삭제하여도 된다.

 파일삭제

ALTER DATABASE [TEMPDB] REMOVE FILE TEMPDEV6

 현재 사용중인 데이터파일 확인

select file_id, name, type_desc, physical_name, size, is_percent_growth, growth, max_size 
from sys.database_files 
order by name;

또는

EXEC SP_HELPFILE

SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id=DB_ID()

WHERE 절을 빼면 모든 데이터베이스의 기록을 볼 수 있다.

SQL Server 데이터베이스 엔진에서는 기본 데이터에 삽입, 업데이트 또는 삭제 작업을 수행할 때마다 인덱스를 자동으로 유지 관리합니다. 이러한 수정이 거듭되면 시간이 흐름에 따라 인덱스의 정보가 조각화되어 데이터베이스 내에 흩어지게 될 수 있습니다. 조각화는 키 값을 기준으로 하는 인덱스의 논리적 페이지 순서가 데이터 파일 내의 물리적 순서와 일치하지 않을 때 나타납니다. 심하게 조각화된 인덱스는 쿼리 성능을 저하시키고 응용 프로그램의 응답을 늦출 수 있습니다.

인덱스를 다시 구성하거나 다시 작성하여 인덱스 조각화 문제를 해결할 수 있습니다.

INDEX REBUILD : 인덱스를 다시 작성하면 이 인덱스가 삭제된 다음 다시 생성됩니다. 이렇게 하면 조각화를 제거하고, 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고, 인덱스 행을 연속된 페이지로 다시 정렬할 수 있습니다. ALL을 지정하면 테이블의 모든 인덱스가 단일 트랜잭션으로 삭제되고 다시 작성됩니다.

INDEX REORGANIZE : 인덱스를 다시 구성할 때는 최소한의 시스템 리소스가 사용됩니다. 이때는 왼쪽에서 오른쪽으로 표시되는 리프 노드의 논리적 순서에 맞도록 리프 수준 페이지를 물리적으로 다시 정렬하여 테이블 및 뷰의 클러스터형 및 비클러스터형 인덱스의 리프 수준에 대한 조각 모음을 수행합니다. 다시 구성 작업을 수행하면 인덱스 페이지도 압축됩니다. 이때 압축은 기존 채우기 비율 값을 기준으로 수행됩니다.

DBCC INDEXDEFRAG 대신 ALTER INDEX REORGANIZE를 사용하여 인덱스의 리프 수준 페이지를 논리적 순서로 다시 정렬합니다. 이 작업은 온라인 작업이므로 문이 실행 중일 때 인덱스를 사용할 수 있습니다. 또한 작업이 중단되더라도 이미 완료된 작업은 손실되지 않습니다. 이 방법의 단점은 데이터를 다시 구성하는 작업이 인덱스를 다시 작성하는 작업만큼 효과적이지 않다는 것과 통계가 업데이트되지 않는다는 것입니다.

인덱스 조각화 검색하기, 그 후에 비율에 따라서 맞는 작업을 수행

힙의 조각화 줄이기

힙의 익스텐트 조각화를 줄이려면 테이블에 클러스터형 인덱스를 만든 다음 해당 인덱스를 삭제

이렇게 하면 클러스터형 인덱스를 만드는 동안 데이터가 다시 구성되고 데이터베이스에서 사용할 수 있는 공간의 분포를 고려하여 최적화된다. 그런 다음 클러스터형 인덱스를 삭제하여 힙으로 다시 만들면 데이터가 이동하지 않고 최적의 분포를 유지

출처 : http://msdn.microsoft.com/ko-kr/library/ms189858(v=sql.120).aspx

 잠금 에스컬레이션(Lock Escalation)

많은 수의 미세 잠금을 더 적은 수의 큰 잠금으로 변환

동시성 경합 가능성 ↑, 시스템 오버헤드 ↓ 프로세스이다.

● 행 또는 인덱스 키 범위를 잠그는 경우 → 행 또는 키를 포함하는 페이지에도 의도 잠금 배치

● 페이지를 잠그는 경우 → 페이지를 포함하는 더 상위 수준의 개체에 의도 잠금 배치

잠금 에스컬레이션 임계값 - ALTER TABLE SET LOCK_ESCALATION

단일 Transaction-SQL 문이 분할 되지 않은 단일 테이블이나 인덱스에 대해 5,000개 이상의 잠금을 획득한 경우(ex 5천개의 행 잠금)

5천개 이상 잠기면 데이터베이스 엔진은 에스컬레이션을 진행시키려 한다.

 전체 잠금 호환성 매트릭스 

Microsoft SQL Server에서 사용할 수 있는 모든 잠금 모드의 호환성을 확인할 수 있는 표

 INDEX SCAN 과 INDEX SEEK

 SQL Server의 인덱스가 B(anlanced) Tree 구조로 처리

 Index Seek는 B-Tree 구조상의 Root 페이지부터 Leaf Level까지 검색 경로를 따라 수행되는 방법. Leaf Level을 제외한 상위 각 레벨에서 1페이지씩을 검색하게 된다.

 Index Scan은 Leaf Level의 첫번째 페이지부터 데이터 검색을 수행하는 방법. 일반적으로 얘기하는 Table Scan과 동일한 방법이지만, 그 대상이 Leaf Level의 인덱스 페이지라는 것이지요.
 
 그러나, Clustered Index의 경우 Leaf Level이 곧 Data Page이기 때문에, Index Scan이란 용어 대신 Clustered Scan이라는 용어로 표현이 됩니다.

 ONLINE = ON | OFF

인덱스 작업 중에 쿼리 및 데이터 수정에 기본 테이블과 관련 인덱스를 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다.

ON - 장기 테이블 잠금이 유지되지 않습니다. 따라서 기본 테이블에 대한 쿼리나 업데이트를 계속할 수 있습니다.

OFF - 테이블 잠금이 적용되어 인덱스 작업 중에 테이블을 사용할 수 없습니다.

ONLINE 옵션은 클러스터형 인덱스를 삭제할 때만 지정할 수 있습니다. 자세한 내용은 주의 섹션을 참조하십시오.

출처 : http://technet.microsoft.com/ko-kr/library/ms176118.aspx

 통계

- 검색 조건이 주어질 때 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 옵티마이저가 판단의 근거로 활용

- 통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질 때 이 컬럼 값의 히스토그램 및 관련정보를 이용하여 만들어진다.

Sysindex 테이블의 image형식의 statblog컬럼에 통계정보가 저장된다.

데이터베이스 옵션의 auto create statistics와 auto update statistics는 모든 데이터베이스에 대해 자동으로ture로 설정되며 이 옵션들이 설정되어 있으면 인덱스뿐만 아니라 인덱스가 없는 컬럼에 대해서도 필요할 경우 자동으로 통계가 만들어지고 일정 비율(전체행의 약20%)의 데이터가 업데이트 되면통계도 자동으로 업데이트 된다. 

밀도(density)는 1/(인덱스키들의 수)로 정의된다. 즉, 성별은 밀도가 0.5(남자, 여자)이지만 나의 주민등록 번호는 1/5000만 이 된다.(대한민국 인구수를 5천만으로 가정 했을 때).

이때 선택도(selectivity)는 주어진 키 값(또는 키 범위)에 해당하는 행수의 정도를 말하는데 행수가 적을수록 선택도가 높다. 그러나 전체 밀도가낮더라도 특정 키에 대한 선택도는 낮을 수도 있다.

[통계 보기]

DBCC SHOW_STATISTICS문을 사용하여 통계를 확인 할수 있다. 실습에서는 AdventureWorks의 [Person.Address]테이블을 사용 한다.

DBCC SHOW_STATISTICS([PERSON.ADDRESS], PK_ADDRESS_ADDRESSID)

Updated : 최근 통계가 업데이트된 날짜.

Rows : 전체 행 수

Rows Sampled : 샘플링된 행 수 (행 수가 많을 경우 일부를 샘플링하여 사용)

Steps : 히스토그램의 단계 수.고유 인덱스가 아닐 경우 단계수가 많아지며 최대 200단계 이다.

Average Key length : 컬럼 값의 평균 길이. Int형이므로 4가 표시 된다.

All density : 밀도. 밀도는 Steps의 값과 무관하며 일반적으로 (1/ 인덱스 키들의 수) 이다.

RANGE_HI_KEY : 각 단계(step)에서샘플링된 최대 값.

RANGE_ROWS : 각 단계에서RANGE_HI_KEY갑을 제외한 값들의 개수.

EQ_ROWS : 최대값의 개수.

DISTINCT_RANGE_ROWS : RANGE_ROWS 컬럼 값들중 고유한 값의 개수.

수동으로 통계 업데이트

UPDATE STATISTICS 테이블이름 

인덱스 정보 확인하기 

EXEC SP_HELPINDEX [PERSON.ADDRESS]

출처 : http://sqlmvp.kr/140165557766

 SINGLE_USER

이 항목에서는 SQL Server Management Studio 또는 Transact-SQL을 사용하여 SQL Server 2012에서 사용자 정의 데이터베이스를 단일 사용자 모드로 설정하는 방법에 대해 설명합니다. 단일 사용자 모드는 한 번에 하나의 사용자만 데이터베이스에 액세스할 수 있도록 지정하며 일반적으로 유지 관리 동작에 사용됩니다.

주의 사항