Presentation 3 2 1 Middle Tier SQL Server

응용 프로그램 쿼리 유형 Presentation 3 2 1 Middle Tier SQL Server 저장 프로시저 dbo. up_Orders. Info Parameterized SELECT. . . FROM dbo. Orders WHERE Order. ID = @Order. ID SELECT. . . FROM dbo. Orders WHERE Order. ID =10248 *RPC 이벤트 Adhoc *Batch 이벤트 영업 Database 저장 프로시저 (dbo. up_Orders. Info)

저장 프로시저 처리 과정-전체 생성 Parsing Resolution [sysobjects]: Name, type, etc. [syscomments]: Text of object [syscolumns]: Parameter list [sysdepends]: Object dependencies Resolution* 실행 (처음 or 재컴파일) Optimization Compiled plan placed in unified cache

성능 추적 및 모니터링 • master. dbo. syscacheobjects – 각 DB별 Cache Plan의 유형, 소비되는 크기, 재사용 률 등의 진단 분석 • 프로필러 혹은 SQL Trace 재컴파일 원인 추적 SP: Recompile, SP: Starting, SP: Stmt. Starting(혹은 SP: Stmt. Completed), SP: Completed Event. Class, Event. Sub. Class, Object ID, Object Name, Text. Data • 성능 카운터 SQLServer: Buffer Manager SQLServer: Cache Manager SQLServer: Memory Manager SQLServer: SQL Statistics Procedure cache pages Cache Hit Ratio, Cache Object Counts, Cache Pages, Cache Use Counts/sec SQL Cache Memory SQL Compilations/sec, SQL Re-Compilations/sec • T-SQL 명령 – DBCC CACHESTATS, DBCC PROCCACHE, DBCC MEMORYSTATUS

RPC Event를 통하지 않은 호출 RPC Message (RPC Event) SQL Language Message (Batch Event) SET rs = New ADODB. Recordset. . . rs. Open “EXEC Proc param 1”. . . cmd. Connection = cnn; cmd. Command. Text = “dbo. Proc param 1”; cmd. Command. Type = Stored. Procedure; NO cmd. Parameters. Add(“@member_no” , Sql. Db. Type. Int , 4). Value = param_value; . . . rdr = cmd. Execute. Reader(); NO cmd. Connection = cnn; cmd. Command. Text = “EXEC Proc param 1”; cmd. Command. Type = Command. Type. Text; rdr = cmd. Execute. Reader(); SQL 엔진이 원하는 것은 RPC Event 실행 계획 재사용 능력 및 성능 향상 파라미터 Caching/재사용 능력 제공 SQL Injection을 포함한 보안 강화

파라미터 개체 자동 생성 Parameter 개체 암시적 생성 NO WITH cmd. Active. Connection = cn. Command. Text = “Proc”. Command. Type = ad. Cmd. Stored. Proc 혹은 이것도 생략 가능. Parameters. Refresh. Parameters(1). Value = 1000. . . 추가 Request, I/O 등 성능 문제 유발 sp_procedure_params_rowset 실행 서버의 라운드 트립 유발 시스템 테이블 접근 유발-sysobjects 등

파라미터 암시적 데이터 형식 지정 Parameter 데이터 형식의 생략 혹은 암시적 지정 NO cmd. Connection = cnn; cmd. Command. Type = Command. Type. Text; cmd. Command. Text = “SELECT * FROM dbo. member WHERE firstname = @fname”; cmd. Parameters. Add. With. Value(“@fname”, lst. Fname. Selected. Item); . . . App/DB에서 암시적 형 변환 유발 불필요한 Procedure Cache 소비 SARG 위반할 경우 성능 문제 유발 Parameterized Query에서 특히 주의

SARG 위반 CREATE PROC dbo. up_1 @ID char(2) AS SELECT * FROM dbo. table WHERE char_col LEFT(char_col, LIKE @ID. . . 2) = @ID CREATE PROC dbo. up_3 @ID varchar(5) @ID char(10) AS SELECT * FROM dbo. table WHERE char_col LIKE RTRIM(@ID) @ID <column_name> <inclusive_operator> <value> [AND…] CREATE PROC dbo. up_2 @IDvarchar(5) int @ID AS SELECT * FROM dbo. table WHERE char_col = @ID CREATE PROC dbo. up_4 @ds datetime AS. . . SET @s = @ds + 7 SELECT * FROM dbo. table WHERE date_col BETWEEN @ds AND(@ds @s + 7) @ds AND @ds 2

