SQL Server 2017에서 향상된 UDF 실행 계획

 

·      Version : SQL Server 202017 CU3

 

SQL Server 2017 CU3 이후 부터 UDF(User-Defined Function) 실행계획이 개선되어 사용자에게 많은 정보를 보여주게 되었다. (필자가 쓰는 시점으로 최신 CU7까지 출시 되었다.) 이전에는 쿼리에 참조된 UDF 사용할 쿼리 실행 시간의 상당 부분이 함수 내에서 발생하여도 쿼리 실행계획을 보고 쉽게 파악하기가 어려웠다. SQL Server 2017 CU3 이후 부터는UdfCpuTime UdfElaspsedTime 가지 새로운 속성이 추가되어 사용자 정의 함수내에서 사용되는 리소스를 확인할 있게 되었다.

 

아래 스크립트는 테스트를 구성하기 위한 UDF 생성 스크립트이다. 테스트 쿼리를 실행하기 위해서는 Adventureworks 설치되어 있어야 한다.

-- Create UDF

CREATE FUNCTION ufn_CategorizePrice(@Price money)

RETURNS NVARCHAR(50)

AS

BEGIN

    DECLARE @PriceCategory NVARCHAR(50)

 

    IF @Price < 100 SELECT @PriceCategory = 'Cheap'

    IF @Price BETWEEN 101 and 500 SELECT @PriceCategory =  'Mid Price'

    IF @Price BETWEEN 501 and 1000 SELECT @PriceCategory =  'Expensive'

    IF @Price > 1001 SELECT @PriceCategory =  'Unaffordable'

    RETURN @PriceCategory

END

GO

 

아래 스크립트는 UDF 사용한 데이터 조회 스크립트이다. 실행 계획을 살펴보면 UDF 관련 정보를 확인할 있다.

-- Execute

SELECT dbo.ufn_CategorizePrice(UnitPrice),

    SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,

    OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount,

    LineTotal, rowguid, ModifiedDate

FROM Sales.SalesOrderDetail

GO

 


 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/more-showplan-enhancements-udfs/

 

  

2018-06-04 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

SQL 2017, MS SQL, UDF 실행계획, 실행계획, DBA, DB튜닝, 향샹된 실행 계획 보기, SQL Server 2017, Showplan enhancements for UDF

Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화

 

  • Version : SQL Server 2012, 2014, 2016

 

SQL Server는 쿼리를 실행 할 때 다양한 비용을 계산하여 최적의 계획을 생성하고 사용한다. 여러 사용 가능한 실행 계획 중에 비용이 가장 적게 드는 하나의 실행 계획을 선택하는 프로세스를 최적화라 한다.

 

쿼리 처리에 대한 아키텍처는 아래 링크를 참고 한다.

 

SQL Server에서 쿼리를 실행 할 때 옵티마이저가 최적의 실행 계획을 만들어 사용할 수도 있지만 쿼리를 튜닝하는 과정에서 옵티마이저의 최적화를 사용하지 않도록 비활성화 할 수 있다. 문서화 되지 않은 Query Rule Off 명령을 사용하여 옵티마이저를 비활성화 하는 방법에 대해서 살펴본다.

 

아래 스크립트를 실행하면 현재 SQL Server의 옵티마이저 규칙을 확인할 수 있다. 매우 많은 규칙 목록이 있음을 확인할 수 있다.

USE master

GO

 

DBCC TRACEON(3604)

GO

 

DBCC SHOWONRULES

GO

 

DBCC SHOWOFFRULES

GO

 

 

규칙 이름은 대부분 해석하기 쉽게 되어 있다. 몇 가지 규칙에 대한 설명을 살펴보면 다음과 같다.

Rule Name

Description

JNtoNL

Join to Nested Loop

JNtoHS

Join to Hash

JNtoSM

Join to Sort Merge

LOJNtoNL

Left Outer Join to Nested Loop

LSJNtoHS

Left Semi join to Hash

LASJNtoSM

Left Anti Semi Join to Sort Merge

 

Query Rule OFF를 적용하면 어떻게 쿼리 실행계획이 변경되는지 실습을 통해서 알아본다. 이번 실습은 AdventureWorks2012를 사용하였다.

 

아래 스크립트를 실행하여 쿼리 실행 계획을 확인해보자. 옵티마이저는 최적화 규칙으로 해시조인을 사용하여 조인 순서를 결정하였다.

USE AdventureWorks2012

GO

 

SELECT

    c.CustomerID ,

    c.PersonID ,

    c.StoreID ,

    c.TerritoryID ,

    c.AccountNumber ,

    c.rowguid ,

    c.ModifiedDate

FROM Sales.SalesOrderHeader OH

    INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

GO

 

 

아래 스크립트는 QUERYRULEOFF JoinCommute를 사용하여 옵티마이저 비활성화와 함께 조인의 순서를 결정하지 않도록 하였다. 조인 순서가 변경된 것을 확인할 수 있다.

USE AdventureWorks2012

GO

 

SELECT c.CustomerID ,

c.PersonID ,

c.StoreID ,

c.TerritoryID ,

c.AccountNumber ,

