[MS SQL/SQL SERVER]sql 플랜-파라미터 스니핑(Parameter Sniffing)test

서론

sql server 세미나..? 간단한 토론을 엿들을(? 기회가 생겨

db는 어렵고 멋지다! 선배님들 개쩔어..만 서른 마흔 다섯번 되네이면서 들었다.

사실 이론적으론 반의반의반도 이해하지못했지만 많은걸 배웠고.. 

이게 맞나 싶긴한데 들은걸 간단하게 정리해본다.

 

본론

!프로시저 플랜 파라미터 스니핑(Parameter Sniffing)!

파라미터 스니핑은 SQL Server의 쿼리 최적화에 관련된 기술 중 하나

쿼리가 실행 시, 해당 쿼리에서 사용되는 파라미터의 값에 따라 실행 계획이 달라질 수 있다는 것을 이용

즉, 해당 쿼리의 실행 계획을 파라미터의 값에 따라 미리 예측하고 저장해 놓은 후, 동일한 쿼리가 실행될 때 저장된 실행 계획을 사용하여 성능을 향상

 

? 그럼 파라미터 스니핑을 사용하면 늘 최적의 결과를 얻을 수 있는거 아닌가? 

확인해보자:)

 

테스트 환경

Microsoft SQL Server 2019 Enterprise Evaluation Edition 

AdventureWorks2019 

 

프로시저 생성

/* 프로시저 생성 */ 
-- 제품 ID에 대한 판매 세부 정보 가져오기
CREATE PROCEDURE USP_GetSales (@ProductID INT)
AS
BEGIN
SELECT
     SalesOrderID,
     OrderQty,
     ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;
END

++ 프로시저를 변경할때는  drop -> create 하면 권한이 날아갈 수 있어서 프로시저 변경시에는 alter권장!

 

statistisc Io, Time on

SET STATISTICS io ON
SET STATISTICS time ON

statistisc 정리 사이트 사용

Statistics Parser

 

CASE1

프로시저 실행

-- 실행1
EXECUTE USP_GetSales 897;

예상행수:75.6667
실제행수:2

테이블	스캔횟수	논리적 읽기	물리적 읽기
SalesOrderDetail	1	14	3
Total	1	14	3

	CPU	소요시간
SQL Server 구문 분석 및 컴파일 시간	00:00:00.000	00:00:00.008
SQL Server 실행시간	00:00:00.030	00:00:00.155
Total	00:00:00.030	00:00:00.163

실행 계획이 ProductID = 897을 기준으로 생성

 

 

프로시저2 실행

-- 실행2
EXECUTE USP_GetSales 870;

예상행수:75.6667
실제행수:4688

테이블	스캔횟수	논리적 읽기	물리적 읽기
SalesOrderDetail	1	18,253	54
Total	1	18,253	54

	CPU	소요시간
SQL Server 구문 분석 및 컴파일 시간	00:00:00.000	00:00:00.000
SQL Server 실행시간	00:00:00.032	00:00:00.745
Total	00:00:00.032	00:00:00.745

productID = 897을 기반으로 생성된 계획은 캐시에 저장되어 동일한 계획이 productID = 870에 사용되었습니다.

 

CASE2

캐시지우고 프로시저2 재 실행

DBCC DROPCLEANBUFFERS 
GO 
DBCC FREEPROCCACHE 
GO

EXECUTE USP_GetSales 870;

예상행수:4688
실제행수:4688

테이블	스캔횟수	논리적 읽기	물리적 읽기
SalesOrderDetail	1	1,248	3
Total	1	1,248	3

	CPU	소요시간
SQL Server 구문 분석 및 컴파일 시간	00:00:00.008	00:00:00.008
SQL Server 실행시간	00:00:00.048	00:00:00.661
Total	00:00:00.056	00:00:00.669

버퍼 캐시를 지웠으므로 프로시저를 컴파일하고 전달된 매개변수(ProductID = 870)를 기반으로 새 쿼리 계획을 생성

 

case 1과 case2의 프로시저2 실행 비교

	case1	case2
예상행수	75.6667	4688
스캔횟수	1	1
논리적 읽기	18,253	1,248
물리적 읽기	54	3
Total CPU	00:00:00.032	00:00:00.056
Total 소요시간	00:00:00.745	00:00:00.669

결론

파라미터 스니핑은 균등하게 나온 통계가있다면 확실하겠지만  위와 같이 데이터가 균등하지 못하다면 오히려 독이 될 수 있는  것 같다.

 

그렇다고 계속 통계를 실시간 업데이트 할수도없고..

고성능의 통계가 나올때 까지 하늘에 빌수도 없고..

sql server에서 통계 값과 실제 값의 차이가 왜생길까?

히스토리를 바라보니까!

 

새로 신규 유입데이터가있으면 통계는 업데이트되지 않았을 것이고

플랜은 하나고 그러면 잘못된 계획을 따를거고 굳이 이걸 따라야하는지 생각해볼필요가 있는 것 같다.

확실하지 않으면 통계를 보지 않게 파라미터 스니핑을 비활성화 해버리자

 

스니핑 비활성화

인스턴스 수준 에서 비활성화

인스턴스레벨 옵션 변경(4136) off

 

데이터베이스 수준에서 비활성화

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = { ON | OFF | PRIMARY}

인스턴스 수준 에서 비활성화

인스턴스레벨 옵션 변경(4136) off

 

_>통계를 아.예 끄는건 좋은 방법은 아님

 

+optimize for unknown
쿼리 최적화 기술 중 하나
쿼리에서 사용되는 파라미터 값을 미리 알 수 없을 때, 실행 계획을 수립가능
**파라미터 스니핑**이 일어나지 않음!

 

+a

이건 아시죠? 라고 말씀해주셨던 쿼리문

기본 of 기본 이지만 나는 몰랐다..😂 나만 웃지못했고.. 더..열심히해야지..

set nocount on
행 수를 읽지 않게, 행 수 표시 메시지 비활성화
마지막에 있는 것만 받아옴

next result on
다중 결과 집합을 반환하는 쿼리를 실행할 때 사용되는 옵션
저장 프로시저에서 여러 개의 결과 집합을 반환하는 경우, 이 옵션을 사용하여 모든 결과 집합을 가져올 수 있음

hash match
해시 알고리즘을 사용하여 두 개의 입력 데이터 집합을 비교하는 방법 중 하나
입력 데이터 집합의 크기에 관계없이 매우 빠르게 작동하며, 내부 조인, 집계 작업 및 해시 인덱스 작업에 사용

- http://udayarumilli.com/parameter-sniffing-performance-impact-in-sql-server/