보미네

http://connect.microsoft.com/SQLServer/feedback/details/593744/worker-time-columns-in-sys-dm-exec-procedure-stats-are-incorrect

로그가 잘리지 않으면, 활성로그, 복제로그 시퀀스, checkpoint, TF(3505)

등을 살펴봐야 한다.

활성로그 + 복제는

-----------------------------------------

dbcc opentran 

TF 는

-----------------------------------------

dbcc tracestatus (-1)

결과중 3505가 있으면 수동 검사점 제어가 동작중이다.

3502 는 검사점 정보를 log 로 남긴다.

checkpoint lsn 은

-----------------------------------------

dbcc traceon(3604)
dbcc dbinfo ('tempdb') with tableresults

-- 결과 중 아래를 보면,

DBINFO @0x000000000A46D900 dbi_checkptLSN m_fSeqNo 77
DBINFO @0x000000000A46D900 dbi_checkptLSN m_blockOffset 145
DBINFO @0x000000000A46D900 dbi_checkptLSN m_slotId 8

혹은 boot page 를 확인하면 되는데,

DBCC PAGE ('tempdb', 1, 9, 3) with tableresults; 
DBINFO @0x000000000A46A060 dbi_checkptLSN m_fSeqNo 77
DBINFO @0x000000000A46A060 dbi_checkptLSN m_blockOffset 145
DBINFO @0x000000000A46A060 dbi_checkptLSN m_slotId 8

혹은 테스트로 데이터베이스 로그가 작다면 다음과 같은 방법도 사용 될 수 있겠다.

SELECT
 [Current LSN]
, [Previous LSN]
, Operation
, [Checkpoint Begin]
, [Checkpoint End]
FROM fn_dblog(NULL, NULL)
WHERE operation IN ( 'LOP_BEGIN_CKPT', 'LOP_END_CKPT')

위 결과를 바탕으로 현재의 로그를 검사해 보자면,

dbcc loginfo (tempdb)

결과중 아래를 보면 Status 가 2인 77 번 FSeqNo 를 확인 할 수 있다.
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
2 13041664 8192 77 2 64 0              <------------- 활성
2 13041664 13049856 0 0 0 0
2 13041664 26091520 0 0 0 0
2 13295616 39133184 0 0 0 0

이 외 다양한 정보를 dbcc dbinfo 를 통해 얻을 수 있다.

예를 들면 db 가 처음 생성된 버전 dbi_createVersion

현재의 버전 dbi_version

가장 오래된 백업 lsn 등등등

참고로 내부 데이터 버전은 다음을 다음과 같이 정의된다.

SQL Server (70) 7.0 databases have version number 515
SQL Server (80) 2000 databases have version number 539
SQL Server (90) 2005 databases have version number 611/612
SQL Server (10) 2008 databases have version number 655
SQL Server (10.5) 2008 R2 databases have version number 661

TRACEFLAG 3512
Description ='Write info on log space used at checkpoint time to errorlog'

TRACEFLAG 3502
Description ='Send checkpoint state changes to errorlog'

TRACEFLAG 3504
Description ='Send checkpoint summary to errorlog ,Displays number of pages written and other stats'

Make sure this TF 3505 is not enabled which TF 3505Allows checkpointing to be dynamically disabled'

sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf

N모사 XX 님의 월척에 걸린....민석, 상현, 교성.......한명 더 있었는데.....누구지?

drop table tblx
go

create table tblx (idx int, d datetime)
go

insert into tblx
select top 1000000 ROW_NUMBER() over(order by (select 1)) , GETDATE()
  from sysobjects a
     , sysobjects b
     , sysobjects c
     , sysobjects d
go

insert into tblx
select top 1000000 ROW_NUMBER() over(order by (select 1)) , GETDATE() +1
  from sysobjects a
     , sysobjects b
     , sysobjects c
     , sysobjects d
go

insert into tblx values (1, '2012-09-15 23:59:59.993')
insert into tblx values (1, '2012-09-13 23:59:59.997')
insert into tblx values (1, '2012-09-14 23:59:59.997')
insert into tblx values (1, '2012-09-15 23:59:59.997')
insert into tblx values (1, '2012-09-16')
insert into tblx values (1, '2012-09-17')
insert into tblx values (1, '2012-09-18')
go

create clustered index cl_tblx on tblx (d)
go

set statistics time on
go

-- 1등

