분류없음2014.06.30 11:43

 

 

[설명]

   

SQL Server 2014에서는 Buffer Pool의 영역을 RAM 뿐만 아니라 디스크의 영역 까지 넓혔습니다.

해당 기능은 두 가지 정도의 이점을 얻을 수 있습니다.

  1. 기존 데이터를 가져오는 방식의 step을 줄일 수 있습니다.(메모리->디스크->메모리)
  2. Standard Edition의 경우에는 지원하는 Memory가 제한이 되어 있는 데, 이러한 부분에 대한 고민을 해소 시켜 줄수 있을 것입니다.

단, 주의 사항이 있습니다. 이러한 Buffer Pool을 SSD와 같이 I/O가 높은 디스크에 구성을 해주셔야 원하는 Buffer Pool 성능을 낼 수 있습니다. 그리고 이 기능을 적용 했다가, Disable을 할 경우에는 인스턴스를 재시작 해야하기 때문에 반드시 운영 환경을 고려하고 적용하길 권고 드립니다.

   

   

[구성]

   

--테스트를 위해, 메모리 적절하게 설정을 합니다.

EXEC sp_configure 'show advanced options', 1

reconfigure

   

EXEC sp_configure 'max server memory (MB)', 1024;

EXEC sp_configure 'min server memory (MB)', 1024;

GO

RECONFIGURE;

GO

   

   

--C:\ BPoolEx 폴더를 생성합니다.

   

--buffer pool Extension 적용합니다.

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION

ON (FILENAME = 'C:\BPoolEx\BPEFILE.BPE', SIZE = 4 GB);

GO

 

 

 

--BPE 상태 확인

select * from sys.dm_os_buffer_pool_extension_configuration

   

   

--테스트 테이블 생성

SELECT * into dbo.FactResellerSales_BP FROM FactResellerSales WHERE 1=0 --테스트 테이블 생성

GO

   

--DMV 확인하면서, 테스트 데이터를 입력합니다.

INSERT INTO dbo.FactResellerSales_BP WITH(TABLOCK)

SELECT * FROM dbo.FactResellerSales

GO 20

   

   

-- sys.dm_os_buffer_descriptors 새로 추가된 컬럼으로 buffer pool Extension 있는 페이지를 확인할 있습니다.

select * from sys.dm_os_buffer_descriptors where is_in_bpool_extension = 1

   

   

--메모리를 다시 원상 복구 합니다.

EXEC sp_configure 'max server memory (MB)', 2048;

EXEC sp_configure 'min server memory (MB)', 2048;

GO

RECONFIGURE;

GO

   

   

[참고]

   

http://msdn.microsoft.com/en-us/library/dn133176.aspx

   

   

 

신고
Posted by TM ~ing
MS SQL2014.04.24 09:20

 

 

[내용]

   

SQL Server 2012에서 부터 Columnstore 인덱스가 나오기 시작했습니다. 하지만 2012 버전에서는 Nonclustered Index만 지원을 했습니다. 2014 버전 부터는 Clustered Conlumnstore 인덱스를 지원하기 시작했습니다. 추가로, 2012에서는 데이터 변경이 불가능 했으나 2014부터는 데이터 변경이 가능하게 되었습니다. 그럼 Clustered Columnstore Index에 대해 좀 더 알아 보도록 하겠습니다.

   

   

[환경]

   

OS: Windows Server 2012 R2 Ent

Product: SQL Server 2014 RTM

   

   

[Test]

   

-- 데이터 베이스에 연결합니다.

USE AdventureWorksDW2012;

GO

   

--테스트용 테이블을 생성합니다.

SELECT *

INTO dbo.FactResellerSales_CCI

FROM dbo.FactResellerSales

GO

   

--인덱스 생성 전에 테이블 크기와 인덱스를 확인합니다.

exec sp_spaceused FactResellerSales_CCI

   

select * from sys.indexes where object_id = object_id('FactResellerSales_CCI')

   

--이제 Clustered Columnstore Index를 생성합니다.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactResellerSales_CCI ON FactResellerSales_CCI

--인덱스 생성 후 테이블 크기와 인덱스를 확인합니다.

exec sp_spaceused FactResellerSales_CCI

   

select * from sys.indexes where object_id = object_id('FactResellerSales_CCI')

   

--테이블의 내용을 수정해 봅니다.

update FactResellerSales_CCI set UnitPrice = UnitPrice * 10

insert into FactResellerSales_CCI select top 1 * from FactResellerSales

   

--테스트 쿼리로 ColumnStoreindex를 잘 사용하는 확인해봅니다.

SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory

FROM FactResellerSales_CCI

GROUP BY SalesTerritoryKey;

   

   

[참고]

   

http://msdn.microsoft.com/en-us/library/gg492088(v=sql.120).aspx

http://msdn.microsoft.com/en-us/library/gg492153(v=sql.120).aspx

 

 

신고
Posted by TM ~ing
MS SQL2014.04.23 12:03

   

   

[내용]

   

SQL Server 2014에서 부터는 파티션 Index Rebuild, 파티션 Switch 시, Online 작업이 가능 합니다.

이런 Online 작업 시에 잠금의 우선 순위를 정하는 옵션이 새롭게 추가 되었습니다.

   

   

[환경]

   

OS: Windows Server 2012 R2 Ent

Product: SQL Server 2014 RTM

   

   

[Test1]

   

--세션1

use AdventureWorksDW2012

go

   

-- 파티션 테이블을 만들기 위하여, 파티션 함수를 만듭니다.

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT

