1. 저장 프로시져 생성시 SET NOCOUNT ON 옵션을 활성화하라
네트워크 소통량을 줄일수 있다.(DONE_IN_PROC 메시지 발
생이 비활성화 되기 때문에 클라이언트로 결과행 갯수를
알려주는 패킷을 전달할 필요가 없다)

2. 프로시져 호출시 소유자를 지정하는 것이 좋다. 소유자를 생략하면
프로시져 캐시에서 프로시져를 찾을수 없기 때문에 SQL 서버가 그
프로시져에 대해 컴파일 잠금을 수행하게 된다.

3. 동적 T-SQL 을 실행할때는 EXEC() 보다 sp_excutesql 을 사용하는것이
좋다.sp_excutesql을 사용하게 되면 실행계획이 캐시로 저장되어 나중에
재사용 될 수 있는 이점을 가지게 때문이다.

* 실행계획이 캐쉬에 있으면 그 프로시져가 호출될때 실행계획을 새로 만들지
않고 캐시에 있는 것을 재사용할 수 있다. 이렇게 되면 프로시져를
처음 실행할때 행하는 쿼리 트리구성과 계획 생성 작업을 수행하지 않아도 되며.
이것이 T-SQL 일괄 처리 보다 뛰어난 성능상의 장점이다.

4. RPC 요청은 SQL Server의 많은 명령문 구문 분석과 매개 변수 처리를 무시하며
Transact-SQL EXECUTE 문을 사용하는 것보다 빠르게 진행됩니다

5. 오브젝트 이름에 소유자를 지정하는 것이 성능향상에 도움이된다.
프로시져의 경우 소유자를 지정하지 않으면 SQL 서버는 그 프로시져에 대해
컴파일 잠금을 필요로 하는데 결국은 SQL서버가 실행계획이 필요하지 않다는
판단을 하게 되어 불필요한 잠금을 하게 되는것이다.이렇게 되면 블로킹 경합을
유발할수 있게 때문에 프로시져 호출시 DB.DBO.proc 의 형태로 호출을 하는것이
성능향상에 유리하다.

6. 임시 테이블을 사용하지 않는것이 유리하다.TEMPDB 에서 리소스 경합이 발생하여
뜻하지 않은 저장 프로시져 재 컴파일과 같은 결과를 가져올 수 있다. table 변수를
사용하게 되면 임시 테이블을 사용하는 것과 같은 일들을 할 수 있다.임시 테이블을
사용했다면 그 사용이 끝났을 경우 꼭 임시 테이블을 삭제한다. 시스템 리소스를 잡아
먹을수 있다.

7. HAVING 절 보단 WHERE 을 사용하라. HAVING 을 인덱스 스캔을 하고 where 는 인덱스
seek를 한다. HAVING을 사용한 경우에는 내부적으로 필터링 방식으로 변환해 주지만
인덱스 사용방식이 비효율적인 방식으로 남아 있기 때문에 where 절을 이용하는것이
더 좋다.

1. Data Fragmentation 정리 작업 이유 및 효과: Index Rebuild를 하게 되면 분산된 데이터를 모을 수 있습니다. 따라서, 적은 수의 IO로 동일한 데이터를 가져오게 됨에 따라 성능이 개선이 됩니다.

방법: 각 데이터베이스내에서 테이블 혹은 개별적인 Index에 대하여 수행할 수 있으며 Index Rebuid란 Index를 생성하는 작업이므로 데이터 량에 따라 수행시간이 오래 걸릴수 있습니다. 또한 Index의 종류에 따라서 Select만 허용하거나 모든 요청을 거부할 수 있으므로 사용자가 Access하지 않는 시간에 수행을 하시기 바랍니다.

ex 1. 테이블에 있는 모든 Index를 Rebuild하고자 하는 경우

DBCC DBREINDEX(table 명)

ex 2. 테이블에 있는 특정 Index를 Rebuild 하고자 하는 경우

DBCC DBREINDEX(table 명, index 명)

수행 주기: DBCC Showcontig를 수행하였을 때 Scan Density가 100%로 표시되면 데이터가 연속적으로 저장되었다는 의미이므로 이 수치가 낮을 경우 성능이 저하가 됩니다. 따라서, 이 문장을 수시로 수행하여서 해당 값이 낮을 경우 수행합니다.

