보미네

원격서버와 조인 할 때 loop join 을 반드시 해야하고, join predicate 를 원격서버로 넘겨주고자 할때는 inner remote join 이라고 명시적으로 힌트를 기술하면 된다. 이 힌트는 inner remote loop join 등과 같이 join 방식(loop, merge, hash)을 명시하지 못한다. 왜냐하면 loop join 으로만 풀리기 때문이다. 자세한 내용은 bol 을 참고하길 바란다.
http://technet.microsoft.com/ko-kr/library/ms173815.aspx

데이터 생성 스크립트 remotedb

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

localdb 데이터 생성 시나리오는 위 쿼리에서 tblx ,y,z 로 이름만 바꾸어서 생성하면 된다.

인덱스 localdb

create clustered index cl_tblx on tblx (idx)
create clustered index cl_tbly on tbly (idx)
create clustered index cl_tblz on tblz (idx)

인덱스 remotedb

create clustered index cl_tblx on tblx (idx)

아래 쿼리는 hash 조인으로 동작한다.

SELECT *
  FROM (
  SELECT TOP 1000000 A.IDX, C.INSERTTIME
    FROM TBLX A 
    JOIN TBLY B
   ON A.IDX = B.IDX
    JOIN TBLZ C
   ON A.IDX = C.IDX
    ) LDB
  INNER JOIN MINSOUK.REMOTEDB.DBO.TBLX RDB
    ON RDB.IDX = LDB.IDX

remotedb 와 inner join 을 하고 싶다고 inner loop join 이라고 명시하면 join predicate 가 사라지고 remote 데이터 전체를 읽어서 local 로 spool 한뒤 join 하려고 한다. 만약, dba 가 outer reference table (local table) 이 매우작고 inner table (remotedb) 이 크다면, 원격 network round trip 을 감수하고 loop join 을 수행하고 join predicate 를 정상적으로 전달 하고자 한다면, 어떻게 해야 할까? 아래와 같이 inner remote join 이라고 명시하면 된다. 아래에서 outer reference table 을 매우크게 만들었는데, 항상 loop join 이 된다는 것과 join predicate 가 항상 전달 된다는 것을 증명하기 위한 것이다.

SELECT *
  FROM (
  SELECT TOP 1000000 A.IDX, C.INSERTTIME
    FROM TBLX A 

    JOIN TBLY B 
   ON A.IDX = B.IDX
    JOIN TBLZ C 
   ON A.IDX = C.IDX
    ) LDB
  INNER REMOTE JOIN MINSOUK.REMOTEDB.DBO.TBLX RDB
    ON RDB.IDX = LDB.IDX

그리고 다음 강좌는 코너 아저씨의 DQ 강좌 입니다.

http://sqlbits.com/Sessions/Event10/Distributed_Query_Deep_Dive

SQL Server 2012 에서 리부팅이나 클러스터 fail-over 후 

identity 값이 건너뛴다. 

Hello,
First off we do apologize for the late response to this issue.

In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability.

If you require the same identity generation semantics as previous versions of SQL Server there are two options available:
•         Use trace flag 272
o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
•         Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx)
o This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE.
o Example:
 CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE;
CREATE TABLE t1 (Id INT PRIMARY KEY DEFAULT NEXT VALUE FOR s1, col INT NOT NULL);

As documented in books online for previous versions of SQL Server the identity property does not guarantee the absence of gaps, this statement remains true for the above workarounds. These solutions do help with removing the gaps that occur as part of restarting the instance in SQL Server 2012.

Best regards
Vishal

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

재현 안됨

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

Feb 10 2012 19:39:15 

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 

Dec 28 2012 20:23:12 

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

- powered by 심규현, 안인옥, 추숙, 차주언, 성치훈-

alter  TRIGGER TRIGGER_MEMBER ON MEMBER
FOR DELETE
AS
IF (SELECT COUNT(*) FROM DELETED) > 0
BEGIN

 DECLARE @SPID INT = @@SPID
 DECLARE @SQL_HANDLE BINARY(20)
 DECLARE @TEXT VARCHAR(MAX)

 DECLARE @LOG_DELETE TABLE
 ( LOGINAME NCHAR(256)
 ,NT_USERNAME NCHAR(256)
 ,ACTION_DATE  DATETIME
 ,PROGRAM_NAME VARCHAR(2000)
 ,NET_ADDRESS NVARCHAR(48)
 ,NET_LIBRARY NCHAR(24)
 ,PROTOCOL_TYPE NVARCHAR(40)
 ,TEXT VARCHAR(MAX)
 )

 declare @sql_statement table (
 language_event NVARCHAR(100),
 parameters INT,
 event_info NVARCHAR(4000),
 event_time DATETIME DEFAULT CURRENT_TIMESTAMP);

 declare @dbcc_cmd nvarchar(2000)
 set @dbcc_cmd = 'DBCC INPUTBUFFER('+ cast(@SPID as varchar(200)) + ');'
 INSERT INTO @sql_statement (language_event, parameters, event_info)
 EXEC(@dbcc_cmd);

 SELECT @TEXT = event_info FROM @sql_statement

 INSERT INTO @LOG_DELETE
 SELECT
  LOGINAME
 ,NT_USERNAME
 ,GETDATE()
 ,PROGRAM_NAME
 ,CAST(CONNECTIONPROPERTY('LOCAL_NET_ADDRESS')  AS NVARCHAR(48))
 ,NET_LIBRARY
 ,CAST(CONNECTIONPROPERTY('PROTOCOL_TYPE') AS NVARCHAR(40))
 ,@TEXT
 FROM MASTER.DBO.SYSPROCESSES WHERE SPID = @SPID

 ROLLBACK TRANSACTION
 
 INSERT INTO JOYMDB.DBO.LOG_DELETE
 SELECT * FROM @LOG_DELETE

 exec joymdb.dbo.[핸드폰메세지_아이폰] 'delete from member', -1, 'lpoint1' , 'Y', 0

END
GO