데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

강제 매개변수화로 인한 성능 저하 사례

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server는 쿼리 요청을 받으면 쿼리를 컴파일하고 통계 및 기타 정보를 이용하여 최적의 실행계획을 선택하여 사용한다. 쿼리 옵티마이저는 무조건 비용이 낮은 계획을 선택하지 않으며 여러 가지 상황을 고려하여 최적의 플랜을 선택 한다.

 

SQL Server 쿼리 처리 아키텍처 : http://sqlmvp.kr/140188321707

 

이번 포스트는 SQL Server Premier Field Engineer Blog에 게시된 내용으로 강제 매개변수화로 인한 성능 저하의 사례를 소개하고 있다. 필자가 읽고 이해한 내용을 바탕으로 정리하였으며 번역의 오류나 기술적 오류가 있음을 미리 알려둔다. 자세한 내용은 원문을 참고하길 바란다.

 

SQL Server를 운영할 때 실행계획을 재사용하기 위하여 또는 내가 원하는 방향으로 실행계획을 유도하기 위하여 강제 매개변수화를 설정하는 경우가 많다. 예를 들어 100개의 서로 다른 매개변수 값을 사용하는 쿼리가 있을 때 단순 매개 변수화는 100개의 다른 플랜을 생성할 수 있다. 하지만 강제 매개변수화를 통하여 준비된 단일 실행계획을 유도하고 재사용할 수 있다.

(내 생각 : 이방법은 일반적인 상황에서 쿼리 처리의 효율을 높일 수 있는 방법을 튜닝 시 많이 사용하기도 한다.)

매개변수화를 강제하고 복잡한 쿼리의 계획을 재사용하는 경우 잠재적인 성능 문제를 유발 할 수 있다. 이 사례를 다음의 예시를 통해 살펴보자.

 

 

실습을 위해 예제 테이블을 생성한다. 데이터베이스는 AdventureWorks (AdventureWorks2012)를 사용하였으며 스크립트는 Adam 블로그를 참고 하였다.

http://sqlblog.com/blogs/adam_machanic/archive/2011/10/17/thinking-big-adventure.aspx

 

/*

This script creates two new tables in AdventureWorks:

 

dbo.bigProduct

dbo.bigTransactionHistory

*/

 

 

SELECT

    p.ProductID + (a.number * 1000) AS ProductID,

    p.Name + CONVERT(VARCHAR, (a.number * 1000)) AS Name,

    p.ProductNumber + '-' + CONVERT(VARCHAR, (a.number * 1000)) AS ProductNumber,

    p.MakeFlag,

    p.FinishedGoodsFlag,

    p.Color,

    p.SafetyStockLevel,

    p.ReorderPoint,

    p.StandardCost,

    p.ListPrice,

    p.Size,

    p.SizeUnitMeasureCode,

    p.WeightUnitMeasureCode,

    p.Weight,

    p.DaysToManufacture,

    p.ProductLine,

    p.Class,

    p.Style,

    p.ProductSubcategoryID,

    p.ProductModelID,

    p.SellStartDate,

    p.SellEndDate,

    p.DiscontinuedDate

INTO bigProduct

FROM Production.Product AS p

CROSS JOIN master..spt_values AS a

WHERE

    a.type = 'p'

    AND a.number BETWEEN 1 AND 50

GO

 

 

ALTER TABLE bigProduct

ALTER COLUMN ProductId INT NOT NULL    

GO

 

ALTER TABLE bigProduct

ADD CONSTRAINT pk_bigProduct PRIMARY KEY (ProductId)

GO

 

 

SELECT

    ROW_NUMBER() OVER

    (

        ORDER BY

            x.TransactionDate,

            (SELECT NEWID())

    ) AS TransactionID,

    p1.ProductID,

    x.TransactionDate,

    x.Quantity,

    CONVERT(MONEY, p1.ListPrice * x.Quantity * RAND(CHECKSUM(NEWID())) * 2) AS ActualCost

INTO bigTransactionHistory

FROM

(

    SELECT

        p.ProductID,

        p.ListPrice,

        CASE

            WHEN p.productid % 26 = 0 THEN 26

            WHEN p.productid % 25 = 0 THEN 25

            WHEN p.productid % 24 = 0 THEN 24

            WHEN p.productid % 23 = 0 THEN 23

            WHEN p.productid % 22 = 0 THEN 22

            WHEN p.productid % 21 = 0 THEN 21

            WHEN p.productid % 20 = 0 THEN 20

            WHEN p.productid % 19 = 0 THEN 19

            WHEN p.productid % 18 = 0 THEN 18

            WHEN p.productid % 17 = 0 THEN 17

            WHEN p.productid % 16 = 0 THEN 16

            WHEN p.productid % 15 = 0 THEN 15

            WHEN p.productid % 14 = 0 THEN 14

            WHEN p.productid % 13 = 0 THEN 13

            WHEN p.productid % 12 = 0 THEN 12

            WHEN p.productid % 11 = 0 THEN 11

            WHEN p.productid % 10 = 0 THEN 10

            WHEN p.productid % 9 = 0 THEN 9

            WHEN p.productid % 8 = 0 THEN 8

            WHEN p.productid % 7 = 0 THEN 7

            WHEN p.productid % 6 = 0 THEN 6

            WHEN p.productid % 5 = 0 THEN 5

            WHEN p.productid % 4 = 0 THEN 4

            WHEN p.productid % 3 = 0 THEN 3

            WHEN p.productid % 2 = 0 THEN 2

            ELSE 1

        END AS ProductGroup

    FROM bigproduct p

) AS p1