2. Data Consistency Check 작업 이유 및 효과: SQL Server가 자체적으로 데이터 관리를 하기는 가끔 데이터의 일관성에 문제가 발생할 수 있습니다. 이러한 문제가 발생하면 성능이 저하되거나 timeout 오류가 발생하게 되므로 정기적으로 일관성 체크를 해야 됩니다.DBCC (Database Consistency Checker)를 사용하여, Database와 Object의 integrity를 monitoring 하여 오류를 점검합니다. DBCC를 수행하지 않고 DB backup을 받게 되면, 문제가 발생한 (예: table corrupt, …) 데이터를 그대로 backup 받을 수 있는 위험성을 내포하고 있고, 제대로 backup이 되지 않아서 문제 발생시에 복구가 불가능해 질 수 있습니다.

방법: 각 데이터베이스에 대해 수행하며, 데이터베이스 전체 혹은 테이블 단위로 수행할 수있습니다.

ex 1. 임의의 데이터베이스 전체에 대하여 수행하고자 하는 경우

DBCC CHECKDB(db 명)

ex2. 임의의 데이터베이스 내의 한 테이블에 대하여 수행하고자 하는 경우

DBCC CHECKTABLE( table 명)

위 수행 결과를 반드시 확인하시어 오류가 발생할 경우 오류 메시지에 지시된 대로 작업을 하시기 바랍니다.

또는 마이크로소프트 고객기술지원부로 연락 주시기 바랍니다.

수행 주기: 위 DBCC 작업은 최소한 주 1회 수행을 해주시기 바랍니다

3. SQL Server Errorlog 내용 확인

- 매일 errorlog file의 내용을 확인하여, 문제를 조기에 발견하고 조치를 취해야 합니다.

- Errorlog file 위치 : Microsoft SQL Server 가 설치되어 있는 위치가 C:\MSSQL 이라고 가정하면 C:\MSSQL\LOG 의 ERRORLOG 라는 이름의 파일이 존재하게 됩니다. - ERRORLOG file 은 마지막으로 SQL Server가 start된 이후부터 지금까지의 errorlog내용을 가지고 있으며, 이외에 ERRORLOG.1, ERRORLOG.2, ... 등의 파일이 존재하는데, 이 파일들은 그 이전의 errorlog file들을 history관리를 위해 backup해 두는 파일입니다. - 문제 발생시에 문제발생시점 및 원인을 정확하게 판단하는데 있어서, 이전의 errorlog file이 필요한 경우도 있으므로 errorlog.* 파일들도 가능한 한 삭제하지 말고 유지하시기 바랍니다.

- Errorlog file에는 심각한 error, SQL Server관리와 관련되는 작업에 대한 내용 등이 기록되므로, 가능한 한 자주 Monitoring해야 하며 일반적인 Database 문제 발생시에도 일차적으로 errorlog 를 확인하는 것이 도움이 됩니다.

- ERRORLOG file에는 Error에 대한 Error Number, Severity Level, Error Message 등이 기록됩니다

4. 계획적인 Database Full Backup 및 Transaction Backup

DB 에 문제가 발생하는 경우에 복구가 가능하도록, backup을 받아야 합니다.

- 주기적으로 아래의 데이터베이스들을 backup해야 합니다.

Master database, Msdb database, 모든 user database들, Distribution database

- Database backup 전략을 수립하여, scripts를 작성하고, SQL Server 7.0 에서 제공되는 "Scheduled Tasks" 기능을 이용하여 주기적으로 수행되도록 작업할 수 있습니다.

- Backup 방법은 아래 두가지로 나누어 집니다.

1) Database 전체를 backup 하는 작업 (Full Backup)

Data 와 Transaction log를 모두 backup 받습니다.

이 방법은 Transaction log를 삭제하지는 않기 때문에, 추가적으로 Database backup 하기 전에 Transaction log를 삭제하는 작업을 수행시켜 주는 것이 필요.

2) Transaction log 만 backup 하는 작업 (Transaction Log Backup)

Transaction log를 backup받고 삭제해 줍니다.

- 데이터베이스를 사용자들이 사용하는 동안에도 backup이 가능합니다.

Backup을 수행하면 SQL Server가 다소 느려질 수 있으므로, SQL Server를 많이 사용하지 않을 때 작업하시는 것이 좋습니다.