c.rowguid ,

c.ModifiedDate

FROM Sales.SalesOrderHeader OH

INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

OPTION( QUERYRULEOFF JoinCommute )

GO

 

 

위의 두 실행계획에서 더 나은 실행 계획을 비교하기 위해 예상 하위 트리 비용을 비교해보았다. 이렇게 하면 JoinCommute를 사용한 쿼리 계획이 더 낮은 비용을 사용하였음을 확인할 수 있다.

 

아래 스크립트는 옵티마이저가 JNtoHS 규칙을 사용하지 않도록 하였다. 옵티마이저는 JNtoHS 규칙을 제외한 나머지 규칙에서 최적화 계획은 Merge를 사용한 것을 확인할 수 있다.

USE AdventureWorks2012

GO

 

SELECT c.CustomerID ,

c.PersonID ,

c.StoreID ,

c.TerritoryID ,

c.AccountNumber ,

c.rowguid ,

c.ModifiedDate

FROM Sales.SalesOrderHeader OH

INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID

WHERE OH.ShipMethodID = 1

OPTION( QUERYRULEOFF JNtoHS )

GO

 

 

하위 트리 비용을 살펴보면 위의 두 상황보다 나쁜 선택을 한 것을 알 수 있다.

 

 

옵티마이저가 최적의 실행계획을 선택하기 위해서는 다양한 정보 (통계, 인덱스 등등)를 고려하여 판단하기 때문에 우리가 생각하는 이상의 복잡도를 가지고 있다. 옵티마이저를 비활성화 하여 사용하는 경우는 옵타미이저의 특성에 대해서 잘 알고 내가 의도하는 방향으로 실행 계획을 유도할 수 있을때만 사용할 수 있도록 한다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4175/disabling-sql-server-optimizer-rules-with-queryruleoff/

 

2016-03-03 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MS SQL, 옵타미이저, Optimizer, 쿼리 최적화, 쿼리 튜닝, TRACEON, QUERYRULEOFF, Optimizer Rule, DB튜닝, SQL튜닝

 

Spool 연산자와 추적 플래그 8690

 

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

 

쿼리 실행계획을 확인하다 보면 스풀(spool) 연산자(인덱스 스풀 또는 테이블 스풀)를 볼 수 있다. 스풀 연산자는 중간 결과를 저장하기 때문에 SQL의 다시 스캔 하거나, 또는 반복 사용을 위해 다시 계산할 필요가 없도록하여 쿼리 성능을 개선하는데 도움이 된다.

 

스풀 테이블은 tempdb 데이터베이스 저장되어 쿼리 사용 기간 중에만 존재한다. 예를 들어 Nested Loops 연산자로 연산자를 다시 수행하지만 다시 바인딩할 필요가 없을 경우 입력 사항을 다시 검색하는 대신 스풀된 데이터를 사용한다. 스풀 실행 계획은 아래 그림과 같은 아이콘으로 표시 된다.

 

스풀의 경우 대부분의 성능을 향상 시킬 수 있다. 그러나 데이터의 분포가 고르지 못하거나 또는 왜곡된 데이터 추정을 사용하여 성능 저하의 원인이 되기도 한다.

추적플래그 8690은 중첩루프 안쪽의 스풀을 사용하지 않도록 설정할 수 있다. 대부분의 상황에서는 수동으로 추적플래그를 사용하여 스풀을 해제할 필요가 없다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2015/12/15/spool-operator-and-trace-flag-8690/

https://technet.microsoft.com/ko-kr/library/ms181032(v=sql.105).aspx

 

 

2016-01-11 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, MSSQL, DB튜닝, 쿼리 튜닝, Table Spool, Trace 8690, Spool operator, tempdb

SQL Server 2016 Query Store

  • 실행된 쿼리에 대한 실행 계획 및 통계 정보를 확인하자
  • Version : SQL Server 2016

 

SQL Server 2016에 도입된 Query Store는 요청된 쿼리가 실행 될 때 실행 된 쿼리를 저장 및 실행 계획 등을 함께 저장하여 통계 및 계획을 추적하는데 도움이 된다.

 

쿼리를 저장하기 위해서는 Query Store를 활성화 해야 한다. SSMS 및 T-SQL을 사용하여 쿼리 스토어 기능을 활성화할 수 있다.

 

ALTER DATABASE [DEMO_1] SET QUERY_STORE = ON

GO

 

 

쿼리 스토어를 활성화하면 쿼리 저장과 관련된 다른 옵션을 설정할 수 있다. SSMS 및 T-SQL을 사용하여 설정을 변경할 수 있다.

 

ALTER DATABASE [DEMO_1]

SET QUERY_STORE (OPERATION_MODE = READ_ONLY,

                CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367),

                DATA_FLUSH_INTERVAL_SECONDS = 900,

                INTERVAL_LENGTH_MINUTES = 60,

                MAX_STORAGE_SIZE_MB = 100,

                QUERY_CAPTURE_MODE = AUTO,

                SIZE_BASED_CLEANUP_MODE = AUTO)

GO

 

설정 변경 옵션에 대한 자세한 내용은 MSDN을 참고 한다.

 

 

