SQL Q&A잠금 검색, 대용량 쿼리, I/O 통계 및 기타

편집자: Nancy Michell

이 기사의 코드 다운로드: SQLQandA2007_08.exe (151KB)

Q: 모든 데이터베이스에서 잠금이 설정된 개체 이름을 확인해야 합니다. 어떻게 해야 합니까?

A: SQL Server™ 2000의 경우 아래와 같이 master에서 syslocks 시스템 테이블을 쿼리하거나 sp_lock을 실행하여 현재 잠금 정보를 가져올 수 있습니다.

SELECT * FROM master..syslocks
EXEC sp_lock

그러나 sp_lock 저장 프로시저의 OUTPUT에 있는 ObjID(또는 master..syslocks의 id 열)에서 실제 개체인 NAMES를 변환해야 할 경우가 있습니다.

SQL Server 2005 SP1 및 이전 버전에서는 OBJECT_NAME 함수를 사용하여 object_id 매개 변수 한 개만 전달할 수 있습니다. 따라서 개체 이름을 제대로 가져오려면 OBJECT_NAME을 실행하기 전에 현재 데이터베이스에서 작업을 수행해야 합니다. 이렇게 하면 정확한 OBJECT_NAME을 가져오기 위해 각 데이터베이스마다 루프를 실행하는 사용자 지정 코드를 작성해야 하기 때문에 현재 잠금을 모니터링하기가 어려워집니다.

USE DBNAME  
SELECT OBJECT_NAME(object_id)

SQL Server 2005 SP2에서는 두 번째 매개 변수 database_id를 추가하여 이런 문제점을 개선했습니다. 이 새로운 매개 변수를 사용하면 현재 연결된 데이터베이스에 상관없이 개체 이름을 쿼리할 수 있습니다.

OBJECT_NAME ( object_id [, database_id ] )

이제 다음과 같이 sys.dm_tran_locks를 쿼리하고 각 데이터베이스의 개체 이름을 검색할 수 있습니다.

SELECT
DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id, resource_database_id)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'

단, 이 매개 변수는 SQL Server 2005 SP2 및 이후 버전에만 적용됩니다. SQL Server 2005 이전 버전에서 이 기능을 실행하면 다음과 같은 오류 메시지가 나타납니다.

Msg 174, Level 15, State 1, Line 1
The object_name function requires 1 argument(s).

그림 1에 나온 다른 예는 sys.dm_exec_sessions를 결합하여 잠금과 연관된 서비스 프로세스 ID(SPIDS)에 관한 정보를 가져옵니다. 자세한 내용은 SQL Server 온라인 설명서(technet.microsoft.com/library/ms130214(sql.90).aspx)를 참조하십시오.

Figure 1 서버 프로세스 ID 검색

SELECT
DB_NAME(resource_database_id) as DBName,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS ObjectName,
request_mode,
request_type,
request_session_id,
es.host_name,
es.login_name,
es.login_time
FROM
sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es
ON tl.request_session_id=es.session_id
WHERE resource_type='OBJECT'

Q: 대용량 동적 SQL 쿼리가 가끔 NVARCHAR(max) 길이를 초과하는 것 같습니다. 이 문제를 피해 하나의 큰 문자열을 실행할 수 있는 방법이 있습니까?

A: 쿼리가 NVARCHAR(max) 길이를 초과한다면 2GB 쿼리인 것입니다. 연결된 모든 문자열을 NVARCHAR(max)로 변환해야 할 수도 있습니다. 그러나 SQL Server 2005 이전 버전에서도 효과적으로 사용할 수 있는 보다 편리한 방법은 여러 개의 작은 문자열을 함께 연결하는 것입니다. 예를 들면 다음과 같습니다.

DECLARE @q1 NVARCHAR(4000), @q2 NVARCHAR(4000), @q3 NVARCHAR(4000)
SET @q1 = 'SELECT...'
SET @q2 = 'FROM...'
SET @q3 = 'WHERE...'
EXEC (@q1 + @q2 + @q3)

팁: OUTPUT 절 사용

이제 트리거를 사용하지 않고도 DML(데이터 조작 언어) 문을 사용하여 수행된 변경 내용을 감사할 수 있습니다. SQL Server 2005에서는 OUTPUT 절이 DML 문의 일부로 도입되어 모든 DML 작업에서 수행된 변경 내용을 추적하는 데 유용하게 사용될 수 있습니다. 이 OUTPUT 절은 결과 집합을 테이블 또는 테이블 변수에 저장할 수 있습니다.

