• 아티클

SQL Q&A데이터베이스 크기 증가, IFilter 사용 및 원격 연결

편집자: Matthew Graven

팁: 캐시 지우기

테스트 환경에서 문제 없이 작동하던 저장 프로시저를 프로덕션 SQL Server에 배포하면 성능이 저하되는 이유가 궁금했던 적이 있습니까? 이는 캐시와 관련된 문제일 수 있습니다. 저장 프로시저를 프로덕션 환경에 배포하기 전에 캐시된 쿼리 계획을 지운 후 테스트 환경에서 프로시저를 테스트하면 "콜드" 캐시 환경에서 저장 프로시저가 어떻게 실행되는지 알 수 있습니다. 다음은 유용하게 사용할 수 있는 몇 가지 방법입니다.

SQL Server의 프로시저 캐시를 지우려면

DBCC FREEPROCCACHE
Go

캐시된 모든 계획을 나열하도록 쿼리하려면

Select * from sys.dm_exec_cached_plans
Go

Q: 주간에 사용률이 매우 높은 데이터베이스를 보유하고 있습니다. SQL Server®에서 데이터베이스 사용률이 높은 주간에 자동 증가를 실행할 경우 제한 시간이 초과될 수 있으므로 이 기능을 사용하고 싶지 않습니다. 사용된 공간에 대한 특정 비율만큼 데이터베이스 파일을 확장하는 정기적인 작업을 실행하고 싶은데, 어떻게 해야 합니까?

A: 파일 증가는 상당한 디스크 I/O를 야기하는 작업이며 SQL Server에서 데이터 또는 로그 파일이 확장될 때까지 대기해야 하는 경우에는 성능 저하 및 응답 시간 지연 현상이 발생할 수 있습니다. 기본 증가치는 데이터 파일의 경우 1MB이고 로그 파일의 경우 10%인데 사용량이 많은 시스템에서 이러한 증가치는 부족할 수도 있습니다. 뿐만 아니라 데이터나 로그 파일은 디스크상에서 연속적으로 배치되어 있지 않으므로 자동 증가 기능을 사용할 경우 디스크 조각화가 일어날 수 있습니다. 즉, 데이터가 디스크에서 물리적으로 분산되어 있으므로 응답 시간이 불필요하게 길어질 수 있습니다.

우수한 성능을 얻기 위해서는 데이터와 로그 파일에 필요한 충분한 파일 공간을 사전에 할당하는 것이 매우 중요합니다. 이렇게 하려면 대개 추세 분석과 증가 예측 작업이 필요합니다. 이를 바탕으로 파일 공간을 사전에 할당하면 파일이 디스크에서 연속적으로 배치되고 사용률이 가장 높은 시간대에 자동 증가 실행에 따르는 I/O 비용이 들지 않으므로 성능이 향상됩니다. 데이터 파일이나 로그 파일이 완전히 꽉 차면 데이터베이스에 액세스할 수 없으므로 자동 증가 기능은 일반적으로 활성화된 상태로 두어야 합니다. 그러나 자동 증가는 데이터베이스 관리를 위한 기능이라기보다는 안전 조치 정도로 간주하는 것이 좋습니다.

정기적 데이터베이스 파일 확장 예약도 하지 말아야 합니다. 이것도 디스크상의 파일 불연속을 야기하여 성능 저하를 가져올 수 있습니다. SQL 에이전트 작업을 통해 스크립트를 정기적으로 실행하여 각 데이터베이스의 사용 가능한 공간 비율을 확인한 다음 데이터베이스 메일을 통해 전자 메일 알림을 보내는 등의 조치를 취함으로써 사전 모니터링을 실현할 수 있습니다. 그림의 코드는 현재 데이터베이스의 사용 가능한 공간 비율을 수집하는 방법을 보여 주는 샘플 스크립트를 제공합니다.

알림이 생성되면 ALTER DATABASE 명령을 사용하여 1회용 파일 증가 스크립트를 작성한 후 SQL 에이전트 작업을 사용하여 이 작업이 사용량이 많지 않은 시간대에 수행되도록 예약할 수 있습니다. 당분간 확장이 필요하지 않을 만큼 충분히 파일 크기를 증가시켜 파일 확장이 작은 단위로 발생하지 않도록 해야 합니다. 모든 데이터베이스에서 자동 축소 기능이 활성화되어 있지 않도록 하는 것도 좋습니다. 이 기능이 활성화되어 있으면 데이터베이스 축소 또는 증가 주기가 불필요하게 발생할 수 있습니다.

—Justin Langford

데이터베이스의 사용 가능한 공간 확인

-- 현재 사용자 데이터베이스의 
-- 크기, 사용 가능한 공간을 수집하고 
-- 사용 가능한 공간 비율을 계산하는 스크립트
DECLARE @size DEC(15,2)
DECLARE @free DEC(15,2)
DECLARE @result DEC(15,2)

SELECT @size = SUM(size)*1.0/128
FROM sys.database_files

SELECT @free = 
(SUM(unallocated_extent_page_count)*1.0/128)
FROM sys.dm_db_file_space_usage

PRINT 'DB Size ' + CONVERT(VARCHAR(15), @size)
PRINT 'Free Space ' + 
CONVERT(VARCHAR(15), @free)

SELECT @result = (@free/@size)*100

PRINT '% Free Space ' + 
CONVERT(VARCHAR(15), @result)

