Bora.Choi

복제 삭제

• 방법 1: http://msdn.microsoft.com/ko-kr/library/ms152757.aspx
• 방법 2: http://support.microsoft.com/kb/324401

• 이상하게 잘 못 삭제했을 경우

2. select replinfo,* from sysobjects where replinfo = 0 을 찾아서 입력합니다.

3.           sp_removedbreplication [ [ @dbname = ] 'dbname' ]
                 [ , [ @type = ] type ]
             

4. 1. [ @dbname=] 'dbname'

   1. 데이터베이스의 이름입니다. dbname은 sysname이며 기본값은 NULL입니다. NULL인 경우 현재 데이터베이스를 사용합니다.

   1. [ @type = ] type

   1. 데이터베이스 개체를 제거 중인 복제의 유형입니다. type은 nvarchar(5)이며 다음 값 중 하나일 수 있습니다.

   tran	트랜잭션 복제 게시 개체를 제거합니다.
   merge	병합 복제 게시 개체를 제거합니다.
   both(기본값)	모든 복제 게시 개체를 제거합니다.

ERROR CASE

게시& 배포자가 먼저 삭제되고 구독이 남아 있을 경우

ALTER DATABASE DB_Name SET EMERGENCY
go
ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
DBCC CHECKDB('DB_Name',REPAIR_ALLOW_DATA_LOSS)
go
ALTER DATABASE DB_Name SET MULTI_USER WITH ROLLBACK IMMEDIATE

http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx

DBCC FREESYSTEMCACHE(TokenAndPermUserStore)    

--보안 캐쉬를 제거

DBCC FREESYSTEMCACHE    

--사용하지 않는 항목을 모든 캐시에서 직접 제거

DBCC FREEPROCCACHE

--프로시져 캐시에서 모든 요소를 제거

DBCC FLUSHPROCINDB (dbid)

--지정한 데이터베이스에 있는 스토어드 프로시저 캐시를 제거

•   SQL Server 구성 관리자 :: 로컬 SQL Server 또는 SQL Server 에이전트 서비스의 인스턴스를 시작, 일시 중지, 재개 및 중지합니다.

•  명령 프롬프트 : net start , sqlservr.exe 실행하여 SQL Server or SQL Server Agent 인스턴스 시작합니다.

  • 직접 입력한 모든 명령 프롬프트 옵션은 SQL Server 설치 동안 Windows 레지스트리에 기록된 기본 명령 프롬프트 옵션보다 우선합니다.
  • 문제 해결을 목적으로 SQL Server 를 시작하려는 경우에만 명령 프롬프트에서 sqlservr.exe를 실행하십시오.
  • Windows를 로그오프하기 전에 SQL Server 인스턴스를 종료해야 합니다.

 net 명령

sqlserver.exe

• SQL Server를 시작할 수 있는지 테스트하는 목적 이외에는 명령줄에서 SQL Server를 실행하지 마십시오. 명령줄에서 SQL Server를 시작한 후 유지 관리 작업을 수행하면 나중에 오류가 발생할 수 있습니다. 예를 들어 관리자로 로그인하여 새 데이터베이스를 만들거나 데이터 파일의 위치를 변경하면 나중에 SQL Server 서비스의 일반 계정에서 SQL Server를 실행한 경우 해당 데이터베이스나 데이터 파일에 액세스할 수 없습니다.

• cd \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn 에 위치

   

서비스 시작옵션 (Using the SQL Server Service Startup Options)

