슬라이드 1

Size: px

Start display at page:

Download "슬라이드 1"

재순 태
6 years ago
Views:

1 BI Notes from the Field 대용량 DW 최적화 quaddimensions 권태돈

2 이주제를이해하는데필요한지식 100 개념및 소개수준 200 중간수준 300 고급수준 400 젂문가 수준 200 에 Level 300 선수지식 불필요 100에더하여기술적세부사항설명 더하여능숙한사용경험, 아키텍처지식필요

3 강사소개 이름 : 권태돈 소속 : Quad Dimensions 분야 : SQL Server 젂반 / OLAP / DW Business Intelligence(BI) 데이터분석 / 통계 활동 : 블로그운영 (blog.naver.com/tdon) 강의 / 세미나 : 씨앤토트, 삼성멀티캠퍼스 Microsoft TechNet 다수 On-Site 강의 이메일 : tdon@naver.com

5 차례 2008/R2 DW 좋아진것들 압축 Bulk Insert SSD 파티션 일반 매개변수강제화 보안 정밀한실행계획

6 Compression 데이터, 인덱스, 백업압축지원 * Buffer Pool 의페이지또한압축됨 행압축과페이지압축지원 행압축 : 데이터형식 (Type) 의최소화 페이지압축 : 반복되는용어의압축 (Data Dictionary) 제한사항 Unicode compression 은지원하지않음 (SQL Server 2008) SQL Server compresses in-row LOB data but not if the LOB data is stored out of row Enterprise edition only 압축률예상 : sp_estimate_data_compression_savings

7 Compression 성능요약 데이터크기 행압축시원본대비 25%, 페이지압축시원본대비 18% 로줄어듦 쿼리성능 프로필러를통해 CPU, Reads, Duration 값을관찰 Warm 쿼리의경우 Duration 측면에서이득없음 / 공갂측면이득 쿼리성능 (Cold cache) 쿼리성능 (Warm cache) 쿼리성능 (Cold cache) - SSD

Compression Buffer Pool Buffer Pool 의압축 Buffer Pool 의페이지또한압축된형태로저장된다 the index/data pages in buffer pool have compressed data and the

com/sqlserverstorageengine/archive/2008/01/27/compression-strategies.aspx select TOP 10 b.database_id, db=db_name(b.database_id),p.

dm_os_buffer_descriptors b on a.allocation_unit_id = b.allocation_unit_id inner join sys.partitions p on a.container_id = p.

8 Compression Buffer Pool Buffer Pool 의압축 Buffer Pool 의페이지또한압축된형태로저장된다 the index/data pages in buffer pool have compressed data and the SQL Server needs to uncompress the row/column, not the whole page, each time column/row(s) needs to be retrieved. select TOP 10 b.database_id, db=db_name(b.database_id),p.object_id,object_name(p.object_id) as objname,p.index_id,buffer_count=count(*) from sys.allocation_units a inner join sys.dm_os_buffer_descriptors b on a.allocation_unit_id = b.allocation_unit_id inner join sys.partitions p on a.container_id = p.hobt_id where b.database_id = db_id() group by b.database_id, p.object_id, p.index_id order by buffer_count desc Buffer Pool 크기 DMV 25% 압축과 Buffer Pool 크기 18%

9 Compression ShrinkFile SHRINKFILE 과조각화 줄어든파일공갂을 OS 로반환하기위한작업 MS 자료와달리파일크기가줄어들지않으면조각화만증가 (99.99%) DBCC SHRINKFILE ( Datafile, EMPTYFILE) 을이용해해결 기묘한현상발생 : 조각화 99.99% 0%, 0% 99.99%) ALTER INDEX REORGANIZE 파일작업과조각화

10 Compression Backup BACKUP WITH COMPRESSION 젂체백업테스트결과원본크기의 1/10로줄어듦 CPU Time 25% 증가 백업이끝나가는시점에파일크기가줄어듦 로그백업은압축률은떨어짐 ( 원본대비 78% 크기 ) 백업압축비교 복원속도비교

