깡민쓰~IT Story

SELECT 컬럼1,컬럼2,컬럼3

FROM     테이블
GROUP BY 컬럼1,컬럼2,컬럼3

HAVING COUNT(*) > 1

서버쿼데이터를 비교하여, 결과값을 보고자하는 경우.

ex) A 테이블과 B 테이블을 비교하여 다른 내용이 있는경우 해당 칼럼을 출력.

select * from A where not exists
(select * from B where A.aa = B.aa)

A 테이블의 aa와 B 테이블의 aa가 다른 값인 경우, 데이터가 틀린 칼럼만 select문이 수행되어 보여진다.

SET LANGUAGE KOREAN --//한국어로 설정

GO

SELECT 1/0 --//0으로 나누기 에러 발생

GO

SET LANGUAGE ENGLISH --// 영어로 설정

GO

SELECT 1/0 --//0으로 나누기 에러 발생

GO

설정할 수 언어의 종류는.. 아래 테이블에 저장이 되어 있습니다. 

SELECT * FROM master.dbo.syslanguages

또한 SET LANGUAGE는 기본 적으로 모든 유저가 설정할 수 있도록 되어있습니다.(= 권한 제약이 없음)

SET LANGUAGE {name | alias}

http://msdn.microsoft.com/en-us/library/aa259215(v=sql.80).aspx

Database 생성하는 방법

- Logical Name: 논리적인 이름

- File Type: Rows Data(MDF 파일), Log(LDF 파일)

- Filegroup: 파일 그룹(기본값 PRIMARY)

- Initial Size (MB): 기본적인 파일 사이즈(단위: MB)

- Autogrowth / Maxsize: 자동 증가 설정 및 최대 사이즈 설정 값

- Enable Autogrowth: 자동증가 활성 여부

   File Growth: 파일 증가 방법

     1. In Percent: 퍼센트

     2. In Megabytes: MB 량으로 늘리기

   Maximun File Size: 최대 파일 사이즈

     1. Limited to (MB): 지정된 파일 사이즈

     2. Unlimited: 제한 없음

- Path: 실제 파일이 위치할 경로

- File Name: 실제 파일 이름

명령으로 생성 방법

트랜잭션 로그의 관리

트랜잭션 로그를 자동증가 옵션으로 사용할 경우 무한적으로 증가할 수 있음.

Disk full을 막기 위하여 트랜잭션 로그의 관리가 필요함.

1. 트랜잭션 로그 삭제
- 트랜잭션 로그의 중요도가 낮아서 백업할 필요가 없는 경우 그냥 삭제

BACKUP LOG DB명 WITH {NO_LOG|TRUNCATE_ONLY}

2. 트랜잭션 로그 백업
- 트랜잭션 로그를 백업하면 자동으로 기존 로그 내용이 정리된다.
단, 이미 늘어나 있는 트랜잭션로그 파일(?.LDF)의 사이즈가 줄어드는건 아니고,
로그의 내용만 삭제된다.

BACKUP LOG DB명 TO DISK = <백업파일명>

3. 트랜잭션 로그 파일 사이즈 줄이기
- ?.LDF 파일의 실제 사이즈를 줄이고 싶을 경우

DBCC SHRINKDATABASE(DB명,TRUNCATEONLY)

or

BACKUP LOG DB명 WITH TRUNCATE_ONLY

트랜잭션 로그 내용이 있는 만큼의 사이즈로 줄어든다.

* 트랜잭션 로그의 MAX 사이즈 제한
- 특정 용량 이상으로 늘어나길 원치 않을 경우 사이즈를 제한한다.
제한사이즈를 넘어설 경우 옛 데이터부터 자동 삭제된다.

DBCC SHRINKDATABASE(DB명,1024)

DB명 뒤의 단위는 MB.

4. 확인 방법

MS-SQL Server Management Studio 등에서 SQL명령어로

Use <DB명>;

DBCC LOGINFO;             /* 로그파일 정보 보기 */

EXEC SP_HELPFILE;       /* 데이터파일,로그파일 정보 보기 */

select * from sysfiles          /* 데이터파일,로그파일 정보 보기 */

dbcc sqlperf(logspace);      /* 로그파일 사용량(%) 보기 */

참고(SQL 2008 이상 버젼)

TEMP DB 로그 축소 방법

방법1. DB Log를 TRUNCATEONLY 하는 방법(로그가 최소 사이즈 부터 시작)

         DBCC SHRINkFILE(templog)

         설명

         DBCC SHRINkFILE(DB로그명)

방법2.  DB Log 초기 크기를 지정하여 줄이는 방법

          -- Templog 사이즈를 300Gb로 초기 사이징 하여 시작하게 설정

          ALTER DATABASE tempdb MODIFY FILE  (NAME = 'templog', SIZE = 307200)  

         설명

          ALTER DATABASE DB명 MODIFY FILE  (NAME = 'file_log Name', SIZE = Mb단위 크기)

서버 이전작업시 master DB를 그대로 이전한다고 했을때
MS-SQL 2000에서는 대상서버로 master DB를 이전하려면 master DB의 백업본을 가지고 단일 사용자 모드에서 복원하던지
대상서버의 MSSQL 서비스를 중지시킨 다음 원본 서버의 mdf 파일을 복사해 서비스를 시작시키면 기존의 master DB를 사용할 수 있었다.