이 기능은 INSERTED 및 DELETED 테이블에서 트리거가 했던 역할과 비슷합니다. 즉, DML 작업 도중 수정된 행을 액세스하는 것입니다. 이 기능이 어떻게 작동하는지 보기 위해 아래 그림과 같이 주소 테이블의 주소를 원래 값과 반대되는 값으로 변경해 보겠습니다. 이 코드에 표시된 논리를 사용하면 데이터의 모든 변경 내용을 추적하여 테이블에 저장할 수도 있습니다.

쿼리 결과는 다음과 같이 나타납니다.

Original Value:'1234 One Microsoft Way, Redmond, Wa.' has been changed to: '.aW,dnomdeR 
,yaW tfosorciM enO 4321'

주소 변경

--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--Insert data
Insert into Address Values (234,567,'1234 One Microsoft Way, Redmond, Wa.')


--Declare a table variable
Declare @Recordchanges table (change Varchar(255))

--Update the address
Update Supplier.Address
Set Address=reverse(address)

--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' 
into @RecordChanges

--Query the changes from table variable
Select * from @RecordChanges

Q: 현재 Windows Server® 2003에 업무에 중요한 SQL Server 2005 클러스터가 설치되어 있습니다. Microsoft® DTC(Distributed Transaction Coordinator)가 클러스터 쿼럼과 함께 동일한 그룹에 "클러스터"되었지만 전용 디스크 리소스를 사용합니다. 다시 말해 MS DTC가 클러스터 쿼럼 그룹과 동일한 네트워크 이름 및 IP 주소를 사용합니다. 이 구성을 Microsoft가 제공하는 최선의 방법에 따라 수정하려고 합니다. 따라서 MS DTC를 전용 클러스터 그룹으로 이동하는 것과 관련하여 조언이 필요합니다. Cluster Admin Tool을 사용하여 MS DTC 서비스를 제거한 후 전용 그룹에서 다시 생성하면 충분할까요?

A: MS DTC를 위한 전용 디스크 리소스가 이미 있기 때문에 리소스를 제거한 후 새 그룹에서 다시 생성하기만 하면 됩니다. 새 그룹에서는 네트워크 이름과 가상 IP 주소도 새로 만들어야 합니다.

또는 클러스터 그룹에서 새 네트워크 이름과 IP 주소를 생성하고 새 리소스에 대한 종속성을 변경할 수도 있습니다. 그런 다음 MS DTC를 새 그룹으로 끌어오면 전용 디스크와 새 리소스도 함께 옮겨집니다.

Q: 데이터베이스에서 물리적 데이터베이스 파일에 대한 I/O 통계를 확인해야 합니다. 어떻게 하면 됩니까?

A: SQL Server 2000 및 SQL Server 2005 모두에서 지원되는 시스템 함수 fn_virtualfilestats나 SQL Server 2005에서만 지원되는 sys.dm_io_virtual_file_stats 함수를 사용하면 됩니다. 이 함수는 SQL Server의 인스턴스가 마지막으로 시작된 이후 수집된 통계 정보를 반환합니다. 그림 2에 결과 예가 나와 있습니다.

Figure 2 데이터베이스의 I/O 통계 보기

기본 데이터 파일에 I/O가 미치는 영향을 제대로 이해하면 데이터 볼륨에 파일 및 파일 그룹을 물리적으로 배치하고, 가능한 I/O 병목 현상을 감지하고, 파일 수준의 데이터베이스 유지 관리 작업을 수행하는 등의 작업을 보다 효과적으로 계획할 수 있습니다. 이 함수는 파일과 파일 그룹이 많은 대용량 데이터베이스에서 I/O가 미치는 영향을 검사할 때 특히 유용합니다.

SQL Server 2000에 대한 파일 I/O 정보를 표시하는 쿼리는 다음과 같습니다.

SELECT *
FROM ::fn_virtualfilestats(default,default)
GO

특정 databaseID를 확인하려면 다음과 같이 해당 데이터베이스에 대한 ID를 전달해야 합니다.

SELECT *
FROM ::fn_virtualfilestats(7,default)
GO

서버의 모든 데이터베이스에 대한 파일 통계를 보여 주는 SQL Server 2005 코드는 다음과 같습니다.

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
GO

다음 쿼리는 현재 데이터베이스에 대한 파일 통계만 반환합니다.

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
WHERE DBID=db_id()
GO

SQL Server 2005의 경우 sys.dm_io_virtual_file_stats라는 새 시스템 함수도 있는데, 이 함수의 역할은 레거시 함수 fn_virtualfilestats를 교체하는 것입니다.

sys.dm_io_virtual_file_stats( 
{ database_id | NULL },
{ file_id | NULL }
)

