아래와 같은 6번 쿼리를 던지면 컴파일이 일어나거나 리컴파일이 일어난 2번만 플랜이 찍히게 된다.
너무 많은 플랜이 찍혀 힘들다면 사용해 볼 수 있다. 그러나 실제 실행계획이 아니라 이 또한 예상 실행 계획 이다.

산술 오류의 변경 SQL SERVER 2000 VS. 2005

아래 쿼리는 SQL SERVER 2000 에서는 에러가 없다.
그런데 SQL SERVER 2005 이상에서는 에러가 난다. 
이 얼마나 황당한가? 왜 그렇게 변경 되었을지 의견을 달아 봐라.

CREATE TABLE T1 (A INT, B CHAR(8))
INSERT T1 VALUES (0, '0')
INSERT T1 VALUES (1, '1')
INSERT T1 VALUES (99, 'ERROR')

CREATE TABLE T2 (X INT)
INSERT T2 VALUES (1)
INSERT T2 VALUES (2)
INSERT T2 VALUES (3)
INSERT T2 VALUES (4)
INSERT T2 VALUES (5)

set showplan_text on

SELECT T1.A, CONVERT(INT, T1.B) AS B_INT
FROM T2 JOIN T1  ON T1.A = T2.X
GO

StmtText
----------------------------------------------------------------------------
SELECT T1.A, CONVERT(INT, T1.B) AS B_INT
FROM T2 JOIN T1  ON T1.A = T2.X

(1개 행이 영향을 받음)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------
  |--Hash Match(Inner Join, HASH:([IOTest].[dbo].[T1].[A])=([IOTest].[dbo].[T2].[X]), RESIDUAL:([IOTest].[dbo].[T2].[X]=[IOTest].[dbo].[T1].[A]))
       |--Compute Scalar(DEFINE:([Expr1008]=CONVERT(int,[IOTest].[dbo].[T1].[B],0)))
       |    |--Table Scan(OBJECT:([IOTest].[dbo].[T1]))
       |--Table Scan(OBJECT:([IOTest].[dbo].[T2]))

(4개 행이 영향을 받음)

--메시지 245, 수준 16, 상태 1, 줄 1
--VARCHAR 값 'ERROR   '을(를) 데이터 형식 INT(으)로 변환하지 못했습니다.

SELECT T1.A, CONVERT(INT, T1.B) AS B_INT
  FROM T2
  JOIN T1 
    ON T1.A = T2.X
OPTION (FORCE ORDER)
GO

-- 정상수행

backup log dbname to disk ='c:\xxx' with norecovery, init, copy_only 옵션을 주면 데이터베스가 복원중 으로 변한다.  

copy 이후 실제 서버의 transaction log 를 apply 시켜 restore 를 했다.

emergency 모드에서 데이터를 확인해 봤다. 여기서 확인되는 데이터만 살릴수 있고,
delete update 는 잘 적용 되어 있고, insert 는 적용되지 않은것을 알 수 있다.

리커버리 후 상태이다.

결국 PFS, GAM만 복구하면 입력한 모든 데이터를 살릴수 있을듯도 하다.
본 테스트에서는 PFS 만 깨어졌지만 insert 용량이 크면 GAM 페이지도 깨어졌다.
그러므로 파일 copy 를 이용한 recovery 모드 변경후 transaction apply 시나리오는 불완전 복구만 가능하다.

이번 테스트를 통해서 비상로그 백업의 용도를 정확히 인지 할 수 있다.

오라클 데이터베이스의 경우 Hot Backup 모드를 지원한다. begin backup 모드로 들어가고 파일 복사 +  log apply 를 통해 데이터베이스를 완전한 상태로 복구 할 수 있다. 정말 부럽지 않은가?

SQL Server 아직 멀었다. 이게 쓸만하게 되려면 한 10년쯤 지나야 하지 않을까 싶다. 그때도 난 SQL 할꺼다...^^; 취미니깐



스크립트는 아래를 참조 한다.


use master
go

alter database CopyOnlyRecovery set single_user with rollback immediate
go

drop database CopyOnlyRecovery
go

use master
go

create database CopyOnlyRecovery
go

use CopyOnlyRecovery
go

create table tblx
(idx int identity(1,1)
,c1 char(5000)
)
go

create clustered index cl_tblx on tblx (idx)
go

insert into tblx values (1)
go 100

select top 1 * from tblx
go

backup database CopyOnlyRecovery to disk ='c:\f1.bak' with init
go

insert into tblx values (2)
go 100

backup log CopyOnlyRecovery to disk ='c:\l1.bak' with init
go

select top 1 * from tblx order by c1 desc
go

insert into tblx values (3)
go 100

select top 1 * from tblx order by c1 desc

checkpoint
go

select * from tblx

-- sqlserver stop
-- 위와 같은 상황에서 database shutdown 후 CopyOnlyRecovery 의 mdf 와 ldf 를 다른곳으로 copy!
-- sqlserver start