11 OLTP Workload Compression OLTP S, U Workload 비율에따라압축형식을결정 성능저하에주의 Table Savings Savings ROW % PAGE % S U Decision Notes Low S, very high U. RO T1 80% 90% 3.80% 57.27% ROW W savings close to PA GE T2 15% 89% 92.46% 0.00% PAGE Very high S T3 30% 81% 27.14% 4.17% ROW Low S T4 38% 83% 89.16% 10.54% ROW High U T5 21% 87% 0.00% 0.00% PAGE Append ONLY table T6 28% 87% 87.54% 0.00% PAGE High S, low U T7 29% 88% 0.50% 0.00% PAGE 99% appends T8 30% 90% 11.44% 0.06% PAGE 85% appends T9 84% 92% 0.02% 0.00% ROW ROW savings ~= PAGE T10 15% 89% % 0.00% PAGE Read ONLY table Table 1: Deciding what to compress Based on the metrics shown in Table 1, the customer decided to page-compress tables T2, T5, T6, T7, T8, and T10. All other tables in the database were row-compressed. Following this plan, the customer achieved 50 percent space savings, and approximately 10 percent increase in CPU utilization. Figure 2 shows the performance achieved by a customer on an OLTP application with high volumes of DML (INSERT, UPDATE, and DELETE) operations. The average response time of four different types of business transactions were measured with NONE, ROW, and PAGE compression.

12 Bulk Insert 최적화 Tempdb 최적화 Bulk Insert 작업시정렬작업이수행됨. Tempdb를빠른디스크에위치 (SSD) 미리정렬된데이터이용 작업시갂비교 : 9분 6초 6분 52초 / 25% 감소 Clustered Index + Non-Clustered Index 상황에서는사용불가 Partitioned Table에서는무조건정렬동작이발생 다른젂략필요 ORDER 힌트사용

13 Bulk Insert 최적화 계속 병렬처리 Bulk Insert 작업은 Single Thread로동작 입력되는데이터를나누어병렬로대상테이블에입력하도록구성 테스트를통해최대성능점을찾아야함 < 병렬처리로 Bulk Insert 하는 SSIS 패키지 > < 동시작업수에따른 ETL 소요시갂비교 >

Bulk Insert 최적화 계속 Compression 시갂 : 행압축시 4%,

14 Bulk Insert 최적화 계속 Compression 시갂 : 행압축시 4%, 페이지압축시 51% 증가 크기 : 행압축시 26%, 페이지압축시 19% 으로원본대비크기감소 압축된테이블로 Load Duration Reads CPU No Compression 83, ,341 76,776 Row Compression 87,760 47,591 84,047 Page Compression 169,929 35, ,922 압축된테이블로 Load시 Profiler 값 Load 이후압축

15 Solid State Disk - SSD 디스크성능비교 SQLIO IOMeter 쿼리성능비교 Query Speed: 동일한데이터를 SAN 과 SSD 에저정한다음 Select 쿼리실행 Tempdb 데이터는 SAN 에저장. Tempdb 는 SSD 에저장. 정렬 (Sort) 을쿼리의성능비교 Index Build 성능비교 (Tempdb 을사용하도록옵션지정가능 ) dbcc checkdb 성능비교 (tempdb 를사용함 ) ETL: 하루치데이터를 SSD 에저장후나중에 SAN 으로데이터를이동하는젂략테스트

16 HP - IO Accelerator 80GB NAND Flash, SLC(AJ876A) High IOPs performance (up to 100K IOPs) Fast read and writes (up to 800 MB/s) Low latency access to storage (~50 microseconds) SQL IO Results - 8 Threads - 16 Outstanding - 30 Seconds SSD block 8k SAN block 8k SSD block 256k SAN block 256k

17 SSD - tempdb Query Performance 결과 FactTable 하루치 Select: 약 400 만행 / 3,928MB( 젂체 ) / 2,745MB( 데이터 ) 정렬 (Sort) 작업을통해 Tempdb 사용유도 : 약 2,675MB Warm cache circumstance 사용량이적을때성능증가폭 : 30% ~ 70% 사용량이많을때성능증가폭 : 200% ~ 220% Tempdb 위치에따른쿼리성능비교

18 SSD - 쿼리성능 Query Performance 데이터파일과인덱스파일을 SSD에저장한다음성능을테스트 Cold cache circumstance 쿼리 ( 인덱스와데이터파일을모두읽도록강제함 ) 1,114% 데이터파일의위치에따른쿼리성능비교 SSD PhysicalDisk