• 서비스 시작되면서 필요한 추척 플래그 옵션 참고 하십시오.

  기본시작옵션	설명
  -d master_file_path	master 데이터베이스 파일의 정규화된 경로 C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\master.mdf 제공되지 않으면 기존의 레지스트리 매개 변수 사용
  -e error_log_path	오류 로그 파일의 정규화된 경로입니다.  C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG
  -I master_log_path	master 데이터베이스 로그 파일의 정규화된 경로입니다. 일반적으로 C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\mastlog.ldf입니다.
  -c	명령 프롬프트에서 SQL 을 시작할때 시간을 단축 시킵니다.
  -f	SQL Server 인스턴스를 최소 구성으로 시작합니다.  예를 들어 오버커밋 메모리 같은 구성 값의 설정 대문에 서버를 시작할 경우에 유용
  -g memory_to_reserve	SQL Server에서 SQL Server 프로세스 내(단, SQL Server 메모리 풀 외부)의 메모리 할당에 대해 사용 가능하도록 둘 메모리를 MB 단위의 정수로 지정합니다. SQL Server 오류 로그에서 다음 경고가 표시되지 않으면 -g 매개 변수의 기본값을 사용해야 합니다. "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"  "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"
  -h	32비트 SQL Server 2005에서 AWE가 사용된 경우 Hot Add 메모리 메타데이터에 대해 가상 주소 공간을 예약합니다. 32비트 AWE가 사용된 Hot Add 메모리에 필요하지만 가상 주소 공간에서 약 500MB를 사용하고 메모리 튜닝도 더 어렵게 만듭니다. 64비트 SQL Server에는 필요하지는 않습니다. Hot Add 메모리는 Enterprise Edition 및 Datacenter Edition에서만 사용할 수 있습니다. 또한 하드웨어 공급업체의 특수한 하드웨어 지원이 필요합니다.
  -n	SQL Server 이벤트를 기록하는 데 Windows 응용 프로그램 로그를 사용하지 않습니다. -n으로 SQL Server 인스턴스를 시작하는 경우 -e 시작 옵션을 함께 사용하는 것이 좋습니다.
  -s	s 매개 변수를 설정하지 않으면 기본 인스턴스가 시작을 시도합니다.
  -T trace#	지정된 추적 플래그(trace#) 적용 시 SQL Server 인스턴스를 시작해야 함을 나타냅니다. 추적 플래그는 비표준 동작으로 서버를 시작하는 데 사용합니다.
  -x	CPU 시간과 캐시 적중률 통계를 유지할 수 없도록 합니다. 최고의 성능을 허용합니다.

1. 1.  

      Sqlservr.exe –c –m –T3609 –T4022

   2.  

      3609 : SQL시작시 tempdb 생성 skip

      4022 : SQL시작시 실행되는 sp무시

작업으로 인해 배포자 agent를 중지하고 작업 처리후 시작하는 것을 잊어버리는 수가 있다. 혹은 다른 문제로 인해 배포 agent가 실행되고 있지 않을때,

오래 동안 실행되지 않으면 복제가 실패되어 다시 구축해야 하는 경우가 발생한다.

아래 쿼리는 배포 agent 실행여부를 확인하여 메일이나 SMS를 받을 수 있게 처리하는 것이다.

이또는 해당 job이 실행되고 있거나 오래 실행되는것도 찾을 수 있다.

[code sql]

DECLARE @is_sysadmin INT 
DECLARE @job_owner sysname 
DECLARE @job_id uniqueidentifier 
DECLARE @job_name sysname 
DECLARE @running int 
DECLARE @cnt int 
DECLARE @msg varchar(8000) 
DECLARE @msg_header varchar(4000) 
DECLARE @categoryid int 


SELECT @job_owner = SUSER_SNAME() 
,@is_sysadmin = 1 
,@running = 0 
,@categoryid = 10 -- Distributor jobs 


CREATE TABLE #jobStatus (job_id UNIQUEIDENTIFIER NOT NULL, 
last_run_date INT , 
last_run_time INT , 
next_run_date INT , 
next_run_time INT , 
next_run_schedule_id INT , 
requested_to_run INT , 
request_source INT , 
request_source_id sysname COLLATE database_default NULL, 
running int , 
current_step INT , 
current_retry_attempt INT , 
job_state INT) 


INSERT INTO #jobStatus 
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner--, @job_id 
--
select j.name, js.command, jss.running
from msdb.dbo.sysjobsteps js
join msdb.dbo.sysjobs j on js.job_id = j.job_id
join #jobStatus jss on js.job_id = jss.job_id
where step_id = 2 and subsystem = 'Distribution'
and command like '%-Continuous'
and jss.running <> 1 -- Not running



[/code]

http://msdn.microsoft.com/ko-kr/library/ms189310.aspx

 NUMA 노드에 TCP/IP 포트 매핑

http://msdn.microsoft.com/ko-kr/library/ms345346.aspx

소프트 NUMA를 사용하도록 SQL Server 구성

   http://msdn.microsoft.com/ko-kr/library/ms345357.aspx 

Attach 스크립트 만들기