use CopyOnlyRecovery
go

insert into tblx values (4)
go 100

update tblx set c1 = 111 where idx = 1
go

select * from tblx
go

backup log CopyOnlyRecovery to disk ='c:\l2.bak' with init
go

insert into tblx values (5)
go 100

backup log CopyOnlyRecovery to disk ='c:\l3.bak' with init
go

update tblx set c1 = 222 where idx = 2
go

delete from tblx where idx = 3
go

select * from tblx where idx < 10

backup log CopyOnlyRecovery to disk ='c:\l4.bak' with init
go

checkpoint

-- 이제부터 예전 copy 한 파일로 부터 복원을 해보자.

--use master
--go
-- ***************************************************
-- 예전에 복사해둔 데이터베이스를 attach 한다. 
-- stop
-- copy
-- start

use master
go

-- 아래 명령을 날리면 3까지 복원된 상태에서 norecovery 모드로 변경된다. 마치 full backup 에서 복원한것 처럼
-- 사실 아래의 명령어는 mission critical server 에서 log backup 후 더이상의 transaction 이 일어나지 못하게
-- 막는 옵션이다.

alter database CopyOnlyRecovery set single_user with rollback immediate
go

backup log CopyOnlyRecovery to disk = 'c:\fail2.bak' with norecovery , init , COPY_ONLY
go

-- 데이터베이스는 복구중 모드로 변경된다. 여기에서 이후 로그를 어플라이 시킨다.

restore log CopyOnlyRecovery from disk = 'c:\fail2.bak' with norecovery
restore log CopyOnlyRecovery from disk = 'c:\l2.bak' with norecovery , continue_after_error
restore log CopyOnlyRecovery from disk = 'c:\l3.bak' with norecovery , continue_after_error
restore log CopyOnlyRecovery from disk = 'c:\l4.bak' with norecovery , continue_after_error
restore database CopyOnlyRecovery with recovery , continue_after_error

use master
go

alter database CopyOnlyRecovery set emergency
go

use CopyOnlyRecovery
go

select count(*) from tblx
go
-- 299

select top 10 * from tblx where idx < 10 order by idx
go

ALTER DATABASE CopyOnlyRecovery SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go

dbcc checkdb ('CopyOnlyRecovery', repair_allow_data_loss)
go

현재 데이터베이스를 다시 시작하지 못했습니다. 현재 데이터베이스가 master로 전환됩니다.
경고: 데이터베이스 'CopyOnlyRecovery'의 로그가 다시 작성되었습니다. 트랜잭션에 일관성이 없습니다. RESTORE 체인이 끊어져 서버에 이전 로그 파일에 대한 컨텍스트가 더 이상 없으므로 해당 로그 파일이 어떤 파일인지 알아야 합니다. DBCC CHECKDB를 실행하여 물리적 일관성을 확인해야 합니다. 데이터베이스가 dbo 전용 모드로 전환되었습니다. 데이터베이스를 사용할 수 있는 준비가 되면 데이터베이스 옵션을 다시 설정하고 모든 추가 로그 파일을 삭제하십시오.
메시지 2510, 수준 16, 상태 17, 줄 1
DBCC checkdb 오류: This system table index cannot be recreated.
'CopyOnlyRecovery'의 DBCC 결과입니다.
Service Broker 메시지 9675, 상태 1: 분석된 메시지 유형: 14.
Service Broker 메시지 9676, 상태 1: 분석된 서비스 계약: 6.
Service Broker 메시지 9667, 상태 1: 분석된 서비스: 3.
Service Broker 메시지 9668, 상태 1: 분석된 서비스 큐: 3.
Service Broker 메시지 9669, 상태 1: 분석된 대화 끝점: 0.
Service Broker 메시지 9674, 상태 1: 분석된 대화 그룹: 0.
Service Broker 메시지 9670, 상태 1: 분석된 원격 서비스 바인딩: 0.
Service Broker 메시지 9605, 상태 1: 대화 우선 순위 분석: 0.
'sys.sysrscols'의 DBCC 결과입니다.
7개 페이지에 개체 "sys.sysrscols"에 대한 행이 634개 있습니다.
'sys.sysrowsets'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysrowsets"에 대한 행이 92개 있습니다.
'sys.sysallocunits'의 DBCC 결과입니다.
2개 페이지에 개체 "sys.sysallocunits"에 대한 행이 104개 있습니다.
'sys.sysfiles1'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysfiles1"에 대한 행이 2개 있습니다.
'sys.syspriorities'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syspriorities"에 대한 행이 0개 있습니다.
'sys.sysfgfrag'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysfgfrag"에 대한 행이 2개 있습니다.
'sys.sysphfg'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysphfg"에 대한 행이 1개 있습니다.
'sys.sysprufiles'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysprufiles"에 대한 행이 2개 있습니다.
'sys.sysftinds'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysftinds"에 대한 행이 0개 있습니다.
'sys.sysowners'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysowners"에 대한 행이 14개 있습니다.
'sys.sysprivs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysprivs"에 대한 행이 130개 있습니다.
'sys.sysschobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysschobjs"에 대한 행이 54개 있습니다.
'sys.syscolpars'의 DBCC 결과입니다.
8개 페이지에 개체 "sys.syscolpars"에 대한 행이 485개 있습니다.
'sys.sysnsobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysnsobjs"에 대한 행이 1개 있습니다.
'sys.syscerts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscerts"에 대한 행이 0개 있습니다.
'sys.sysxprops'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysxprops"에 대한 행이 0개 있습니다.
'sys.sysscalartypes'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysscalartypes"에 대한 행이 34개 있습니다.
'sys.systypedsubobjs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.systypedsubobjs"에 대한 행이 0개 있습니다.
'sys.sysidxstats'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysidxstats"에 대한 행이 111개 있습니다.
'sys.sysiscols'의 DBCC 결과입니다.
메시지 8948, 수준 16, 상태 1, 줄 1
데이터베이스 오류: 페이지 (1:93)이(가) PFS 페이지 (1:1)에서 잘못된 유형으로 표시되어 있습니다. PFS 상태 0x0에는 0x60이(가) 필요합니다.
        오류가 복구되었습니다.
