SQL Server의 매개 변수 스니핑 (또는 스푸핑)

SQL Server의 매개 변수 스니핑 (또는 스푸핑)

---

얼마 전에 내 사용자 중 한 명을 위해 꽤 많이 실행하는 질문이있었습니다. 여전히 진화하고 조정 중이었지만 결국 안정화되고 매우 빠르게 실행되었으므로 여기에서 저장 프로 시저를 만들었습니다.

지금까지는 정상입니다.

그러나 저장 프로시 저는 매우 느 렸습니다. 쿼리와 proc간에 물질적 차이는 없지만 속도 변화는 엄청났습니다.

[배경, 우리는 SQL Server 2005를 실행하고 있습니다.]

친숙한 로컬 DBA (더 이상 여기서 일하지 않음)는 저장 프로 시저를 한 번 살펴보고 "매개 변수 스푸핑!"이라고 말했습니다. ( 편집 : '매개 변수 스니핑'이라고도 알려져 있지만 검색하려고 할 때 Google 히트의 부족함을 설명 할 수 있습니다.)

저장 프로 시저 중 일부를 두 번째 프로 시저로 추상화하고이 새로운 내부 프로 시저에 대한 호출을 외부 프로 시저라고하는 기존 외부 프로 시저로 래핑했으며, 원래 쿼리만큼 빠릅니다.

그래서, 무엇을 제공합니까? 누군가 매개 변수 스푸핑을 설명 할 수 있습니까?

에 대한 보너스 크레딧

• 그것을 피하는 방법을 강조
• 가능한 원인을 인식하는 방법 제안
• 상황을 완화하기위한 통계, 인덱스, 키와 같은 대체 전략을 논의합니다.

---

참고로-SQL 2005로 작업하고 매개 변수가있는 저장된 procs를 사용할 때 다른 것을 알고 있어야합니다.

SQL Server는 사용되는 첫 번째 매개 변수를 사용하여 저장된 proc의 실행 계획을 컴파일합니다. 따라서 이것을 실행하면 :

usp_QueryMyDataByState 'Rhode Island'

실행 계획은 작은 상태의 데이터에서 가장 잘 작동합니다. 하지만 누군가 돌아 서서 달리면 :

usp_QueryMyDataByState 'Texas'

로드 아일랜드 크기 데이터 용으로 설계된 실행 계획은 텍사스 크기 데이터에 비해 효율적이지 않을 수 있습니다. 새로 생성 된 실행 계획은 가장 먼저 사용되는 매개 변수를 대상으로하기 때문에 서버를 다시 시작할 때 놀라운 결과를 생성 할 수 있습니다. 계획은 통계가 다시 작성되는 것과 같이 큰 이유가있을 때까지 다시 컴파일되지 않습니다.

이것이 쿼리 계획이 들어오는 곳이며 SQL Server 2008은 DBA가 어떤 매개 변수를 먼저 호출하더라도 특정 쿼리 계획을 장기간 제자리에 고정하는 데 도움이되는 많은 새로운 기능을 제공합니다.

내 관심사는 저장된 proc을 다시 빌드 할 때 실행 계획을 다시 컴파일하도록 강제했다는 것입니다. 좋아하는 매개 변수를 사용하여 호출 한 다음 물론 빠르지 만 저장된 프로 시저가 문제가 아닐 수도 있습니다. 저장된 proc이 비정상적인 매개 변수 집합을 사용하여 어떤 시점에서 재 컴파일되어 비효율적 인 쿼리 계획이 발생했을 수 있습니다. 아무것도 수정하지 않았을 수 있으며 다음에 서버가 다시 시작되거나 쿼리 계획이 다시 컴파일 될 때 동일한 문제에 직면 할 수 있습니다.

---

예, SQL Server 최적화 프로그램이 쿼리에 가장 적합한 실행 계획을 선택할 수 있도록 매개 변수 값 / 범위를 파악하는 데 사용하는 기술인 매개 변수 스니핑을 의미한다고 생각합니다. 경우에 따라 SQL Server는 매개 변수 스니핑에 대한 작업을 제대로 수행하지 않고 쿼리에 대한 최상의 실행 계획을 선택하지 않습니다.

