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 챕터로 쓸 예정 입니다.

Error: 18456, Severity: 14, State: 8 에서 State 를 자세히 봅니다. bol 이나 다음 사이트에서 18456 의 설명을 참고한다.
http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

runas 를 통해서 로그인을 가장하고, 11 번 에러를 고의로 발생시킨 경우이다.
그러므로 11번 에러가 나면 다음 그림을 참고해서 데이터베이스 엔진에 연결할 권한을 주도록 한다.

이 외, 윈도우 계정의 암호를 바꾸면 Service 시작 계정의 암호도 바꾸어 주어야 하며, 특별한 경우에는 sql server 에 등록된 윈도우 계정을 삭제 했다가 다시 넣어줘야 되는 경우도 있다.

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

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

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

언제? 어떤 호스트에서? 어떤 프로그램을 이용해서? 호스트의 프로세스 아이디는? 로그인 네임은 뭘로? text 는 뭐야?

아래 그림을 보면 음...... 이제 다 찾았습니다. 
용서를 할지.....로그인 기록부터 뒤져서....기때기를 때릴지...

if object_id('tbl_audit') is not null
 drop table tbl_audit
 go
 
create table tbl_audit
(eventtime datetime
 ,hostname varchar(100)
 ,program_name varchar(100)
 ,hostprocess int
 ,loginame varchar(100)
 ,text varchar(max))
 go
 
if object_id ('tblx') is not null
 drop table tblx
go
 
create table tblx
(idx int identity(1,1)
 ,c1 int)
 go
 
insert into tblx values (1)
 go 100
 
 
 
if object_id('tr_delete_tblx') is null
exec ('create trigger tr_delete_tblx on tblx after delete as select 1')
 go
 
alter trigger tr_delete_tblx
on tblx
after delete
as
 if @@rowcount > 0 begin
 set nocount on  -- trigger 에서는 주의!
 declare @buffertext table
 (eventtype varchar(100),parameters varchar(100),eventinfo nvarchar(max))
  declare @text nvarchar(max)
 
 insert into @buffertext exec('dbcc inputbuffer('+@@spid+')')
  select @text = eventinfo from @buffertext
 
 insert into tbl_audit (eventtime, hostname, program_name, hostprocess, loginame, text)
  select getdate() eventime, hostname, program_name, hostprocess, loginame, @text text
   from master.dbo.sysprocesses a
   where a.spid = @@spid
end
 go
 
delete from tblx where idx = 10
 go
 
select * from tblx
select * from tbl_audit

 

복잡하게 셋팅해야하는 sqldiag xml 파일을 이제는 툴로 만들어 배포하고 있다.
좀 더 쉽게 성능을 모니터링 할 수 있다.

요런툴 자기만 쓸 수 있다고 편하게 생각하지 말자 툴은 툴이고, 현상을 분석하고 원리를 아는데 투자하는 시간을 많이 가지도록 한다.

뭐 다 알던 기능이라 흥미 있는 기능은 없다.
그래도 스크린샷 구경이나 한번 해보자
사용방법도 너무 쉬워서 아무 설명도 필요 없다. 
그래도 혹시 모르겠으면 소개 Page 의 instruction 을 읽어봐라.

다음은 Coniguration Manager 에서 간단하게 셋팅한 후 분석할 수 있는 스크린 샷이다.

위 툴은 너무 많이 알려줘 아무나 할 수 있다. 그런데, 현상에 대한 분석은 Google 신의 도움이 필요 할 수도 있다. 분석을 위한 시간이 많은 공부가 될 것이다.

이건 예전에 MS 내부 기술지원을 할 때 엔지니어가 셋팅하는 버전으로 있었는데 배포되었다. CASE 번호도 적을수 있도록 되어있다. (이건 좀 지우고 배포하지 -_-) 예전에 MS 내부 분석용 프로그램을 보니 저 넥서스에서 분석한것 외에도 sqldig 의 output 값으로 파싱해 리포트 결과를 보는 다른 툴도 있더라.

회사의 SQL Server 에 문제에 대한 선 분석이 필요하다고 생각되면 MS 에서 SQLRAP 을 받아보는 것도 좋다.

위 Config Manager 를 소개한 링크는 다음 링크를 참고한다.

http://blogs.msdn.com/b/psssql/archive/2011/05/24/pssdiag-sqldiag-configuration-manager-released-to-codeplex.aspx