이 함수를 사용하는 방법은 다음과 같습니다.

SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)

출력 결과를 사용하여 실제 데이터베이스 이름과 파일 이름을 보여 주는 보다 읽기 쉬운 보고서를 작성하려면 SQL Server 2000 또는 SQL Server 2005에서 사용할 수 있는 다운로드로 제공되는 코드를 사용하십시오. 이 코드는 TechNet Magazine웹 사이트에서 확인할 수 있습니다.

Q: 삭제 트랜잭션이 트리거를 발생시켰는지 쉽게 확인할 수 있는 방법을 알고 싶습니다. 이런 정보를 얻는 방법이 있을까요?

A: 삭제, 삽입 및 업데이트 작업을 처리하는 트리거를 다룰 때는 보통 여러 가지 기법을 사용하여 삭제 트랜잭션이 트리거를 발생시켰는지 확인합니다. 가장 많이 사용되는 방법은 삽입 및 삭제된 가상 테이블의 수를 비교하여 일치하는지 확인하는 것입니다. 그러나 이보다 간단한 방법이 있습니다. 바로 Columns_Updated 함수를 사용하는 것입니다.

삭제 트랜잭션이 트리거를 발생시키면 Columns_Updated는 항상 varbinary 값 0x를 반환합니다. 아래를 확인하면 삭제 트랜잭션이 트리거를 발생시켰음을 알 수 있습니다.

IF Columns_Updated() = 0x

팁: 데이터 및 로그 파일 초기화

이전에 삭제한 파일로부터 디스크에 남아 있는 기존 데이터를 덮어쓰기 위해 데이터 및 로그 파일이 초기화되는 것을 알고 계십니까? 데이터 및 로그 파일은 데이터베이스를 생성하거나, 기존 데이터베이스에 파일이나 로그 또는 데이터를 추가하거나, 기존 파일의 크기를 늘리거나(자동 증가 작업 포함), 데이터베이스 또는 파일 그룹을 복원할 때 파일을 0으로 채움으로써 처음 초기화됩니다. 파일을 초기화하면 이러한 작업이 더 오래 걸립니다. 그러나 데이터가 파일에 처음 기록될 때 운영 체제는 파일을 0으로 채울 필요가 없습니다.

SQL Server 2005에서는 데이터 파일을 즉각 초기화할 수 있습니다. 이 기능을 사용하면 앞서 언급한 파일 작업을 훨씬 신속하게 실행할 수 있을 뿐만 아니라 사용된 디스크 공간을 0으로 채우지 않고도 공간을 회수할 수 있습니다. 대신 새 데이터가 파일에 기록되면서 디스크 콘텐츠를 덮어쓰게 됩니다. 로그 파일은 즉각 초기화할 수 없습니다. 즉각적 파일 초기화 기능은 Windows XP Professional과 Windows Server 2003 및 이후 버전에서만 지원됩니다.

삭제된 디스크 콘텐츠는 파일에 새 데이터가 기록될 때만 덮어쓰게 되므로 권한이 없는 사용자가 이 삭제된 콘텐츠를 액세스할 수 있습니다. 데이터베이스 파일이 SQL Server 인스턴스에 연결되어 있는 동안에는 파일의 DACL(임의 액세스 제어 목록)을 통해 이러한 정보 노출 위험을 줄일 수 있습니다. 이 DACL을 사용하면 SQL Server 서비스 계정 및 로컬 관리자만 파일을 액세스할 수 있습니다. 그러나 파일이 분리되면 SE_MANAGE_VOLUME_NAME이 없는 사용자나 서비스가 파일을 액세스할 수 있습니다. 데이터베이스를 백업할 때도 이와 비슷한 위험이 존재합니다. 적절한 DACL로 백업 파일을 보호하지 않으면 권한이 없는 사용자나 서비스가 삭제된 콘텐츠를 무단으로 액세스할 수 있습니다.

삭제된 콘텐츠의 노출 위험이 걱정된다면 분리된 모든 데이터 파일과 백업 파일에 제한적인 DACL이 포함되도록 항상 주의해야 합니다. 또한 SQL Server 서비스 계정에서 SE_MANAGE_VOLUME_NAME을 취소하여 SQL Server 인스턴스에 대한 즉각적인 파일 초기화 기능을 해제해야 합니다.

Thanks to the following Microsoft IT pros for their technical expertise: Christian Bolton, Dan Carollo, Robert Davis, Jorge Guzman, Saleem Hakani, Ward Pond, Kalyan Yella, and Paolo Zavatarelli.

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