메시지 8948, 수준 16, 상태 1, 줄 1
데이터베이스 오류: 페이지 (1:94)이(가) PFS 페이지 (1:1)에서 잘못된 유형으로 표시되어 있습니다. PFS 상태 0x0에는 0x60이(가) 필요합니다.
        오류가 복구되었습니다.
2개 페이지에 개체 "sys.sysiscols"에 대한 행이 262개 있습니다.
CHECKDB이(가) 테이블 'sys.sysiscols'(개체 ID 55)에서 2개의 할당 오류와 0개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'sys.sysiscols'(개체 ID 55)에서 2개의 할당 오류와 0개의 일관성 오류를 수정했습니다.
'sys.sysbinobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysbinobjs"에 대한 행이 23개 있습니다.
'sys.sysaudacts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysaudacts"에 대한 행이 0개 있습니다.
'sys.sysobjvalues'의 DBCC 결과입니다.
복구: 데이터베이스 "CopyOnlyRecovery"에서 개체 "sys.sysobjvalues"의 Clustered 인덱스가 다시 작성되었습니다.
메시지 8945, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1을(를) 다시 작성합니다.
        오류가 복구되었습니다.
메시지 8976, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 부모 (1:142)과(와) 이전의 (1:152)이(가) 페이지 (1:568)을(를) 참조하지만 이 페이지가 검색에 없습니다. 이전 오류를 확인하십시오.
        오류가 복구되었습니다.
메시지 8980, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 인덱스 노드 페이지 (1:142), 슬롯 16에서 자식 페이지 (1:569)과(와) 이전 자식 페이지 (1:568)을(를) 참조하지만 해당 페이지가 없습니다.
        오류가 복구되었습니다.
메시지 8980, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 인덱스 노드 페이지 (1:142), 슬롯 17에서 자식 페이지 (1:570)과(와) 이전 자식 페이지 (1:569)을(를) 참조하지만 해당 페이지가 없습니다.
        오류가 복구되었습니다.
