저장 프로시저 인덱싱 분리 및 실행 계획 분리

1. 실행 계획 캐싱의 특성과 문제점

SQL Server 실행 계획 캐싱 원리

1. 문자열이 동일한 쿼리는 동일한 실행 계획을 사용합니다.
   • SQL Server는 쿼리 텍스트를 해싱하여 실행 계획을 캐싱합니다.
2. 쿼리가 약간이라도 다르면, 별도의 실행 계획이 생성됩니다.
   • 대소문자 차이 (SELECT vs select)나 공백이 달라도 서로 다른 쿼리로 인식됩니다.
3. 동일한 쿼리라도 실행 시 전달된 파라미터 값에 따라 실행 계획이 달라질 수 있습니다.
   • 파라미터 스니핑(Parameter Sniffing): SQL Server는 처음 실행된 쿼리의 파라미터 값을 기준으로 실행 계획을 최적화합니다. 이후 다른 파라미터 값이 전달되더라도 초기 계획을 재사용하므로 비효율이 발생할 수 있습니다.

---

2. sp_executesql의 장점

• 동적 쿼리를 작성하면서도 파라미터화를 통해 실행 계획 캐싱을 재사용할 수 있습니다.
• 조건에 따라 다른 실행 계획을 생성하도록 쿼리 텍스트를 유연하게 구성할 수 있습니다.
• SQL 인젝션 방지에 효과적입니다.

---

3. 고급 검색 시 실행 계획 분리 방법

예제: 게시판 검색에서 다양한 조건에 따른 실행 계획 분리

GO

-- =============================================

-- Author:      <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [up_List_테이블]

    @ID       INT,

    @CONTENTS nvarchar(max),

    @TYPE     VARCHAR(50) = NULL

AS

BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  /* ID, CONTENTS, TYPE 은 파라미터로 받는다. */

  DECLARE @쿼리 NVARCHAR(500);

  DECLARE @파라미터정의 NVARCHAR(500);

  SET @쿼리 = N'SELECT * FROM 테이블 WHERE ID = @EXEC_ID and CONTENTS = @EXEC_CONTENTS';

  SET @파라미터정의 = N'@EXEC_ID INT, @EXEC_CONTENTS NVARCHAR(MAX), @EXEC_TYPE VARCHAR(50) = NULL';

  IF @TYPE IS NOT NULL

  BEGIN

    SET @쿼리 = @쿼리 + N' AND TYPE = @EXEC_TYPE'

  END

  EXECUTE sp_executesql @쿼리, @파라미터정의, @EXEC_ID = @ID, @EXEC_CONTENTS = @CONTENTS, @EXEC_TYPE = @TYPE;

END

---

4. 주의사항 및 최적화 팁

1) 파라미터화로 SQL 인젝션 방지

sp_executesql을 사용할 경우, 쿼리 문자열 내에서 직접 값을 삽입하지 않고 파라미터화하면 SQL 인젝션을 방지할 수 있습니다.

2) 인덱스 설계

• 실행 계획이 달라질 조건(예: @TYPE이나 @SORT)에 맞춰 적절한 인덱스를 설계해야 합니다.
  • TYPE, ID, CONTENTS 조합에 적합한 복합 인덱스를 생성.
  • ORDER BY가 빈번히 사용되는 열에 대해 정렬된 인덱스를 추가.

3) 파라미터 스니핑 문제

동일한 저장 프로시저가 다른 파라미터 값으로 호출될 경우 실행 계획이 비효율적으로 재사용될 수 있습니다.

• OPTION (RECOMPILE): 매번 새로운 실행 계획을 생성하도록 설정.
   

  EXECUTE sp_executesql @쿼리, @파라미터정의, @EXEC_ID = @ID, @EXEC_CONTENTS = @CONTENTS, OPTION (RECOMPILE);
• 강제 계획 고정: 실행 계획 고정을 통해 특정 계획을 재사용하도록 설정(SQL Server Management Studio에서 설정 가능).

4) 동적 쿼리 디버깅

PRINT를 이용해 생성된 동적 쿼리를 확인하여 디버깅합니다.

PRINT @쿼리;

---

5. 실행 계획 분석 도구 활용

• **SQL Server Management Studio(SSMS)**의 실행 계획 보기 기능을 사용하여 쿼리 실행 계획을 확인합니다.
  • 실행 계획 비교: 조건에 따라 실행 계획이 어떻게 달라지는지 확인.
• Query Store: SQL Server의 Query Store 기능을 활성화하면 실행 계획 히스토리를 분석하고 비효율적인 실행 계획을 확인할 수 있습니다.

---

6. 결과

위 방법을 통해 다음과 같은 결과를 얻을 수 있습니다:

1. 조건별로 실행 계획이 독립적으로 생성되어, 효율적인 인덱스 활용이 가능합니다.
2. SQL 인젝션 방지 및 유지보수성 향상.
3. sp_executesql과 OPTION (RECOMPILE)을 적절히 사용하여 파라미터 스니핑 문제를 해결.