시스템동작에관계없이사용할수있는메모리크기에비해상대적으로큰데이터베이스에대해 DBCC CHECKDB 를실행하면 tempdb 데이터베이스로스풀링이이루어집니다 -

19 SSD - 유지관리 DBCC CHECK DBCC CHECKDB 의내부데이터중일부를 tempdb 데이터베이스로스풀링 tempdb 데이터베이스는디스크에위치하므로데이터가디스크에서기록될때 I/O 작업의병목상태로인해성능이저하됩니다. 시스템동작에관계없이사용할수있는메모리크기에비해상대적으로큰데이터베이스에대해 DBCC CHECKDB 를실행하면 tempdb 데이터베이스로스풀링이이루어집니다 - BOL Database Size: 82,944 MB Max memory 102,400 MB / 64k Temp alloc Max memory 1,024 MB / 884MB Temp alloc DBCC CHECKDB 시갂비교 Perfmon 에서 SSD

20 SSD - 인덱스 CREATE INDEX. WITH (SORT_IN_TEMPDB = ON) 다른사용자가해당데이터베이스를사용하고별도의디스크주소를액세스하는경우에도읽기및쓰기의젂반적인패턴은 SORT_IN_TEMPDB 가지정되는것이지정되지않을때보다좀더효율적으로나타남 Index Size: 359MB / Temp alloc: 368MB Cold cache circumstance ALTER INDEX. REBUILD WITH Index Size: 8,264MB / Temp alloc: 320MB 인덱스생성시갂비교 인덱스재생성시갂비교

SSD Backup BACKUP DATABASE 저장소위치와압축여부에따른속도변화관찰 Database Size: 50G 속도는데이터베이스파일이위치한저장소와백업대상의저장소에종속적인결과를보임 데이터베이스느린곳에위치 : 어디에백업하든느림 데이터베이스빠른곳에위치 &

21 SSD Backup BACKUP DATABASE 저장소위치와압축여부에따른속도변화관찰 Database Size: 50G 속도는데이터베이스파일이위치한저장소와백업대상의저장소에종속적인결과를보임 데이터베이스느린곳에위치 : 어디에백업하든느림 데이터베이스빠른곳에위치 & 백업빠른곳에 : 가장빠름 데이터베이스빠른곳에위치 & 백업느린곳에 : 중간빠름 RESTORE DATABASE 압축젂백업파일크기 : 11.1G SSD 와 SAN 에각각복원 (Restore) 후시갂비교 백업파일은물리적으로분리된위치 (SAN) 에졲재함 복원속도비교

22 파티션전략 Monthly(Non-Daily) Partition 장점 단점 관리이슈의감소 ETL 작업중 Load 시갂이월말로갈수록증가 일단위의데이터삭제시상당한시갂소요 Daily Partition 장점 단점 ETL 작업중 Load 시갂이일정하며, 그시갂도획기적으로줄일수있음 잘못된데이터에대한삭제시갂단축 (1 초 ) 관리이슈의증가 파티션 Merge 에는많은시갂이필요 ( 하루치 Merge: 12~25 분 ) Daily One Partition Monthly

파티션전략 최소단위파티션 Daily Partition 젃차 1. Staging Table 테이블생성 2. Switching될 Partition과동일한파일그룹으로 Clustered Index 생성 3. Bulk Insert 4. Non-Clustered 생성 5. Check Constraint 생성 6.

23 파티션전략 최소단위파티션 Daily Partition 젃차 1. Staging Table 테이블생성 2. Switching될 Partition과동일한파일그룹으로 Clustered Index 생성 3. Bulk Insert 4. Non-Clustered 생성 5. Check Constraint 생성 6. Switching to partition Monthly Daily 항목 시갂 ( 초 ) 항목 시갂 ( 초 ) Bulk Insert 901Bulk Insert 306 Create Index 122 Create PK 11 Switching 0 합계 항목별 Load 시갂 ( 초 ) 50% 하루치데이터 Load 시갂 ( 초 )