15개 페이지에 개체 "sys.sysobjvalues"에 대한 행이 89개 있습니다.
CHECKDB이(가) 테이블 'sys.sysobjvalues'(개체 ID 60)에서 0개의 할당 오류와 3개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'sys.sysobjvalues'(개체 ID 60)에서 0개의 할당 오류와 3개의 일관성 오류를 수정했습니다.
'sys.sysclsobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysclsobjs"에 대한 행이 16개 있습니다.
'sys.sysrowsetrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysrowsetrefs"에 대한 행이 0개 있습니다.
'sys.sysremsvcbinds'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysremsvcbinds"에 대한 행이 0개 있습니다.
'sys.sysxmitqueue'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysxmitqueue"에 대한 행이 0개 있습니다.
'sys.sysrts'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysrts"에 대한 행이 1개 있습니다.
'sys.sysconvgroup'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysconvgroup"에 대한 행이 0개 있습니다.
'sys.sysdesend'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysdesend"에 대한 행이 0개 있습니다.
'sys.sysdercv'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysdercv"에 대한 행이 0개 있습니다.
'sys.syssingleobjrefs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.syssingleobjrefs"에 대한 행이 146개 있습니다.
'sys.sysmultiobjrefs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysmultiobjrefs"에 대한 행이 106개 있습니다.
'sys.sysguidrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysguidrefs"에 대한 행이 0개 있습니다.
'sys.syscompfragments'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscompfragments"에 대한 행이 0개 있습니다.
'sys.sysftstops'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysftstops"에 대한 행이 0개 있습니다.
'sys.sysqnames'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysqnames"에 대한 행이 97개 있습니다.
'sys.sysxmlcomponent'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlcomponent"에 대한 행이 99개 있습니다.
'sys.sysxmlfacet'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlfacet"에 대한 행이 112개 있습니다.
'sys.sysxmlplacement'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlplacement"에 대한 행이 18개 있습니다.
'sys.sysobjkeycrypts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysobjkeycrypts"에 대한 행이 0개 있습니다.
'sys.sysasymkeys'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysasymkeys"에 대한 행이 0개 있습니다.
'sys.syssqlguides'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syssqlguides"에 대한 행이 0개 있습니다.
'sys.sysbinsubobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysbinsubobjs"에 대한 행이 3개 있습니다.
'sys.syssoftobjrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syssoftobjrefs"에 대한 행이 0개 있습니다.
'sys.queue_messages_1977058079'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_1977058079"에 대한 행이 0개 있습니다.
'sys.queue_messages_2009058193'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_2009058193"에 대한 행이 0개 있습니다.
'sys.queue_messages_2041058307'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_2041058307"에 대한 행이 0개 있습니다.
'sys.filestream_tombstone_2073058421'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.filestream_tombstone_2073058421"에 대한 행이 0개 있습니다.
'sys.syscommittab'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscommittab"에 대한 행이 0개 있습니다.
'tblx'의 DBCC 결과입니다.
메시지 8914, 수준 16, 상태 1, 줄 1
개체 ID 2105058535, 인덱스 ID 0, 파티션 ID 72057594038779904, 할당 단위 ID 72057594039828480(In-row data 유형)의 페이지 (1:77)에 대한 사용 가능한 PFS 공간 정보가 잘못되었습니다. 값   0_PCT_FULL이(가) 필요한데 실제 값은  80_PCT_FULL입니다.
        오류가 복구되었습니다.
300개 페이지에 개체 "tblx"에 대한 행이 299개 있습니다.
CHECKDB이(가) 테이블 'tblx'(개체 ID 2105058535)에서 0개의 할당 오류와 1개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'tblx'(개체 ID 2105058535)에서 0개의 할당 오류와 1개의 일관성 오류를 수정했습니다.
CHECKDB이(가) 데이터베이스 'CopyOnlyRecovery'에서 2개의 할당 오류와 4개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 데이터베이스 'CopyOnlyRecovery'에서 2개의 할당 오류와 4개의 일관성 오류를 수정했습니다.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

use CopyOnlyRecovery
go

alter database CopyOnlyRecovery set multi_user
go

select top 100 * from tblx order by idx 

select count(*) from tblx

select * from tblx

 
-- 기타
DBCC SHOWFILESTATS
DBCC EXTENTINFO(CopyOnlyRecovery, TBLX, -1)
DBCC TRACEON(3604)
go
DBCC PAGE('CopyOnlyRecovery',1, 1,2)
go
EXEC SP_RESETSTATUS 'CopyOnlyRecovery';
go
dbcc checkdb (CopyOnlyRecovery)
go
use master
go

restore headeronly from disk = 'e:\backupProject20121108.bak'
restore filelistonly from disk = 'e:\backupProject20121108.bak'

restore database backupProject from disk = 'e:\backupProject20121108.bak'
with recovery
, move 'backupProject' to 'l:\mssql\backupProject.mdf'
, move 'backupProject_log' to 'l:\mssql\backupProject_log.ldf'

Next up in the Inside the Storage Engine series is a discussion of page structure. Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Here's a picture of the basic structure:

Header

The page header is 96 bytes long. What I'd like to do in this section is take an example page header dump from DBCC PAGE and explain what all the fields mean. I'm using the database from the page split post and I've snipped off the rest of the DBCC PAGE output.

DBCC

TRACEON (3604)

DBCC

PAGE ('pagesplittest', 1, 143, 1);

GO

m_pageId = (1:143) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 68 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594042384384
Metadata: PartitionId = 72057594038386688 Metadata: IndexId = 1
Metadata: ObjectId = 2073058421 m_prevPage = (0:0) m_nextPage = (1:154)
pminlen = 8 m_slotCnt = 4 m_freeCnt = 4420
m_freeData = 4681 m_reservedCnt = 0 m_lsn = (18:116:25)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 1333613242

Note that I didn't include the fields starting with Metadata:. That's because they're not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table lookups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.

Records

See this blog post for details.

Slot Array

It's a very common misconception that records within a page are always stored in logical order. This is not true. There is another misconception that all the free-space in a page is always maintained in one contiguous chunk. This also is not true. (Yes, the image above shows the free space in one chunk and that very often is the case for pages that are being filled gradually.)

