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

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

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

지금까지는, 아주 평범합니다.

하지만 저장 절차는 매우 느렸습니다.쿼리와 proc 사이에 중요한 차이는 없지만 속도 변화는 매우 컸습니다.

[SQL Server 2005를 실행하고 있습니다.]

친절한 현지 DBA(더 이상 여기서 일하지 않음)가 저장 프로시저를 한 번 보고 "매개 변수 스푸핑!"이라고 말했습니다(편집: '매개 변수 스푸핑'이라고도 할 수 있습니다. 이는 제가 검색을 시도할 때 Google 검색 횟수가 부족하다는 것을 설명할 수 있습니다.).

우리는 저장 프로시저의 일부를 두 번째 프로시저로 추상화하고, 이 새로운 내부 프로시저에 대한 호출을 외부 프로시저라고 불리는 기존의 외부 프로시저로 감쌌습니다. 그리고 이것은 원래 쿼리만큼 빨랐습니다.

그럼, 뭐가 문제죠?누가 매개 변수 스푸핑을 설명할 수 있습니까?

보너스 크레딧:

• 피하는 방법 강조하기
• 가능한 원인을 인식하는 방법을 제안하기
• 상황 완화를 위한 대체 전략(예: 통계, 지수, 키)을 논의합니다.

참고로 SQL 2005 및 매개 변수가 있는 저장 프로시저를 사용할 때는 다른 것을 알아야 합니다.

SQL Server는 사용된 첫 번째 매개 변수로 저장된 Proc의 실행 계획을 컴파일합니다.실행하면 다음과 같습니다.

usp_QueryMyDataByState 'Rhode Island'

실행 계획은 소규모 주의 데이터에서 가장 잘 작동합니다.하지만 누군가가 돌아서서 뛰어간다면,

usp_QueryMyDataByState 'Texas'

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

여기에 쿼리 계획이 포함되며, SQL 서버 2008은 어떤 매개 변수가 먼저 호출되든 상관없이 DBA가 특정 쿼리 계획을 장기적으로 고정할 수 있도록 지원하는 많은 새로운 기능을 제공합니다.

저장된 proc를 재구축할 때 실행 계획을 강제로 다시 컴파일하는 것이 걱정됩니다.좋아하는 매개 변수를 사용하여 호출한 다음에는 물론 속도가 빠릅니다. 하지만 문제는 저장된 프로시저가 아닐 수도 있습니다.저장된 프로세스가 비정상적인 매개 변수 집합을 사용하여 어느 시점에 다시 컴파일되었기 때문에 비효율적인 쿼리 계획일 수 있습니다.수정한 내용이 없을 수 있으며, 다음에 서버가 재시작되거나 쿼리 계획이 다시 컴파일될 때 동일한 문제가 발생할 수 있습니다.

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

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

예에서 DBA는 쿼리를 다른 저장 프로시저로 이동하기 위해 옵션 #4를 선택한 것 같습니다.

또한 원래 저장 프로시저에서 재컴파일과 함께 를 사용하거나 매개 변수에서 최적화 옵션을 사용할 수도 있습니다.

속도를 높이는 간단한 방법은 저장 프로시저의 시작 부분에 있는 로컬 매개 변수에 입력 매개 변수를 재할당하는 것입니다.

CREATE PROCEDURE uspParameterSniffingAvoidance
    @SniffedFormalParameter int
AS
BEGIN

    DECLARE @SniffAvoidingLocalParameter int
    SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

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

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

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

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

다음 중 하나를 사용하여 이 문제를 해결할 수 있습니다.

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

심지어 저는 저장 프로시저를 아예 사용하지 않고 직접 서버로 문의 사항을 보내는 것이 좋다고 들었습니다.저는 최근에 아직 실질적인 해결책이 없는 같은 문제를 발견했습니다.일부 쿼리의 경우 로컬 변수에 복사하면 올바른 실행 계획으로 돌아갈 수 있습니다. 일부 쿼리의 경우 로컬 변수에 따라 성능이 저하됩니다.

SQL 서버가 최적이 아닌 실행 계획을 캐시하고 재사용하는 방법에 대해 더 많은 조사를 해야 합니다.

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

쿼리 최적화 도구는 쿼리를 자주 실행할 때 오래된 쿼리 계획을 사용합니다.그러나 실제로는 데이터의 크기도 증가하고 있기 때문에 그 당시에는 최적화된 새로운 계획이 필요하고 여전히 기존의 쿼리 계획을 사용하여 최적화된 계획을 쿼리합니다.이를 매개 변수 스니핑이라고 합니다.저는 이것에 대한 자세한 게시물도 만들었습니다.다음 URL을 방문하십시오. http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

배치로 실행하도록 저장 프로시저를 변경하면 속도가 증가합니다.

배치 파일 선택(예:

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

일반 저장 프로시저 대신 다음을 선택합니다.

select * from order where  order id = @ordersID

다음과 같이 매개 변수를 전달합니다.nvarchar그리고 당신은 더 빠른 결과를 얻어야 합니다.

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