24 바뀐 ETL 구성 스테이징테이블생성, Switch To 과정이추가됨 수행시갂 50% 감소 1 SELECT * INTO dbo.stgfacttable FROM dbo.facttable WHERE 1=0 CREATE CLUSTERED INDEX CIDX_StgFactTable on StgFactTable (reg_dt) WITH (DATA_COMPRESSION=ROW) ON FG_ Data 1 2 ALTER TABLE StgFactTable ADD CONSTRAINT PK_StgFactTable PRIMARY KEY (ul_key, rg_reg_dt) WITH (DATA_COMPRESSION=ROW) ON FG_ Index CREATE INDEX IDX_StgFactTable02 ON StgFactTable (s_key) WITH (DATA_COMPRESSION=ROW, FILLFACTOR=100, SORT_IN_TEMPDB=ON) ON FG_ Index ALTER TABLE dbo.stgfacttable ADD CONSTRAINT FK StgFactTable FOREIGN KEY (scd_no_key) REFERENCES dbo.sdimuser (sdu_key) ALTER TABLE dbo.stgfacttable WITH CHECK ADD CONSTRAINT CK_StgFactTable CHECK (rg_reg_dt >= ' ' AND rg_reg_dt < ' ') ALTER TABLE dbo.stgfacttable SWITCH TO dbo.facttable PARTITION $partition.pdaily_range (' ) 2

파티션전략 압축과파티션병합 병합 (Merge) 되는파티션의속성유지 병합당하는파티션속성소멸 Compression setting of t he destination partition What happens to the data moving in from the source to the destination partition NONE The incoming

25 파티션전략 압축과파티션병합 병합 (Merge) 되는파티션의속성유지 병합당하는파티션속성소멸 Compression setting of t he destination partition What happens to the data moving in from the source to the destination partition NONE The incoming data is decompressed duri ng merge ROW The incoming data is row-compressed d uring merge - Heap: The incoming data is row-comp ressed during merge PAGE - Clustered index: The incoming data is page-compressed during merge

26 정밀한실행계획 정말좋아지나? Cpu 비용은얼마나증가하나? 컴파일속도 / 시갂은얼마나증가하나? 젂체쿼리의성능은빨라지나? Trace flag 2301 Trace flag 2301: Enable advanced decision support optimizations Trace flag 2301 enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of large data sets. You can turn on trace flag 2301 at startup or in a user session. When you turn on trace flag 2301 at startup, the trace flag has global scope. When you turn on trace flag 2301 in a user session, the trace flag has session scope. I tried to use this traceflag after reading the High Performance article from microsoft. I find it very useful on individual queries where you are accessing large tables. I have a base table of just over 320 million records and under certain circumstances I have seen improvements of over 1000%

64bit Enterprise Edition The computer must have 8Gb or more of

27 Large Page Extension Buffer Pool 이저장되는물리적메모리의페이지크기를 2MB 로늘리고연속된공갂으로할당 Default: 4k 다음조건을충족하면자동으로 Enabled SQL Server 64bit Enterprise Edition The computer must have 8Gb or more of physical RAM The Lock Pages in Memory privilege is set for the service account

Linked Server Kerberos Authentication in SQLServer 경우에따라

사용자에대한자격증명을젂달하여다른 SQL Server 인스턴스에연결할수있게 SQL Server

위임을사용하면다른인스턴스와통싞할때 Windows 사용자가 Windows 인증을사용하여연결된 SQL

28 Linked Server Kerberos Authentication in SQLServer 경우에따라 NTML, Kerberos 프로토콜을사용 SQL Server 및 Windows 는인증된 Windows 사용자에대한자격증명을젂달하여다른 SQL Server 인스턴스에연결할수있게 SQL Server 인스턴스에연결된클라이언트를활성화하도록구성할수있습니다. 이러한정렬을위임이라고합니다. 위임을사용하면다른인스턴스와통싞할때 Windows 사용자가 Windows 인증을사용하여연결된 SQL Server 인스턴스가해당사용자를가장합니다. 특정연결된서버에대한특정로그인에자체매핑이사용되는경우분산쿼리에는보안계정위임이필요합니다

29 감사합니다.

Microsoft PowerPoint - 1_3_DBA_SQL_tuning

Microsoft PowerPoint - 1_3_DBA_SQL_tuning 아주특별한 SQL 튜닝 씨퀄로 김정선 아주특별한 Level 400 100 개념및소개수준 200 중간수준 300 고급수준 400 전문가수준 선수지식불필요 100 에더하여기술적세부사항설명 200 에더하여능숙한사용경험, 아키텍처지식필요 SINCE 2002 SQL Server Specialist Member & Leading PASS Korea Member Microsoft