If a record is deleted from a page, everything remaining on the page is not suddenly compacted - inserters pay the cost of compaction when its necessary, not deleters.

Consider a completely full page - this means that record deletions cause free space holes within the page. If a new record needs to be inserted onto the page, and one of the holes is big enough to squeeze the record into, why go to the bother of comapcting it? Just stick the record in and carry on. What if the record should logically have come at the end of all other records on the page, but we've just inserted it in the middle - doesn't that screw things up somewhat?

No, because the slot array is ordered and gets reshuffled as records are inserted and deleted from pages. As long as the first slot array entry points to the logically first record on the page, everything's fine. Each slot entry is just a two-byte pointer into the page - so its far more efficient to manipulate the slot array than it is to manipulate a bunch of records on the page. Only when we know there's enough free space contained within the page to fit in a record, but its spread about the page do we compact the records on the page to make the free space into a contiguous chunk.

One interesting fact is that the slot array grows backwards from the end of the page, so the free space is squeezed from the top by new rows, and from the bottom by the slot array.

1. 두 테이블에 인덱스가 없으면, hash 조인으로만 푼다.
   아니다 오히려 hash 로 못 푸는 경우가 발생 할 수도 있다.

if object_id('tblx') is not null
drop table tblx
go

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,c1 int
)
go

create table tbly
(idx int
,c1 int
)
go

insert into tblx values (1,1)
insert into tblx values (1,2)
insert into tbly values (1,1)
insert into tbly values (2,null)
go

set statistics profile on

if object_id('tblx') is not null
drop table tblx
go

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,c1 int
)
go

create table tbly
(idx int
,c1 int
)
go

insert into tblx values (1,1)
insert into tblx values (1,2)
insert into tbly values (1,1)
insert into tbly values (2,null)
go

set statistics profile on

select *
  from tblx a
  join tbly b
    on a.idx = b.idx
 where a.idx > 1
option (hash join)

select *    from tblx a    join tbly b      on a.idx = b.idx    where a.idx > 1   option (hash join)
  |--Hash Match(Inner Join, HASH:([a].[idx])=([b].[idx]), RESIDUAL:([a].[dbo].[tbly].[idx] as [b].[idx]=[a].[dbo].[tblx].[idx] as [a].[idx]))
       |--Table Scan(OBJECT:([a].[dbo].[tblx] AS [a]), WHERE:([a].[dbo].[tblx].[idx] as [a].[idx]>(1)))
       |--Table Scan(OBJECT:([a].[dbo].[tbly] AS [b]), WHERE:([a].[dbo].[tbly].[idx] as [b].[idx]>(1)))
      
      
select *
  from tblx a
  join tbly b
    on a.idx = b.idx
 where a.idx = 1
option (hash join)

메시지 8622, 수준 16, 상태 1, 줄 1
이 쿼리에 정의된 힌트로 인해 쿼리 프로세서에서 쿼리 계획을 생성할 수 없습니다. 힌트를 지정하거나 SET FORCEPLAN을 사용하지 않고 쿼리를 다시 전송하십시오.

select *
  from tblx a
  join tbly b
    on a.idx = b.idx
 where a.idx = 1

select *    from tblx a    join tbly b      on a.idx = b.idx    where a.idx = 1
  |--Nested Loops(Inner Join) -- 뒤에 어떠한 join predicate 가 없다.
       |--Table Scan(OBJECT:([a].[dbo].[tbly] AS [b]), WHERE:([a].[dbo].[tbly].[idx] as [b].[idx]=(1)))
       |--Table Scan(OBJECT:([a].[dbo].[tblx] AS [a]), WHERE:([a].[dbo].[tblx].[idx] as [a].[idx]=(1)))

hash join 은 적어도 하나의 equijoin predicate 가 있어야만 풀 수 있기 때문이다. merge join 도 마찬가지 이다.

이것은 프로시저로 작성할 때 변수에 의해서 쿼리가 실행되고 안되고 하는 샘플을 만들 수 있고 변수에 따라서 쿼리가 동작 할 수 있고 없는 조건이 발생 하기도 한다.

a.idx between 1 and 100 이 입력되는 경우도 있고, 1 and 1 이라고 입력 된다고 생각해 보라. 이 또한 이행적 폐쇄(transitive closure) 법칙이 적용될 수 있고, 이것은 sql server 에서는 힌트로 막을 방법이 없다. 이러한 변경이 일어나는 경우 hash join, merge join 의 기본 원칙인 equijoin 이 predicate 로 반드시 들어가야 하는 기본 원칙을 위배 하므로 실행 될 수 없다.

(oracle 의 경우 query_rewrite_enabled 파라미터를 이용해 막을 수 있다.)


select *
  from tblx a
  join tbly b
    on a.idx = b.idx
 where a.idx = 1
option (merge join)

