SQL Server 2016 Database Scoped Configuration Options(데이터베이스 범위 구성 옵션)

   

SQL Server 2016 의 새로운 기능이다.

각 데이터베이스의 옵션중에서 추가된 옵션이 아래와 같이 존재한다.

어떤 쓰임새가 있을지는 상황에 맞게 설정하면 좋을 것 같다.

   

Max DOP : Maximum degree of parallelism 개별 데이터베이스의 적용.

레거시 카디널리티 추정 (Legacy Cardinality Estimation) : 개별 데이터베이스의2012의 CE 를 적용하는 듯.

매개변수 검사(Parameter Sniffing) : 기존에는 엔진전체에 TF4136으로 Disabled 처리를 했었다.

쿼리 최정화 프로그램 수정(Query Optimizer Fixes) : 무슨 기능인지 잘 모르겠다.

   

보조(For Secondary)에 대한 옵션은 AlwaysOn Availability Groups 의 보조서버에 대한 설정이며, 서로 다르게 설정이 가능하다. 해당 옵션은 복제가 되는 부분이다.

   

해당 옵션을 확인하는 DMV도 있다.

   

use TD

go

-- MaxDOP 기        본값은 0 -> 2로 수정해봤다. 즉시적용되는지는 좀더 확인하자

select * from sys.database_scoped_configurations

   

   

해당 옵션을 T-SQL로도 가능하다.

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;

   

-- Enable or disable option for secondary

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = ON;

-- Set option for secondary to be the same as primary

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;

   

   

한 가지 더 옵션 창에서 확인이 안되는 기능도 있다.

데이터베이스로 플랜캐쉬를 초기화하는 방법도 있다. 기존의 DBCC 명령어로도 가능했다.

   

use TD

go

 

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

   

위 옵션은 아래와 같이 테스트를 해봤다.

TD 의 플랜캐쉬만 초기화를 하는 것을 볼 수 있다.

   

   

use TD

go

 

CREATE TABLE TBL(COL1 INT, COL2 VARCHAR(10))

 

INSERT INTO TBL (COL1, COL2)

VALUES (1, 1), (2,2)

 

SELECT * FROM TBL

 

-- 쿼리

select * from tbl where col1 = 1

-- SP

create proc a

as

select * from tbl where col2 = 1

exec a

 

 

SELECT DB_NAME(p.dbid) AS DBName ,

OBJECT_NAME(p.objectid, p.dbid) AS OBJECT_NAME ,

cp.usecounts ,

p.query_plan ,

q.text ,

p.dbid ,

p.objectid ,

p.number ,

p.encrypted ,

cp.plan_handle

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q

WHERE cp.cacheobjtype = 'Compiled Plan'

and p.dbid = 10 -- 전체, 개별 확인 조건

option(recompile)

 

--dbcc freeproccache

 

   

[참고문서]

SQL Server 2016 Database Scoped Configuration Options

https://www.mssqltips.com/sqlservertip/4245/sql-server-2016-database-scoped-configuration-options/