하지만 이때 master DB에 저장되어 있는 연결된 서버 정보를 그대로 사용할 수 있으므로
이런 정보를 이용하여 다른 서버에 특정 권한으로 접근할 수 있게 된다.
이러한 문제 때문인지 MS-SQL 2005에서 보안이 강화되어 master DB를 사용하기 위한 추가적인 작업이 필요해졌다.

MS-SQL 2005에서는 "서비스 마스터 키" 라는 개념이 도입되었다.
BOL을 보면 "서비스 마스터 키"는 다음과 같이 설명되어 있다.

만일 master DB를 복사해 오거나 복원을 했다면 서비스를 시작했을 때 sp_readerrorlog를 통해 다음과 같은 오류 메시지를 확인할 수 있다.
(사용자 DB도 모두 정상적으로 시작되고 언뜻 보기엔 정상으로 보일 수 있다.)

    2008-06-06 12:08:08.680 spid4s 오류: 15466, 심각도: 16, 상태: 1.
    2008-06-06 12:08:08.680 spid4s An error occurred during decryption.

그리고 연결된 서버를 읽거나 추가/수정/삭제 하려고 하면 다음과 같은 화면을 만나게 된다.
"암호화 해독 중에 오류가 발생했습니다."

이때는 원본 서버에서 서비스 마스터키를 파일로 백업해서 대상 서버에서 복원시키면 정상적으로 암호화된 정보를 이용할 수 있다.
     BACKUP SERVICE MASTER KEY TO FILE = 'c:\service_master_key' ENCRYPTION BY PASSWORD = 'password'
     RESTORE SERVICE MASTER KEY FROM FILE = 'c:\service_master_key' DECRYPTION BY PASSWORD = 'password' -- [FORCE]

RESTORE시 FORCE 옵션은 서비스 마스터 키 복구로 인해 기존에 생성된 정보가 손실될 수 있는 경우 오류 메시지가 발생하게 되는데 이를 무시하고 적용하겠다는 옵션이다.

이렇게 서비스 마스터 키를 복원하고 나서 MS-SQL 2005 서비스를 재시작하면 sp_readerrorlog에서 오류 메시지가 사라진 것을 확인할 수 있다.

이런 이유로 master DB를 백업할 때 서비스 마스터 키도 함께 백업을 해 두어야
나중에 복구할 이슈가 발생했을 때 master DB의 암호화된 정보를 사용할 수 있을 것 같다.

[별첨] BOL에서 설명하고 있는 보안수준.
SQL Server 수준에서의 최상위 정보가 서비스 마스터 키 이며,
데이터베이스 암호화시도 이 서비스 마스터 키를 이용하여 암호화를 한다고 한다.

DB서버를 신규 장비로 교체하거나 2대를 1대로 합치는 작업을 종종하는데요, 자주하는 작업이 아니라서, 조금씩 놓치는 부분이 발생하게 됩니다.
팀에서 DB서버 이전시 챙겨야 하는 작업에 대해서 함께 정리해 보았습니다.

글을 보다가 재미난 테스트 내용이 있어 공유드립니다.

테스트 내용은 연결된 서버를 통해서 데이터를 가지고 올 때 Pull/Push(땡겨오는/밀어넣는) 방식에 따른
성능 차이가 얼마나 발생하는지 비교한 내용입니다.

1. 테스트 스크립트 (5만건의 데이터 이관할 때 사용한 스크립트)

-- Push Script

insert openquery(SQL02, 'select * from testDB.dbo.target_table') select * from source_table;

-- Pull Script

insert target_table select * from openquery(SQL01, 'select * from testDB.dbo.test')

2. 테스트 결과

3. 결과에 대한 의견
  > Pull 방식이 Push 방식에 비해서 성능이 매우 좋은 것을 볼 수 있습니다. (약 120배)
  > 대량 데이터 이관시 Pull 방식을 사용하시기를 권장합니다.
  > 단순한 쿼리를 사용한 테스트이므로 모든 경우에 Push 방식이 더 빠르다고 이야기하기는 힘들 것 같습니다.
     (Join이나 subquery가 있는 경우 결과가 달라질 수 있지 않을까 생각해봅니다.)

4. 연결된 서버 사용에 대한 개인적인 의견
  > 서비스 프로시져 내에서 연결된 서버를 사용하는 것은 권장하고 싶지 않습니다.
     이유는 1대의 DB서버 장애시 다른 DB서버까지 장애가 확대되어지기 때문입니다.
  > 필요한 부분이나 상황에 맞추어 사용할 필요는 있지만 가능한 서비스 프로시져 내에서 여러 "연결된 서버"에
     접근하는 것은 제거 또는 최소화하는 것이 좋지 않을까합니다.


출처. Linked servers and performance impact: Direction matters!

올해초에 SSMS Tools Pack을 소개시켜드린 적이 있었는데, 신규 버젼이 나와서 공유드립니다.

신규 버전의 가장 큰 특징은 아래와 같습니다.
  1. Tab Sessions : 종료된 세션에 대한 기록 조회 가능     

  2. Execution Plan Analyzer : 쿼리 실행계획 분석, 실행계획을 이미지로 바로 캡쳐
     - 왼쪽 상단에 있는 3개의 아이콘이 비용/개선 중요도가 높은 작업을 찾아서 보여주는 기능을 가지고 있습니다. (검색도 포함)
     

한번 더 소개를 드린 이유는 Execution Plan Analyzer 기능이 추가되어서 입니다. 실행계획을 분석할 때 도움이 될 것 같네요.

출처 : http://www.ssmstoolspack.com/