CROSS APPLY

(

    SELECT

        transactionDate,

        CONVERT(INT, (RAND(CHECKSUM(NEWID())) * 100) + 1) AS Quantity

    FROM

    (

        SELECT

            DATEADD(dd, number, '20050101') AS transactionDate,

            NTILE(p1.ProductGroup) OVER

            (

                ORDER BY number

            ) AS groupRange

        FROM master..spt_values

        WHERE

            type = 'p'

    ) AS z

    WHERE

        z.groupRange % 2 = 1

) AS x

 

 

 

ALTER TABLE bigTransactionHistory

ALTER COLUMN TransactionID INT NOT NULL

GO

 

 

ALTER TABLE bigTransactionHistory

ADD CONSTRAINT pk_bigTransactionHistory PRIMARY KEY (TransactionID)

GO

 

 

CREATE NONCLUSTERED INDEX IX_ProductId_TransactionDate

ON bigTransactionHistory

(

    ProductId,

    TransactionDate

)

INCLUDE

(

    Quantity,

    ActualCost

)

GO

 

 

아담의 빅쿼리 테이블이 완성되었으면 다음의 스크립트를 실행한다. 이때 실행계획을 볼 수 있도한다. 스크립트에서 매개변수화의 조건을 1002로 하였다.

select

    p.ProductID,

    p.Name as Product,

    th.ActualCost,

    th.Quantity,

    pm.Name as ProductModel

from dbo.bigTransactionHistory th

    inner join dbo.bigProduct p on th.ProductID = p.ProductID

    left join Production.ProductModel pm on p.ProductModelID = pm.ProductModelID

where p.ProductID < 1002;

 

 

 

이번에는 매개변수화의 조건을 50532로 지정한다.

select

    p.ProductID,

    p.Name as Product,

    th.ActualCost,

    th.Quantity,

    pm.Name as ProductModel

from dbo.bigTransactionHistory th

    inner join dbo.bigProduct p on th.ProductID = p.ProductID

    left join Production.ProductModel pm on p.ProductModelID = pm.ProductModelID

where p.ProductID < 50532;

 

 

다음 스크립트를 사용하여 캐시된 실행 계획 및 재사용 여부를 확인 할 수 있다.

select

    cp.objtype,

    cp.cacheobjtype,

    cp.usecounts,

    st.text,

    qp.query_plan

from sys.dm_exec_cached_plans cp

outer apply sys.dm_exec_sql_text(cp.plan_handle) st

outer apply sys.dm_exec_query_plan(cp.plan_handle) qp

where st.text like '%bigTransactionHistory%' and st.text not like '%dm_exec_cached_plans%';

 

결과를 보면 두 Ad-hoc 쿼리에 대한 실행계획이 있음을 알 수 있다. Query_plan을 클릭하여 보면 실행 계획을 볼 수 있다. 서로 다른 두 쿼리에 대해서 두 개의 실행계획이 있으며 서로 다른 계획으로 실행 된 것을 확인 할 수 있다.

 

 

강제 매개변수화를 사용하여 플랜을 재사용하도록 하여 보자.

exec sp_executesql N'

select

    p.ProductID,

    p.Name as Product,

    th.ActualCost,

    th.Quantity,

    pm.Name as ProductModel

from dbo.bigTransactionHistory th

    inner join dbo.bigProduct p on th.ProductID = p.ProductID

    left join Production.ProductModel pm on p.ProductModelID = pm.ProductModelID

where p.ProductID < @a', N'@a int', @a = 1022

 

 

50532의 매개 변수 값을 사용하였으나 OutofMemory로 인하여 부득이하게 수치를 좀더 낮은 숫자로 변경하였다.

exec sp_executesql N'

select

    p.ProductID,

    p.Name as Product,

    th.ActualCost,

    th.Quantity,

    pm.Name as ProductModel

from dbo.bigTransactionHistory th

    inner join dbo.bigProduct p on th.ProductID = p.ProductID

    left join Production.ProductModel pm on p.ProductModelID = pm.ProductModelID

where p.ProductID < @a', N'@a int', @a = 7000

 

 

 

7000에 대한 계획을 보면 매우 심하게 실망할 수도 있다. 이는 초기의 계획(1002)이 재사용되었기 때문이다. 예상행수는 약 5000건 정도였지만 실제 행수는 300만 이상이었다. 이 게시물의 XML 실행계획을 살펴보면 컴파일값과 런타임 값의 차이를 확인 할 수 있다.

 

 

 

캐시된 플랜을 보면 재사용 된 것을 확인 할 수 있다.

 

결국에 특정 문제를 해결하기 위해 강제 매개 변수화를 하는 것은 상황에 따라 더 큰 문제를 불러 올 수도 있다. 따라서 강제 매개 변수화를 하기 전에 변수화 계획을 광범위 하게 분석하고 테스트할 필요가 있다.

 

 

[참고자료]

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/09/03/forced-parameterization-can-lead-to-poor-performance.aspx

SQL Server 쿼리 처리 아키텍처 : http://sqlmvp.kr/140188321707

SQL Server 강제 매개 변수화 : http://sqlmvp.kr/140189090317

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2306
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1560
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1743
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4606
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1744
» 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1562
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1540
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7854
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2214
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1243
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1772
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1782
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1371
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1473
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1862
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1651
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1392
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1161
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2534





XE Login