보미네

IF DB_ID('PAGING') IS NOT NULL
BEGIN
 USE MASTER
 ALTER DATABASE PAGING SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DROP DATABASE PAGING
END

CREATE DATABASE [PAGING] ON  PRIMARY
( NAME = N'PAGING', FILENAME = N'L:\MSSQL\PAGING.MDF' , SIZE = 20MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB )
 LOG ON
( NAME = N'PAGING_LOG', FILENAME = N'L:\MSSQL\PAGING_LOG.LDF' , SIZE = 20MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB)
GO

USE PAGING
GO
 
WITH TEMP AS
(
SELECT TOP 1000000
    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())) % 1000000 AS INT) PRICE
  , DATEADD(MINUTE, CAST(ABS(CHECKSUM(NEWID())) % 20000 AS INT) * -1, GETDATE()) INSERTTIME
  INTO TBLX
  FROM TEMP
GO

CREATE UNIQUE CLUSTERED INDEX UCL_TBLX ON TBLX (IDX)
CREATE UNIQUE NONCLUSTERED INDEX NC_TBLX_01 ON TBLX (INSERTTIME, IDX)

DECLARE @ROWS_PER_PAGE INT
,@JUMP_PAGE INT
,@INSERTTIME DATETIME
,@IDX INT

SELECT @ROWS_PER_PAGE = 2
,@JUMP_PAGE = 100
,@INSERTTIME = '2012-10-15 07:10:09.557'
,@IDX = 73413

--SELECT TOP 100 * FROM TBLX ORDER BY INSERTTIME, IDX

SELECT *
  FROM (SELECT *
    FROM (SELECT TOP (@ROWS_PER_PAGE + (@ROWS_PER_PAGE * @JUMP_PAGE)) T.IDX, T.INSERTTIME
      , ROW_NUMBER() OVER (ORDER BY T.INSERTTIME, T.IDX) RNUM   
      FROM DBO.TBLX T
     WHERE T.INSERTTIME >= @INSERTTIME
       AND ((T.INSERTTIME = @INSERTTIME AND T.IDX > @IDX) OR (T.INSERTTIME > @INSERTTIME))
     ORDER BY T.INSERTTIME, IDX) V
   WHERE RNUM >= @ROWS_PER_PAGE * @JUMP_PAGE + 1
     AND RNUM <= @ROWS_PER_PAGE * @JUMP_PAGE + @ROWS_PER_PAGE) A
  JOIN TBLX B
    ON A.IDX = B.IDX
 ORDER BY B.INSERTTIME, B.IDX;

IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO

CREATE TABLE TBLX
(IDX INT
,C1 INT
,C2 INT)
GO

INSERT INTO TBLX VALUES (1,1,1), (2,2,2), (3,3,3),(4,4,4)
GO

SELECT *
  FROM (SELECT * FROM (VALUES (1), (3)) V (INSERTORDER)) A
 CROSS APPLY (SELECT * FROM TBLX WHERE IDX = INSERTORDER) B
 
SELECT *
  FROM (SELECT * FROM (VALUES (1), (3)) V (INSERTORDER)) A
 INNER LOOP JOIN TBLX B
    ON B.IDX = INSERTORDER

IF OBJECT_ID('DBO.USP_A') IS NULL
EXEC ('CREATE PROC USP_A AS SELECT 1 ')
GO   

ALTER PROC DBO.USP_A
(@A1 INT
,@A2 INT
,@A3 INT)
AS

SELECT *
  FROM (SELECT * FROM (VALUES (@A1), (@A2), (@A3)) V (INSERTORDER)) A
 INNER LOOP JOIN TBLX B
    ON B.IDX = INSERTORDER
GO

EXEC DBO.USP_A 1,2,3
EXEC DBO.USP_A 3,1,2

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 의 똘똘이 현주의 강의를 듣고....