sql 전문가 과정 9장

 

17장 인메모리 OLTP is a highly optimized

 

Memory optimization advisor

메모리 옵티마이즈드 파일그룹이 추가된 db환경에서만 가능하다.

 

 

 

18장 columnstore indexes

 

B-tree 구조와 다르다.

2012부터 도입되었다.

2012	2014	2016
·  Type : non-clustered columnstore index ·  일반적인 b-tree는 필요한 인덱스만 추가하는 거였는데 ·  가능한 많은 필드를 포함하라는 게 포인트였음. ·  DML : 더 이상 insert/delete 이런 게 안됨. ·  인덱스를 drop하고 신규로 생성해줘야 하는 이런 작업이 발생됨. ·  여기서는 b-tree 인덱스 생성 가능.	·  Type : Updatable clustered columnstore index ·  테이블을 통째로 클러스터 인덱스로 만듦. ·  테이블에다 제약조건처럼 컬럼스토어 인덱스를 걸어버리면 b-tree 인덱스는 못 만들지만 대신 데이터 갱신은 가능하다. n  B-tree형식을 못 만들어서 primary key 못 만들고 foreign key 못 만든다. ·  DW형 workload에 적당하다라고 본다. ·  데이터스토어는 첨 들어오면 b-tree형식으로 들어오다가 데이터가 압축이 되면 그때 columnstore형으로 변형 ·  Columnstore형으로 해놨을 때 데이터를 삭제하는 게 아니라 필드만 삭제하고 삭제하진 않는다. ·  삭제된 데이터도 컬럼스토어에 남아있는 비효율적인 면이 있다.	·  Type : updatable clustered + updatable non-clustered column store index 가능 ·  B-tree 인덱스 생성도 가능.

 

 

 

 

 

 

컬럼스토어 아카이브라고 할 수도 있다. 기존 대비 약 30% 압축됨. 대신 조회시 cpu 연산은 더 많이 소요됨.

• Varchar 타입 평균치

 

Rowgroup의 단위가 1024 * 1024 인데 delta store에 1024*1024 (약 백만건) 저장된 이후에는 압축을 시키고 그 뒤에 columnstore로 이동된다.

Open된 상태에서 데이터를 지우면 바로 지워지지만, 컬럼스토어에 저장된 이후에는 비트맵 정보만 지우고 실제 데이터는 나중에 지워진다.

 

 

LAB1 컬럼스토어 인덱스

Non clustered 컬럼스토어 생김

 

 

 

 

 

 

 

 

 

 

 

?? ??: SQL Server 2012 - SQL Server 2016

??????? ?? ??? ?????.

NONE = ???? ?? ?????????. PARTIAL = ????? ??? ?????????.

http://nexondbteam.tistory.com/84

*/

CONTAINMENT = NONE

ON  PRIMARY

( NAME = N'Contoso_DR_Data', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\DATA\\Contoso_Restore_Data.mdf' , SIZE = 51200KB , MAXSIZE = 204800KB , FILEGROWTH = 20480KB ),

( NAME = N'Contoso_DR_Data1', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\DATA\\Contoso_Restore_Data1.ndf' , SIZE = 51200KB , MAXSIZE = 204800KB , FILEGROWTH = 20480KB ),

FILEGROUP [Archive]

( NAME = N'Contoso_DR_Archive', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\DATA\\Contoso_Restore_Archive.ndf' , SIZE = 51200KB , MAXSIZE = 102400KB , FILEGROWTH = 51200KB ),

FILEGROUP [Filegroup1]

( NAME = N'Contoso_DR_Current', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\DATA\\Contoso_Restore_Current.ndf' , SIZE = 51200KB , MAXSIZE = 102400KB , FILEGROWTH = 51200KB )

LOG ON

( NAME = N'Contoso_DR_Log', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQL\\DATA\\Contoso_Restore_Log.ldf' , SIZE = 51200KB , MAXSIZE = 204800KB , FILEGROWTH = 20480KB )

GO

 

--2. ? FileGroup? ??? ??

 

use contoso_restore

go

 

CREATE TABLE dbo.T1(

ID INT IDENTITY NOT NULL,

NAME VARCHAR(30)

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Sales](

[SalesNumber] [int] IDENTITY(1,1) NOT NULL,

[SalesDate] [datetime] NOT NULL,

[TotalSales] [money] NULL

) ON Filegroup1

GO

 

CREATE TABLE [dbo].[Sales_archive](

[SalesNumber] [int] IDENTITY(1,1) NOT NULL,

[SalesDate] [datetime] NOT NULL,

[TotalSales] [money] NULL

) ON Archive

GO

 

insert into T1 values ('A')

go

insert into sales_archive values ('2014-01-01', 300)

insert into sales_archive values ('2014-02-01', 200)

insert into sales_archive values ('2014-04-01', 500)

go

insert into sales values (getdate(), 1100)

go

 

ALTER DATABASE [Contoso_Restore] MODIFY FILEGROUP [Archive] READONLY

go

 

3. Full / Filegroup ?? ??

 

-- Full backup

backup database Contoso_Restore to disk = 'C:\\Backup\\Contoso_Restore_full.bak' with init, stats=10

go

 

--Filegroup backup

Backup Database Contoso_Restore FILEGROUP = 'Primary' to disk = 'C:\\backup\\Contoso_Restore_FG_Primary.bak'

Backup Database Contoso_Restore FILEGROUP = 'FileGroup1' to disk = 'C:\\backup\\Contoso_Restore_FG_FileGroup1.bak'

Backup Database Contoso_Restore FILEGROUP = 'Archive' to disk = 'C:\\backup\\Contoso_DR_Restore_Archive.bak'

go

 

4. ??? ?? ? Log, FG ?? ??

 

insert into T1 values ('B')

go

insert into sales values (getdate(), 5000)

go

 

Backup log Contoso_Restore to disk = 'C:\\backup\\Contoso_Restore_Log1.trn'

go

Backup Database Contoso_Restore FILEGROUP = 'Primary' to disk = 'C:\\backup\\Contoso_Restore_FG_Primary_2nd.bak'

Backup Database Contoso_Restore FILEGROUP = 'FileGroup1' to disk = 'C:\\backup\\Contoso_Restore_FG_FileGroup1_2nd.bak'

go

 

insert into T1 values ('C')

go

insert into sales values (getdate(), 3000)

go

 

Backup database Contoso_Restore to disk = 'C:\\backup\\Contoso_Restore_FG_Diff.bak' WITH DIFFERENTIAL

 

insert into T1 values ('D')

go

insert into sales values (getdate(), 7000)

go

 

Backup log Contoso_Restore to disk = 'C:\\backup\\Contoso_Restore_Log2.trn'

go

 

5. Backup History ??

 

Use MSDB

go

 

SELECT  

  msdb.dbo.backupset.database_name,  

  msdb.dbo.backupset.backup_start_date,  

  msdb.dbo.backupset.backup_finish_date,

  CASE msdb..backupset.type  

     WHEN 'D' THEN 'Database'  

     WHEN 'L' THEN 'Log'  

  WHEN 'I' THEN 'Differential'

  WHEN 'F' THEN 'Filegroup'

  END AS backup_type,  

  msdb.dbo.backupmediafamily.physical_device_name

FROM   msdb.dbo.backupmediafamily  

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  

WHERE msdb.dbo.backupset.database_name = 'Contoso_Restore'

ORDER BY  

  msdb.dbo.backupset.backup_finish_date