SQL Server 2005 서버 구성 옵션 설정

SQL Server Management Studio 또는 sp_configure 시스템 저장 프로시저를 사용하면 구성 옵션을 통해 SQL Server 리소스를 관리하고 최적화할 수 있습니다. 이들 옵션 중 일부는 SQL Server 노출 영역 구성 도구를 사용하여 구성할 수 있습니다. 자주 사용하는 서버 구성 옵션은 SQL Server Management Studio를 통해 사용할 수 있으며 모든 구성 옵션에 액세스하려면 sp_configure를 사용해야 합니다. 이러한 옵션을 변경하기 전에 시스템에 주는 영향을 신중히 고려해야 합니다.

중요:
고급 옵션은 숙련된 데이터베이스 관리자나 인증된 SQL Server 기술 지원 담당자만이 변경하도록 해야 합니다.

sp_configure 시스템 저장 프로시저 사용

sp_configure를 사용할 때는 구성 옵션을 설정한 뒤에 RECONFIGURE 또는 RECONFIGURE WITH OVERRIDE를 실행해야 합니다. RECONFIGURE WITH OVERRIDE 문은 각별한 주의가 필요한 구성 옵션에 주로 사용되지만 모든 구성 옵션에 사용할 수 있으며 RECONFIGURE 대신 사용할 수 있습니다.

참고:
RECONFIGURE는 트랜잭션 내에서 실행됩니다. 다시 구성 작업 중 하나가 실패하면 다시 구성 작업이 하나도 적용되지 않습니다.

다음 문을 사용하여 각 옵션의 값을 결정할 수 있습니다.

SELECT * FROM sys.configurations ORDER BY name ; GO

다음은 기본값으로 설정된 fill factor 옵션의 값을 sp_configure 설정을 통해 100으로 변경하는 스크립트의 예입니다.

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'fill factor', 100; GO RECONFIGURE; GO

구성 옵션 범주

구성 옵션은 다음 경우에 적용됩니다.

• 옵션 설정 및 RECONFIGURE(또는 경우에 따라 RECONFIGURE WITH OVERRIDE) 문 실행 후 즉시
  
  -또는-
  
  
• 위의 작업을 수행하고 SQL Server 인스턴스를 다시 시작한 후
  
  

sp_configure로 고급 옵션을 구성하려면 sp_configure 명령에서 'show advanced options' 옵션을 1로 설정한 다음 RECONFIGURE를 실행해야 합니다.

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'cursor threshold', 0; GO RECONFIGURE; GO

앞의 예에서 cursor threshold 옵션은 다시 구성하는 즉시 적용됩니다. cursor threshold의 새 값은 구성 옵션인 value_in_use 열 및 value 열에 표시됩니다.

SQL Server 인스턴스를 다시 시작해야 하는 옵션은 초기에 value 열에만 변경된 값이 표시되며 다시 시작한 뒤에는 value 열과 value_in_use 열 모두에 새 값이 표시됩니다.

일부 옵션은 서버를 다시 시작해야 새 구성 값이 적용됩니다. 새 값을 설정하고 sp_configure를 실행해도 서버를 다시 시작하지 않으면 구성 옵션 value 열에만 새 값이 나타나고 value_in_use 열에는 나타나지 않습니다. 서버를 다시 시작하면 value_in_use 열에도 새 값이 나타납니다.

자체 구성 옵션은 SQL Server가 시스템의 필요에 따라 조정하는 옵션입니다. 대부분의 경우 이 값을 수동으로 설정할 필요가 없습니다. 자체 구성 옵션의 예로는 min server memory, max server memory 및 user connections이 있습니다.

구성 옵션 표

다음 표에서는 사용할 수 있는 모든 구성 옵션, 가능한 설정 범위 및 기본값을 보여 줍니다. 구성 옵션은 다음과 같은 문자 코드로 표시됩니다.

