보미네

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- WAIT 조사용 프로시저 생성
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++

USE MASTER
GO

IF OBJECT_ID ('AP_GET_WAITSTATS_1') IS NOT NULL
DROP PROC AP_GET_WAITSTATS_1
GO

CREATE PROC AP_GET_WAITSTATS_1
  @TYPE INT = 6
, @GROUP_MIN INT = 3
, @LOGINAME NVARCHAR(256) = NULL
, @NAME NVARCHAR(256) = NULL
, @HOSTNAME NVARCHAR(256) = NULL
, @PROGRAM_NAME NVARCHAR(256) = NULL
, @SQL_HANDLE BINARY(20) = NULL
, @START_TIME SMALLDATETIME = NULL
, @END_TIME SMALLDATETIME = NULL
, @SPID INT = NULL
AS

IF @TYPE <> 6 BEGIN

 ; WITH DATA AS
 (SELECT CONVERT(SMALLDATETIME,FLOOR((CAST(D AS FLOAT) * 24 * 60) / @GROUP_MIN ) * @GROUP_MIN / 24 / 60, 108) TIMEX, LOGINAME, NAME, HOSTNAME, PROGRAM_NAME, SQL_HANDLE, LASTWAITTYPE
   FROM ##TBLWAITS
  WHERE SQL_HANDLE = CASE WHEN @SQL_HANDLE IS NULL THEN SQL_HANDLE ELSE @SQL_HANDLE END
    AND LOGINAME = CASE WHEN @LOGINAME IS NULL THEN LOGINAME ELSE @LOGINAME END
    AND NAME = CASE WHEN @NAME IS NULL THEN NAME ELSE @NAME END
    AND HOSTNAME = CASE WHEN @HOSTNAME IS NULL THEN HOSTNAME ELSE @HOSTNAME END
    AND PROGRAM_NAME = CASE WHEN @PROGRAM_NAME IS NULL THEN PROGRAM_NAME ELSE @PROGRAM_NAME END
    AND SPID = CASE WHEN @SPID IS NULL THEN SPID ELSE @SPID END
    AND D BETWEEN CASE WHEN @START_TIME IS NULL THEN '19750523' ELSE @START_TIME END AND CASE WHEN @END_TIME IS NULL THEN '20790606' ELSE @END_TIME END )
 , PER_WAITTYPE AS
 (SELECT TIMEX, LASTWAITTYPE VAL, COUNT(*) CNT FROM DATA GROUP BY TIMEX, LASTWAITTYPE WITH ROLLUP)
 , PER_LOGINAME AS
 (SELECT TIMEX, LOGINAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, LOGINAME WITH ROLLUP)
 , PER_DATABASE AS
 (SELECT TIMEX, NAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, NAME WITH ROLLUP)
 , PER_HOSTNAME AS
 (SELECT TIMEX, HOSTNAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, HOSTNAME WITH ROLLUP)
 , PER_PROGRAM_NAME AS
 (SELECT TIMEX, PROGRAM_NAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, PROGRAM_NAME WITH ROLLUP)
 , PER_SQL_HANDLE_RAW AS
 (SELECT TIMEX, SQL_HANDLE, COUNT(*) CNT FROM DATA GROUP BY TIMEX, SQL_HANDLE WITH ROLLUP)
 , PER_SQL_HANDLE AS
 (SELECT TIMEX, CAST(SQL_HANDLE AS SQL_VARIANT) SQL_HANDLE, CNT, QT.TEXT FROM PER_SQL_HANDLE_RAW A OUTER APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE) QT
 )
 SELECT *
   FROM (SELECT * FROM PER_WAITTYPE WHERE @TYPE = 1
   UNION ALL SELECT * FROM PER_LOGINAME WHERE @TYPE = 2
   UNION ALL SELECT * FROM PER_DATABASE WHERE @TYPE = 3
   UNION ALL SELECT * FROM PER_HOSTNAME WHERE @TYPE = 4
   UNION ALL SELECT * FROM PER_PROGRAM_NAME WHERE @TYPE = 5) A
  ORDER BY CASE WHEN TIMEX IS NULL THEN '20790606' ELSE TIMEX END , CNT
 OPTION (RECOMPILE)