SELECT
 CONVERT(datetime,cast(CAST (d as float)as int))
,COUNT(*)
FROM dbo.tblx WITH(NOLOCK)
GROUP BY cast(CAST (d as float)as int)
order by 1
option (maxdop 1)

-- 2등
SELECT
 DATEADD( day, DATEDIFF(day, 0, d), 0)
,COUNT(*)
FROM dbo.tblx WITH(NOLOCK)
GROUP BY DATEADD( day, DATEDIFF(day, 0, d), 0)
order by 1
option (maxdop 1)

-- 3등?

convert (char(8),d, 112)

CPU 시간 = 1560밀리초, 경과 시간 = 1646밀리초 -- 버리삼......아래 달리기 리스트에 들어가지도 못 함

-- 결과 틀릴수 있음
SELECT
 cast(CAST (d as int) as datetime)
,COUNT(*)
FROM dbo.tblx WITH(NOLOCK)
GROUP BY CAST (d as int)
order by 1
option (maxdop 1)

1분 단위 append only store 의 저장소가 생기고 Truncate 하는 식으로 동작해

생기는 현상을 이해를 할 수 있어 좋았다. 만든사람 참 고민을 많이 한듯하다.

스터디는 매일 뭔가 새로운 것을 배울 수 있어 너무 좋다.

SQLTAG 의 똘똘이 현주의 강의를 듣고....

최대 개수를 정할 경우

Physical Core 개수로 정하는게 올바를 듯 하다.

이유는 나중에 설명해 보겠다.

사실 본인은 별로 중요하게 생각하지 않음.......^.^ 숙봉이도 어그리함, 그러나.....원리는 한번 고민해 보면 좋겠다.

Nexon 정상급 엔지니어 숙봉의 거친 지름을 받고서....쓰러짐...

서로 고민해 본다는 것이 즐거울 뿐이다. ;-)

스터디 중 송혁(MSSQL PFE)의 필드 경험 하나를 블로그에 담아둔다.

아래 현상이 일어나는 원인은 잘 모르겠다. 돈있고 힘있는 분은 escalation 해 차근 차근 설명 좀 해주세요~

SQL Server 의 Affinity Mask 를 설정해 SQL Instance 가 총 16개 core중 8개의 core 를 사용하게 설정 했을경우 (다른 경우도 마찬가지 이다) Serial 하게 열심히 돌아야 하는 쿼리를 동작 시키면 하나의 core 만 열심히 사용한다.

Affinity Mask 가 설정되지 않았을 경우나, Network 로 Bind 시킬 경우(Soft NUMA TCP/IP 로 여러개의 코어를 할당함) 선택된 코어 중 일량(load factor) 이 낮은 곳으로 옮겨 다시며 실행한다. 이는 같은 코어에서 실행하는 것이 해당 작업은 더 빠르게 끝날 수 있지만 전체 시스템 성능(효율)을 좋게 하기위해 한가한 core Pool 내에서 옮겨 다니며 실행 하도록 한 원래 아키텍처에 위배되는 행위가 아닐까 생각된다. (이런경우 난 이해가지 않으면 버그 스럽다 혹은 개발자의 한계가 이까지 인갑다 라고 생각 한다. -_-+)

다른 OS 스케줄러도 거의 다 비슷하게 동작한다. 로드를 보고 이쪽 저쪽 옮겨 다니며 실행. 그런데, SQL Server 의 경우 Affinity Mask 를 설정 했을때 Serial 한 쿼리를 동작 시키면 하나의 코어먄 100% 다 쓴다.

참고로 Affinity Mask 는 향후 없어진다고 한다.

위 현상은 SQL Server 2005 이상에서 비슷 할 것으로 보이고, 우린 SQL Server 2008R2 에서 테스트 했다.

affinity mask 설정 후 다음을 쿼리 해보면, manual 로 바뀌어 있는것을 볼 수 있다.

select affinity_type_desc from sys.dm_os_sys_info

몇 몇 경우에 min max 함수를 사용 할 때와 order by top 1 을 사용할 때의 성능이 틀릴 때가 있다.

이 경우 workaround 로 top 1 을 사용하면 된다.

Fix 된 버그

http://support.microsoft.com/kb/973255/en-us

Hotfix 적용 후 4199 TF 를 enable 해줘야 한다.

파티션 테이블에서의 문제

http://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance

이거 만든사람 귀차니스트가 아닐까...... row_numner()