아래 명령을 쓰면, detach 후 attach 명령으로 붙인 데이터베이스가 norecovery 상태로 되고 이후 수행된 transaction log backup 으로 복구 할 수 있다.

여기에 continue_after_error 옵션도 적용 할 수 있으므로 oracle 과 같이 단순이 split 을 시키고 그다음 바로 복구를 할 수 있다. 그러므로 vdi 를 이용한 복원을 할 필요 없이 단순히 미러 이미지만 만들고 norecovery 상태로 만들 수 있다. 또한 Transaction log backup 도 계속 적용 할 수 있다.

backup log d1 to disk ='c:\d1.bak' with norecovery 

use master
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

insert into tblx values (1)
go 1000

select top 1 * from tblx
go

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

insert into tblx values (2)
go 1000

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 1000

select top 1 * from tblx order by c1 desc

checkpoint
go

-- 위와 같은 상황에서 database shutdown 후 copy
-- 정상인 상태로 다시 start 한다.

use CopyOnlyRecovery
go

insert into tblx values (4)
go 1000

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

insert into tblx values (5)
go 1000

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

insert into tblx values (6)
go 1000

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

checkpoint

-- 현재 데이터베이스는 6 까지 들어가 있음

--use CopyOnlyRecovery
--go
--select top 1 * from tblx order by c1 desc

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

--use master
--go
-- ***************************************************
-- 예전에 복사해둔 데이터베이스를 attach 한다. 
-- stop
-- copy
-- start
-- select * from tblx 해당 파일에는 3까지 들어있다.

use CopyOnlyRecovery
go

select top 1 * from tblx order by c1 desc-- 3 확인완료
go

use master
go

-- 아래 명령을 날리면 3까지 복원된 상태에서 recovery 된다.
backup log CopyOnlyRecovery to disk = 'c:\fail2.bak' with norecovery , init , COPY_ONLY
go

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

-- with norecovery
--restore log CopyOnlyRecovery from disk = 'c:\l1.bak' with norecovery , continue_after_error
restore log CopyOnlyRecovery from disk = 'c:\l2.bak' with norecovery , continue_after_error
파일 1에서 데이터베이스 'CopyOnlyRecovery', 파일 'CopyOnlyRecovery'에 대해 0개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'CopyOnlyRecovery', 파일 'CopyOnlyRecovery_log'에 대해 1517개의 페이지를 처리했습니다 .
RESTORE WITH CONTINUE_AFTER_ERROR가 성공했지만 약간의 손상이 발생했습니다. 데이터베이스 불일치가 발생할 수 있습니다.
RESTORE LOG이(가) 1517개의 페이지를 0.220초 동안 처리했습니다(53.868MB/초).
메시지 3456, 수준 17, 상태 1, 줄 1
페이지 (1:1), 데이터베이스 'CopyOnlyRecovery'(데이터베이스 ID 22)에서 트랜잭션 ID (0:6813)에 대한 로그 레코드 (35:10242:4)을(를) 다시 실행할 수 없습니다. 페이지: LSN = (35:10200:9), 유형 = 11. 로그: OpCode = 4, 컨텍스트 11, PrevPageLSN: (35:10229:13). 데이터베이스 백업에서 복원하거나 데이터베이스를 복구하십시오.

restore log CopyOnlyRecovery from disk = 'c:\l3.bak' with norecovery , continue_after_error
파일 1에서 데이터베이스 'CopyOnlyRecovery', 파일 'CopyOnlyRecovery'에 대해 0개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'CopyOnlyRecovery', 파일 'CopyOnlyRecovery_log'에 대해 759개의 페이지를 처리했습니다 .
RESTORE WITH CONTINUE_AFTER_ERROR가 성공했지만 약간의 손상이 발생했습니다. 데이터베이스 불일치가 발생할 수 있습니다.
RESTORE LOG이(가) 759개의 페이지를 0.110초 동안 처리했습니다(53.848MB/초).
메시지 3456, 수준 17, 상태 1, 줄 1
페이지 (1:2), 데이터베이스 'CopyOnlyRecovery'(데이터베이스 ID 22)에서 트랜잭션 ID (0:8811)에 대한 로그 레코드 (35:22354:4)을(를) 다시 실행할 수 없습니다. 페이지: LSN = (35:10115:4), 유형 = 8. 로그: OpCode = 7, 컨텍스트 8, PrevPageLSN: (35:22257:4). 데이터베이스 백업에서 복원하거나 데이터베이스를 복구하십시오.

