블로그 이미지
010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (500)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total
Today
Yesterday

달력

« » 2024.4
일 월 화 수 목 금 토
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30

공지사항

최근에 올라온 글

점검, 설정, OS, SQL Server

카테고리 없음 / 2011. 7. 7. 21:37


점검

Version 1.0.20110707

## OS 점검
@. 전원계획 선택 : 전원계획을 고성능으로 고정 하시길 바랍니다. (2008)
    Windows 2008 의 경우 제어판>전원>전원계획 선택> 고성능 으로 변경 하시면 됩니다.
    powercfg.cpl

@. NIC 전원 옵션 : 전원을 절약하기 위해 컴퓨터가 이 장치를 끌 수 있음 uncheck(2008)

@. NIC 옵션 정리 (보안팀 + 시스템 협의 후 반드시 설정 하도록 합니다.)
    netsh interface tcp set global chimney=disabled
    netsh interface tcp set global rss=disabled
    netsh interface tcp set global autotuninglevel=disabled
    netsh interface tcp set global netdma=disabled

@. HyperThread  1


@. 자동 업데이트 사용안함

@. 프로세서 사용계획 백그라운드 (0)
시스템 속성 > 성능 > 설정 > 성능 옵션 > 고급 > 프로세서 사용 계획
여기서 프로그램을 선택하시면 절대 아니되옵니다.
조금이라도 이해하고 있는 사람이라면 바보짓이라는 것을 쉽게 알 수 있습니다.
다음 아티클을 참고해 보세요
http://blogs.technet.com/b/sankim/archive/2009/06/10/windows-vs.aspx

@. 메모리 사용게획 프로그램
   http://blogs.technet.com/b/sankim/archive/2008/05/19/largesystemcache.aspx
    SQL Server  는 SingleBuffered Cache System 이다. 그러니 반드시 0으로 설정해주는 것이 좋다.
    Large system cache : 0

@. 네트워크 응용 프로그램을 위한 최대화

@. SynAttackProtect  설정
 http://support.microsoft.com/kb/899599
시작 을 누르고 실행 을 regedit 를 입력한 다음 확인 을 누릅니다.
찾은 후 다음 레지스트리 키를 누릅니다.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
편집 메뉴에서 새로 만들기를 가리킨 다음 DWORD 값 을 클릭하십시오.
SynAttackProtect 를 입력한 다음 Enter 키를 누릅니다.
편집 메뉴에서 수정 을 클릭하십시오.
값 데이터 상자에 00000000을 입력하십시오. 확인 을 클릭하십시오.
레지스트리 편집기를 종료하십시오.


@. 바이러스 제외폴더 설정

@. OS ServicePack  확인
   winver

@.  secpol
   볼륨 관리 작업을 수행,  AWE 설정,  등등

@. 방화벽 wf.msc
   포트 오픈
   DAC 등

## SQL 점검

@. SQL Service Pack
http://support.microsoft.com/kb/2534352 r2 cu8
http://blogs.msdn.com/b/sqlreleaseservices/
http://www.mskbarticles.com/index.php?last-updated-products

@. traceflag 1118, 1204, 1222, 845 설정
sqlservermanager10.msc > sql server 서비스 > 속성 > 고급 > 시작 매개 변수 끝에 삽입
;-T1118;-T845;-T1112;-T1204
1118 의 경우 의미를 알고 넣는것이 좋습니다.
845는 64bit SE 만 설정 합니다.
1112, 1204 는 deadlock
확인
dbcc tracestatus(-1)

@. SQL Alias 설정 (0)
   32bit 64bit alias 는 같게 설정한다.
   sqlservermanager10.msc

 

