SQL Server 2008 & R2 New Features 에이디컨설팅하성희
이주제를이해하는데필요한지식 T-SQL Programming SQL Server Administration 100 개념및 소개수준 200 중간수준 300 고급수준 400 전문가 수준 200 에 100 에 더하여 선수지식 더하여 능숙한사용 Level 300 불필요 기술적세부사항설명 경험, 아키텍처지식필요
강사소개 현재 : 에이디컨설팅대표컨설턴트 옥션 DBA 팀장 마이크로소프트기술지원부 SQL Server 기술지원엔지니어 포스데이타 SYBASE 사업팀 DB 엔지니어 포스데이타 POSCO 기술운영팀 DBA
내용 데이터형식 FILESTREAM T-SQL 프로그래밍 개체종속성 압축 스파스열 리소스관리자 정책기반관리 감사 변경내용추적 변경데이터캡처 TDE 필터링된인덱스및통계
새로운데이터형식 날짜및시갂데이터형식 DATE TIME DATETIME2 DATETIMEOFFSET HIERARCHYID 공갂데이터형식 GEOMETRY GEOGRAPHY 사용자정의테이블형식
날짜및시갂데이터형식 새로운데이터형식의홗용 날짜만필요한경우 : 예 ) 생일 시갂만필요한경우 1753년이젂의이력일자관리가필요한경우 보다정밀한시각정보가필요한경우
날짜및시갂데이터형식 데이터형식형식범위정확도 TIME DATE SMALLDATETIME DATETIME hh:mm:ss[.nnnnnnn] YYYY-MM-DD YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss[.nnn] 00:00:00.0000000 ~ 23:59:59.9999999 0001-01-01 ~ 9999-12-31 1900-01-01 ~ 2079-06-06 1753-01-01 ~ 9999-12-31 저장소크기 ( 바이트 ) 100 나노초 3 ~ 5 1 일 3 1 분 4 0.00333 초 8 DATETIME2 YYYY-MM-DD hh:mm:ss[.nnnn nnn] 0001-01-01 00:00:00.0000000 ~ 9999-12-31 23:59:59.9999999 100 나노초 6 ~ 8 DATETIMEOFFSET YYYY-MM-DD hh:mm:ss[.nnnn nnn] [+ -]hh:mm 0001-01-01 00:00:0 0.0000000 ~ 9999-12-31 23:59:5 9.9999999(UTC) 100 나노초 8 ~ 10
HIERARCHYID 데이터형식 계층적트리구조의데이터처리에대한편의성제공 HIERARCHYID 데이터형식의도입으로다음과같은구조의데이터를보다쉽게처리가능 조직구조 파일시스템 프로젝트태스크집합 웹페이지갂링크그래프
HIERARCHYID 데이터형식 예제 : CREATE TABLE HR.EmpOrg ( OrgNode hierarchyid, EmployeeID int, LoginID nvarchar(50), ManagerID int, Title nvarchar(100), HireDate date CONSTRAINT PK_NewOrg_OrgNode PRIMARY KEY CLUSTERED (OrgNode) ); GO -- 직계상사가 david0 인직원반홖 SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, EmployeeID, LoginID FROM HR.EmpOrg WHERE OrgNode.GetAncestor(1) = ( SELECT OrgNode FROM HR.EmpOrg WHERE LoginID = 'adventure-works\david0'); GO
HIERARCHYID 데이터형식 HIERARCHYID 형식을위한메서드 GetAncestor GetDescendant GetLevel GetRoot IsDescendantOf Parse Read GetReparentedValue ToString Write
공갂데이터형식 SQL Server 2008 에서두가지공갂데이터형식지원 GEOMETRY: 2 차원평면위의점, 선, 다각형등을표현 ( 일반적으로 XY 좌표정보저장 ) GEOGRAPHY: GPS 위도및경도좌표와같은타원 ( 둥근지구 ) 데이터저장.NET CLR( 공용언어런타임 ) 데이터형식으로구현됨
사용자정의테이블형식 테이블형식 ( 및테이블값매개변수 ) 지원 CREATE TYPE UDT_SSN FROM char(13) NOT NULL; GO CREATE TABLE t1 ( MembID SSN ); GO int, UDT_SSN, + CREATE TYPE UDT_Order AS TABLE ( pos int NOT NULL PRIMARY KEY, orderid int NOT NULL UNIQUE ); GO DECLARE @T AS UDT_Order; INSERT INTO @T (pos, orderid) VALUES (1, 10248), (2, 10250), (3, 10249); SELECT * FROM @T; GO
사용자정의테이블형식 테이블구조의사용자정의형식 저장프로시저와함수의테이블반홖매개변수로사용가능 저장프로시저또는함수또는일괄처리에서사용가능 기본키, UNIQUE, CHECK 제약조건생성가능
FILESTREAM LOB 데이터를파일시스템에파일로저장하면서데이터베이스의일부로처리가능 어플리케이션에서다양한스트리밍 API 와파일시스템의성능을홗용할수있으면서, 트랜잭션일관성유지가능 Transact-SQL 문으로 FILESTREAM 데이터를삽입, 업데이트, 쿼리, 검색, 백업가능 FILESTREAM 데이터처리 T-SQL Win32 API 서버구성옵션 'filestream_access_level 0, 1, 2
FILESTREAM 데이터베이스생성 하나이상의파일그룹에대해 CONTAINS FILESTREAM 젃지정 CREATE DATABASE Archive ON PRIMARY ( NAME = Arch1, FILENAME = 'c:\data\archdat1.mdf'), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM ( NAME = Arch3, FILENAME = 'c:\data\filestream1') LOG ON ( NAME = Archlog1, FILENAME = 'c:\data\archlog1.ldf') GO FILESTREAM 열테이블생성 ROWGUIDCOL 속성의 uniqueidentifier 열이있어야함 CREATE TABLE Archive.dbo.Records ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [SerialNumber] INTEGER UNIQUE, [Chart] VARBINARY(MAX) FILESTREAM NULL ) GO
FILESTREAM 고려사항 적젃한크기의데이터에대하여홗용 대량의크기가작은 LOB 에대하여 FILESTREAM 을사용하면성능이좋지않을수있다 일반적인권고사항 256KB 미만의 LOB 데이터 : 데이터베이스에저장 1MB 이상의 LOB 데이터 : 파일시스템에저장 256 KB ~ 1MB 의 LOB 데이터 : 다른요소고려, 어플리케이션에대한심층테스트필요 다른데이터베이스파일들과분리배치 디스크헤드에대한경합감소 FILESTREAM 데이터를저장하는 NTFS 볼륨에대해적젃한 RAID 사용 쓰기작업이많은작업부하에대해서는 RAID-5 를사용하지말것 [ 참고 ] http://msdn.microsoft.com/en-us/library/cc949109.aspx
T-SQL 프로그래밍 복합연산자 GROUPING SETS MERGE 문 행생성자 테이블반홖매개변수
복합연산자 특정연산을실행하고그결과를원래값에저장하는복합연산자지원 연산자 += -= *= /= %= &= ^= = UPDATE Inventory SET Quantity += s.quantity FROM Inventory AS I INNER JOIN Sales AS s ON i.id = s.id; DECLARE @x1 int = 27; SET @x1 += 2 ; SELECT @x1 AS Added_2; Added_2 ----------- 29
GROUPING SETS 젃 GROUP BY 젃에연산자추가 하나의쿼리에서다중그룹정의 여러그룹의행들을 UNION ALL 한것과동일한단일결과집합생성 SQL Server 2005 및이젂버젂 SQL Server 2008 SELECT CustomerType, NULL AS TerritoryID, MAX(ModifiedDate) FROM Sales.Customer GROUP BY CustomerType UNION ALL SELECT NULL AS CustomerType, TerritoryID, MAX(ModifiedDate) FROM Sales.Customer GROUP BY TerritoryID; SELECT CustomerType, TerritoryID, MAX(ModifiedDate) FROM Sales.Customer GROUP BY GROUPING SETS ( (CustomerType), (TerritoryID) );
MERGE 문 UPSERT 하나의쿼리에서여러가지 DML 을수행가능 여러개의 DML 문을단일쿼리로대체가능 예 : 원본테이블과의차이점에따라대상테이블에서행을 insert, update, delete 함으로써두개의테이블을동기화 구문 : MERGE INTO USING WHEN
이벤트 MATCHED MERGE 문 NOT MATCHED NOT MATCHED BY SOURCE -- 일치하면타겟의구매일자를 update, 일치하지않으면타겟에 insert MERGE dbo.factbuyinghabits AS Target USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.purchases) AS Source ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID) WHEN MATCHED THEN UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate WHEN NOT MATCHED BY TARGET THEN INSERT (CustomerID, ProductID, LastPurchaseDate) VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate) OUTPUT $action, Inserted.*, Deleted.*;
행생성자 - VALUES 젃 VALUES 젃을사용하여행집합구성 하나의 INSERT 문으로다중행 INSERT 가능 다중행 insert INSERT INTO Customers (CustomerID, CompanyName, Phone, Address) VALUES (1, cust 1, 02-999-9999, address1 ), (2, cust 2, 031-999-9999, address2), (3, cust 3, 032-999-9999, address3 ), (4, cust 4, 042-999-9999, address4 ), (5, cust 5, 051-999-9999, address5 ); 테이블식정의 SELECT * FROM (VALUES (1, 'cust 1', '02-999-9999', 'address1'), (2, 'cust 2', '031-999-9999', 'address2'), (3, 'cust 3', '032-999-9999', 'address3'), (4, 'cust 4', '042-999-9999', 'address4'), (5, 'cust 5', '051-999-9999', 'address5') ) AS C (CustomerID, ComnpanyName, Phone, Address);
테이블반홖매개변수 SQL Server 2008 에서테이블형식과테이블매개변수추가 테이블구조의사용자정의개체를 table 변수에대한데이터형식또는테이블값매개변수로사용가능 테이블값매개변수를사용하면임시테이블이나많은수의매개변수를사용하지않고, 저장프로시저또는함수또는루틴에여러행의데이터를젂달가능 저장프로시저와함수에대한매개변수로사용시에는 READONLY 로선언해야함
테이블반홖매개변수 table 변수에대한형식으로사용 : 테이블정의를반복하지않아도됨 저장프로시저와 UDF 의입력매개변수로사용하는것이지원목적 CREATE TYPE dbo.orderids AS TABLE ( pos int NOT NULL PRIMARY KEY, orderid int NOT NULL UNIQUE ); GO DECLARE @T AS dbo.orderids; INSERT INTO @T (pos, orderid) VALUES (1, 10248), (2, 10250), (3, 10249); SELECT * FROM @T; GO
테이블반홖매개변수 테이블반홖매개변수사용예 : CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50), CostRate INT ); GO CREATE PROCEDURE usp_insertproductionlocation @TVP LocationTableType READONLY 테이블값매개변수 AS SET NOCOUNT ON INSERT INTO [AdventureWorks].[Production].[Location]([Name],[CostRate],[Availability],[Modified Date]) SELECT *, 0, GETDATE() FROM @TVP; GO DECLARE @LocationTVP AS LocationTableType; INSERT INTO @LocationTVP (LocationName, CostRate) SELECT [Name], 0.00 FROM [AdventureWorks].[Person].[StateProvince]; EXEC usp_insertproductionlocation @LocationTVP; GO
기타 T-SQL 문변경사항 변수선언문에서값초기화가능 DECLARE @v int = 5; DECLARE @v1 varchar(10) = xxxxx ; 이짂데이터와 16 짂문자열갂의변홖지원 SELECT CONVERT (VARCHAR(100), CAST(0x1010101 AS VARBINARY(100)), 1); 호홖성모드변경 ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 90 100 };
개체종속성 sys.sql_expression_dependencies 새로운카탈로그뷰 : sys.sql_dependencies 대체 스키마바운드및 non- 스키마바운드에대하여종속성정보제공 데이터베이스갂및서버갂참조정보재공 ( 이름기준 ) 예 : 뷰에서참조되는개체조회, 테이블을참조하는개체조회 sys.dm_sql_referenced_entities 새로운동적관리함수 : sp_depends 대체 지정한엔터티에서참조되는엔터티들에대한정보제공 예 : 저장프로시저 p1 에서참조되는모든개체조회 sys.dm_sql_referencing_entities 새로운동적관리함수 : sp_depends 대체 지정한엔터티를참조하는엔터티들에대한정보제공 예 : 테이블 t1 을참조하는모든개체조회
저장소의효율적인사용 백업압축지원 데이터압축지원 스파스열지원 디스크공갂의효율적사용저장소요구량감소 I/O 작업성능개선
백업압축 SQL Server 2008 에서새롭게지원됨 Enterprise Edition SQL Server 2008 R2 에서는 Standard Edition 에서도지원 백업압축효과 공갂젃약 백업및복원소요시갂단축 (CPU 는더많이사용하지만 I/O 감소 ) 유의사항 TDE 홗성화데이터베이스에대해서는비압축백업방식을사용
데이터압축 Enterprise Edition/Developer Edition 에서지원 압축된테이블또는인덱스가포함된데이터베이스는 Standard Edition 으로복원또는부착 (attach) 할수없음 어플리케이션에영향을미치지않음 테이블, 인덱스, 파티션에각각적용가능 테이블과인덱스를서로독립적으로압축가능 분할테이블의경우젂체또는일부파티션압축가능 쓰기작업이많은 OLTP 시스템에데이터압축을적용하면성능저하발생할수있음
압축방식 행압축 데이터압축 고정길이데이터형식을가변길이데이터형식처럼저장 행메타데이터최적화 페이지압축 행압축 접두사및사젂
행압축 고정길이데이터형식을가변길이데이터형식처럼저장 INT 데이터형식 SQL Server 2005 0 0 0 1 4 바이트 SQL Server 2008 압축 1 1 바이트
접두사 페이지압축 각컬럼에서저장공갂을줄이는데사용할수있는접두사식별 각컬럼의접두사값을나타내는행이압축정보에저장 컬럼에서반복되는값은해당참조접두사로대체 접두사값과정확히일치하지않을경우부분일치로표시 [ 적용젂 ] [ 적용후 ] 페이지헤더 ABCD FFF ABC ABC SEED FDW NULL SEA XLJ FFF KDS FFF 페이지헤더 ABCD SEED NULL FFF ABC 3 FDW NULL 2A XLJ FFF KDS FFF
사젂 페이지압축 접두사압축완료후적용 데이터형식에상관없이모든컬럼및행에대하여반복되는값식별 식별된값이압축정보에저장됨 페이지에서반복되는값은해당참조사젂으로대체 [ 적용젂 ] [ 적용후 ] 페이지헤더 ABCD SEED NULL FFF ABC 3 FDW NULL 2A XLJ FFF KDS FFF 페이지헤더 ABCD SEED NULL S1=FFF S1 ABC 3 FDW NULL 2A XLJ S1 KDS S1
스파스열 NULL 값에대해저장소를최적화한열 열의값이대부분 NULL 인경우, 스파스열 (sparse column) 로변경하여 NULL 값압축 효과 저장소크기감소 I/O 감소 오버헤드 압축으로인한 CPU 오버헤드발생 NULL 이아닌값을저장하고조회하는경우오버헤드발생 NULL 값행이많으므로, 필터링인덱스에적합 이기능이미치는영향에대하여충분한고려및테스트필요 스파스열로변경후인덱스재작성작업실행
스파스열 스파스열정의 CREATE TABLE / ALTER TABLE CREATE TABLE DocumentStore ( DocID int PRIMARY KEY, Title varchar(200) NOT NULL, ProductionSpecification varchar(20) SPARSE NULL, ProductionLocation smallint SPARSE NULL, MarketingSurveyGroup varchar(20) SPARSE NULL ) ; 스파스열을사용하면행에많은수의열을관리가능 넓은테이블 (wide table) 최대열 30000 개, 인덱스 1000 개, 통계 30000 개
테스트예 스파스열 페이지수 크기 ( 원본대비 ) 평균행크기 ( 바이트 ) 원본테이블 521,450 100% 1910.4 인덱스재작성 422,092 81% 1881 (Rebuild Index 실행 ) 스파스열로변경 164,503 31.50% 697.3 (36%) 페이지압축 85,116 16% 286.57 (15%) 행압축 191,047 36% 817 (42%) < 주 > 상기테스트결과예제는특정테이블을대상으로테스트된결과이며, 테이블의데이터특성에따라결과가달라질수있습니다
리소스관리자 작업부하그룹과리소스풀생성 작업부하에대하여우선숚위지정가능 개별작업별제한설정가능 (CPU, 메모리 ) 시나리오예 : 쿼리분석기를통해서실행되는 Ad-hoc 쿼리가 CPU 시갂을 30 초이상사용하는경우에이벤트를실행하도록함 (KILL, 메일발송등 ) Admin 작업이 10% 이상의서버메모리자원을사용할수없도록제한 특정작업부하그룹에대해우선숚위지정 (IMPORTANCE 지정 )
정책기반관리 다중 SQL Server 2008 인스턴스관리 구성설정의배포 SQL Server 인스턴스모니터링정책포함 적은노력으로많은일을할수있도록 시스템구성정책관리작업갂소화 관리비용감소 정책저장소 정책은 msdb 데이터베이스에저장 정책또는조건변경시 msdb 백업
시나리오 정책기반관리 저장프로시저명명규칙정의및적용 저장프로시저이름을 up_ 로시작하도록정책관리 한인스턴스의노출영역구성설정을다른인스턴스에적용
데이터액세스및변경추적 SQL Server 2005 SQL Server 2008 SQL 추적 DML 트리거 DDL 트리거 DMV SQL 추적 DML 트리거 DDL 트리거 DMV + 확장이벤트 SQL 서버감사 변경내용추적 변경데이터캡처
데이터액세스및변경추적 확장이벤트 SQL 서버감사 변경내용추적 (Change Tracking) 변경데이터캡처 (Change Data Capture)
확장이벤트 SQL Server 엔짂에내장된이벤트처리시스템 Low-level & low-impact SQL Server 데이터와의연계가가능하며, 운영체제및데이터베이스응용프로그램데이터와연계해서사용할수있는기능도지원 주요장점 T-SQL 로완젂한제어가능 최소의영향으로프로세스모니터링가능
확장이벤트 ETW 와연동가능 확장이벤트시스템이 ETW(Event Tracing for Windows) 를 target 으로지원 Target ETW 파일 링버퍼 모니터링및문제해결에홗용 과도한리소스사용원인파악 교착상태원인파악 오류발생모니터링
SQL Server 감사 Auditing 및 Compliance 홗용솔루션 SQL Server 감사 SQL Server 2008 이젂에는추적, 프로파일러, DML 트리거를통해감사구현 2008 에서는 Auditing 은 internal object ( 내부적으로확장이벤트사용 )
감사범위 SQL Server 감사 서버수준의관리변경내용 로그인 / 로그아웃작업에대한감사 데이터베이스수준의 DML 및 DDL 작업 데이터베이스개체 ( 테이블, 뷰, 저장프로시저, 함수, 확장저장프로시저등 ) 에대한특정동작감사가능
변경내용추적 홗용용이 컬럼차원의추적은제공되지않음 가정 대부분의어플리케이션이현재값이중요한경우 홗용시나리오 외근시휴대폰상의인터넷연결을통하여변경된데이터수싞을필요로하는경우 직원의사짂을사짂이실제로변경된경우에만다운로드하고자하는경우
변경데이터캡처 Enterprise Edition 기능 SQL Agent Job 을통하여동작 트랜잭션로그를읽고 데이터변경내용을변경추적테이블에기록 소스시스템에미치는영향 트랜잭션복제가트랜잭션로그에미치는영향과유사 데이터베이스백업, 데이터베이스미러링, 로그젂달지원 KEEP_CDC 옵션 용도 데이터웨어하우스의 incremental populating 에홗용가능 감사에는부적젃 ( 트랜잭션로그의한계 )
TDE (Transparent Data Encryption) Enterprise/Developer Edition 대칭키를사용하여젂체데이터베이스를암호화 어플리케이션을수정하지않고데이터베이스파일을암호화하는새로운데이터베이스옵션 권한이없는사용자는데이터베이스파일이나백업파일이있어도데이터베이스에액세스불가 DB 데이터를암호화하고, 인증서와함께데이터를암호화하는데사용된키보호 DB 수준암호화설정 페이지단위암호화및복호화 데이터페이지쓰기시디스크의데이터페이지암호화 데이터페이지읽기시메모리의데이터페이지복호화
TDE 자동으로데이터암호화수행 DB 암호화키 (DEK, Database Encryption Key) 를이용하며, DB 의 Boot Page 에저장됨 디스크에있는데이터만암호화되고, 메모리에있는데이터는암호화되지않음 암호화 / 복호화시 AES, 3DES 알고리즘사용 기존의 SQL Server 컬럼수준암호화를대체하는것은아님
젂체 DB 보호 TDE 를사용하는이유 물리적미디어 ( 디스크또는백업파일 ) 를도난당한경우악의적인사용자에의한 DB 복원 / 연결방지 키없이는데이터를사용할수없음 DB 개체에대한스키마수정불필요 데이터를암호화 / 복호화하기위한어플리케이션을수정불필요 성능저하가비교적적음
TDE 백업및복원 인증서를백업할때개인키를함께백업해야함 인증서만백업할경우정상적으로 DB 복원또는연결을수행할수없음 백업파일을복원하거나분리된 (Detach) 데이터파일을연결 (Attach) 할때 DEK 가필요함 DEK 를분실하면복원할수있는방법은없음 서버인증서백업도필요함 TDE 를사용한 DB 의백업파일도암호키를사용하여암호화됨 TDE 를사용한 DB 의압축백업시압축률이낮음
필터링된인덱스및통계 필터조건자를사용하여, 테이블의일부행을인덱싱 ( 비클러스터형 ) 장점 크기가작음 인덱스유지관리비용감소 : 인덱스의데이터가영향을받을때만유지관리됨 인덱스저장소비용감소
필터링된인덱스및통계 인덱싱된뷰대비장점 인덱스유지관리비용감소 : 인덱스업데이트시 CPU 리소스사용감소 향상된계획품질 : 쿼리컴파일중더많은경우에필터링된인덱스사용 옦라인인덱스재작성 고유하지않은인덱스사용가능
제약사항 필터링된인덱스 조건자 : 갂단한비교논리 계산된열, UDT 열, 공갂데이터형식의열, HierarchyID 데이터형식의열참조불가 NULL 값자체비교불가, IS NULL, IS NOT NULL 사용 XML 인덱스, 젂체텍스트인덱스적용불가 IGNORE_DUP_KEY 옵션사용불가
필터링된통계 잘정의된데이터의부분집합을선택하는쿼리에대하여통계의정확도를높임 필터조건자를사용하여, 잘정의된데이터의부분집합만을선택하여통계생성 필터링된인덱스를사용하는쿼리 : 자동으로필터링된통계를만들고사용
기타성능관렦개선사항 OPTIMIZE FOR 힌트 UNKNOWN 변수값추가 테이블힌트 : FORCESEEK 힌트추가 쿼리최적화프로그램이 Index Seek 연산만사용하여데이터액세스 테이블에대한잠금에스컬레이션제어강화 ALTER TABLE 문의 LOCK_ESCALATION 옵션 AUTO / TABLE / DISABLE
SQL SERVER 2008 & R2 NEW FEATURES
감사합니다.