restore log CopyOnlyRecovery from disk = 'c:\l4.bak' with norecovery , continue_after_error
파일 1에서 데이터베이스 'CopyOnlyRecovery', 파일 'CopyOnlyRecovery'에 대해 0개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'CopyOnlyRecovery', 파일 'CopyOnlyRecovery_log'에 대해 759개의 페이지를 처리했습니다 .
RESTORE WITH CONTINUE_AFTER_ERROR가 성공했지만 약간의 손상이 발생했습니다. 데이터베이스 불일치가 발생할 수 있습니다.
RESTORE LOG이(가) 759개의 페이지를 0.105초 동안 처리했습니다(56.403MB/초).
메시지 3456, 수준 17, 상태 1, 줄 2
페이지 (1:2), 데이터베이스 'CopyOnlyRecovery'(데이터베이스 ID 22)에서 트랜잭션 ID (0:10811)에 대한 로그 레코드 (36:9161:4)을(를) 다시 실행할 수 없습니다. 페이지: LSN = (35:10115:4), 유형 = 8. 로그: OpCode = 7, 컨텍스트 8, PrevPageLSN: (36:9060:4). 데이터베이스 백업에서 복원하거나 데이터베이스를 복구하십시오.

restore log CopyOnlyRecovery from disk = 'c:\fail2.bak' with norecovery

restore database CopyOnlyRecovery with recovery , continue_after_error
메시지 3456, 수준 16, 상태 1, 줄 2
페이지 (1:2), 데이터베이스 'CopyOnlyRecovery'(데이터베이스 ID 22)에서 트랜잭션 ID (0:10811)에 대한 로그 레코드 (36:9161:4)을(를) 다시 실행할 수 없습니다. 페이지: LSN = (35:10115:4), 유형 = 8. 로그: OpCode = 7, 컨텍스트 8, PrevPageLSN: (36:9060:4). 데이터베이스 백업에서 복원하거나 데이터베이스를 복구하십시오.
메시지 3313, 수준 16, 상태 2, 줄 2
데이터베이스 'CopyOnlyRecovery'에 로그된 작업을 다시 실행하는 중 로그 레코드 ID (36:9161:4)에서 오류가 발생했습니다. 일반적으로 특정 실패는 Windows 이벤트 로그 서비스에서 이미 오류로 로그됩니다. 전체 백업에서 데이터베이스를 복원하거나 데이터베이스를 복구하십시오.
복원에 성공했으나 지연된 트랜잭션이 남아 있습니다. 이러한 트랜잭션에는 사용할 수 없는 데이터가 있으므로 해결할 수 없습니다. RESTORE를 사용하여 데이터를 사용 가능하게 만들거나, 이 데이터가 다시 필요하지 않으면 파일 그룹을 삭제하십시오. 파일 그룹을 삭제하면 파일 그룹이 존재하지 않게 됩니다.
RESTORE WITH CONTINUE_AFTER_ERROR가 성공했지만 약간의 손상이 발생했습니다. 데이터베이스 불일치가 발생할 수 있습니다.
RESTORE DATABASE이(가) 0개의 페이지를 4.849초 동안 처리했습니다(0.000MB/초).
메시지 942, 수준 14, 상태 1, 줄 2
데이터베이스 'CopyOnlyRecovery'은(는) 오프라인 상태이므로 열 수 없습니다.
메시지 3013, 수준 16, 상태 1, 줄 2
RESTORE DATABASE이(가) 비정상적으로 종료됩니다.

--offline 상태

stop
start /m

 

DBCC SHOWFILESTATS

DBCC EXTENTINFO(CopyOnlyRecovery, TBLX, -1)

DBCC TRACEON(3604)
go

DBCC PAGE('CopyOnlyRecovery',1, 1000,2)

 

EXEC SP_RESETSTATUS 'CopyOnlyRecovery';

alter database CopyOnlyRecovery set emergency

use CopyOnlyRecovery
go

select top 1 * from tblx order by c1 desc
select count(*) from tblx

dbcc checkdb (CopyOnlyRecovery)
use master
go

ALTER DATABASE CopyOnlyRecovery SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
dbcc checkdb ('CopyOnlyRecovery', repair_allow_data_loss)
go

alter database CopyOnlyRecovery set multi_user

use CopyOnlyRecovery
go

select * from tblx

select * from tblx

 

 

 

 

restore database d1 with recovery


현재 나타나고 있는 현상은