FOR VALUES (

20050701, 20050801, 20050901, 20051001, 20051101, 20051201,

20060101, 20060201, 20060301, 20060401, 20060501, 20060601,

20060701, 20060801, 20060901, 20061001, 20061101, 20061201,

20070101, 20070201, 20070301, 20070401, 20070501, 20070601,

20070701, 20070801, 20070901, 20071001, 20071101, 20071201,

20080101, 20080201, 20080301, 20080401, 20080501, 20080601,

20080701, 20080801, 20080901, 20081001, 20081101, 20081201

)

GO

   

-- 파티션 테이블을 만들기 위하여, 파티션 구성표를 만듭니다.

CREATE PARTITION SCHEME [ByOrderDateMonthRange]

AS PARTITION [ByOrderDateMonthPF]

ALL TO ([PRIMARY])

GO

   

-- 파티션 구성표에 의거한, 테이블을 생성합니다.

CREATE TABLE [dbo].[FactResellerSalesPtnd](

[ProductKey] [int] NOT NULL,

[OrderDateKey] [int] NOT NULL,

[DueDateKey] [int] NOT NULL,

[ShipDateKey] [int] NOT NULL,

[CustomerKey] [int] NOT NULL,

[EmployeeKey] [int] NOT NULL,

[PromotionKey] [int] NOT NULL,

[CurrencyKey] [int] NOT NULL,

[SalesTerritoryKey] [int] NOT NULL,

[SalesOrderNumber] [nvarchar](20) NOT NULL,

[SalesOrderLineNumber] [tinyint] NOT NULL,

[RevisionNumber] [tinyint] NULL,

[OrderQuantity] [smallint] NULL,

[UnitPrice] [money] NULL,

[ExtendedAmount] [money] NULL,

[UnitPriceDiscountPct] [float] NULL,

[DiscountAmount] [float] NULL,

[ProductStandardCost] [money] NULL,

[TotalProductCost] [money] NULL,

[SalesAmount] [money] NULL,

[TaxAmt] [money] NULL,

[Freight] [money] NULL,

[CarrierTrackingNumber] [nvarchar](25) NULL,

[CustomerPONumber] [nvarchar](25) NULL,

OrderDate [datetime] NULL,

DueDate [datetime] NULL,

ShipDate [datetime] NULL

) ON ByOrderDateMonthRange(OrderDateKey);

GO

   

--파티션된 테이블에 데이터를 입력합니다.

INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)

SELECT * FROM dbo.FactResellerSales

GO

   

   

--PK 생성 및 NCI를 생성합니다.

alter table FactResellerSalesPtnd add constraint PK_FactResellerSalesPtnd_SalesOrderNumber_SalesOrderLineNumber primary key (OrderDateKey, SalesOrderNumber, SalesOrderLineNumber)

GO

   

create index IX_FactResellerSales_ShipDateKey on FactResellerSalesPtnd(ShipDateKey)

GO

   

exec sp_helpindex FactResellerSalesPtnd

   

--파티션(4) 인덱스 리빌드를 온라인으로 진행합니다.

ALTER INDEX IX_FactResellerSales_ShipDateKey ON dbo.FactResellerSalesPtnd

REBUILD Partition = 4

WITH ( ONLINE = ON)

GO

   

   

[Test2]

   

--잠금 우선 순위를 낮게 설정 하고, 리빌드 작업을 진행합니다.

ALTER INDEX IX_FactResellerSales_ShipDateKey ON dbo.FactResellerSalesPtnd

REBUILD Partition = 5

WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 minutes, ABORT_AFTER_WAIT = SELF )))

GO

   

   

[Test3]

   

--세션2에서 파티션(5)에 대한 X잠금을 설정합니다.

begin tran

update FactResellerSalesPtnd set ShipDateKey = ShipDateKey + 1 where OrderDateKey = 20051001

   

--세션1에서 잠금 우선 순위를 낮게 설정 하고, 리빌드 작업을 진행합니다.

ALTER INDEX IX_FactResellerSales_ShipDateKey ON dbo.FactResellerSalesPtnd

REBUILD Partition = 5

WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 minutes, ABORT_AFTER_WAIT = SELF )))

GO

   

-- 아래와 같이 인덱스 리빌드 작업이 취소 된 것을 확인할 수 있습니다.

   

-- 세션1에서 ABORT_AFTERWAIT 옵션을 blockers로 변경하고, 리빌드 작업을 진행합니다.

ALTER INDEX IX_FactResellerSales_ShipDateKey ON dbo.FactResellerSalesPtnd

REBUILD Partition = 5

WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 minutes, ABORT_AFTER_WAIT = blockers )))

GO

   

-- 아래와 같이 인덱스 리빌드 작업이 완료 된것을 확인할 수 있습니다.

   

[Test4]

   

--데이터베이스로 이동합니다.

use AdventureWorksDW2012

go

   

--Switch 대상 테이블 생성합니다.

SELECT * INTO FactResellerSalesPtnd_SWITCH FROM FactResellerSalesPtnd WHERE 1=0;

go

alter table FactResellerSalesPtnd_SWITCH add constraint PK_FactResellerSalesPtnd_SWITCH primary key (OrderDateKey, SalesOrderNumber, SalesOrderLineNumber);

go

   

SP_HELP '[dbo].[FactResellerSalesPtnd_SWITCH]'

   

--파티션(2)를 switch 대상 테이블로 swith 합니다.

ALTER TABLE FactResellerSalesPtnd SWITCH PARTITION 2 TO FactResellerSalesPtnd_SWITCH

WITH ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF ))

GO

   

   

[추가 설명]

   

   

   

[참고]

   

http://msdn.microsoft.com/en-us/library/ms188388(v=sql.120).aspx

   

   

신고
Posted by TM ~ing

티스토리 툴바