--==========================================
--DB 복원
--==========================================
SET NOCOUNT ON
DECLARE @sql            nvarchar(max)
DECLARE @sql_move       nvarchar(max)
DECLARE @move           nvarchar(200)
DECLARE @dbid           int


SET @sql_move = ''
SET @sql = ''
DECLARE cur_restore CURSOR FOR
      SELECT dbid
            , 'CREATE DATABASE ' + name + ' ON' + char(13)
      FROM sys.sysdatabases WHERE dbid > 4 and NAME != 'LiteSpeedLocal' 
      ORDER BY name

OPEN cur_restore
FETCH NEXT FROM cur_restore
INTO @dbid, @sql_move

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @move = ''

        DECLARE cur_move CURSOR FOR
            
            select '(NAME = ''' + name + ''', FILENAME = ''' + filename + '''),' 
            from sys.sysaltfiles
            where dbid = @dbid

        OPEN cur_move
        FETCH NEXT FROM cur_move
        INTO @move


        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql_move = @sql_move + @move + char(13)

            FETCH NEXT FROM cur_move
            INTO @move
        END
        CLOSE cur_move
        DEALLOCATE cur_move


    SET @sql = @sql + @sql_move + 'FOR ATTACH' + char(13)
  --  select @sql
    print @sql
    set @sql = ''
    FETCH NEXT FROM cur_restore 
    INTO @dbid, @sql_move

END
CLOSE cur_restore
DEALLOCATE cur_restore

테이블의 rowcount와 예약된 공간, 인덱스 공간 사이즈.

sp_spaceused 를 사용해도 되지만, 테이블 여러개를 한번에 조회시 사용하면  좋다.

--==================================
-- DB 테이블 목록, row수 ceusee
-- =================================
DECLARE @tablename sysname
SET @tablename = ''
SELECT sys.name, ind.row_count
    ,convert(int,(sum(convert(numeric(15,2),reserved) * m.low /1024))) as 'reserved(KB)'
    ,convert(int,(sum(convert(numeric(15,2),used) * m.low /1024))) as 'indexp(KB)'
FROM sys.sysobjects as sys 
    join sys.dm_db_partition_stats as ind on sys.id = ind.object_id
    join sysindexes as i on  i.id = ind.object_id
    ,master.dbo.spt_values  as m
WHERE sys.type = 'U' and ind.index_id < 2 
    and m.number = 1 and m.type = 'E'
    and i.indid in (0,1,255)
    and sys.name  = @tablename
)
GROUP BY sys.name, ind.row_count

관련글 :

Lock::Trace Flag 1204

deadlock이 자주 발생하는 쿼리가 있다면,  해당 원인을 찾아야 한다.

이때 T 1204를와 T 1222를 켜줘서 실행 확인이 가능하다.

1204만을 적용되었을 때는 dbcc page 번호를 통해 발생원인이 되는 객체와 인덱스를 찾으면 된다.

몇번의 deadlock이 발생후 멈추는거라면 쿼리를 반복 실행해서 원하는 결과를 얻게 할 수  있다.

이때

** DeadLock 발생 예제 **

 1. 필요한 객체 생성

[code sql]
USE AdventureWorks;
GO

-- Verify that the table does not exist.
IF OBJECT_ID (N'my_sales',N'U') IS NOT NULL
    DROP TABLE my_sales;
GO

-- Create and populate the table for deadlock simulation.
CREATE TABLE my_sales 
    (
    itemid       INT PRIMARY KEY,
    sales        INT not null
    );
GO

INSERT my_sales (itemid, sales) VALUES (1, 1);
INSERT my_sales (itemid, sales) VALUES (2, 1);
GO

-- Create a stored procedure for printing error information.
CREATE PROCEDURE usp_MyErrorLog
AS
    PRINT 
        'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
        ', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
        ', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) + 
        ', Line ' + CONVERT(VARCHAR(5), ERROR_LINE());
    PRINT 
        ERROR_MESSAGE();
GO
[/code]

2. session # 1, #2에 동시에 해당 쿼리를 실행한다.

#1을 먼저 실행하게되면, #2에서 deadlock가 발생하며, 재시도 처리하고 

그후 원하는 내용으로 update 된다.

[code sql]

USE AdventureWorks;
GO

-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
DECLARE @count INT;
SET @retry = 2;
SET @count = 0

-- Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- victim.
WHILE (@retry > 0)
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 1;

        WAITFOR DELAY '00:00:8';
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 2;

        SET @retry = 0;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH 
        -- Check error number.
        -- If deadlock victim error,
        -- then reduce retry count
        -- for next update retry. 
        -- If some other error
        -- occurred, then exit
        -- retry WHILE loop.
        IF (ERROR_NUMBER() = 1205)
        BEGIN
            SET @count = @count + 1;
            SET @retry = @retry - 1;
            SELECT @count as '[재 실행count]',  ERROR_MESSAGE()
            
        END
        ELSE
            SET @retry = -1;

        -- Print error information.
        EXECUTE usp_MyErrorLog;
  
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH;
END; -- End WHILE loop.
SELECT @count as '[총 실행count]'
GO

[/code]

deadlock이 발생했을 경우 errorlog 기록된 내역

2009-11-24 17:30:10.780 spid4s Wait-for graph
2009-11-24 17:30:10.780 spid4s NULL
2009-11-24 17:30:10.780 spid4s Node:1
2009-11-24 17:30:10.780 spid4s KEY: 10:72057594054639616 (020068e8b274) CleanCnt:3 Mode:X Flags: 0x0
2009-11-24 17:30:10.780 spid4s  Grant List 1:
2009-11-24 17:30:10.780 spid4s    Owner:0x054AD9A0 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x0962185C
2009-11-24 17:30:10.780 spid4s    SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 24
2009-11-24 17:30:10.780 spid4s    Input Buf: Language Event: 
-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
DECLARE @count INT;
SET @retry = 2;
SET @count = 1;
--Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- vict
2009-11-24 17:30:10.780 spid4s  Requested By: 
2009-11-24 17:30:10.780 spid4s    ResType:LockOwner Stype:'OR'Xdes:0x09621290 Mode: X SPID:59 BatchID:0 ECID:0 TaskProxy:(0x097F8378) Value:0x54ae760 Cost:(0/208)
2009-11-24 17:30:10.780 spid4s NULL
2009-11-24 17:30:10.780 spid4s Node:2
2009-11-24 17:30:10.780 spid4s KEY: 10:72057594054639616 (010086470766) CleanCnt:2 Mode:X Flags: 0x0
2009-11-24 17:30:10.780 spid4s  Grant List 1:
2009-11-24 17:30:10.780 spid4s    Owner:0x054AF040 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x096212B4
2009-11-24 17:30:10.780 spid4s    SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 23
2009-11-24 17:30:10.780 spid4s    Input Buf: Language Event: 
-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
SET @retry = 2;

-- Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- victim.
WHILE (@retry > 0)
BEGIN
   
2009-11-24 17:30:10.780 spid4s  Requested By: 
2009-11-24 17:30:10.780 spid4s    ResType:LockOwner Stype:'OR'Xdes:0x09621838 Mode: X SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0983C378) Value:0x54adc40 Cost:(0/208)
2009-11-24 17:30:10.780 spid4s NULL
2009-11-24 17:30:10.780 spid4s Victim Resource Owner:
2009-11-24 17:30:10.780 spid4s  ResType:LockOwner Stype:'OR'Xdes:0x09621838 Mode: X SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0983C378) Value:0x54adc40 Cost:(0/208)
2009-11-24 17:30:10.780 spid15s deadlock-list
2009-11-24 17:30:10.780 spid15s  deadlock victim=process2e58e38
2009-11-24 17:30:10.780 spid15s   process-list
2009-11-24 17:30:10.780 spid15s    process id=process2e58e38 taskpriority=0 logused=208 waitresource=KEY: 10:72057594054639616 (010086470766) waittime=3978 ownerId=347267 transactionname=user_transaction lasttranstarted=2009-11-24T17:30:03.803 XDES=0x9621838 lockMode=X schedulerid=2 kpid=9648 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-11-24T17:30:03.783 lastbatchcompleted=2009-11-24T17:30:03.780 clientapp=Microsoft SQL Server Management Studio - 쿼리 hostname=최보라 hostpid=8252 loginname=sa isolationlevel=read committed (2) xactid=347267 currentdb=10 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2009-11-24 17:30:10.780 spid15s     executionStack
2009-11-24 17:30:10.780 spid15s      frame procname=adhoc line=24 stmtstart=38 sqlhandle=0x020000007245e338daab90ce8e17c9bb5e80187b4d642c14
2009-11-24 17:30:10.780 spid15s UPDATE [my_sales] set [sales] = [sales]+@1  WHERE [itemid]=@2     
2009-11-24 17:30:10.780 spid15s      frame procname=adhoc line=24 stmtstart=938 stmtend=1106 sqlhandle=0x020000002ac2d80354f3e91e11b6251c8c189c5d7eccce5d
2009-11-24 17:30:10.780 spid15s UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 5
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 1;     
2009-11-24 17:30:10.780 spid15s     inputbuf
2009-11-24 17:30:10.780 spid15s -- Declare and set variable
2009-11-24 17:30:10.780 spid15s -- to track number of retries
2009-11-24 17:30:10.780 spid15s -- to try before exiting.
2009-11-24 17:30:10.780 spid15s DECLARE @retry INT;
2009-11-24 17:30:10.780 spid15s DECLARE @count INT;
2009-11-24 17:30:10.780 spid15s SET @retry = 2;
2009-11-24 17:30:10.780 spid15s SET @count = 1;
2009-11-24 17:30:10.780 spid15s --Keep trying to update 
2009-11-24 17:30:10.780 spid15s -- table if this task is 
2009-11-24 17:30:10.780 spid15s -- selected as the deadlock 
2009-11-24 17:30:10.780 spid15s -- victim.
2009-11-24 17:30:10.780 spid15s WHILE (@retry > 0)
2009-11-24 17:30:10.780 spid15s BEGIN
2009-11-24 17:30:10.780 spid15s     BEGIN TRY
2009-11-24 17:30:10.780 spid15s        BEGIN TRANSACTION;
2009-11-24 17:30:10.780 spid15s         UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 5
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 2;
2009-11-24 17:30:10.780 spid15s         WAITFOR DELAY '00:00:03';
2009-11-24 17:30:10.780 spid15s         UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 5
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 1;
2009-11-24 17:30:10.780 spid15s         SET @retry = 0;
2009-11-24 17:30:10.780 spid15s         COMMIT TRANSACTION;
2009-11-24 17:30:10.780 spid15s     END TRY
2009-11-24 17:30:10.780 spid15s     BEGIN CATCH 
2009-11-24 17:30:10.780 spid15s         -- Check error number.
2009-11-24 17:30:10.780 spid15s         -- If deadlock victim error,
2009-11-24 17:30:10.780 spid15s         -- then reduce retry count
2009-11-24 17:30:10.780 spid15s         -- for next update retry. 
2009-11-24 17:30:10.780 spid15s         -- If some other error
2009-11-24 17:30:10.780 spid15s         -- occurred, then exit
2009-11-24 17:30:10.780 spid15s         -- retry WHILE loop.
2009-11-24 17:30:10.780 spid15s         IF (ERROR_NUMBER() = 1205)
2009-11-24 17:30:10.780 spid15s         BEGIN
2009-11-24 17:30:10.780 spid15s             SET @retry = @retry - 1;
2009-11-24 17:30:10.780 spid15s             SELECT @count as '[실행count]',  ERROR_MESSAGE()
2009-11-24 17:30:10.780 spid15s    process id=process2e58f28 taskpriority=0 logused=208 waitresource=KEY: 10:72057594054639616 (020068e8b274) ownerId=347255 transactionname=user_transaction lasttranstarted=2009-11-24T17:30:02.793 XDES=0x9621290 lockMode=X schedulerid=2 kpid=6876 status=suspended spid=59 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-11-24T17:30:02.793 lastbatchcompleted=2009-11-24T17:30:02.793 clientapp=Microsoft SQL Server Management Studio - 쿼리 hostname=최보라 hostpid=8252 loginname=sa isolationlevel=read committed (2) xactid=347255 currentdb=10 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2009-11-24 17:30:10.780 spid15s     executionStack
2009-11-24 17:30:10.780 spid15s      frame procname=adhoc line=23 stmtstart=38 sqlhandle=0x020000007245e338daab90ce8e17c9bb5e80187b4d642c14
2009-11-24 17:30:10.780 spid15s UPDATE [my_sales] set [sales] = [sales]+@1  WHERE [itemid]=@2     
2009-11-24 17:30:10.780 spid15s      frame procname=adhoc line=23 stmtstart=868 stmtend=1036 sqlhandle=0x020000008a4537232ab1f574eb75390b8f81a691db149df4
2009-11-24 17:30:10.780 spid15s UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 1
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 2;     
2009-11-24 17:30:10.780 spid15s     inputbuf
2009-11-24 17:30:10.780 spid15s -- Declare and set variable
2009-11-24 17:30:10.780 spid15s -- to track number of retries
2009-11-24 17:30:10.780 spid15s -- to try before exiting.
2009-11-24 17:30:10.780 spid15s DECLARE @retry INT;
2009-11-24 17:30:10.780 spid15s SET @retry = 2;
2009-11-24 17:30:10.780 spid15s -- Keep trying to update 
2009-11-24 17:30:10.780 spid15s -- table if this task is 
2009-11-24 17:30:10.780 spid15s -- selected as the deadlock 
2009-11-24 17:30:10.780 spid15s -- victim.
2009-11-24 17:30:10.780 spid15s WHILE (@retry > 0)
2009-11-24 17:30:10.780 spid15s BEGIN
2009-11-24 17:30:10.780 spid15s     BEGIN TRY
2009-11-24 17:30:10.780 spid15s         BEGIN TRANSACTION;
2009-11-24 17:30:10.780 spid15s         UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 1
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 1;
2009-11-24 17:30:10.780 spid15s         WAITFOR DELAY '00:00:8';
2009-11-24 17:30:10.780 spid15s         UPDATE my_sales
2009-11-24 17:30:10.780 spid15s         SET sales = sales + 1
2009-11-24 17:30:10.780 spid15s         WHERE itemid = 2;
2009-11-24 17:30:10.780 spid15s         SET @retry = 0;
2009-11-24 17:30:10.780 spid15s         COMMIT TRANSACTION;
2009-11-24 17:30:10.780 spid15s     END TRY
2009-11-24 17:30:10.780 spid15s     BEGIN CATCH 
2009-11-24 17:30:10.780 spid15s         -- Check error number.
2009-11-24 17:30:10.780 spid15s         -- If deadlock victim error,
2009-11-24 17:30:10.780 spid15s         -- then reduce retry count
2009-11-24 17:30:10.780 spid15s         -- for next update retry. 
2009-11-24 17:30:10.780 spid15s         -- If some other error
2009-11-24 17:30:10.780 spid15s         -- occurred, then exit
2009-11-24 17:30:10.780 spid15s         -- retry WHILE loop.
2009-11-24 17:30:10.780 spid15s         IF (ERROR_NUMBER() = 1205)
2009-11-24 17:30:10.780 spid15s         BEGIN
2009-11-24 17:30:10.780 spid15s             SET @retry = @retry - 1;
2009-11-24 17:30:10.780 spid15s             SELECT ERROR_MESSAGE()
2009-11-24 17:30:10.780 spid15s         END
2009-11-24 17:30:10.780 spid15s         ELSE
2009-11-24 17:30:10.780 spid15s             SET @retry = -1;
2009-11-24 17:30:10.780 spid15s   resource-list
2009-11-24 17:30:10.780 spid15s    keylock hobtid=72057594054639616 dbid=10 objectname=AdventureWorks.dbo.my_sales indexname=PK__my_sales__7CA47C3F id=lock5446f00 mode=X associatedObjectId=72057594054639616
2009-11-24 17:30:10.780 spid15s     owner-list
2009-11-24 17:30:10.780 spid15s      owner id=process2e58f28 mode=X
2009-11-24 17:30:10.780 spid15s     waiter-list
2009-11-24 17:30:10.780 spid15s      waiter id=process2e58e38 mode=X requestType=wait
2009-11-24 17:30:10.780 spid15s    keylock hobtid=72057594054639616 dbid=10 objectname=AdventureWorks.dbo.my_sales indexname=PK__my_sales__7CA47C3F id=lock5447340 mode=X associatedObjectId=72057594054639616
2009-11-24 17:30:10.780 spid15s     owner-list
2009-11-24 17:30:10.780 spid15s      owner id=process2e58e38 mode=X
2009-11-24 17:30:10.780 spid15s     waiter-list
2009-11-24 17:30:10.780 spid15s      waiter id=process2e58f28 mode=X requestType=wait

현재 존재하는 Index 스크립트 생성.

T-SQL::List all indexes in database  에 관련 함수, view 생성해야함.

-- INDEX Script
SELECT  
    CASE WHEN T.TABLE_NAME IS NULL THEN 
        'CREATE ' 
        + CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END 
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END 
        + ' INDEX [' + INDEX_NAME + '] ON [' + v.TABLE_NAME + ']' 
        + ' (' + COLUMN_LIST + ') ON ' + FILE_GROUP 
    ELSE 
        'ALTER TABLE ['+T.TABLE_NAME+']' 
        +' ADD CONSTRAINT ['+INDEX_NAME+']' 
        +' PRIMARY KEY ' 
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END 
        + ' (' + COLUMN_LIST + ')' 
    END 
FROM 
    dbo.vAllIndexes v 
LEFT OUTER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
ON 
    T.CONSTRAINT_NAME = v.INDEX_NAME 
    AND T.TABLE_NAME = v.TABLE_NAME  
    AND T.CONSTRAINT_TYPE = 'PRIMARY KEY' 
ORDER BY 
    v.TABLE_NAME, 
    IS_CLUSTERED DESC

데이터 베이스에 있는 모든 인덱스 정보를 보기 위함.

sys.indexes, sys.index_columns, sys.columns  정보를 보면 복합 인덱스 일경우 row가 한개 이상으로 나타나서 한눈에 보기가 힘들다.

select  t.name as table_name ,ind.name as index_name, 
        ind.index_id, ic.index_column_id, col.name as column_name 
from    sys.indexes ind
inner join    sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join    sys.columns col on  ic.object_id = col.object_id and ic.column_id = col.column_id 
inner join    sys.tables t on  ind.object_id = t.object_id
where   ind.is_primary_key = 0   
    and ind.is_unique = 0     
    and ind.is_unique_constraint = 0    
    and t.is_ms_shipped = 0
order by    t.name, ind.name, ind.index_id, ic.index_column_id
 

그래서 다음과 같이 처리한다.

CREATE FUNCTION dbo.GetIndexColumnOrder 
( 
    @object_id INT, 
    @index_id TINYINT, 
    @column_id TINYINT 
) 
RETURNS NVARCHAR(5) 
AS 
BEGIN 
    DECLARE @r NVARCHAR(5) 
    SELECT @r = CASE INDEXKEY_PROPERTY 
    ( 
        @object_id, 
        @index_id, 
        @column_id, 
        'IsDescending' 
    ) 
        WHEN 1 THEN N' DESC' 
        ELSE N'' 
    END 
    RETURN @r 
END ;

CREATE FUNCTION dbo.GetIndexColumns 
( 
    @table_name SYSNAME, 
    @object_id INT, 
    @index_id TINYINT 
) 
RETURNS NVARCHAR(4000) 
AS 
BEGIN 
    DECLARE 
        @colnames NVARCHAR(4000),  
        @thisColID INT, 
        @thisColName SYSNAME 
         
    SET @colnames = '[' + INDEX_COL(@table_name, @index_id, 1) + ']'  
        + dbo.GetIndexColumnOrder(@object_id, @index_id, 1) 
 
    SET @thisColID = 2 
    SET @thisColName = '[' + INDEX_COL(@table_name, @index_id, @thisColID) + '] '
        + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
 
    WHILE (@thisColName IS NOT NULL) 
    BEGIN 
        SET @thisColID = @thisColID + 1 
        SET @colnames = @colnames + ', ' + @thisColName 
 
        SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID) 
            + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID) 
    END 
    RETURN @colNames 
END ;

CREATE VIEW dbo.V_ALLINDEXES 
AS
SELECT TABLE_NAME = OBJECT_NAME(i.id), 
      INDEX_NAME = i.name, 
      COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id, i.indid), 
      IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
      IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'), FILE_GROUP = g.GroupName 
FROM sysindexes i
INNER JOIN sysfilegroups g ON i.groupid = g.groupid
WHERE (i.indid BETWEEN 1 AND 254) -- leave out AUTO_STATISTICS: 
 AND (i.Status & 64)=0 -- leave out system tables: 
 AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0 ; 

One of the main benefits of using a stored procedure to execute Transact-SQL code is that once a stored procedure is compiled and executed the first time, the query plan is cached by SQL Server. So the next time the same stored procedure is run (assuming the same connection parameters are used), SQL Server does not have to recompile the stored procedure again, instead reusing the query plan created during the first compilation of the stored procedure.

 If the same stored procedure is called over and over again, with the query plan being reused each time, this can help reduce the burden on SQL Server's resources, boosting its overall performance.

그러나 application에서 SQL Server로 보내는 쿼리는 동적 쿼리도 존재한다. 이런 쿼리들은 매개 변수화 되지 않아서 재 사용이 이루어 지지 않는다.

 ALTER DATABASE 문에서 PARAMETERIZATION 옵션을 FORCED로 설정하여 강제 매개 변스화를 설정하면 쿼리 컴파일 및 재 컴파일을 빈도를 줄여 특정 데이터베이스의 성능을 향상 시킬 수 있다.

이것을 설정하면 SELECT, INSERT, UPDATE, DELETE 문에 표시되는 리터럴 값이 쿼리 컴파일 중 매개변수로 변환된다.

다음 구문에 나타나는 리터럴은 예외이다.

[code sql]

ALTER DATABASE FORCED

[/code]

SQL Server의 데이터베이스는 데이터 파일과 로그 파일로 구성됨.

로그 파일은 앞에서 설명한 VLF로 구성된다.

반면, 데이터 파일의 가장 작은 단위는 페이지이다.

연속적인  8개의 페이지로 extent가 구성된다.

extents는 'single-extent' (균일 익스텐트), 'mixed extent'(혼합 익스텐트) 로 나뉜다.

균일 익스텐트는 하나의 개체가 8개의 페이지를 모두 사용 하며, 혼합 익스텐트는 하나의 익스텐트에 여러 개체가 들어간다.

테이블을 만들때는 익스텐트 단위로 일어나는데 SQL은 처음 테이블을 만들때는 항상 혼합 익스텐트 내에서 생성한다.

테이블이 커져가서 8 개의 page를 채훈 후 부터 균일 인스텐트를 할당한다.

* GAM, SGAM

SQL Server는 전역 할당 맵(GAM) 과 공유 전역 할당 맵(SGAM) 이라는 두가지 할당 맵을 사용하여 익스텐트, GAM, SGAM은 각 1bit로 하나의 익스텐트를 관리함으로 총 6400 익세텐트를 관리 (4GB)

GAM : 어떤 익스텐트가 할당되었는지 관리

SGAM : 어떤 익스텐트가 현재 혼합 익스텐트로 사용되는지, 빈 페이지를 가지는지 여부 관리

SGAM : 1인것이 존재 하지 않으면, 1:0 인것을 찾아서 할당하고 비트 패턴을 0:1 로 변경한다.

2. GAM과 SGAM은 데이터 파일 하나씩 있으므로 해당 DB의 파일 개수를 늘려주면 SGAM에 대한 요구가 분산되어 보다 좋은 성능을 낼 수 잇다.

임시테이블은 모두 tempdb에서 처리됨으로 tempdb의 데이터 파일을 여러개로 분리하는 것이 많은 수의 임시 테이블 요구에 대한 할당 잠금 측면에서 유리하다.

* PFS (Page Free Space)

힙, ntext, text, image 컬럼의 페이지가 할당되었는지 여부와 각 페이지의 빈 공간 크기를 기록한다.

하나의 PFS 페이지는 약 8000 page = 1000 익스텐트 관리하며, 파일이 커지면 연결 리스트 구조로 새로운 PFS 페이지가 추가된다.

* BCM (Bulked Change Map)

마지막 Backup log 문 이후 대량 기록 작업에 의해 수정된 익스텐트를 관리, 대량 로그 복구 모델일 때 사용됨

* DCM

마지막 bakcup dataase (full backup) 이후에 변경된 익스텐트를 관리하는 페이지이며, 차등 백업의 대상 익스텐트를 찾기 위해 사용,

BCM, DCM도 1bit로 하나의 익스텐트를 관리함으로 총6400 익스텐트 관리하게 된다.

* IAM (Index Allocation Map)

개체가 사용하는 데이터베이스 파일의 익스텐트를 매핑한다.

http://www.errorhelp.com/ 에서 찾아봤음.

미러링되는 미러서버의 DB가 정합성이 맞지 않아서 발생. 미러도 일시 중지 상태가 됨.

미러를 재 구축 해야함.

미러DB가 아니라면 응급 모드로 변경해서 읽기라도 가능하게 하여 조치할것 조치함.