Holding a database frozen for long periods of time may result in server-wide effects. This is particularly true for SQL Server 2000. In SQL Server 2005, background operations such as the lazy writer and checkpoint processes have been improved to avoid some of this "freeze spillover" effect.

v_backup.exe

SQLWriter.doc

VSS
VSS is basically a framework that consists of a set of functions that enable applications to perform backups of volumes.
 

Writer
Writers are applications that store information on disk and that work together with providers through the VSS interface.
SQL Server has 2 writers available:-
 

1) MSDE Writer - MSDEWriter works with SQL Server 7.0, SQL Server 2000, and SQL Server 2005.
2) SQL Writer - SqlServerWriter only works with instances of SQL Server 2005.

두개중 어떤것을 쓰고 있는지 확인해 봐야 한다.

확인해 보려는 근거는 아래와 같다.

A VSS writer (MSDE Writer) shipped with the VSS framework in Microsoft Windows XP and Microsoft Windows Server 2003. This writer coordinates with SQL Server 2000 and earlier versions to help in backup operations. Starting with SQL Server 2005 installation, SQL Writer is the preferred writer, though MSDE Writer will continue to work and will be the default writer if installed and SQL Writer is not enabled. To start and use the SQL Writer, first disable the MSDE writer from enumerating SQL Server 2005 databases.

SQL Writer 설명에 보면,
http://msdn.microsoft.com/en-us/library/ms175536.aspx 
The VSS is a set of COM APIs that implements a framework to allow volume backups to be performed while applications on a system continue to write to the volumes.
2008 이니 SQL Writer 일것 같은데.......그럼 Write 가 계속 가능한거 아닌가? 부작용이 있을수 있군...
http://technet.microsoft.com/en-us/library/bb795744.aspx 에 보면 WRITER 를 선택하는 REGI 값을 찾을 수 있다.
http://support.microsoft.com/kb/919023/en-us 

Even though SqlServerWriter is installed with SQL Server 2005, it is not configured as the default writer for SQL Server 2005. You must manually configure SqlServerWriter as the default writer. 이런말이 있어 찾아 볼려고 한다. -_- 근데 7 에서는 기본값 이네..

1.
Creation of a Snapshot 파트에서는 the application receives a command to perform the snapshot. To prevent torn pages, writes to the database files are suspended within Microsoft® SQL Server™ until this command completes or aborts 라고 기술되어 있다. 그럼 log 파일에 하나도 기록 못한다는 소린데....... TEST 해봐야 알겠다.

2.
또한 VDF_SnapshotPrepare bit 가 설정되면, 볼륨의 여러 데이터베이스를 한번에 Snapshot 할 수 있다고 한다. 이건 설정되면 안될듯 하다. 여러 데이터베이스에서 한번에 동기화를 맞출려면 시간이 더 오래 걸릴 수 있을듯 하다. bit 값을 확인해 본다.

3.
The duration of the snapshot is the length of time between SQL Server's issuance of the snapshot command to the snapshot provider, and the return of a successful completion indication. Writes to the database files being captured are suspended for the duration of the snapshot operation. Hence, the snapshot must be completed as quickly as possible in order to avoid impact on SQL Server users.

Provider
Providers own the shadow copy data and instantiate the shadow copies.
 