END

IF @TYPE = 6 BEGIN
 ; WITH DATA AS
 (SELECT CONVERT(SMALLDATETIME,FLOOR((CAST(D AS FLOAT) * 24 * 60) / @GROUP_MIN ) * @GROUP_MIN / 24 / 60, 108) TIMEX, LOGINAME, NAME, HOSTNAME, PROGRAM_NAME, SQL_HANDLE, LASTWAITTYPE
   FROM ##TBLWAITS
  WHERE SQL_HANDLE = CASE WHEN @SQL_HANDLE IS NULL THEN SQL_HANDLE ELSE @SQL_HANDLE END
    AND LOGINAME = CASE WHEN @LOGINAME IS NULL THEN LOGINAME ELSE @LOGINAME END
    AND NAME = CASE WHEN @NAME IS NULL THEN NAME ELSE @NAME END
    AND HOSTNAME = CASE WHEN @HOSTNAME IS NULL THEN HOSTNAME ELSE @HOSTNAME END
    AND PROGRAM_NAME = CASE WHEN @PROGRAM_NAME IS NULL THEN PROGRAM_NAME ELSE @PROGRAM_NAME END
    AND SPID = CASE WHEN @SPID IS NULL THEN SPID ELSE @SPID END
    AND D BETWEEN CASE WHEN @START_TIME IS NULL THEN '19750523' ELSE @START_TIME END AND CASE WHEN @END_TIME IS NULL THEN '20790606' ELSE @END_TIME END )
 , PER_SQL_HANDLE AS
 (SELECT TIMEX, SQL_HANDLE, COUNT(*) CNT FROM DATA GROUP BY TIMEX, SQL_HANDLE WITH ROLLUP )
 SELECT A.*, SQL_TEXT
   FROM PER_SQL_HANDLE A
   LEFT JOIN ##TBLSQLTEXT B
  ON B.SQL_HANDLE = A.SQL_HANDLE
  ORDER BY CASE WHEN TIMEX IS NULL THEN '20790606' ELSE TIMEX END , CNT
END
GO

IF OBJECT_ID('AP_PUT_WAITSTATS_1') IS NOT NULL
DROP PROC AP_PUT_WAITSTATS_1
GO

CREATE PROC AP_PUT_WAITSTATS_1
@MIN INT = 10
AS

SET NOCOUNT ON
-- SCRIPT BY MINSOUK KIM (SQL SERVER MVP SINCE 2006)
-- DEBUG
-- DECLARE @MIN INT
-- SET @MIN = 1
-- END OF DEBUG
DECLARE @END_TIME DATETIME

SELECT @END_TIME = DATEADD(MINUTE, ABS(@MIN), GETDATE())
SELECT @END_TIME

BEGIN TRY
 DROP TABLE ##TBLWAITS
END TRY BEGIN CATCH END CATCH  

BEGIN TRY
 DROP TABLE ##TBLSQLTEXT
END TRY BEGIN CATCH END CATCH  

SELECT * INTO ##TBLWAITS
  FROM (SELECT TOP 0 CAST(S.LOGINAME AS NVARCHAR(256)) LOGINAME
    , CAST(D.NAME AS NVARCHAR(256)) NAME
    , CAST(S.HOSTNAME AS NVARCHAR(256)) HOSTNAME
    , CAST(S.PROGRAM_NAME AS NVARCHAR(256)) PROGRAM_NAME , S.SQL_HANDLE
    , S.SPID, CONVERT(SMALLINT, S.WAITTYPE) WAITTYPE
    , S.LASTWAITTYPE, S.ECID, S.WAITTIME , S.BLOCKED, GETDATE() D
    FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
    , MASTER..SYSPROCESSES AS S WITH(NOLOCK)
   WHERE S.CMD<>'AWAITING COMMAND'
     AND S.CMD NOT LIKE '%BACKUP%'
     AND S.CMD NOT LIKE '%RESTORE%'
     AND S.SPID>50
     AND S.SPID<>@@SPID
     AND S.DBID = D.DBID 
   ORDER BY S.SPID
    , S.ECID ASC) A