Parameter Sniffing • 문제: (초기/재)컴파일 시 사용된 파라미터로 생성된 실행 계획이, 재사용 시에는 잘못된 실행 계획이 되는 경우 @B_Date=‘ 19960710’ @E_Date=‘ 19960930’ FROM dbo. Orders WHERE Order. Date Between @B_Date AND @E_Date @B_Date=‘ 19960710’ @E_Date=‘ 19960711’ 선택도 = 65/830 실행계획 = CL Index Scan 선택도 = 2/830 실행계획 = Index Seek

동적 검색 조건-예제 모든 경우의 수에 따른 성능 확인 필요 @orderid int = NULL , @fromdatetime = NULL , @todatetime = NULL , @prodname nvarchar(40) = NULL 개별 구문 별 재사용 처리. . . SELECT @sql = 'SELECT * FROM Northwind. dbo. Orders o JOIN Northwind. dbo. [Order Details] od 동일 구문에 대해서는 여전 ON o. Order. ID = od. Order. ID 히 재사용 문제 내포 JOIN Northwind. dbo. Products p ON p. Product. ID = od. Product. ID WHERE 1 = 1 ' 프로시저와 다른 Scope로 처리 IF @orderid IS NOT NULL Recompile 강제 어려움 SET @sql = @sql + 'AND o. Order. ID = @xorderid ' sp_recompile 테이블 IF @fromdate IS NOT NULL SET @sql = @sql + 'AND o. Order. Date >= @xfromdate ' IF @todate IS NOT NULL SET @sql = @sql + 'AND o. Order. Date <= @xtodate ' IF @prodname IS NOT NULL SET @sql = @sql + 'AND p. Product. Name LIKE @xpname + ''%'' '. . . EXEC sp_executesql @sql, @paramlist, @orderid, @fromdate, @todate, @prodname

사례-1: SARG 시간(ms) CPU(ms) 읽기 쓰기 B Avg. 11, 007 1, 812 186, 400 0 A Avg. 334 246 3, 695 0 SELECT TOP 3 이력번호, . . . FROM 이력 R JOIN 사용자 U ON U. ID = R. ID WHERE 기본상태 = 1 AND ( 수정일자 >= Date. Add(d, -1, getdate()) ). . . ORDER BY 등록일자 DESC SELECT TOP 3 이력번호, . . . FROM 이력 R JOIN 사용자 U ON U. ID = R. ID WHERE 기본상태 = 1 AND (수정일자 >= (SELECT DISTINCT Date. Add(d, -1, getdate())) ) Constant Scan. . . ORDER BY 등록일자 DESC

사례-3: SARG & 조건식 제한 시간(ms) SELECT * FROM A WITH(NOLOCK), B WITH(NOLOCK) WHERE A. 제품 =‘V' AND A. 제품 = B. 제품 AND A. 항목 = B. 항목 AND A. 일련번호 = B. 일련번호 AND B. 표시 ='' AND A. 요청번호 BETWEEN 5346 AND 5362 AND LEN(A. 일련번호) = 8 AND A. 금액 <> B. 금액 AND B. 일자 >= '2005 -05 -01' ORDER BY A. 요청번호 CPU(ms) 읽기 쓰기 B Avg. 102, 220 5, 485 2, 824, 895 0 A Avg. 766 78 19, 856 0

사례-5: 조정 결과 SELECT TOP 1 주문번호, '02' AS 구분 FROM dbo. M , dbo. D WHERE M. 회원 = 15000 AND M. 구분1 != '03' AND M. 주문번호 = B. 주문번호 AND D. 구분1 != '03' AND D. 출고일자 BETWEEN ‘ 20060101’ AND ‘ 20060107’ ORDER BY 출고일자 DESC OPTION (FORCE ORDER) Hint를 사용한 조인 순서 강제 쿼리 재 작성을 통한 조인 순서 강제 1 2 SELECT TOP 1 주문번호, '02' AS 구분 FROM ( SELECT M. 주문번호 , 최종일자 = ( SELECT TOP 1 출고일자 FROM dbo. D WHERE D. 구분1 <> '03' AND D. 출고일자 BETWEEN ‘ 20060101’ AND ‘ 20060107’ AND D. 주문번호 = D. 주문번호 ORDER BY 출고일자 DESC ) FROM dbo. M WHERE M. 회원번호 = 15000 AND M. 구분1 <> '03' ) A WHERE 최종일자 IS NOT NULL ORDER BY 최종일자 DESC