메시지 8622, 수준 16, 상태 1, 줄 1
이 쿼리에 정의된 힌트로 인해 쿼리 프로세서에서 쿼리 계획을 생성할 수 없습니다. 힌트를 지정하거나 SET FORCEPLAN을 사용하지 않고 쿼리를 다시 전송하십시오.


select *
  from tblx a
  join tbly b
    on a.idx = b.idx

select *    from tblx a    join tbly b      on a.idx = b.idx
  |--Hash Match(Inner Join, HASH:([b].[idx])=([a].[idx]), RESIDUAL:([pc].[dbo].[tbly].[idx] as [b].[idx]=[pc].[dbo].[tblx].[idx] as [a].[idx]))
       |--Table Scan(OBJECT:([pc].[dbo].[tbly] AS [b]))
       |--Table Scan(OBJECT:([pc].[dbo].[tblx] AS [a]))

select *
  from tblx a
  inner loop join tbly b
    on a.idx = b.idx

select *    from tblx a    inner loop join tbly b      on a.idx = b.idx
  |--Nested Loops(Inner Join, WHERE:([pc].[dbo].[tbly].[idx] as [b].[idx]=[pc].[dbo].[tblx].[idx] as [a].[idx]))
       |--Table Scan(OBJECT:([pc].[dbo].[tblx] AS [a]))
       |--Table Scan(OBJECT:([pc].[dbo].[tbly] AS [b]))

select *
  from tblx a
  inner merge join tbly b
    on a.idx = b.idx

select *    from tblx a    inner merge join tbly b      on a.idx = b.idx
  |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([a].[idx])=([b].[idx]), RESIDUAL:([pc].[dbo].[tbly].[idx] as [b].[idx]=[pc].[dbo].[tblx].[idx] as [a].[idx]))
       |--Sort(ORDER BY:([a].[idx] ASC))
       |    |--Table Scan(OBJECT:([pc].[dbo].[tblx] AS [a]))
       |--Sort(ORDER BY:([b].[idx] ASC))
            |--Table Scan(OBJECT:([pc].[dbo].[tbly] AS [b]))

또하나의 간단한 이야기를 해보자.

if object_id('tblx') is not null
drop table tblx
go

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,c1 int
)
go

create table tbly
(idx int
,c1 int
)
go

insert into tblx values (1,1)
insert into tblx values (2,2)
insert into tbly values (1,1)

select *
  from tblx a
  join tbly b
    on a.idx = 1
option (hash join)
go

select *    from tblx a    join tbly b      on a.idx = 1
  |--Nested Loops(Inner Join) -- 여기에 아무것도 없다.
       |--Table Scan(OBJECT:([a].[dbo].[tblx] AS [a]), WHERE:([a].[dbo].[tblx].[idx] as [a].[idx]=(1)))
       |--Table Scan(OBJECT:([a].[dbo].[tbly] AS [b]))

해당 쿼리는 a.idx = 1 인것을 찾고 그냥 cross join 을 수행한다.
그러므로 hash function 을 수행 할 equijoin predicate 가 역시 없다. 그러므로 hash join 으로 풀 수 없다.
아래 not exists 에서 hash join 으로 못 푸는 것 역시 이렇게 해석 할 수 있다.

그러나 full outer merge join 의 경우 non equijoin 도 가능하기 때문에 다음 쿼리가 가능하다.

select *
  from tblx a
  full outer join tbly b
    on a.idx = 1
option (merge join)


필터 조건이 없으면 hash 만 되는가?
아니다 다음 쿼리의 예제를 보자

select *
  from tblx a
  join tbly b
    on a.idx > b.idx

select *    from tblx a    join tbly b      on a.idx > b.idx
  |--Nested Loops(Inner Join, WHERE:([pc].[dbo].[tblx].[idx] as [a].[idx]>[pc].[dbo].[tbly].[idx] as [b].[idx]))
       |--Table Scan(OBJECT:([pc].[dbo].[tbly] AS [b]))
       |--Table Scan(OBJECT:([pc].[dbo].[tblx] AS [a]))

힌트주면 되지요? 
아니다. 해쉬펑션을 통과한 값의 크다 작다를 비교 할 수 있으면 넌 신이다. 난 신 많이 봤다. ;)

select *
  from tblx a
  inner hash join tbly b
    on a.idx > b.idx

메시지 8622, 수준 16, 상태 1, 줄 1
이 쿼리에 정의된 힌트로 인해 쿼리 프로세서에서 쿼리 계획을 생성할 수 없습니다. 힌트를 지정하거나 SET FORCEPLAN을 사용하지 않고 쿼리를 다시 전송하십시오.

2. in 과 not in 은 반대 인가요?
   아니다

다음을 살펴보자.

if object_id('tblx') is not null
drop table tblx
go

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,c1 int
)
go

create table tbly
(idx int
,c1 int
)
go

insert into tblx values (1,1)
insert into tblx values (2,2)
insert into tbly values (1,1)
insert into tbly values (2,null)
go