SELECT * INTO ##TBLSQLTEXT
  FROM (SELECT TOP 0 S.SQL_HANDLE, S.STMT_START, S.STMT_END, CAST('' AS VARCHAR(8000)) SQL_TEXT
    FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
    , MASTER..SYSPROCESSES AS S WITH(NOLOCK)
   WHERE S.CMD<>'AWAITING COMMAND'
     AND S.CMD NOT LIKE '%BACKUP%'
     AND S.CMD NOT LIKE '%RESTORE%'
     AND S.SPID>50
     AND S.SPID<>@@SPID
     AND S.DBID = D.DBID 
   ORDER BY S.SPID
    , S.ECID ASC) A

WHILE (GETDATE() < @END_TIME) BEGIN
 INSERT INTO ##TBLWAITS
 SELECT S.LOGINAME, D.NAME, S.HOSTNAME, S.PROGRAM_NAME, S.SQL_HANDLE
   , S.SPID, CONVERT(SMALLINT, S.WAITTYPE) WAITTYPE
   , S.LASTWAITTYPE, S.ECID, S.WAITTIME , S.BLOCKED, GETDATE() D
   FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
   , MASTER..SYSPROCESSES AS S WITH(NOLOCK)
  WHERE S.CMD<>'AWAITING COMMAND'
    --AND S.CMD NOT LIKE '%BACKUP%'
    --AND S.CMD NOT LIKE '%RESTORE%'
    AND S.SPID>50
    AND S.SPID<>@@SPID
    AND S.DBID = D.DBID 
  ORDER BY S.SPID
   , S.ECID ASC

 INSERT INTO ##TBLSQLTEXT (SQL_HANDLE, STMT_START, STMT_END, SQL_TEXT)
 SELECT A.*, C.TEXT
   FROM (SELECT S.SQL_HANDLE, S.STMT_START, S.STMT_END
     FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
     , MASTER..SYSPROCESSES AS S WITH(NOLOCK)
    WHERE S.CMD<>'AWAITING COMMAND'
      AND S.CMD NOT LIKE '%BACKUP%'
      AND S.CMD NOT LIKE '%RESTORE%'
      AND S.SPID>50
      AND S.SPID<>@@SPID
      AND S.DBID = D.DBID) A
   LEFT JOIN ##TBLSQLTEXT B
  ON B.SQL_HANDLE = A.SQL_HANDLE
  CROSS APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE) C
  WHERE B.SQL_HANDLE IS NULL

 WAITFOR DELAY '00:00:00.100'
END
GO

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- WAIT 조사용 프로시저 돌리기 
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++

EXEC MASTER.DBO.AP_PUT_WAITSTATS_1 10

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 다른 세션에서 다음을 확인 한다.
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++

-- 구조 확인용
-- SELECT TOP 10 * FROM ##TBLWAITS
-- SELECT TOP 10 * FROM ##TBLSQLTEXT

---- 어떤 WAIT  이 있나?
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =1
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =2
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =3
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =4
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =5
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =6

---- 디테일 정보 확인
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1
--   @TYPE = 1
-- , @GROUP_MIN = 5
-- --, @LOGINAME = 'NB10202-PC\Administrator'
-- --, @NAME = 'TEMPDB'
-- --, @HOSTNAME = 'NB10202-PC'                                                                                                                 
-- --, @PROGRAM_NAME = 'Microsoft SQL Server Management Studio - 쿼리'
-- --, @SQL_HANDLE = 0x01000100FAE6651BF09DF1050000000000000000
-- --, @START_TIME = '2010-01-06 04:21:00'
-- --, @END_TIME = '2010-01-06 04:24:00'

---- +++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 클리어
---- +++++++++++++++++++++++++++++++++++++++++++++++++++++
--BEGIN TRY
-- DROP TABLE ##TBLWAITS
--END TRY BEGIN CATCH END CATCH  

--BEGIN TRY
-- DROP TABLE ##TBLSQLTEXT
--END TRY BEGIN CATCH END CATCH 


돌린 결과는 다음과 같다.

프로파일러(링크를 따라가면, 오른쪽 상단에 자세한 DOC 문서거 첨부되어 있습니다. )

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

DMV

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

XEvents (2012 버전부터 쓸만 합니다. 2008R2 버전까지는 RPC 에서의 SQL_TEXT Action 을 캡처하지 못해 쓸만하지 못합니다. )

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

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