사례-6: 조정 결과 조건 별 프로시저 분기 처리 CREATE PROC dbo. usp_List_가_A. . . AS select 이름, 코드 from dbo. A a where exists ( select 1 FROM 가 b Where 입력일 >= @입력일 and b. 대분류 = @addr 1 and b. 중분류 = @addr 2 and a. 코드 = b. 시세코드 and (case @A 3 Kind. SQL when 1 then 동 when 2 then 읍 when 3 then CO 14 end) = @addr 3 ) and a. 대분류 = @addr 1 and a. 중분류 = @addr 2 order by 이름 조건 별 프로시저 분리 작성 CREATE PROC dbo. usp_List_Main. . . AS IF @table. Name = ‘가' BEGIN IF @akind = '0' exec dbo. usp_List_가_A. . . ELSE exec dbo. usp_List_가_B. . . END ELSE IF @table. Name = ‘나' BEGIN IF @akind = '0' exec dbo. usp_List_나_A. . . ELSE exec dbo. usp_List_나_B. . . END ELSE IF @table. Name = ‘다'. . .

사례-7: 뷰 내에서의 SARG 시간(ms) B Avg. 2, 163 CPU(ms) 1, 040 읽기 3211 쓰기 0 CREATE VIEW dbo. 물건_003 A Avg. 6 6 141 0 AS SELECT 대분류, 중분류, 읍, COUNT(*) AS 갯수 FROM dbo. 거래 WITH WHERE ( 입력일 >= REPLACE(CONVERT(varchar(10), GETDATE()-30, 21), '-', '')+'000000') GROUP BY 대분류, 중분류, 읍 select a. 읍 from ( 뷰 내에서 SARG 위반 select 읍 from dbo. A where 대분류 = '01' and 중분류 = '01' group by 읍면) a where exists ( select 'r' from dbo. 물건_003 b where b. 대분류 = '01' and b. 중분류 = '01' and b. 읍 = a. 읍 ) order by 읍

사례-7: 조정 결과 CREATE FUNCTION dbo. uf_물건_003 (@Date varchar(12)) RETURNS TABLE AS RETURN (SELECT 대분류, 중분류, 읍, COUNT(*) AS 갯수 FROM dbo. 거래 WHERE (입력일 >= @Date + '000000') select a. 읍 GROUP BY 대분류, 중분류, 읍 from ( ) select 읍 from dbo. A 인라인-테이블 값 함수 활용 where 대분류 = '01' and 중분류 = '01' group by 읍) a where exists ( select 'r' from dbo. uf_물건_003(‘ 20051101’) b where b. 대분류 = '01' and b. 중분류 = '01' and b. 읍 = a. 읍 ) order by 읍

사례-8: 일반적인 최적화 사례 시간(ms) CPU(ms) 읽기 쓰기 B Avg. 11, 007 1, 812 186, 400 0 A Avg. 334 246 3, 695 0 SELECT TOP 15 R. *, U. *, S. * FROM 이력 R JOIN 사용자 U ON U. ID = R. ID LEFT JOIN 학력 S ON R. ID = S. ID WHERE <R. 조건식> SELECT R. *, U. *, S. * AND <U. 조건식> FROM ( SELECT TOP 15 R. * ORDER BY R. 수정일자 DESC FROM 이력 R WHERE <R. 조건식> Row Count Spool AND ID IN (SELECT TOP 1 ID FROM 사용자 U WHERE <U. 조건식> AND ID = R. ID) ORDER BY R. 수정일자 DESC ) AS R LEFT JOIN 학력 S ON R. ID = S. ID LEFT JOIN 사용자 U ON U. ID = R. ID

추가 자료들 • Microsoft SQL Server Query Processor Internals and Architecture http: //msdn. microsoft. com/library/default. asp? url=/library/enus/dnsql 7/html/sqlquerproc. asp • (기술자료) 저장 프로시저 재컴파일 문제 해결 http: //support. microsoft. com/kb/243586/ko • Microsoft SQL Server 성능 튜닝 Technical Reference Whalen, Garcial, Deluca, Thompson | 정보문화사 • Inside Microsoft SQL Server 2000 Kalen Delaney | 정보문화사 • SQL Server 최적화 튜닝 정원혁, iti 교육센타 • Programming a Microsoft SQL Server 2000 Database 2073, MOC 교육 과정