이주제를이해하는데필요한지식 SQL Programming 인덱스와실행계획확인 데이터압축 100 개념및소개수준 200 중간수준 300 고급수준 400 전문가수준 200 에 100 에 더하여 선수지식 더하여 능숙한사용 Level 300 불필요 기술적세부사항설명 경험, 아키텍

SQL Server 기반의 고성능-대용량 SAP 시스템 만들기 이랜드 시스템스 유규성

이주제를이해하는데필요한지식 SQL Programming 인덱스와실행계획확인 데이터압축 100 개념및소개수준 200 중간수준 300 고급수준 400 전문가수준 200 에 100 에 더하여 선수지식 더하여 능숙한사용 Level 300 불필요 기술적세부사항설명 경험, 아키텍처 지식필요

강사소개 2000.01 ~ 현재 : 이랜드시스템스 DBA / DA

80:20 법칙? 16 19,078 6,155 224,000

순서 시스템정보 성능관리 통계정보관리 인덱스관리 공간관리 Summary

시스템 정보

SAP System Landscape(Korea) DEV QAS PRD Retail ERP Netweaver 7.0 BW Netweaver BI 7.0 DR CRM CRM 5.0 Fashion ERP Netweaver 7.0 BW Netweaver BI 7.0 공통 HR Netweaver 7.0 # 각부문 ( 유통, 패션, 공통 ) 별 User Interface 는 SAP EP 로구축됨.

SAP System Landscape(China) Fashion ERP Netweaver 7.0 BW Netweaver BI 7.0 BPC BPC 5.0 DEV QAS PRD Standby Server ( 한국 ) # User Interface 는 SAP EP 로구축됨.

System 구성및현황 (Korea) Div. Appl. Type No. H/W model CPU RAM Disk OS DBMS DB Size ERP DB 1 APPL 5 Unisys ES7000/one Unisys ES7000/one 32 96 48 96 Hitachi USP Windows 2003 Datacenter IA64 Windows 2003 Datacenter IA64 SQL Server 2008 Enterprise 7 TB 5GB/Day 유통 BW DB 1 APPL 4 HP Superdome HP Superdome 16 32 24 48 Hitachi USP Windows 2003 Datacenter IA64 Windows 2003 Datacenter IA64 SQL Server 2008 Enterprise 6.5 TB CRM DB 1 HP DL580 16 32 APPL 3 HP DL580 24 48 EMC CX700 Windows 2003 Enterprise X64 Windows 2003 Enterprise X64 SQL Server 2005 Enterprise 0.7 TB 패션 ERP BW DB 1 APPL 5 DB 1 APPL 4 HP Superdome HP Superdome HP Superdome HP Superdome 34 96 40 80 28 88 34 64 Hitachi NSC55 EMC CX700 Windows 2003 Datacenter IA64 Windows 2003 Datacenter IA64 Windows 2003 Datacenter IA64 Windows 2003 Datacenter IA64 SQL Server 2008 Enterprise SQL Server 2008 Enterprise 16TB 8TB 12GB/Day 2.9 TB 공통 HR DB 1 APPL 2 HP Superdome HP Superdome 8 24 16 40 Hitachi NSC55 Windows 2003 Datacenter IA64 Windows 2003 Datacenter IA64 SQL Server 2005 Enterprise 0.4 TB

System 구성및현황 (China) Div. Appl. Type No. H/W model CPU RAM Disk OS DBMS DB Size 패션 ERP BW DB 2 Unisys 24 96 APPL 5 Dell Blade 40 80 DB 1 Unisys 24 96 APPL 5 Dell Blade 40 80 Hitachi USP Hitachi USP Windows 2003 Enterprise x64 Windows 2003 Enterprise x64 Windows 2003 Enterprise x64 Windows 2003 Enterprise x64 SQL Server 2008 Enterprise SQL Server 2008 Enterprise 1.1 TB 581 GB

통계 정보 관리

무슨문제일까요? select * from m where firstname = 'JHWHJUG' (1 개행이영향을받음 ) 테이블 'm'. 검색수 1, 논리적읽기수 146, 물리적읽기수 0 Rows Exec StmtText 1 1 SELECT * FROM [m] WHERE [firstname]=@1 1 1 --Clustered Index Scan(OBJECT:([Credit].[dbo].[m].[m_ident]), WHERE:([ Credit].[dbo].[m].[firstname]='JHWHJUG'))

Query execution steps Use Statistics

통계정보란? 옵티마이저가최적의실행계획을판단하는기준 데이터분포정보를통해 dencity를제공하고, 조회결과를예측하게함 sampling... Table 1 Table 2 Table 3 data Key value

통계정보조회하기 sys.sysindexes Statblob 칼럼에저장됨 sys.stats DBCC SHOW_STATISTICS

통계정보조회 DBCC SHOW_STATISTICS ( 테이블, 칼럼 통계명 ) 기본정보 Density vector Histogram

통계정보조회 Density d = 1 / (distinct row count) Predicate Cardinality estimate

통계정보조회 Histogram Captures distribution of values for a column Max number of steps = 200 Used for estimating cardinality of search predicates RANGE_HI_KEY RANGE_ROWS Histogram 의 step 기준칼럼의값 이전 RANGE_HI_KEY < ( row count) < 현재 RANGE_HI_KEY EQ_ROWS RANGE_HI_KEY 값에해당하는 (row count) AVG_RANGE_ROWS RANGE_ROWS / DISTINCT_RANGE_ROWS DISTINCT_RANGE_ROWS 이전 RANGE_HI_KEY < (distinct row count) < 현재 RANGE_HI_KEY

통계정보조회