이 블로그 기사 http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx 에 좋은 설명 이 있다고 생각합니다 .

귀하의 예제에서 DBA는 쿼리를 별도의 절차 컨텍스트로 다른 sproc로 이동하기 위해 옵션 # 4를 선택한 것 같습니다.

원래 sproc 에서 with recompile 을 사용하거나 매개 변수 에서 optimize for 옵션을 사용할 수도 있습니다.

---

속도를 높이는 간단한 방법은 sproc의 맨 처음에 입력 매개 변수를 로컬 매개 변수에 다시 할당하는 것입니다.

CREATE PROCEDURE uspParameterSniffingAvoidance
    @SniffedFormalParameter int
AS
BEGIN

    DECLARE @SniffAvoidingLocalParameter int
    SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

    --Work w/ @SniffAvoidingLocalParameter in sproc body 
    -- ...

---

제 경험상 매개 변수 스니핑을위한 최상의 솔루션은 '동적 SQL'입니다. 두 가지 중요한 점은 1. 동적 SQL 쿼리에서 매개 변수를 사용해야합니다. 2. 각 매개 변수 값에 대한 실행 계획을 저장하는 sp_executesql (sp_execute가 아님)을 사용해야합니다.

---

매개 변수 스니핑은 SQL Server가 저장 프로 시저에 대한 쿼리 실행 계획을 최적화하는 데 사용하는 기술입니다. 저장 프로 시저를 처음 호출 할 때 SQL Server는 호출의 지정된 매개 변수 값을보고 매개 변수 값에 따라 사용할 인덱스를 결정합니다.

따라서 첫 번째 호출에 일반적인 매개 변수가 포함되어 있지 않은 경우 SQL Server는 저장 프로 시저의 다음 호출과 관련하여 차선의 실행 계획을 선택하고 저장할 수 있습니다.

이 문제를 해결하려면

• 사용 WITH RECOMPILE
• 매개 변수 값을 저장 프로 시저 내의 지역 변수에 복사하고 쿼리에서 지역을 사용합니다.

저장 프로 시저를 전혀 사용하지 않고 쿼리를 서버로 직접 보내는 것이 더 좋다고 들었습니다. 나는 최근에 아직 실제 해결책이없는 동일한 문제를 발견했습니다. 일부 쿼리의 경우 로컬 변수에 대한 복사는 올바른 실행 계획으로 돌아가는 데 도움이되며, 일부 쿼리의 경우 로컬 변수로 인해 성능이 저하됩니다.

SQL Server가 실행 계획을 캐시하고 재사용하는 방법에 대해 더 많은 연구를해야합니다.

---

비슷한 문제가있었습니다. 내 저장 프로 시저의 실행 계획은 30-40 초가 걸렸습니다. 쿼리 창에서 SP 문을 사용해 보았는데 동일한 실행에 몇 ms가 걸렸습니다. 그런 다음 저장 프로 시저 내에서 지역 변수를 선언하고 매개 변수 값을 지역 변수로 전송하는 작업을 수행했습니다. 이로 인해 SP 실행이 매우 빨라졌고 이제 동일한 SP가 30-40 초가 아닌 몇 밀리 초 내에 실행됩니다.

---

Very simple and sort, Query optimizer use old query plan for frequently running queries. but actually the size of data is also increasing so at that time new optimized plan is require and still query optimizer using old plan of query. This is called Parameter Sniffing. I have also created detailed post on this. Please visit this url: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

---

Changing your store procedure to execute as a batch should increase the speed.

Batch file select i.e.:

exec ('select * from order where  order id ='''+ @ordersID')

Instead of the normal stored procedure select:

select * from order where  order id = @ordersID

Just pass in the parameter as nvarchar and you should get quicker results.

참고URL : https://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server