@. 자동통계 update , 비동기 통계 업데이트
   http://msdn.microsoft.com/en-us/library/dd535534(v=sql.100).aspx

   SELECT NAME AS "NAME",
   IS_AUTO_CREATE_STATS_ON AS "AUTO CREATE STATS",
   IS_AUTO_UPDATE_STATS_ON AS "AUTO UPDATE STATS",
   IS_READ_ONLY AS "READ ONLY",
   CASE IS_AUTO_CREATE_STATS_ON WHEN 0 THEN 'ALTER DATABASE ['+NAME+'] SET  AUTO_CREATE_STATISTICS ON' ELSE '' END CMD1,
   CASE IS_AUTO_UPDATE_STATS_ON WHEN 0 THEN 'ALTER DATABASE ['+NAME+'] SET  AUTO_UPDATE_STATISTICS ON' ELSE '' END CMD2
   FROM SYS.DATABASES
   WHERE DATABASE_ID > 4;

   SELECT NAME AS "NAME",
   IS_AUTO_UPDATE_STATS_ASYNC_ON AS "ASYNCHRONOUS UPDATE" ,
   CASE IS_AUTO_UPDATE_STATS_ASYNC_ON WHEN 0 THEN 'ALTER DATABASE ['+NAME+'] SET  AUTO_UPDATE_STATISTICS_ASYNC ON' ELSE '' END CMD
   FROM SYS.DATABASES
   WHERE DATABASE_ID > 4;

@. db 파일 자동증가 옵션 전체 모두 50MB ~ 500MB  설정
    충분히 확장해 둘것
    datafile 40 GB 로 확장
    ldf file 5GB

@. tempdb size 확장 -> MDF 3GB,  LDF 3GB
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 3072000KB , FILEGROWTH = 51200KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 3072000KB , FILEGROWTH = 51200KB )
GO

@. backup 설정
    새벽2시 fullbackup
    30분마다 logbackup
    masterkey backup

@. identity 최대값 확인

@. Service 확인 fulltext off , integration off, sql browser on

@. SQL 오류로그개수 30개

@. VLF 확인

@. msdb 백업관련 프로시저 수정 (암호 설정,  index 필요하면)

@. remote admin connections 활성화
exec sp_configure 'show advanced options', 1
reconfigure with override
go
exec sp_configure 'remote admin connections', 1
reconfigure with override
go
exec sp_configure 'remote admin connections'
go

@. sp_configure
  exec sp_configure 'optimize for ad hoc workloads', 1
  exec sp_configure 'max server memory (MB)', 700 -- 적절히 셋팅하세요~
  exec sp_configure 'min server memory (MB)', 700 --

@. 원본서버의  sp_configure 확인
declare @tbl_configuration_value table
(configuration_id int
,value_in_use bigint
)