VDI
SQL Server provides application programming interfaces (API's) that enable an ISV to integrate SQL Server into its products. These specifications are available publicly for third-party vendors to develop backup solutions. Some popular ones include Symantec NetBackup, SQL Litespeed, Legato etc.
 

VDI 개념도
http://www.sqlbackuprestore.com/3rdpartybackupapps_vdi.htm


CASE
http://blogs.msdn.com/b/psssql/archive/2009/03/03/how-it-works-sql-server-vdi-vss-backup-resources.aspx


VDI 를 통해 백업을 할때 데이터베이스마다 3개의 Thread 가 동작한다. 그런데 데이터베이스가 500개이다.
그럼 1500개의 Thread 가 필요하다

라고 CASE 가 Open 되었고, 결론은 Worker Therad 를 늘여라 라고 나왔고 다음 버전에서는 이 비효율을 고려하겠다라고 한다.

그런데, 두둥...Copy-on-Write 기술과 Split/Mirror 기술의 차이점은 무엇을까? 두둥 HDS HtPM 은 Split 을 하기 때문에 Write 를 못하고 EMC 솔루션은 가능한가? 이게 만약 Write 를 못한다고 한다면 해당 솔루션은 문제인데...라고 처음에 생각했는데.....SQL 이 아예 차단하는데 API 가 무슨수로 쓴단 말인가? 라는 생각도 든다.....단지 Snapshot 의 속도만 차이가 날 뿐 이라고 생각된다. 근본 Snapshot 의 속도를 빠르게 하는 수 밖에 없다. Universal Replicator 2분이나 걸려야 하나....MS 권고값은 10초 이하로 되어 있다. ShadowCopy 는 3초 였는데...ㅠ.ㅠ

그리고 MSDEWriter 와 SQLWriter 가 있다고 하는데 우리는 뭘 쓰고 있을까?
vssadmin list writers 한번 때려보고 싶다.

누군가 내가 하고 싶은 질문을 똑같이 한 것이 있다 저 위에 API 를 이용한 백업 말고 진정한 H/W 를 이용한 백업과 SQL Server 의 복구 기능만을 이용해서 백업 한다는 계획이다. 이런면에서 Oracle 은 넘사벽인듯 하다. 

Oracle 은 Archive Log 로 부터 DB 를 동기화 해 올릴 수 있는 방법이 있는데, SQL Server 는 없는것 같다. 아 씨봉~
Oracle 은 begin backup 을 하면 log 를 다른방식으로 생성 한다고 한다. 그런데 SQL Server 는 테스트 해보니 backup 상태에서도 로그가 같다. 그렇기 때문에 split 밖에 선택할 수 없는 것인가? by design 이라면 최악이다. 사람들이 갈수록 구멍이란다.

I have implemented Split Mirror backups in many locations for Oracle and a few for DB2. I have a number of clients wanting to do this with SQL Server as well. Oracle has the ability to quisce the database and bring the log files current via RMAN scripts. I can not find a similar way to do this with SQL Server, but have found references to the VDI process. I am comfortable programming in C# and VB, is there a way to call these APIs in .NET? If so do you know of any samples that are available? A vbscript process would be the preferred solution for most of my clients though. Thanks for any info you may have

I have same issue like kds facing. We have requirement to do split/mirror backup and we would like put SQL database in backup mode or quies mode before splitting disks. How do we do that ? Oracle/SAP/Informix etc.. database easy to handle with Split mirror and I am struggling with SQL method. Any advice will be greatly appreciated

짜슥들 맛보기 함 해보삼 글
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/28/informational-shedding-light-on-vss-vdi-backups-in-sql-server.aspx


METADATA 에 들어있는 정보 (LOG SEQUENCE 번호 같은거 없구만.....)
Writer Metadata Document: An Example
Given an example database named DB1, which belongs to SQL Server instance Instance1 on machine Server1, and which contains the following database/log files:
• Database file named “primary” stored at c:\db\DB1.mdf
• Database file named “secondary” stored at c:\db\DB1.ndf
• Database log file named “log” stored at c:\db\DB1.ldf
• Full-text catalog named “foo” stored under the directory c:\db\ftdata\foo
• Full-text catalog named “bar” stored under the directory c:\db\ftdata\bar.
Following is the database’s writer metadata:
Database level filegroup component
• ComponentType: VSS_CT_FILEGROUP
• LogicalPath: “Server1\Instance1”
• ComponentName: “DB1”
• Caption: NULL
• pbIcon: NULL
• cbIcon: 0
• bRestoreMetadata: FALSE
• NotifyOnBackupComplete: TRUE
• Selectable: TRUE
• SelectableForRestore: TRUE
• ComponentFlags: VSS_CF_APP_ROLLBACK_RECOVERY
Filegroup file
• LogicalPath: “Server1\Instance1”
• GroupName: “DB1”
• Path: “c:\db”
• FileSpec: “DB1.mdf”
• Recurisve: FALSE
• AlternatePath: NULL
• BackupTypeMask: VSS_FSBT_ALL_BACKUP_REQUIRED | VSS_FSBT_ALL_SNAPSHOT_REQUIRED
 
Filegroup file
• LogicalPath: “Server1\Instance1”
• GroupName: “DB1”
• Path: “c:\db”
• FileSpec: “DB1.ndf”
• Recursive: FALSE
• AlternatePath: NULL
• BackupTypeMask: VSS_FSBT_ALL_BACKUP_REQUIRED | VSS_FSBT_ALL_SNAPSHOT_REQUIRED
Filegroup file
• LogicalPath: “Server1\Instance1”
• GroupName: “DB1”
• Path: “c:\db”
• FileSpec: “DB1.ldf”
• Recursive: FALSE
• AlternatePath: NULL
• BackupTypeMask: VSS_FSBT_ALL_BACKUP_REQUIRED | VSS_FSBT_ALL_SNAPSHOT_REQUIRED
Filegroup file:
• LogicalPath: “Server1\Instance1”
• GroupName: “DB1”
• Path: “c:\db\ftdata\foo”
• FileSpec: “*”
• Recursive: TRUE
• AlternatePath: NULL
• BackupTypeMask: VSS_FSBT_ALL_BACKUP_REQUIRED | VSS_FSBT_ALL_SNAPSHOT_REQUIRED
 
Filegroup file:
• LogicalPath: “Server1\Instance1”
• GroupName: “DB1”
• Path: “c:\db\ftdata\bar”
• FileSpec: “*”
• Recursive: TRUE
• AlternatePath: NULL
• BackupTypeMask: VSS_FSBT_ALL_BACKUP_REQUIRED | VSS_FSBT_ALL_SNAPSHOT_REQUIRED
Note that if the server instance is the default instance on the machine, the logical path becomes one part – “Server1”.

1.
어제 넘 과로를 해서 이야기의 포인트를 잊어 버렸다..... 늙으면 죽어야지....에혀~
freeze unfreeze 과정에서 meta 파일이 있어야지 norecovery 모드로 만들 수 있다.
이 meta 파일이 없어 norecovery 로 만들수 있냐는 것이 질문이였는데....바보같이...ㅠㅗㅠ
MS 의 일반적인 복구 절차로는 없다라는 답변을 받았다......없는줄 알고 있다. 돌아삐겠다.
HDS 에서 어떻게 구현했는지 모림......

2.
Meta 파일에 들어가는 정보를 정확하게 받아야 한다.

3.
COW 가 Split/Mirror 보다 훨씬 빠른것 같고 HDS 에서는 COW 를 쓰지 않는것 같다.

지우든지 index 를 만든다.

1) 인덱스 만들기
Another suggestion to speed up the deletion is to create an index on the media_set_id column in the backupset table. However, modifying system tables should always be done with caution.