통계정보자동생성 / 업데이트 Database option AUTO_CREATE_STATISTICS AUTO_UPDATE_STATISTICS AUTO UPDATE uses default sampling sp_autostats Display, change auto-stats setting (ON/OFF) Can disable auto-stats per index, table sp_createstats, sp_updatestats, sp_helpstats

통계정보자동업데이트시점 각통계정보에포함된칼럼의변경된비율 (= sysindexes.rowmodctrs / total rows) 값을기반으로판단 rowmodctrs >= 500 + 20%

통계자동업데이트 - sampling 샘플링비율 55,000 건 : 100 % 100 만건 : 6% ~ 10% 1000 만건 : 1.5 % 1 억건 0.45 % Row count

통계정보관리 대용량 DB 자동통계관리취약점 오래된통계정보 빈약한통계정보 통계정보업데이트많은비용 sampling... 통계정보수정시점 1. 통계정보미반영데이터 2. 자주사용하는데이터 Table 1 time data Key value

Best Practices Use auto create and auto update statistics ALTER DATABASE [DB 명 ] SET AUTO_CREATE_STATISTICS ON; ALTER DATABASE [DB 명 ] SET AUTO_UPDATE_STATISTICS ON; If needed, selectively use FULLSCAN statistics UPDATE STATISTICS WITH FULLSCAN

Best Practices Consider limiting use of multi-statement TVFs and table variables Avoid modification of stored procedure parameters in an SP prior to use in a query CREATE PROCEDURE GetRecentSales (@date datetime) WITH RECOMPILE AS BEGIN IF @date IS NULL SET @date = dateadd("mm",-3,(select MAX(OrderDATE) FROM Sales.SalesOrderHeader)) SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.salesorderid = d.salesorderid AND h.orderdate > @date END

Best Practices Consider more frequent statistics gathering for ascending keys Use asynchronous statistics update if synchronous update causes undesired delay ALTER DATABASE [Credit] SET AUTO_UPDATE_STATISTICS_ASYNC ON

Best Practices Consider filtered statistics for heterogeneous data, for partitioned tables Auto Stats OFF for tables VBHDR, VBDATA, VBMOD(SAP)

Best Practices 상황에맞는적절한수동업데이트작업 인덱스별샘플링비율관리 통계업데이트주기관리 행수 통계업데이트기준 1 만 ~200 만 rowmodctr >= 1.0% 200 만 ~500 만 rowmodctr >= 0.8% 500 만 ~1 천만건 rowmodctr >= 0.5% 1 천만건 ~ 변경 rows >= 5 만건 수정빈도높은최근데이터와 filtered statistics 에대한별도관리

Creating indexes Guidelines Check table statistics Only selective fields should be used Selective fields should take precedence, Avoid creating unselective index

Creating indexes Guidelines Limit the number of fields used in an index to as few as possible Reduce the number of indexes created per table to as slow as possible If >=, <=, >, < or between is used, indexes can be only used up to the column where these are used

Creating indexes Guidelines Check missing indexes (SAP) covering index, filtered index 활용 필요시적절한 clustered 인덱스로교체 운영 DB에서온라인으로생성후 CTS 전송 (SAP) index 사용정보를확인후불필요한인덱스는비활성화 (SAP) 인덱스힌트를지양하고세밀한통계정보로인덱스사용률높이기

공간 관리

효율적인대용량데이터관리 데이터보존정책 아카이빙 데이터압축 저장공간감소, 성능향상기대 압축백업 백업공간감소 백업및복원시간단축

데이터보존정책 데이터압축 아카이빙 [ 데이터저장소재배치 ] 자주사용되는데이터만최적의장소에보관하도록하여속도향상, 저장소비용절감 초고속저장소 가장빈번히사용되는데이터 고속대용량저장소 빈도낮으나, 필요시사용할수있도록유지하는데이터 저속대용량저장소 비즈니스상더이상필요없는데이터는제거 재무 3 년 재무 회계 3 년 회계 [ 프로그램최적화 ] 필요한데이터만처리하도록프로그램개선하여시스템속도향상 생산 영업 1 년 1 년 저장소이관 생산 영업 5 년, 10 년 저장소이관 영구보존 / 제거 물류 18 개월 물류 데이터생명주기 (Data Life Cycle)

데이터압축 행압축 4 바이트 1 바이트

데이터압축 페이지압축 : 접두사

데이터압축 페이지압축 : 사전

데이터압축사례 대상테이블선정 Table size 기준 Top 150 (80:20) 변경비율 < 5% (update+delete)/(slect single + select + insert) 압축방식 페이지압축선택 반복되는데이터패턴이많음 (SAP 특성 ) 순차저장, 조회위주의데이터 CPU 유휴자원준비

ST04 테이블크기상위 150 개추출

ST02 - 테이블변경비율확인 ST02> 세부분석메뉴 > 테이블호출통계

데이터 압축 결과

데이터압축결과 50% 의데이터공간절감 데이터증가속도둔화

데이터압축작업을할때.. 대용량테이블의압축 압축대상테이블의별도의통계관리 압축작업 통계업데이트작업간 blocking 발생 압축수행전 DB 복구모델을 Simple이나 Bulk logged 모드로변경할것 압축작업진행간모니터링및결과기록프로그램

SUMMARY

Summary 세밀한통계정보관리로똑똑한옵티마이저만들자 자동통계업데이트활성화 상황에맞는적절한수동업데이트작업 테이블파티셔닝활용 SQL Server의특성을활용한인덱스관리 선택도, 적절한클러스터드인덱스사용하도록변경 온라인인덱스생성 filtered index, covering index

Summary SQL Server의특성을활용한공간관리 보존기간내 : 압축, 보존기간이후 : 아카이빙 아카이빙작업에적절한인덱스추가 데이터압축시변경비율 (5% 이내 ) + Size 기준으로대상선정, 통계관리작업과조율 백업정책, 압축백업적용

감사합니다