쿼리 스토어 속성 정보에서 현재 쿼리 스토어가 사용하고 있는 디스크 사용량을 확인할 수 있다. [Purge Query Data] 버튼을 사용하여 쿼리 스토어의 내용을 제거하거나 스크립트를 사용하여 제거할 수 있다. (참고로 CTP 버전에서는 sp_query_store_flush_db에 몇 가지 버그가 있다고 한다.)

 

ALTER DATABASE [DEMO_1] SET QUERY_STORE CLEAR ALL

GO

 

EXEC sys.sp_query_store_flush_db

 

 

아래 스크립트는 쿼리 저장 시스템 개체 및 새로운 확장 이벤트를 확인할 수 있다.

SELECT name, type_desc FROM sys.all_objects

WHERE name LIKE '%query_store%' or name= 'query_context_settings'

 

 

확장이벤트

설명

query_store_background_task_persist_started

Fired if the background task for Query Store data persistence started execution

query_store_background_task_persist_finished

Fired if the background task for Query Store data persistence is completed successfully

query_store_load_started

Fired when query store load is started

query_store_db_data_structs_not_released

Fired if Query Store data structures are not released when feature is turned OFF.

query_store_db_diagnostics

Periodically fired with Query Store diagnostics on database level.

query_store_db_settings_changed

Fired when Query Store settings are changed.

query_store_db_whitelisting_changed

Fired when Query Store database whitelisting state is changed.

query_store_global_mem_obj_size_kb

Periodically fired with Query Store global memory object size.

query_store_size_retention_cleanup_started

Fired when size retention policy clean-up task is started.

query_store_size_retention_cleanup_finished

Fired when size retention policy clean-up task is finished.

query_store_size_retention_cleanup_skipped

Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.

query_store_size_retention_query_deleted

Fired when size based retention policy deletes a query from Query Store.

query_store_size_retention_plan_cost

Fired when eviction cost is calculated for the plan.

query_store_size_retention_query_cost

Fired when query eviction cost is calculated for the query.

query_store_generate_showplan_failure

Fired when Query Store failed to store a query plan because the showplan generation failed.

query_store_capture_policy_evaluate

Fired when the capture policy is evaluated for a query.

query_store_capture_policy_start_capture

Fired when an UNDECIDED query is transitioning to CAPTURED.

query_store_capture_policy_abort_capture

Fired when an UNDECIDED query failed to transition to CAPTURED.

query_store_schema_consistency_check_failure

Fired when the Query Store schema consistency check failed.

 

자세한 내용은 MSDN을 참고 한다.

 

데이터베이스 속성에서 쿼리 스토어를 활성화 하면 SSMS에서 Query Store 컨테이너를 확인할 수 있다.

 

쿼리 저장 컨테이너에서 마우스 오른쪽을 클릭하면 기본으로 제공되는 리포트를 확인할 수 있다.

 

컨테이너를 확장하여 수집된 쿼리 스토어에 대한 정보를 확인할 수 있다.

 

수집된 데이터 중에 Top Resource Consuming Queries 정보를 살펴보자. (대부분 구성은 비슷하다.)

 

왼쪽의 차트는 수직 및 수평축에 대해 선택된 통계를 기반으로 전체 통계를 표시한다. 메트릭(1)과 통계(2)의 드롭다운 버튼으로 통계를 선택할 수 있다.

 

오른쪽 차트는 다른 색상으로 각 계획을 표시한다. 마우스 포인터를 올려놓으면 (3) 개체에 대한 query id 또는 Plan id에 대한 자세한 정보를 참조할 수 있다. 선택된 메트릭에 따라 상세 정보는 다르다.

 

 

오른쪽 차트의 버블 크기(4)는 실행의 총 수에 따라 달라진다. 다른 계획을 클릭하면(3, 4) 창 하단의 부분(5)이 특정 plan id에 대한 실행 계획을 표시한다. (6)은 창 이름을 나타내며 (7), (8), (9)는 현재 리포트 정보를 나타낸다.

 

 

아래 그림은 드롭 다운 메뉴를 살펴 본다. 모니터 해상도가 작을 경우 오른쪽의 일부 메뉴(6)가 숨겨질 수 있다. 숨겨진 메뉴는 확장해서 볼 수 있다.

 

Track Query(1) 버튼은 추적 쿼리 창을 보여준다.

 

View Query(2)는 T-SQL 스크립트로 새로운 SSMS창에 보여준다.

 

 

Detail Grid(3), Grid(4) 버튼은 통계 정보를 제공한다.

 

구성(5) 버튼은 한 곳에서 모든 창을 구성할 수 있다.

 

 

여러 계획 쿼리가 있는 경우 Compare Plans 버튼을 사용하여 왼쪽 차트와 실행계획을 비교해볼 수 있다.

 

강제 계획 버튼을 눌러 해당 계획을 사용할 수 있다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4009/sql-server-2016-query-store-introduction/

 

 

2015-09-01 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQL 2016, SQL Query Store, 쿼리 튜닝, DB튜닝, 쿼리 스토어, 실행계획 비교, 쿼리 모니터링

+ Recent posts