2) 지우기
예전에 나두 하나 만들어 두었는데, 저기 있는게 더 좋아 보인다. -_-
http://www.sqlbackuprestore.com/backuprestorehistorytables.htm

백업 및 복원 기록 테이블에서 오래된 행을 삭제하려면

• sp_delete_backuphistory(Transact-SQL)
  
  

백업 및 복원 기록 테이블에서 특정 데이터베이스에 대한 모든 행을 삭제하려면

• sp_delete_database_backuphistory(Transact-SQL)
  

http://www.sqlbackuprestore.com/introduction.htm

이 사이트에 다 있다고 본다. 이거 번역해서 책으로 내면 좋을듯 하다.
대단한 분인듯.....

Backup 시 Disk SubSystem 의 Read Performance 를 측정하고 싶다는 욕구가 생기는가?
다음 command 를 사용해 봐라

backup database db1 to disk ='NUL' with copy_only

파일 1에서 데이터베이스 'db1', 파일 'DB1'에 대해 10184개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'db1', 파일 'DB1_log'에 대해 1개의 페이지를 처리했습니다 .
BACKUP DATABASE이(가) 10185개의 페이지를 0.397초 동안 처리했습니다(200.427MB/초).

내 로컬 컴퓨터의 백업 읽기 속도이다. (SSD 임 -_-)

이런 유용한 명령어 뿐만 아니라. VDI 를 이용한 3rd Party Com 프로그램의 내용도 기술되어 있다.
하여간 내가본 Backup Restore 의 최강 사이트이다.

백업 후 설정이 어떻게 되어 있는지 확인 할려면!

DBCC TRACEON (3604, -1)
DBCC TRACEON (3605, -1)
DBCC TRACEON (3213, -1)

 

restore verifyonly 를 하면 100% 복구 가능한가?
아니다.
그럼 확실한 방법은 뭔가?
실제로 restore 한 후 checkdb 를 수행하는 것 뿐이다.
근거는 뭔가?
http://www.yohz.com/help/sqlverify/howreliableisrestoreverify.htm 읽어봐라

DBCC FORCEGHOSTCLEANUP
● (dbname|id)
● Used to force all ghost records in a database to be cleaned up
● Very useful to reclaim space without having to do index rebuilds\

등의 명령어 설명