반응형

2013년에 포스팅한 내용이 너무 간단한 것 같아서 좀 더 자세한 내용으로 다시 올립니다.

hackhyun.tistory.com/231

 

[SQL서버] PK(Primary Key) 변경 하기

SQL서버에서 기존의 PK를 다른 컬럼 구성으로 변경 하려면 다음과 같이 한다. -- 기존 PK명 확인 sp_help 테이블명 --기존 PK 삭제 ALTER TABLE 테이블명 DROP CONSTRAINT 기존_PK명 --새로운 PK 정의 ALTER TABLE..

hackhyun.tistory.com

SQL서버에서 기존의 PK로 사용된 컬럼을 다른 컬럼으로 변경하려면 다음과 같이 합니다.

1. 기존 PK 명(인덱스 명) 확인

sp_helpindex 테이블명
  • primary key로 지정된 인덱스 명을 확인 합니다.

2. 기존 PK 삭제

ALTER TABLE 테이블명
DROP CONSTRAINT 기존_PK명
  • 1에서 확인된 인덱스 명을 이용합니다.

3. 새로운 PK 정의

ALTER TABLE 테이블명
ADD CONSTRAINT 새_PK명 
PRIMARY KEY [NONCLUSTERED](컬럼1, 컬럼2, ...)
  • 새 PK에 포함될 컬럼은 NULL이 허용되지 않는 컬럼이어야 합니다.
  • 별도로 지정하지 않으면 PK 컬럼이 클러스터 인덱스로 생성됩니다.
  • PK가 아닌 다른 컬럼을 클러스터 인덱스로 사용할 계획이라면 NONCLUSTERED 옵션을 지정합니다.

4. 잘 변경되었는지 확인

 

반응형
반응형

SSMS 18.3 버전 부터 쿼리를 실행 하고 나면 항상 완료시간이 표시 됩니다.

특별히 도움 되지는 않고 귀찮기만 한 경우에는 다음 옵션에서 체크를 해제 하면 됩니다.

도구 > 옵션 > 쿼리 실행 > SQL Server > 고급 

 
 
 
 
반응형
반응형

원격지 서버의 테이블에 대한 갱신 작업을 위해서 다음과 같이 Linked Server의 테이블을 JOIN 하여 UPDATE 하였습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'

BEGIN DISTRIBUTED TRAN

UPDATE Trgt
SET
    AcptYn = 'N',
    AcptEmpCode = '', 
    AcptDeptCode = '',
    AcptDateTime = NULL
FROM
    RemoteServer.RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
        JOIN RemoteServer.RemoteDB.dbo.FIAccSlipAggreationNo AS A
            ON a.SlipNo = Trgt.SlipNo
            AND A.AggregationNo = @ano

SET XACT_ABORT OFF;

COMMIT

그러나 동작은 잘 되지만 성능이 너무 나오지 않았습니다.

실행계획을 보니 원격지 테이블을 모두 읽어와서 JOIN 시키다 보니 실제 UPDATE 시킬 행은 1개 인데 수십만 건의 데이터를 읽고 있었습니다.

그래서 OpenQuery로 변경 하려고 했으나, 문제는 OpenQuery의 SQL문이 순수한 문자열만 가능 하도록 되어 있어서 파라미터(변수)를 사용할 수가 없었습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'

BEGIN DISTRIBUTED TRAN

UPDATE x
SET
    AcptYn = ''N'',
    AcptEmpCode = '''', 
    AcptDeptCode = '''',
    AcptDateTime = NULL
FROM
    OpenQuery (RemoteServer,
        '
        SELECT
            Trgt.*
        FROM RemoteDB.dbo.FIAccSlipAggreationNo AS A
            JOIN RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
                ON a.SlipNo = Trgt.SlipNo
        WHERE
            A.AggregationNo = ''' + @ano + '''
        '
    ) x

SET XACT_ABORT OFF;

COMMIT

그래서 최종적으로 OpenQuery를 포함한 전체 쿼리 문장을 문자열 변수에 넣고 EXEC()로 처리 하였습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'
DECLARE @sql varchar(max)

BEGIN DISTRIBUTED TRAN

set @sql = 
    '
    UPDATE x
    SET
        AcptYn = ''N'',
        AcptEmpCode = '''', 
        AcptDeptCode = '''',
        AcptDateTime = NULL
    FROM
        OpenQuery (RemoteServer,
            ''
            SELECT
                Trgt.*
            FROM RemoteDB.dbo.FIAccSlipAggreationNo AS A
                JOIN RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
                    ON a.SlipNo = Trgt.SlipNo
            WHERE
                A.AggregationNo = ''''' + @ano + '''''
            ''
        ) x
    '
EXEC(@sql)

SET XACT_ABORT OFF;

COMMIT

성능도 좋고, 파라미터(변수)도 처리할 수 있어서 만족할 만한 결과가 나왔습니다.
원격지 서버에 대한 갱신작업이 필요하실 때 참고 하시기 바랍니다.

반응형
반응형

SSMS 에서 Ctrl + M 을 눌러서 '실제 실행 계획 포함' 이 활성화 된 상태에서

쿼리를 실행 시키고 나면 실행계획이 함께 나타납니다.

실행계획 상의 특정 노드에 마우스를 가져가면 상세한 내용이 나타 납니다.

위 그림에서 '예상 행 수' 가 2.77684로 되어 있어 옵티마이저가 2.7행을 예상 했다고 생각할 수 있으나

실제 예상 행 수는 '예상 실행 횟수'를 곱한 값이 됩니다.

따라서 해당 노드에서 반환되는 총 예상 행 수는 2.77684 × 6985.3104807 = 19397.08956 입니다.

이 총 예상 행 수 값이 노드 하단의 분모 값 입니다.

그리고 분자 값은 '실제 행 수' 입니다. 

괄호 안의 %값은 총 예상 행 수 대비 실제 행 수의 백분율 입니다. 이 백분율 값이 100%에 가까울 수록

통계값에 의한 적절한 실행계획이 수립되었다고 볼 수 있습니다.

 

그러나 아래의 경우 처럼 이 값이 과도하게 적거나 많을 경우 통계가 제대로 갱신되지 않아서 엉뚱한 실행계획이

수립되었을 가능성이 있습니다.

이 경우 UPDATE STATISTICS 로 최신 통계로 업데이트가 필요 합니다.


※ [2020.10.29] 추가.
SSMS 버전 18.5 이후에 실행계획 항목(속성) 일부의 명칭이 바뀌고, 추가된 것으로 확인 됩니다.

SSMS v18.6

기존 '실제 행 수'가 '모든 실행에 대한 실제 행 수'로, 기존 '예상 행 수'가 '실행당 예상 행 수'로 
각각 명칭이 변경 되었습니다. 그리고 '모든 실행에 대한 예상 행 수' 속성이 추가 되었습니다.

같은 실행계획을 이전의 SSMS 에서 확인했을 때는 다음과 같았습니다.

SSMS v12.0

반응형

+ Recent posts