- Backup 에 대한 책임은 기본적으로 SA 와 Database Owner 에게 부여되며, Database Owner가 다른 사용자(user)에게 권한을 부여할 수도 있다.

- <주의> 다른 Character set과 Sort order를 사용하는 SQL Server에서 backup(dump database) 받은 데이터베이스는 Restore (Load Database)할 수 없습니다.

- 정기적으로 데이터베이스전체를 backup 받습니다.

backup database to

는 Disk, Tape 모두 가능합니다.

- Transaction Log full이 발생하지 않도록 짧은 기간 단위로 Log를 backup 받습니다.

(매우 중요합니다. 소홀히 하면 Log가 가득 차게 되어 그 데이터베이스에 대해서는 데이터에 대한변경작업을 할 수가 없게 됩니다.)

backup transaction to

- DB 의 Log 가 full 된 경우에는, 다음과 같이 Log 를 비워 줍니다.

backup transaction with truncate_only

위 작업 수행후에도 Log 가 회수되지 않으면, 아래와 같이 수행해 줍니다.

backup transaction with no_log

- Master DB 및 주요 System Table backup

- master database 도 주기적으로 backup (dump database)을 받아야 하며, 특히, master database 에 변경이 발생하는 작업 (create database, sp_addlogin, sp_addserver, ...)을 수행한 후에는 즉시 Backup을 받아야 합니다.

<예> backup database master to disk='C:\MSSQL\BACKUP\master_0820.bak'

6. 정기적인 Bulk-copy로 SAM File 확보

- 모든 DB, Obj- master database의 주요 system table들에 대해서는 주기적으로 bcp out을 받습니다. (sysdatabases, sysdevices, sysusages table은 데이터베이스들에 대한 정보를 관리하므로 특히 주의해서 변동이 발생할 때마다 bcp out 을 받아 두시는 것이 좋습니다.) 명령프롬프트에서 bcp Utility를 사용하여 파일로 받으시면 됩니다.

Bcp master.. out -Usa -P -S -c

<예> C:\MSSQL\BACKUP> mkdir bcpout C:\MSSQL\BACKUP> cd bcpout C:\MSSQL\BACKUP\bcpout> bcp master..sysdatabases out sysdatabases.csv -Usa -P -c

C:\MSSQL\BACKUP\bcpout> bcp master..sysusages out sysusages.csv -Usa -P -c C:\MSSQL\BACKUP\bcpout> bcp master..sysdevices out sysdevices.csv -Usa -P -c - 이 작업을 batch file (*.bat) 로 작성해서 필요할 때 그 batch file을 수행하면 편리합니다.

- 주요 데이터를 저장하고 있는 Table인 경우에는, bcp 를 사용하여 주기적으로 backup 을 받아 두는 것이 좋습니다.

7. 모든 DB, Objects에 대한 Scripts 유지, 관리

- 모든 데이터베이스구축과정에서 작성한 모든 Scripts들에 대하여 최신Version을 관리합니다.

- 주기적으로 DB관리자는 SQL Enterprise Manager 를 사용하여 Scripts 들을 파일로 받아서 관리합니다.

- SQL Enterprise Manager 에서 SQL Server 를 선택하고 Databases에서 데이터베이스를 선택한 다음 Object 메뉴를 선택하고 Generate SQL Scripts 를 선택하면 Scripts를 파일로 받을 수 있습니다.

8. DB free space 확인

- 주기적으로 데이터베이스별로 free space를 확인해서 여유공간이 부족한 경우에는, 미리 영역을 확보해줍니다.

- 데이터영역에 free space가 없는 경우 : 데이터베이스의 Data영역을 확장하거나 불필요한 데이터를 삭제하는 작업을 통해 free space를 확보합니다.

(SQL Enterprise Manager 또는 ALTER DATABASE command를 사용함.) Alter database on =

- Log영역에 free space가 없는 경우 : Transaction Log 정보를 backup 받거나 Transaction Log 를 삭제합니다.

backup tran to 또는 backup tran with truncate_only 또는 backup tran with no_log go

-- 다중 볼륨 백업
backup database test to disk='d:\north.bak' , disk= 'd:\north2.bak'

-- format missing
backup database test to disk='d:\north.bak' , disk= 'd:\north2.bak' , disk= 'd:\north3.bak'

-- format 지정
backup database test to disk='d:\north.bak' , disk= 'd:\north2.bak' , disk= 'd:\north3.bak' with format