Q: 우리 회사에서는 varbinary 및 image 열을 사용하여 회사 데이터베이스 내에 다양한 형식의 파일을 저장하고 있습니다. 이러한 다양한 파일 형식을 검색할 수 있는 기능이 SQL Server에 통합되었다고 들었습니다. 이 기능을 사용하려면 SQL Server를 어떻게 구성해야 합니까?

A: 이 기능은 전체 텍스트 인덱싱 서비스에 기본으로 제공됩니다. 이 서비스에서는 IFilter 인터페이스를 사용할 수 있으므로 독점적인 데이터에서 유용한 정보를 추출할 수 있는 필터를 개발하여 로드할 수 있습니다. 이 IFilter는 크롤링된 파일에 대한 정보를 수집하기 위해 Microsoft® Office SharePoint® Server와 같은 다른 제품에도 사용됩니다.

IFilter는 해당 파일 형식의 작성자 또는 타사 공급업체에서 제공합니다. SQL Server에는 HTML 및 DOC 파일용 필터를 비롯하여 FTS(FulltextService)가 설치될 때 로드되는 몇 가지 IFilter가 이미 포함되어 있습니다. 필요에 따라 다른 IFilter를 추가할 수 있습니다. 예를 들어 Adobe 웹 사이트에서 Adobe PDF용 필터를 얻을 수 있으며 2007년 말에 게시된 2007 Office system 확장 기능용 새 필터 팩을 사용할 수도 있습니다. 필요한 IFilter 버전을 알고 있어야 합니다. 예를 들어 32비트 시스템용 IFilter는 64비트 버전의 SQL Server에서 작동하지 않습니다.

클라이언트에서 설치 패키지를 실행하면 일반적으로 OS 환경에서 IFilter가 등록됩니다. OS에 IFilter가 등록되면 FTS에서 필터를 로드할 수 있도록 몇 가지 단계를 수행해야 합니다. 쿼리 실행 도구를 시작한 후 다음 명령을 실행합니다.

  • sp_fulltext_service 'load_os_resources',1. 이 문은 FTS에서 단어 분리기 및 형태소 분석기와 같은 구성 요소를 비롯하여 처리를 위해 등록된 IFilter를 로드할 수 있도록 합니다.
  • sp_fulltext_service 'verify_signature',0. 표준에 따라 필터에 서명해야 하는데 대부분의 공급업체는 그렇게 하고 있지 않기 때문에, 이 문은 SQL Server에서 사용된 필터의 서명 여부를 확인하는 절차를 생략합니다.
  • SQL Server 인스턴스와 FTS 인스턴스를 다시 시작합니다.
  • SQL Server에서 내용을 리디렉션할 필터를 선택할 수 있도록 IFilter가 크롤링할 내용이 포함된 이진 열과 확장명 열(DOCX와 같은 확장명 유형이 있는 열)에 대해 전체 텍스트 인덱스를 만듭니다.

자세한 내용은 go.microsoft.com/?linkid=7912971을 참조하십시오.

—Jens Suessmeyer

Q: 원격 SQL Server에 연결할 수 없습니다. 클라이언트나 서버 컴퓨터에 방화벽을 구성해야 합니까?

A: SQL Server 2005에 대한 원격 연결은 여러 가지 이유로 실패할 수 있는데, 가장 일반적인 문제 중 하나로 방화벽 구성이 있습니다. SQL Protocols 블로그(blogs.msdn.com/sql_protocols)에서 SQL 연결과 관련된 많은 리소스를 찾아볼 수 있습니다.

SQL Server 2005의 기본 설치에서는 원격 연결이 허용되지 않습니다. 원격 연결을 사용하려면 SQL을 실행하는 컴퓨터에서 시작 메뉴를 클릭하고 Microsoft SQL Server 2005 | 구성 도구 | SQL Server 노출 영역 구성을 차례로 선택합니다. 서비스 및 연결에 대한 노출 영역 구성으로 이동한 다음 원격 연결을 선택하고 "TCP/IP 및 명명된 파이프 모두 사용" 라디오 단추를 선택합니다. 그런 다음 SQL을 다시 시작하면 변경 사항이 적용됩니다.

기본적으로 SQL Server에서는 포트 1433을 사용합니다. 이 포트가 열려 있는지 확인하려면 다음 텔넷 명령을 사용합니다. 이 명령에서 <ipaddress>를 SQL Server를 실행하는 컴퓨터의 실제 IP 주소로 바꿉니다.

telnet <ipaddress> 1433

연결이 실패했다는 응답이 표시되면 Windows® 방화벽을 열고 예외 탭으로 이동한 다음 포트 추가를 선택하고 TCP 포트 1433을 추가합니다. 그러면 텔넷 연결에 성공합니다. 텔넷은 Windows Vista®에 기본적으로 설치되어 있지 않습니다.

—Rick Anderson

이 칼럼을 기고하는 데 도움을 주신 SQL Server 전문가:
Justin Langford는 영국에 본사를 둔 시스템 통합 전문 기업이자 Microsoft 인증 파트너인 Coeo Ltd.에서 근무하고 있습니다. Jens Suessmeyer는 독일 Microsoft에서 데이터베이스 컨설턴트로 근무하고 있습니다. Rick Anderson은 Microsoft의 개발자 교육 팀에서 근무하고 있습니다. Saleem Hakani는 Microsoft의 수석 데이터베이스 엔지니어이자 SQL Server 커뮤니티 운영자입니다.

© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..