insert into @tbl_configuration_value (configuration_id, value_in_use) values (101,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (102,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (103,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (106,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (107,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (109,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (114,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (115,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (116,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (117,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (124,29)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (400,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (503,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (505,4096)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (518,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (542,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (544,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1126,1042)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1127,2049)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1505,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1517,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1519,20)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1520,600)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1531,-1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1532,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1534,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1535,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1536,65536)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1537,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1538,5)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1539,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1540,1024)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1541,-1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1543,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1544,2147483647)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1545,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1546,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1547,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1548,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1550,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1555,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1556,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1557,60)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1562,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1563,4)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1564,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1565,100)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1566,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1567,100)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1568,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1569,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1570,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1576,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1577,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1578,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1579,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1580,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1581,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1582,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (1583,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16384,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16385,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16386,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16387,1)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16388,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16390,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16391,0)
insert into @tbl_configuration_value (configuration_id, value_in_use) values (16392,0)

 

select 'exec sp_configure ''show advanced options'', 1' diff_config
union all select 'exec sp_configure ''allow updates'', 1'
union all select 'reconfigure with override '
union all
select 'exec sp_configure '''+name+''',' + ' '+cast(a.value_in_use as varchar(100))diff_config-- , a.*, b.value_in_use
from master.sys.configurations a
join @tbl_configuration_value b
on b.configuration_id = a.configuration_id
where b.value_in_use <> cast(a.value_in_use as bigint)
and a.configuration_id not in (102,518)
--select * from sys.configurations where configuration_id = 1547
union all select 'reconfigure with override '

 


 

 

저작자표시
Posted by 보미아빠
, |

하루에 하나씩 올릴려고 했는데 오늘도 PASS~

카테고리 없음 / 2011. 7. 6. 03:38

음~ 오늘은 4시간동안 푸닥거리 했는데 건진게 없다. ...ㅠ.ㅠ 바보인가봥~
그래도 상현이가 쓸 챕터의 advanced 부분이 될만한 꺼리를 찾은거 같다.
치매방지 활동이지만 너무 재미있다.

얏호~ 자야지~
내일도 즐거운 DR Planning~ 큭~ 


저작자표시
Posted by 보미아빠
, |

프로파일러로 플랜은 받고 싶고, 플랜이 변할때만 받고 싶다면 어떻게 하면 될까?

카테고리 없음 / 2011. 7. 5. 02:15

다음과 같은 이벤트로 프로파일링을 설정 하면 된다.



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


저작자표시
Posted by 보미아빠
, |

산술 오류의 변경 sql 2000 Vs sql 2005

카테고리 없음 / 2011. 7. 5. 01:27

산술 오류의 변경 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

-- 정상수행


 

저작자표시
Posted by 보미아빠
, |

NL 조인을 사용해서 outer reference index 를 힌트로 박으면 반드시 해당 인덱스 순서를 유지 하는가?

카테고리 없음 / 2011. 7. 5. 00:51
oracle 은 유지하지 않습니다. sql server 는 유지 할까요?

1) 유지된다.
2) 유지되지 않는다.

숙봉양의 access pattern part 의 advanced 챕터 입니다.

저작자표시
Posted by 보미아빠
, |

backup log with norecovery?

카테고리 없음 / 2011. 6. 27. 01:29

sql server 를 full backup 을 이용하지 않고 복구 할 수 있을까?
그냥 단순한 copy 본으로 recovery 모드로 변경 후 transaction log 를 계속 복구하면 어떻게 될까?
테스트 이다.

update delete 는 복구가 가능하며, file copy 시점 이후의 insert 데이터는 살리지 못한다.
그러나 binary 파일을 열어보면 pfs 페이지와 gam 페이지의 결함으로 출력하지 못한다는 것을 알 수 있고
이 후 복구 방법을 스크립트화 했다.


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'

 

 

저작자표시
Posted by 보미아빠
, |

와인나라 호스텐 와인이 도착 했습니다.

카테고리 없음 / 2011. 6. 26. 19:23
sqler 에서 알게된 형주 형님이 와인을 선물해 주셨다.

울 둘째 여름이(첫째 이름은 보미 입니다.) 최종 빌드 완료되면 둘이 개봉샷 하겠습니다.
감사합니다. 이얏호~ 근데 4시즌 할 수 있을지는 미정 입니다.

와인에 와짜도 모르는데 요즘 고급 와인만 맛들이고 있습니다. 큰일 입니다.
베리짜노 호스텐....아 ~ 행복합니다.

자 오픈샷 입니다. 저렇게 고급 와인은 첨봐용~ 디캔팅도 해야 한답니다.


저작자표시
Posted by 보미아빠
, |

page

카테고리 없음 / 2011. 6. 24. 18:42

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:

page.gif

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

Here's what all the fields mean (note that the fields aren't quite stored in this order on the page):

  • m_pageId
    • This identifies the file number the page is part of and the position within the file. In this example, (1:143) means page 143 in file 1.
  • m_headerVersion
    • This is the page header version. Since version 7.0 this value has always been 1.
  • m_type
    • This is the page type. The values you're likely to see are:
      • 1 - data page. This holds data records in a heap or clustered index leaf-level.
      • 2 - index page. This holds index records in the upper levels of a clustered index and all levels of non-clustered indexes.
      • 3 - text mix page. A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap.
      • 4 - text tree page. A text page that holds large chunks of LOB values from a single column value.
      • 7 - sort page. A page that stores intermediate results during a sort operation.
      • 8 - GAM page. Holds global allocation information about extents in a GAM interval (every data file is split into 4GB chunks - the number of extents that can be represented in a bitmap on a single database page). Basically whether an extent is allocated or not. GAM = Global Allocation Map. The first one is page 2 in each file. More on these in a later post.
      • 9 - SGAM page. Holds global allocation information about extents in a GAM interval. Basically whether an extent is available for allocating mixed-pages. SGAM = Shared GAM. the first one is page 3 in each file. More on these in a later post.
      • 10 - IAM page. Holds allocation information about which extents within a GAM interval are allocated to an index or allocation unit, in SQL Server 2000 and 2005 respectively. IAM = Index Allocation Map. More on these in a later post.
      • 11 - PFS page. Holds allocation and free space information about pages within a PFS interval (every data file is also split into approx 64MB chunks - the number of pages that can be represented in a byte-map on a single database page. PFS = Page Free Space. The first one is page 1 in each file. More on these in a later post.
      • 13 - boot page. Holds information about the database. There's only one of these in the database. It's page 9 in file 1.
      • 15 - file header page. Holds information about the file. There's one per file and it's page 0 in the file.
      • 16 - diff map page. Holds information about which extents in a GAM interval have changed since the last full or differential backup. The first one is page 6 in each file.
      • 17 - ML map page. Holds information about which extents in a GAM interval have changed while in bulk-logged mode since the last backup. This is what allows you to switch to bulk-logged mode for bulk-loads and index rebuilds without worrying about breaking a backup chain. The first one is page 7 in each file.
  • m_typeFlagBits
    • This is mostly unused. For data and index pages it will always be 4. For all other pages it will always be 0 - except PFS pages. If a PFS page has m_typeFlagBits of 1, that means that at least one of the pages in the PFS interval mapped by the PFS page has at least one ghost record.
  • m_level
    • This is the level that the page is part of in the b-tree.
    • Levels are numbered from 0 at the leaf-level and increase to the single-page root level (i.e. the top of the b-tree).
    • In SQL Server 2000, the leaf level of a clustered index (with data pages) was level 0, and the next level up (with index pages) was also level 0. The level then increased to the root. So to determine whether a page was truly at the leaf level in SQL Server 2000, you need to look at the m_type as well as the m_level.
    • For all page types apart from index pages, the level is always 0.
  • m_flagBits
    • This stores a number of different flags that describe the page. For example, 0x200 means that the page has a page checksum on it (as our example page does) and 0x100 means the page has torn-page protection on it.
    • Some bits are no longer used in SQL Server 2005.
  • m_objId
  • m_indexId
    • In SQL Server 2000, these identified the actual relational object and index IDs to which the page is allocated. In SQL Server 2005 this is no longer the case. The allocation metadata totally changed so these instead identify what's called the allocation unit that the page belongs to (I'll do another post that describes these later today).
  • m_prevPage
  • m_nextPage
    • These are pointers to the previous and next pages at this level of the b-tree and store 6-byte page IDs.
    • The pages in each level of an index are joined in a doubly-linked list according to the logical order (as defined by the index keys) of the index. The pointers do not necessarily point to the immediately adjacent physical pages in the file (because of fragmentation).
    • The pages on the left-hand side of a b-tree level will have the m_prevPage pointer be NULL, and those on the right-hand side will have the m_nextPage be NULL.
    • In a heap, or if an index only has a single page, these pointers will both be NULL for all pages.
  • pminlen
    • This is the size of the fixed-length portion of the records on the page.
  • m_slotCnt
    • This is the count of records on the page.
  • m_freeCnt
    • This is the number of bytes of free space in the page.
  • m_freeData
    • This is the offset from the start of the page to the first byte after the end of the last record on the page. It doesn't matter if there is free space nearer to the start of the page.
  • m_reservedCnt
    • This is the number of bytes of free space that has been reserved by active transactions that freed up space on the page. It prevents the free space from being used up and allows the transactions to roll-back correctly. There's a very complicated algorithm for changing this value.
  • m_lsn
    • This is the Log Sequence Number of the last log record that changed the page.
  • m_xactReserved
    • This is the amount that was last added to the m_reservedCnt field.
  • m_xdesId
    • This is the internal ID of the most recent transaction that added to the m_reservedCnt field.
  • m_ghostRecCnt
    • The is the count of ghost records on the page.
  • m_tornBits
    • This holds either the page checksum or the bits that were displaced by the torn-page protection bits - depending on what form of page protection is turnde on for the database.

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.

저작자표시
Posted by 보미아빠
, |

join 만만한가?

카테고리 없음 / 2011. 6. 24. 00:11


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


저작자표시
Posted by 보미아빠
, |

18456 에러 찾기

카테고리 없음 / 2011. 6. 22. 22:58
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 에 등록된 윈도우 계정을 삭제 했다가 다시 넣어줘야 되는 경우도 있다.


Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.

SQL Server v.Next (Denali) : Troubleshooting Error 18456

I think we've all dealt with error 18456, whether it be an application unable to access SQL Server, credentials changing over time, or a user who can't type a password correctly.  The trick to troubleshooting this error number is that the error message returned to the client or application trying to connect is intentionally vague (the error message is similar for most errors, and the state is always 1).  In a few cases, some additional information is included, but for the most part several of these conditions appear the same to the end user.  In order to figure out what is really going wrong, you need to have alternative access to the SQL Server and inspect the log for the true state in the error message.  I helped our support team just today solve a client's 18456 issues - once we tracked down the error log and saw that it was state 16, it was easy to determine that their login had been setup with a default database that had been detached long ago.

In the next version of SQL Server, there is a new feature called "contained databases" - I've blogged about it here and here.  With this feature comes a new layer of security that may creep onto your radar if you use this functionality: contained user login failures.  There are a variety of things that can go wrong here.  If you connect with a contained user but forget to specify a database name, SQL Server will attempt to authorize you as a SQL login, and you will fail with state 5 (if there is no SQL login with that name) or state 8 (if there is also a SQL login with the same name and the password doesn't match).  There is also a new state 65 which occurs if you have specified the correct username and contained database, but entered an incorrect password.

When I see folks struggling with this problem, I almost always see the answer point to this blog post, which has a very brief partial list and a lot of unanswered questions:

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

So here is what I consider a more complete listing of all the various states for login failures, including changes for Denali (I tried to highlight them, but Community Server doesn't want to obey simple HTML).  I included an instance of 18470 under state 1 for completeness.

State Description Example (note: the verbose message always has [CLIENT: <IP>] suffix)
1 State 1 now occurs when a login is disabled - but actually, the error in the log is 18470, not 18456 - because the login is disabled, it doesn't get that far.  See state

Pre-SQL Server 2005, State 1 always appeared in the log for all login failures, making for fun troubleshooting.  :-)
 Error: 18470, Severity: 14, State: 1.
Login failed for user '<x>'.
Reason: The account is disabled.
2 The login (whether using SQL or Windows Authentication) does not exist.  For Windows Auth, it likely means that the login hasn't explicitly been given access to SQL Server - which may mean it is not a member of an appropriate domain group.  State 2 indicates that the login attempt came from a remote machine.
 Error: 18456, Severity: 14, State: 2.
Login failed for user '<x>'.
Reason: Could not find a login matching the name provided.
5 Like state 2, the login does not exist in SQL Server, but the login attempt came from the local machine.  For both state 2 and 5, prior to SQL Server 2008, the reason was not included in the error log - just the login failed message.  And starting in Denali, for both state 2 and 5, this error can happen if you specify the correct username and password for a contained database user, but the wrong (or no) database. Error: 18456, Severity: 14, State: 5.
Login failed for user '<x>'.
Reason: Could not find a login matching the name provided.
6 This means you tried to specify SQL authentication but entered a Windows-style login in the form of Domain\Username.  Make sure you choose Windows Authentication (and you shouldn't have to enter your domain / username when using Win Auth).
 Error: 18456, Severity: 14, State: 6.
Login failed for user '<x\y>'.
Reason: Attempting to use an NT account name with SQL Server Authentication.
7 The login is disabled *and* the password is incorrect.  This shows that password validation occurs first, since if the password is correct and the login is disabled, you get error 18470 (see state 1 above).
 Error: 18456, Severity: 14, State: 7.
Login failed for user '<x>'.
Reason: An error occurred while evaluating the password.
8 Probably the simplest of all: the password is incorrect (cASe sEnsiTiVitY catches a lot of folks here).  Note that it will say "login" even if you attempted to connect as a contained user but forgot to specify a database (or specified the wrong database).
 Error: 18456, Severity: 14, State: 8.
Login failed for user '<x>'.
Reason: Password did not match that for the login provided.
9 Like state 2, I have not seen this in the wild. It allegedly means that the password violated a password policy check, but I tried creating a login conforming to a weak password policy, strengthened the policy, and I could still log in fine. And obviously you can't create a login with, or later set, a password that doesn't meet the policy. Let me know if you've seen it. Error: 18456, Severity: 14, State: 9.
???
10 This is a rather complicated variation on state 9; as KB #925744 states, this means that password checking could not be performed because the domain account being used for the SQL Server service is disabled or locked on the domain controller. No reason is given in the verbose message in the error log. Error: 18456, Severity: 14, State: 10.
Login failed for user '<x\y>'.
11 States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (see this post from Simon Sabin).
 Error: 18456, Severity: 14, State: 11.
Login failed for user '<x>'.
Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.
12 See state 11 above.
 Error: 18456, Severity: 14, State: 12.
Login failed for user '<x>'.
Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
13 This state occurs when the SQL Server service has been paused (which you can do easily from the context menu in Object Explorer). Error: 18456, Severity: 14, State: 13.
Login failed for user '<x>'.
Reason: SQL Server service is paused. No new connections can be accepted at this time.
16 State 16, which only occurs prior to SQL Server 2008, means that the default database was inaccessible. This could be because the database has been removed, renamed, or is offline. This state does not indicate a reason in the error log.  In 2008 and beyond, this is reported as state 40 (see below), with a reason.  In SQL Server 2005, this state may also be reported if the user's default database is online but the database they explicitly requested is not available (see state 27).
 Error: 18456, Severity: 14, State: 16.
Login failed for user '<x>'.
18 Supposedly this indicates that the user needs to change their password. In SQL Server 2005, 2008 R2 and Denali, I found this was raised as error 18488, not 18456; this is because for SQL logins the change password dialog just delays logging in, and is not actually a login failure.  I suspect that, like state 16, this state will go away.
 Error: 18456, Severity: 14, State: 18.
???
23 There could be a few reasons for state 23. The most common one is that connections are being attempted while the service is being shut down. However if this error occurs and it is not surrounded in the log by messages about SQL Server shutting down, and there is no companion reason along with the message, I would look at KB #937745, which implies that this could be the result of an overloaded server that can't service any additional logins because of connection pooling issues. Finally, if there *is* a companion reason, it may be the message indicated to the right, indicating that SQL Server was running as a valid domain account and, upon restarting, it can't validate the account because the domain controller is offline or the account is locked or no longer valid. Try changing the service account to LocalSystem until you can sort out the domain issues. Error: 18456, Severity: 14, State: 23.
Login failed for user '<x>'.
Reason: Access to server validation failed while revalidating the login on the connection.
27 State 27, like state 16, only occurs prior to SQL Server 2008. It means that the database specified in the connection string has been removed, renamed, or is offline (though in every case I tried, it was reported as state 16). This state does not indicate a reason in the error log.  In 2008 and onward this is reported as state 38 (see below), with a reason.
 Error: 18456, Severity: 14, State: 27.
Login failed for user '<x>'.
28 I have not experienced this issue but I suspect it involves overloaded connection pooling and connection resets. I think you will only see state 28 prioer to SQL Server 2008.
 Error: 18456, Severity: 14, State: 28.
Login failed for user '<x>'.
38 The database specified in the connection string, or selected in the Options > Connection Properties tab of the SSMS connection dialog, is no longer valid or online. I came across this once when I typed <default> here instead of picking that option from the list.  This is reported as state 27 or state 16 prior to SQL Server 2008.
 Error: 18456, Severity: 14, State: 38.
Login failed for user '<x>'.
Reason: Failed to open the database specified in the login properties.
40 Usually this means the login's default database is offline or no longer exists.  Resolve by fixing the missing database, or changing the login's default database using ALTER LOGIN (for older versions, use sp_defaultdb (deprecated)).  This is reported as state 16 prior to SQL Server 2008.

Error: 18456, Severity: 14, State: 40.
Login failed for user '<x>'.
Reason: Failed to open the explicitly specified database.

46 State 46 may occur when the login (or login mapping to the service account) does not have a valid database selected as their default database. (I am guessing here but I think this may occur when the login in question is attempting to perform log shipping. Again, just a guess based on the few conversations I discovered online.) It can also occur if the classifier function (Resource Governor) Error: 18456, Severity: 14, State: 46.
Login failed for user '<x>'.
Reason: Failed to open the database configured in the login object while revalidating the login on the connection.
50 As the message implies, this can occur if the default collation for the login does not match the collation of their default database (or the database explicitly specified in the connection string). Error: 18456, Severity: 14, State: 50.
Login failed for user '<x>'.
Reason: Current collation did not match the database's collation during connection reset.
51 Like states 11 & 12, this could have to do with UAC, or that the domain controller could not be reached, or that the domain account could not authenticate against the log shipping partner, or that the log shipping partner was down. Try changing the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts, and validating that the partner instance is accessible. Error: 18456, Severity: 14, State: 51.
Login failed for user '<x>'.
Reason: Failed to send an environment change notification to a log shipping partner node while revalidating the login.
56 State 56 is not very common - again, like states 11 & 12, this could have to do with UAC, or that the domain controller could not be reached. Try changing the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts. Error: 18456, Severity: 14, State: 56.
Login failed for user '<x>'.
Reason: Failed attempted retry of a process tokenvalidation.
58 State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication. Error: 18456, Severity: 14, State: 58.
Login failed for user '<x>'.
Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
65
Container user exists, the database is correct, but the password is invalid.
This can also happen if you use a SQL login to connect to a contained database that has a contained user with the same name (one reason why this is not recommended).
 Error: 18456, Severity: 14, State: 65.
Login failed for user '<x>'.
Reason: Password did not match that for the user provided. [Database: '<x>']

I am sure I missed some, but I hope that is a helpful summary of most of the 18456 errors you are likely to come across. Please let me know if you spot any inaccuracies or if you know of any states (or reasons) that I missed.

If you are using contained databases in Denali, there will be a little extra complication in solving login failures, especially if you try to create contained users with the same name as server-level logins.  This is a ball of wax you just probably don't want to get into...

Thanks to Jonathan Kehayias (blog | twitter), Bob Ward (CSS blog | twitter), and Rick Byham for helping with sanity checking.

저작자표시
Posted by 보미아빠
, |

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

티스토리툴바