관리자를 위한 튜닝 가이드 - SQL인스턴스 환경 설정

SQL Server for Developer: 관리자를 위한 튜닝 가이드

SQL인스턴스 환경 설정

차주언 | SQL 컨설턴트

SQL인스턴스 환경 설정

다른 SQL인스턴스 설정값들은 상황에 따라 특수하게 공부를 해야하겠지만 아래의 3가지에 비에 그 중요도가 무척이나 떨 어집니다 왜냐하면 SQL이 거의 자동으로 최적값에 세팅되어 있기 때문입니다.

번호	수칙	체크
1	서비스 이상 발생시 자동 시작을 설정해두었는가?
2	고정 메모리를 사용하도록 해두었는가?
3	SET OPTION을 사용하는가?

수칙1. 서비스 이상 발생시 자동시작을 설정해두었는가?

첫번째는 시스템 가동시 항상 서비스는 시작되어야 한다는 것이고 두번째는 문제가 생겨서 갑자기 작동을 멈추면 알아서 다시 시작되어야 한다는 것입니다.

[따라하기]
SQL Server및 Agent 서비스 자동시작 모드로 설정하기
1.자동시작 설정을 해보겠습니다.[서비스 관리자]를 선택한 후 다음의 [자동 시작]항목을 확인합니다.

2.그 다음은 문제 발생시 자동시작 설정입니다. SQL서버 엔터프라이즈 관리자에서 SQL Server 에이전트 서비스에서 [등록정보]를 선택한 후 [고급]텝을 선택한 후 다음을 체크합니다.

[01-03 자동으로 다시 시작]

3.위와 같이 적용해둔 후 SQL서버 서비스 및 에이전트 서비스를 재시작 합니다.

수칙2. 고정 메모리를 사용하도록 해두었는가?

SQL 서버는 필요할 때 메모리를 필요한 만큼 가져갔다가 다른 응용프로그램이 가져가게 할 수 도 있으며, 또한 SQL 서버 전용으로 메모리를 아예 고정하는 방법도 있습니다. 상식적으로 생각해보아도 전용으로 사용하는 것이 SQL 서버를 위해 효과적인 것을 알 수 있습니다.

[따라하기]
SQL 서버는 오직 데이터베이스 서비스만을 위해 사용하는 것이 좋다. 운영체제가 사용할 약간량만을 제외한 나머지를 SQL 서버 서비스만을 위해 강제 할당 하는 것이 가장 효율적입니다.

1.엔터프라이즈 관리자를 실행하여 메모리를 설정하고자 하는 서버를 선택합니다.

[02-01 엔터프라이즈 관리자 실행]

2.서버 노드를 선택한후 등록 정보를 실행합니다. 실행후 메모리텝을 선택합니다. 기본으로 [SQL Server 메모리를 동적으로 구성]에 체크되어있습니다.

[02-02 메모리텝(기본은 동적으로 구성)]

3.운영체제를 위한 적당량(200메가) 를 남겨두고 나머지는 SQL 서버를 위해 할당합니다. [고정된 메모리] 체크박스를 선택하고 [SQL 서버용으로 실제 메모리 예약]도 선택합니다. 이렇게 세팅해야만 다른 응용프로그램이 SQL 서비스에게서 메모리를 빼앗아가지 못합니다.

[02-03 고정된 메모리 크기 사용 및 실제메모리 예약]

4.설정이 끝나면 역시 서비스를 재시작해야합니다.

[02-04 서비스 재시작]

수칙 3. SET OPTION을 사용하는가?

가. SET NOCOUNT ON
나. SET ANSI_NULLS ON
다. SET QUOTED_IDENTIFIER ON
라. SET LOCKTIMEOUT 값 지정(디폴트 -1 : 무한대)

SET NOCOUNT ON 이란? 메시지 창에 ~행이 적용되었습니다 하고 나타나는 시스템 메시지 입니다. 쿼리수가 많아지면 이런 메시지는 네트워크상에 쓰레기가 되니 아예 발생하지 않도록 하는 것이 시스템에 유리합니다.
SET ANSI_NULLS ON이란? SQL-92 표준에서는 NULL 값에 대한 Equals(=) 또는 Not Equal(<>) 비교의 결과가 검색되지 않아야 합니다. 쿼리분석기에서 도움말 SET ANSI_NULLS ON를 검색한 후 예제를 따라해보십시오.
SET QUOTED_IDENTIFIER ON을 설정하면 SET QUOTED_IDENTIFIER 옵션을 ON으로 설정하면 식별자를 큰따옴표로 구분할 수 있으며, 리터럴은 작은따옴표로 구분해야 합니다.
SET LOCKTIMEOUT으로 잠금이 해제될 때가지 명령문이 기다려야 할 시간을 밀리초 단위로 지정할 수 있습니다. -1은 초기상태 무한대기 입니다.
다음의 따라하기를 참조로 위의 4가지 설정을 서버차원에서 하도록 합니다.

[따라하기 - SET NOCUNT ON의 의미실험]
1.쿼리분석기에서 다음의 쿼리를 실행합니다.

USE pubs
GO

SELECT * FROM titles

2.결과창에서 메시지 탭을 선택합니다.

3.메시지 탭에 (18개 행 적용됨) 이 보입니다. 이런 메세지도 수가 많아지면 부하가 발생합니다.

4.쿼리에서 SET NOCOUNT ON 실행 후 다시해봅니다.(일반적으로 모든 저장 프로시저의 첫번째 문장은 SET NOCOUNT ON 문이 되어야 합니다.)

5.훌륭하게 적용되었으나, SET문은 단지 지금 세션에만 적용되는 것 입니다. 서버차원에서 다시 설정할 필요가 있습니다.

6.훌륭하게 적용 되었습니다. 다른 것도 실험해 봅시다.

제공 : DB포탈사이트 DBguide.net

출처명 : 한국마이크로소프트(주)

[출처] 관리자를 위한 튜닝 가이드 - SQL인스턴스 환경 설정|작성자 필립박