select *
  from tblx
 where c1 in (select c1 from tbly)

idx         c1
----------- -----------
1           1

(1개 행이 영향을 받음)

select *
  from tblx
 where c1 not in (select c1 from tbly)

idx         c1
----------- -----------

(0개 행이 영향을 받음)


2 는 어디로 갔나?
왜 그런가? null 이있다는 생각을 하자 null 의 오묘함에 빠져보면 재미있을 것이다.

3. table 의 check 조건 써야 하나요?
   그렇다. 꼭 써 주는게 좋습니다. 이러한 체크조건이 있냐 없냐에 따라 join 방법도 틀리게 할 수 있다.
   써주면 optimizer 가 매우 효율적으로 동작 할 수 있는 실마리를 준다.

if object_id('tblx') is not null
drop table tblx
go

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,c1 int
)
go

create table tbly
(idx int
,c1 int
)
go

insert into tblx values (1,1)
insert into tblx values (2,2)
insert into tbly values (1,1)

go

select *
  from tblx
 where c1 not in (select c1 from tbly)

select *     from tblx    where c1 not in (select c1 from tbly)
  |--Nested Loops(Left Anti Semi Join, WHERE:([pc].[dbo].[tblx].[c1] IS NULL OR [pc].[dbo].[tbly].[c1] IS NULL OR [pc].[dbo].[tblx].[c1]=[pc].[dbo].[tbly].[c1]))
       |--Table Scan(OBJECT:([pc].[dbo].[tblx]))
       |--Table Scan(OBJECT:([pc].[dbo].[tbly]))

hash join 으로 풀 수 있는가?

select *
  from tblx
 where c1 not in (select c1 from tbly)
option (hash join)

메시지 8622, 수준 16, 상태 1, 줄 2
이 쿼리에 정의된 힌트로 인해 쿼리 프로세서에서 쿼리 계획을 생성할 수 없습니다. 힌트를 지정하거나 SET FORCEPLAN을 사용하지 않고 쿼리를 다시 전송하십시오.

테이블에 not null 체크 조건이 있으면 어떻게 되는가?
잘푼다.
if object_id('tblx') is not null
drop table tblx
go

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,c1 int not null
)
go

create table tbly
(idx int
,c1 int not null
)
go

insert into tblx values (1,1)
insert into tblx values (2,2)
insert into tbly values (1,1)
go

-- 아까 위헤서 아래 퀴리는 위에서 실행계획을 생성 할 수 없습니다. 그 쿼리이다.


select *
  from tblx
 where c1 not in (select c1 from tbly)
option (hash join)

select *     from tblx    where c1 not in (select c1 from tbly)  option (hash join)
  |--Hash Match(Right Anti Semi Join, HASH:([pc].[dbo].[tbly].[c1])=([pc].[dbo].[tblx].[c1]))
       |--Table Scan(OBJECT:([pc].[dbo].[tbly]))
       |--Table Scan(OBJECT:([pc].[dbo].[tblx]))

테이블 제약 조건 주지말고 hash join 을 하고 싶은가?

select *
  from tblx
 where c1 not in (select c1 from tbly where c1 is not null)
   and c1 is not null
option (hash join)

select *     from tblx    where c1 not in (select c1 from tbly where c1 is not null)     and c1 is not null  option (hash join)
  |--Hash Match(Right Anti Semi Join, HASH:([pc].[dbo].[tbly].[c1])=([pc].[dbo].[tblx].[c1]), RESIDUAL:([pc].[dbo].[tblx].[c1]=[pc].[dbo].[tbly].[c1]))
       |--Table Scan(OBJECT:([pc].[dbo].[tbly]), WHERE:([pc].[dbo].[tbly].[c1] IS NOT NULL))
       |--Table Scan(OBJECT:([pc].[dbo].[tblx]), WHERE:([pc].[dbo].[tblx].[c1] IS NOT NULL))

풀리긴 풀리는데 residual 이나 where 필터가 형성된다. 


체크조건이 있으면 residual 등이 없고 깔끔하지 않은가? 어떤가? check 조건 꼭 기술하는게 좋을것 같지 않은가?
이 외 다양한 이슈가 있다. 재미 있는가? 나머지 이슈는 나중에 책을 통해서 만나보자! (sqltag 에서는 sql 책을 준비하고 있어요 ;) 교성아 join 다 적어가나?????

if object_id('tblx') is not null
drop table tblx
go

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,c1 char(8000) not null
)
go

create table tbly
(idx int
,c1 char(8000) not null
)
go

insert into tblx values (1,1)
insert into tblx values (2,2)
insert into tbly values (1,1)
go

select *
  from tblx a
  join tbly b
    on a.idx = b.idx
 order by a.idx    
go
 

select *
  from tblx a
 inner hash join tbly b
    on a.idx = b.idx
 order by a.idx    
option(hash join)        