ERROR 찾기

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

if object_id('sm_cpu_pfmnc_log') is not null
drop table sm_cpu_pfmnc_log
go

create table sm_cpu_pfmnc_log
(
     host_id              CHARACTER VARYING(13)
,     log_ocr_ymdt         datetime
,     cpu_idx              SMALLINT
,     used_rto             NUMERIC(20,2)
,     idel_rto             NUMERIC(20,2)
,     user_rto             NUMERIC(20,2)
,     sys_rto              NUMERIC(20,2)
,     nice_rto             NUMERIC(20,2)
,     irq_rto              NUMERIC(20,2)
,     softirq_rto          NUMERIC(20,2)
,     io_wait_rto          NUMERIC(20,2)
,     prv_mde_exec_tm_rto  NUMERIC(20,2)
,     dly_pcd_call_tm_rto  NUMERIC(20,2)
)

--select * from sm_cpu_pfmnc_log

;with nums as (
select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9
)
, mm as (select a.a+b.a*10 mm from nums a ,nums b where a.a+b.a*10 < 60)
, hh as (select a.a+b.a*10 hh from nums a ,nums b where a.a+b.a*10 < 24)
, hostname as (select a.a+b.a*10+c.a*100+d.a*1000 hostname from nums a, nums b, nums c, nums d where a.a+b.a*10+c.a*100+d.a*1000 < 2)
insert into sm_cpu_pfmnc_log
select right('000000000000000'+cast(hostname as varchar(100)), 13) host_id
  , dateadd(hh, hh, dateadd(mi, mm , getdate())) log_ocr_ymdt
  , hh % 8 cpu_idx
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) used_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) idel_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) user_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) sys_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) nice_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) irq_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) softirq_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) io_wait_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) prv_mde_exec_tm_rto
  , cast(abs(checksum(newid()))/3.33 as numeric(20,2)) dly_pcd_call_tm_rto
  from hostname, hh, mm
order by 2

-- 아래와 같이 파일로 생성
select right(cast(used_rto as varchar(20)),5), * from sm_cpu_pfmnc_log order by log_ocr_ymdt
-- 생성된 파일을 array insert 하기로 함

Begin tran Commit tran 은 빼고 Test 하시길 바랍니다.

alter database t set recovery simple with rollback immediate
go

if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(c1 int
,c2 int
,c3 int
,c4 int
,c5 int
,c6 int
,c7 int
,c8 int
,c9 int
,c10 int
,c11 int
,c12 int
,c13 int
,c14 int
,c15 int
)
go

declare
  @total_loop_count int = 100000
 ,@current_loop_count int = 0
 ,@array_insert int = 0
 ,@start_time datetime2 = getdate()
 ,@end_time datetime2 = getdate()