• A= 고급 옵션이며 숙련된 데이터베이스 관리자나 인증된 SQL Server 기술 지원 담당자를 위한 옵션입니다. show advanced options를 1로 설정해야 이용할 수 있습니다.
  
  
• RR = 데이터베이스 엔진을 다시 시작해야 하는 옵션입니다.
  
  
• SC = 자체 구성 옵션입니다.
  
  

  구성 옵션	최소값	최대값	기본값
  Ad Hoc Distributed Queries(A)	0	1	0
  affinity I/O mask(A, RR)	-2147483648	2147483647	0
  affinity64 I/O mask(A, SQL Server 64비트 버전에만 해당)	-2147483648	2147483647	0
  affinity mask(A)	-2147483648	2147483647	0
  affinity64 mask(A, SQL Server 64비트 버전에만 해당)	-2147483648	2147483647	0
  Agent XPs(A)	0	1	0 SQL Server 에이전트를 시작하면 1로 변경됩니다. 설치 중에 SQL Server 에이전트가 자동으로 시작되도록 설정하면 1이 됩니다.
  allow updates(구식. 사용할 수 없으며 사용할 경우 다시 구성하는 동안 오류 발생)	0	1	0
  awe enabled(A, RR)	0	1	0
  blocked process threshold(A)	0	86400	0
  c2 audit mode(A, RR)	0	1	0
  clr enabled	0	1	0
  common criteria compliance enabled(A, RR)	0	1	0
  cost threshold for parallelism(A)	0	32767	5
  cross db ownership chaining	0	1	0
  cursor threshold(A)	-1	2147483647	-1
  Database Mail XPs(A)	0	1	0
  default full-text language(A)	0	2147483647	1033
  default language	0	9999	0
  default trace enabled(A)	0	1	1
  disallow results from triggers(A)	0	1	0
  fill factor(A, RR)	0	100	0
  ft crawl bandwidth(max), ft crawl bandwidth(A) 참조	0	32767	100
  ft crawl bandwidth(min), ft crawl bandwidth(A) 참조	0	32767	0
  ft notify bandwidth(max), ft notify bandwidth(A) 참조	0	32767	100
  ft notify bandwidth(min), ft notify bandwidth(A) 참조	0	32767	0
  index create memory(A, SC)	704	2147483647	0
  in-doubt xact resolution(A)	0	2	0
  lightweight pooling(A, RR)	0	1	0
  locks(A, RR, SC)	5000	2147483647	0
  max degree of parallelism(A)	0	64	0
  max full-text crawl range(A)	0	256	4
  max server memory(A, SC)	16	2147483647	2147483647
  max text repl size	0	2147483647	65536
  max worker threads(A, RR)	128	32767 32비트 SQL Server에는 최대 1024, 64비트 SQL Server에는 최대 2048을 설정하는 것이 좋습니다.	0 값 0을 선택하면 32비트 SQL Server의 경우 (256+(<processors> -4) * 8) 수식을 사용하고 64비트 SQL Server의 경우 이 값의 두 배를 사용하여 프로세서 수에 따라 max worker threads의 수가 자동으로 구성됩니다.
  media retention(A, RR)	0	365	0
  min memory per query(A)	512	2147483647	1024
  min server memory(A, SC)	0	2147483647	8
  nested triggers	0	1	1
  network packet size(A)	512	32767	4096
  Ole Automation Procedures(A)	0	1	0
  open objects(A, RR, 구식)	0	2147483647	0
  PH_timeout(A)	1	3600	60
  precompute rank(A)	0	1	0
  priority boost(A, RR)	0	1	0
  query governor cost limit(A)	0	2147483647	0
  query wait(A)	-1	2147483647	-1
  recovery interval(A, SC)	0	32767	0
  remote access(RR)	0	1	1
  remote admin connections	0	1	0
  remote login timeout	0	2147483647	20
  remote proc trans	0	1	0
  remote query timeout	0	2147483647	600
  Replication XPs 옵션(A)	0	1	0
  scan for startup procs(A, RR)	0	1	0
  server trigger recursion	0	1	1
  set working set size(A, RR, 구식)	0	1	0
  show advanced options	0	1	0
  SMO and DMO XPs(A)	0	1	1
  SQL Mail XPs(A)	0	1	0
  transform noise words(A)	0	1	0
  two digit year cutoff(A)	1753	9999	2049
  user connections(A, RR, SC)	0	32767	0
  User Instance Timeout(A, SQL Server 2005 Express Edition에서만 나타남)	5	65535	60
  user instances enabled(A, SQL Server 2005 Express Edition에서만 나타남)	0	1	0
  user options	0	32767	0
  Web Assistant Procedures(A)	0	1	0
  xp_cmdshell(A)	0	1	0