메시지 8618, 수준 16, 상태 2, 줄 1
작업 테이블이 필요하며 최소 행 크기가 허용되는 최대 크기 8060바이트를 초과하므로 쿼리 프로세서에서 쿼리 계획을 생성할 수 없습니다. 작업 테이블이 필요한 일반적인 이유는 쿼리의 GROUP BY 또는 ORDER BY 절 때문입니다. 쿼리에 GROUP BY 또는 ORDER BY 절이 있을 경우 이 절의 필드 수 및/또는 크기를 줄이십시오. 또한 필드의 접두사(LEFT()) 또는 해시(CHECKSUM())를 사용하여 그룹화하거나 접두사를 사용하여 정렬하십시오. 그러나 이 작업을 수행하면 쿼리의 동작이 변경됩니다.

다음은 full outer join 의 실행 예이다.



재미 있지 않은가? join 된 결과를 넣어두고 sort 해야 하는데 두 컬럼의 합이 8060을 넘으니 hash join 한 후 work table 을 만들수 없어 일어나는 에러이다.  

교성이의 join advanced 챕터로 쓸 예정 입니다.

누군가 tempdb 가 이상해요 라고 연락이 왔다. 아래 문서와 쿼리를 이용해 문제를 해결한다.

workingwithtempdb.doc

tempdb 를 core 수만큼 만드는 것은 tempdb 유지관리 비용으로 인해 allocation contention 해결 비용보다 더 클 수 도 있다는 설명도 들어있는 좋은 문서이다. workload 에 따라서 튜닝방법은 달라져야 한다. 
 

Optimizing_tempdb_Performance_chicago.pdf

CREATE database perf_warehouse
GO
USE perf_warehouse
GO

CREATE TABLE tempdb_space_usage (
  -- This represents the time when the particular row was
  -- inserted
  dt datetime DEFAULT CURRENT_TIMESTAMP,
  -- session id of the sessions that were active at the time
  session_id int DEFAULT null,
  -- this represents the source DMV of information. It can be
  -- track instance, session or task based allocation information.
  scope varchar(10), 
  -- instance level unallocated extent pages in tempdb
  Instance_unallocated_extent_pages bigint,
  -- tempdb pages allocated to verstion store
  version_store_pages bigint,
  -- tempdb pages allocated to user objects in the instance
  Instance_userobj_alloc_pages bigint,   
  -- tempdb pages allocated to internal objects in the instance
  Instance_internalobj_alloc_pages bigint,
  -- tempdb pages allocated in mixed extents in the instance
  Instance_mixed_extent_alloc_pages bigint,
  -- tempdb pages allocated to user obejcts within this sesssion or task.
  Sess_task_userobj_alloc_pages bigint,   
  -- tempdb user object pages deallocated within this sesssion
  -- or task.
  Sess_task_userobj_deallocated_pages bigint,
  -- tempdb pages allocated to internal objects within this sesssion
  -- or task
  Sess_task_internalobj_alloc_pages bigint,
  -- tempdb internal object pages deallocated within this sesssion or
  -- task
  Sess_task_internalobj_deallocated_pages bigint,   
  -- query text for the active query for the task 
  query_text nvarchar(max) 
)
go

-- Create a clustered index on time column when the data was collected
CREATE CLUSTERED INDEX cidx ON tempdb_space_usage (dt)
go

아래 프로시저를 주기적으로 돌려 tempdb 문제를 일으키는 쿼리를 찾는다.

CREATE PROC sp_sampleTempDbSpaceUsage AS
  -- Instance level tempdb File space usage for all files within
  -- tempdb
  INSERT tempdb_space_usage (
    scope,
    Instance_unallocated_extent_pages,
    version_store_pages,
    Instance_userobj_alloc_pages,
    Instance_internalobj_alloc_pages,
    Instance_mixed_extent_alloc_pages)
  SELECT
    'instance',
    SUM(unallocated_extent_page_count),
    SUM(version_store_reserved_page_count),
    SUM(user_object_reserved_page_count),
    SUM(internal_object_reserved_page_count),
    SUM(mixed_extent_page_count)
  FROM sys.dm_db_file_space_usage
   
    -- 2. tempdb space usage per session
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages)
  SELECT
    'session',
    session_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
  FROM sys.dm_db_session_space_usage
    WHERE session_id > 50

    -- 3. tempdb space usage per active task
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages,
    query_text)
  SELECT
    'task',
    R1.session_id,
    R1.user_objects_alloc_page_count,
    R1.user_objects_dealloc_page_count,
    R1.internal_objects_alloc_page_count,
    R1.internal_objects_dealloc_page_count,
    R3.text
  FROM sys.dm_db_task_space_usage AS R1
    LEFT OUTER JOIN
    sys.dm_exec_requests AS R2
    ON R1.session_id = R2.session_id
    OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
  WHERE R1.session_id > 50