http://cafe.naver.com/sqlmvp/3160

DeadLock MSDN

http://msdn.microsoft.com/en-us/library/ms178104(SQL.105).aspx

(마지막 그림은 화살표가 잘못 그려져 있다. babo msdn....-_-)

Request wait 는 spid -> resource 로

owner 는 spid <- resource 방향으로 그릴것  

내가본 가장 좋은 글들

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/13/751343.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx

Work-around

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

정상적인 경우만.....

DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
BEGIN
  BEGIN TRANSACTION
  BEGIN TRY
    INSERT Authors VALUES
      (@au_id, @au_lname, '', '', '', '', '',
'11111', 0)
    WAITFOR DELAY '00:00:05'
    SELECT * FROM authors WHERE au_lname LIKE 'Test%'
    COMMIT
    BREAK
  END TRY
  BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
    ROLLBACK
    SET @Tries = @Tries + 1
    CONTINUE
  END CATCH;
END

특이현상들

1. 리소스 없는 Deadlock

리소스 없는 deadlock 이 발생하면 row lock 이 가능한지 인덱스 상황을 점검하는것이 좋다.

row lock 이 불가능한 상황에서 여러 세션에서 update 를 치면 리소스가 없는 deadlock 이 발생하는 현상이 발생하게 된다.

CREATE TABLE TBLX
(IDX INT )

CREATE CLUSTERED INDEX CL_TBLX ON TBLX (IDX)

SELECT ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, *
  FROM SYS.INDEXES
 WHERE OBJECT_ID = OBJECT_ID ('TBLX')
 
EXEC SP_INDEXOPTION TBLX, DISALLOWROWLOCKS, 1;

SELECT ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, *
  FROM SYS.INDEXES
 WHERE OBJECT_ID = OBJECT_ID ('TBLX')
 
-- ALTER TABLE DBO.TBLX SET (LOCK_ESCALATION = DISABLE )

2. 중첩Tran에 의한 DeadLock

dbcc traceon (1204, 1222, -1) 옵션으로 추적 후 그린것

http://www.sqler.com/511218

예전에 정리한 내글

http://cafe.naver.com/sqlmvp/465

sp_lock2

http://sqlsql.tistory.com/8

클러스터 인덱스와 비클러스터 인덱스 업데이트 deadlock 제거 

drop table tblx 

go

WITH TEMP AS 

(

SELECT TOP 10000

    CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS INT) IDX 

  , 'PNAME_'+RIGHT(REPLICATE('0',7)+CAST(CAST(ABS(CHECKSUM(NEWID())) % 1000 AS INT) AS VARCHAR(10)),7)+CHAR(ASCII('A') + ABS(CHECKSUM(NEWID()))%10) PRODUCTNAME

  FROM SYS.OBJECTS A1

 CROSS JOIN SYS.OBJECTS A2

 CROSS JOIN SYS.OBJECTS A3

 CROSS JOIN SYS.OBJECTS A4

 CROSS JOIN SYS.OBJECTS A5

) 

SELECT IDX, PRODUCTNAME, CAST(SUBSTRING(PRODUCTNAME,7,7) AS INT) PRODUCTID

  , CAST(ABS(CHECKSUM(NEWID())) % 100 AS INT) SHOPID

  , CAST(ABS(CHECKSUM(NEWID())) % 100 AS INT) PRICE

  , cast ('a' as char(3000)) d

  , DATEADD(MINUTE, CAST(ABS(CHECKSUM(NEWID())) % 20000 AS INT) * -1, GETDATE()) INSERTTIME

  INTO TBLX 

  FROM TEMP 

GO

select * from tblx 

create clustered index cl_tblx on tblx (idx) 

create nonclustered index nc_tblx_01 on tblx (shopid) 

update a

 set a.inserttime = getdate()

from (

 select idx 

 from tblx with (nolock) 

 where shopid = 61

 ) b 

 join tblx a 

 on a.idx = b.idx 

select top 10 * from t_outer option (loop join, hash join)

이렇게 돌려도 쿼리가 돌아갑니다.

"SQL Server 운영과 튜닝"책에서 dbcc ruleon off 라는 기능을 소개한 적인 있었잖아요,

해당 rule 을 on off 시키는 기능과 동일하지 않을까 생각 합니다.

option (loop join, hash join) 이라고 주면, join 이 필요 할 때 merge join 만 제외하고 physical join 이 선택될 수 있습니다.