begin tran
while (1=1) begin
 if @array_insert = 1 begin
  insert into tblx values
       (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
        , (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
  set @current_loop_count += 10
  print @current_loop_count
  if @current_loop_count >= @total_loop_count break;
 end else begin
  insert into tblx values
       (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
  set @current_loop_count += 1
  print @current_loop_count
  if @current_loop_count >= @total_loop_count break;
 end
end
commit tran

select datediff(second, @start_time, getdate()) '입력시간 sec'

select *
  from tblx a
  left join tbly b
    on a.idx = b.idx
   and a.idx in (1,2)
   and b.col1 = 10

if object_id('tbly') is not null
drop table tbly
go

create table tblx
(idx int
,col1 int
,col2 int
)
go

insert into tblx values (1,10,100)
insert into tblx values (2,20,200)
insert into tblx values (3,30,300)
insert into tblx values (4,40,400)
insert into tblx values (5,50,500)

go

create table tbly
(idx int
,col1 int
,col2 int
)
go

insert into tbly values (1,10,100)
insert into tbly values (2,20,200)
insert into tbly values (3,30,300)
go

내가 위 질문을 했을때 10명에 1명도 답을 못하더라....... 이는 sql join 의 on 과 where 를 아직도 이해하지 못하고 마구 쓰는 사람이 대부분 이라는 것이다. 심심하면 당신이 일하는 회사의 sql 팀장에게 문의해 봐라....모르는 사람도 있을듯 하다.

create database l
go

use l
go
if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int
,c1 int
,c2 int
)
go

if object_id ('tbly') is not null
drop table tbly
go

create table tbly
(idx int
,c1 int
,c2 int
)
go

insert into tblx values (1,1,1),(2,2,1),(3,null,1),(4,4,null)
insert into tbly values (1,1,1),(2,2,1),(4,4,null)
go

--exec sp_dbcmptlevel l, 80

select *
  from tblx a, tbly b
 where a.idx *= b.idx
   and (b.idx = 1 or b.idx is null)
  
이건 이렇게 풀 수 있다.

select *
  from tblx a
  left join tbly b
    on a.idx = b.idx
   and (b.idx = 1 or b.idx is null)

다음의 경우를 생각해 보자.
   
select *
  from tblx a
  left join tbly b
    on a.idx = b.idx
 where b.idx = 1
    or b.idx is null
   
위 쿼리를 tsql 조인으로 해보라 결과가 나오지 않는다.

select *
  from tblx a, tbly b
 where a.idx *= b.idx
   and (b.idx = 1 or b.idx is null)

이렇게 풀 수 밖에 없다. 이건 불편하다.

select *
  from (select a.*, b.idx bidx, b.c1 bc1, b.c2 bc2
    from tblx a, tbly b
   where a.idx *= b.idx) a
 where bidx = 1 or bidx is null
     
     
정의를 내려보면 다음과 같다.

아래와 같이 해결 가능하다.
(이건 모 sql 모니터링 업체에서 질문한 내용입니다.)

if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int)
go

insert into tblx (idx) select 1 union all select 2
go
insert into tblx (idx) select 2 union all select 3
go
--syscacheobjects sql
--insert into tblx (idx) select 1 union all select 2

insert into tblx values(1),(2)
go
insert into tblx values(2),(3)
go
--syscacheobjects sql
--(@1 int,@2 int)INSERT INTO [tblx] values(@1),(@2)

dbcc freeproccache
select * from master.dbo.syscacheobjects

로킹 시스템 DB 아키텍팅이라는 새로운 업무를 진행중이다. 
어떻게 하면 가장 빠르게 데이터를 넣을 수 있고,
어떻게 하면 가장 빠르게 데이터를 인출 할 수 있을 것인가에 대한 고민이다.

먼저 데이터 삽입에 대해서는 이러한 방법이 테스트의 대상이 될 듯 하다.

방법1
insert into tblx values (1,'minsouk')
go
insert into tblx values (2,'eunkyoung')
go
insert into tblx values (3,'bomi')
go

방법2
begin tran
   insert into tblx values (1,'minsouk')
   go
   insert into tblx values (2,'eunkyoung')
   go
   insert into tblx values (3,'bomi')
   go
commit tran

방법3

if object_id('usp_insert_tblx') is null
exec ('create proc usp_insert_tblx as select 1')
go

alter proc usp_insert_tblx
 @idx int
,@cname varchar(200)
as
insert into tblx values(@idx, @cname)
go

exec usp_insert_tblx 1, 'minsouk'
go
exec usp_insert_tblx 2, 'eunkyoung'
go
exec usp_insert_tblx 3, 'bomi'
go

방법4
begin tran
   exec usp_insert_tblx 1, 'minsouk'
   go
   exec usp_insert_tblx 2, 'eunkyoung'
   go
   exec usp_insert_tblx 3, 'bomi'
   go
commit tran

방법5
sqlcli를 테이블 변수 이용

방법6
insert into tblx values (1,'minsouk'), (2,'eunkyoung'), (3,'bomi')

글을 읽고있는 당신은 무엇이 가장 빠르다고 생각되는가?
더 빠르게 넣을 수 있는 방법이 있다고 생각하는가?
단 쿼리문의 변경과 insert 하는 방법론으로만 이야기를 한정 하고자 한다.
로그 디스크를 어떻게 구성하고 등등은 2부로 하자.

sp_reset_connection 이 무엇을 하는가? 또 무엇을 하지 못하는가?

sp_reset_connection resets the following aspects of a connection:

It resets all error states and numbers (like @@error)
It stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query
It will wait for any outstanding I/O operations that is outstanding
It will free any held buffers on the server by the connection
It will unlock any buffer resources that are used by the connection
It will release all memory allocated owned by the connection
It will clear any work or temporary tables that are created by the connection
It will kill all global cursors owned by the connection
It will close any open SQL-XML handles that are open
It will delete any open SQL-XML related work tables
It will close all system tables
It will close all user tables
It will drop all temporary objects
It will abort open transactions
It will defect from a distributed transaction when enlisted
It will decrement the reference count for users in current database; which release shared database lock
It will free acquired locks
It will releases any handles that may have been acquired
It will reset all SET options to the default values
It will reset the @@rowcount value
It will reset the @@identity value
It will reset any session level trace options using dbcc traceon()

sp_reset_connection will NOT reset:
Security context, which is why connection pooling matches connections based on the exact connection string
If you entered an application role using sp_setapprole, since application roles can not be reverted

Note: Pasting the content as I do not want it to be lost in the ever transient web

logon trigger 의 발동

sp_reset_connection 이 발생되면, logon trigger 도 동작하게 된다 예전에는 sp_reset_connection 을 호출 하지 않을 방법이 있었지만, 해당 옵션은 버전이 올라가면서 제거 되었다. logon trigger 는 시간으로도 접근제어가 가능하도록 디자인 되어 있어 sp_reset_connection 이 발생 할때마다 체크 되도록 되어 있다. (by design)

linked server 연결에서 logon trigger 가 문제인데, sp_reset_connection 을 호출 하지 않게 할 수 없나?

있다. 다음과 같이 다른 방식으로 연결하면 사용하지 않는다.

-- 기존 전통적인 방식
EXEC master.dbo.sp_addlinkedserver @server = N'TEST1', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST1',@useself=N'False',@locallogin=NULL,@rmtuser=N'sqler',@rmtpassword='!1234'

-- login logout 을 하지 않기 위해서 아래와 같이 odbc 를 사용합니다.
EXEC master.dbo.sp_addlinkedserver @server = N'test2', @srvproduct=N' ', @provider=N'MSDASQL', @provstr=N'Driver={SQL Server};Database=master;Server=10.1.1.1;UID=sqler;PWD=!1234;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'test2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

while (1=1) begin
 waitfor delay '00:00:00.500'
 select top 1  * from test1.master.dbo.sysprocesses -- 이넘은 login logout 을 계속 합니다.
 --select top 1  * from test2.master.dbo.sysprocesses -- 이건 login logout 을 하지 않습니다.
end

참고자료
http://msdn.microsoft.com/en-us/library/aa172690(v=SQL.80).aspx

다음은 같은 event 로 잡은 프로파일링 데이터 이다.

test1 의 프로파일링
sp_reset_connection 이 발생하고 eventsubclass 에 pooled 라고 찍혀 있다.
여담이지만, sp_reset_connection 은 connection pooling 이 되고 있을때만 발생한다. 이걸보고 login logout 이 생긴다고 pooling 을 사용하지 못한다라고 말하는이가 있거나 오해가 없길 바란다.

http://technet.microsoft.com/en-us/library/cc966500.aspx
http://technet.microsoft.com/en-us/library/cc917726.aspx
http://blogs.technet.com/b/josebda/archive/2009/03/31/sql-server-2008-i-o-performance.aspx

Microsoft SQL Server IO Internals.pptx

읽기의 물리적인 단위는 extents 이고 논리적인 읽기의 단위는 page 이다. 쓰기의 물리적인 단위는 page 이다.
그리고, 823,824 등등 오류의 정의도 볼 수 있다.

Object ID

This is the ID of the object to which the page is assigned within the schema of the database. A page can be assigned to only a single object. When the page is read from disk, the object ID is checked on the page. If the object ID does not match the expected object ID, SQL Server will generate Error 605.

SQL Server often performs its writes on page-sized, 8-KB, or larger boundaries.

Extents

SQL Server generally (except for nonmixed extents) allocates space an extent at a time. An extent is eight 8-KB pages, or 64 KB. SQL Server often performs reads in extent-sized (64 KB or 128 KB) boundaries as well.