테이블의 모든 정보 확인하기
[따라하기]
Employees 테이블에 관련된 컬럼, 인덱스, 제약 조건 등의 정보를 반환합니다.
USE Northwind
GO
EXEC sp_help Employees
GO
테이블이 사용하는 공간 확인하기
[구문] sp_spaceused [[@objname =] 'objname']
[,[@updateusage =] 'updateusage']
[따라하기] 데이터베이스 내의 모든 테이블의 사용 공간 확인하기
/ 방법1. 기존의 시스템 SP를 단순히 활용한 예제 */
EXEC sp_MSforeachtable 'EXEC sp_spaceused [?], ''TRUE'''
GO
/* 방법2. 기존의 시스템 SP를 활용하여 결과를 테이블에 저장한 예제 */
USE DBAdmin
GO
CREATE TABLE spaceused_pubs (
TableName sysname,
Rows int,
Reserved varchar(20),
Data varchar(20),
Index_size varchar(20),
Unused varchar(20))
GO
USE pubs
GO
INSERT INTO DBAdmin..spaceused_pubs
EXEC sp_MSforeachtable 'EXEC sp_spaceused [?], ''TRUE'''
GO
SELECT * FROM DBAdmin..spaceused_pubs
GO
/* 방법3. sp_spaceused의 소스 코드를 수정하여 사용하기 */
CREATE PROCEDURE sp_spaceused_all
AS
/*
작성자 : 전현경
내용 : 데이터베이스내의 모든 테이블의 크기
계산 근거 :
reserved: sum(reserved) where indid in (0, 1, 255)
data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
indexsize: sum(used) where indid in (0, 1, 255) - data
unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
SET NOCOUNT ON
DECLARE @Low bigint
SELECT @Low = low FROM mASter.dbo.spt_values
WHERE number = 1 and type = 'E'
SELECT OBJECT_NAME(tmp.id) AS Name
,convert(char(11),sum(rows)) AS Rows
,ltrim(str(sum(tmp.reserved) * @Low / 1024.,15,0) + ' ' + 'KB') AS Reserved
,ltrim(str(sum(tmp.data) * @Low / 1024.,15,0) + ' ' + 'KB') AS Data
,ltrim(str((sum(tmp.used) - sum(tmp.data)) * @Low / 1024.,15,0) + ' ' + 'KB') AS Index_Size
,ltrim(str((sum(tmp.reserved) - sum(tmp.used)) * @Low / 1024.,15,0) + ' ' + 'KB') AS Unused
FROM (
SELECT obj.id AS id, sum(ind.rows) AS rows, sum(ind.reserved) AS reserved, sum(dpages) AS data,
isnull(sum(used), 0) AS used
FROM sysindexes ind JOIN sysobjects obj ON ind.id = obj.id
WHERE obj.xtype='U' AND ind.indid < 2
GROUP BY obj.id
UNION
SELECT obj.id AS id, 0 AS rows, sum(ind.reserved) AS reserved, isnull(sum(used), 0) AS data,
isnull(sum(used), 0) AS used
FROM sysindexes ind JOIN sysobjects obj ON ind.id = obj.id
WHERE obj.xtype='U' AND ind.indid = 255
GROUP BY obj.id ) tmp
GROUP BY tmp.id
ORDER BY sum(tmp.data) desc, sum(tmp.used) desc
SET NOCOUNT OFF
GO
테이블 옵션 설정하기
사용자 정의 테이블의 옵션 값을 설정합니다.
[구문] sp_tableoption [ @TableNamePattern = ] 'table'
[@OptionName = ] 'option_name'
[@OptionValue = ] 'value'
[따라하기]
A. Orders 테이블에 'text in row' 옵션 설정하기
'text in row' 옵션을 설정하면, Text, ntext, image 컬럼의 행에 저장할 최대 크기를 지정할 수 있습니다. 기본값은 256바이트이고, 값의 범위는 24에서 7000바이트입니다. 다음은 Orders 테이블의 text 컬럼에 저장할 데이터를 1000바이트로 지정합니다.
EXEC sp_tableoption 'orders', 'text in row', '1000'
GO
-- 설정값 확인
USE Northwind
GO
SELECT OBJECTPROPERTY(OBJECT_ID('orders'),'TableTextInRowLimit')
GO
B. Orders 테이블에 'pintable' 옵션 설정하기
'pintable' 옵션을 설정하면, 지정한 테이블의 데이터가 메모리에 상주합니다. 테이블 크기가 작고, 자주 사용하는 코드 테이블을 대상으로 사용할 수 있습니다.
USE Northwind
GO
EXEC sp_tableoption 'Orders', 'pintable', 'on'
GO
--메모리 고정 테이블 확인
SELECT OBJECTPROPERTY (OBJECT_ID('Orders'), 'TableIsPinned')
GO
[참고] 메모리에 테이블의 데이터를 상주시키기 위해 DBCC PINTABLE을 사용할 수도 있습니다.
-- 메모리 고정
DECLARE @objid int, @dbid int
SELECT @dbid = DB_ID('Northwind'), @objid = OBJECT_ID('Northwind..Orders')
DBCC PINTABLE (@dbid, @objid)
GO
-- 메모리 고정 해제
DECLARE @objid int, @dbid int
SELECT @dbid = DB_ID('Northwind'), @objid = OBJECT_ID(' Northwind..Orders')
DBCC UNPINTABLE (@dbid, @objid)
GO
-- 데이터베이스내의 메모리 고정 테이블의 전체 크기 확인
SELECT sum(i.used * 8) AS [pin table space used (KB)]
FROM sysindexes i JOIN sysobjects o ON i.id = o.id
WHERE o.status & 1048576 <> 0 AND indid < 2
GO
[주의]
이 기능은 성능을 향상시킬 수 있지만 주의해서 사용해야 합니다. 커다란 테이블을 고정할 경우 많은 용량의 버퍼 캐시를 사용하기 때문에 다른 테이블에서 사용할 캐시가 부족하게 됩니다. 버퍼 캐시보다 용량이 큰 테이블을 고정하면 전체 버퍼 캐시를 채울 수도 있습니다. 이런 경우 sysadmin 고정 서버 역할의 구성원이 SQL Server를 중지한 후 다시 시작한 다음, 테이블을 고정 해제해야 합니다. 너무 많은 테이블을 메모리에 고정해도 이와 같은 문제가 발생할 수 있습니다.
시스템 오브젝트 생성
시스템 저장 프로시저 생성하기
시스템 저장 프로시저는 master 데이터베이스에 있으며 이름이 sp_ 라는 접두사로 시작하며, 모든 데이터베이스에서 master 데이터베이스라고 지정하지 않고 저장 프로시저의 이름 만으로 실행이 가능합니다. 그리고 master 데이터베이스가 아닌 데이터베이스에서 실행하면 그 데이터베이스의 컨텍스트 내에서 수행되는 특징을 가지고 있습니다. 예를 들어 저장 프로시저가 sysobjects 테이블을 참조한다고 가정하면 이 프로시저는 실제로는 master에 있음에도 불구하고 그 프로시저를 실행할 때 연결되어 있던 데이터베이스에 있는 sysobjects를 액세스합니다.
[따라하기] 시스템 저장 프로시저를 생성하기
1. master 데이터베이스에 연결합니다.
2. sp_ 로 시작하는 이름으로 저장 프로시저를 생성합니다.
시스템 함수 조회 및 생성하기
SQL Server가 설치되는 동안 시스템 UDF(사용자 정의 함수)들이 생성되며, 이 시스템 함수는 모든 데이터베이스에서 함수의 이름만 사용하여 액세스가 가능합니다.
시스템 뷰를 만드는 것과 유사하게 master 데이터베이스에 존재하지만 모든 데이터베이스에서 데이터베이스를 지정하지 않고 쿼리를 수행할 수 있는 함수를 사용자가 직접 생성할 수 있습니다. 시스템 함수가 되기 위해서는 함수를 생성할 때 소유자를 system_function_schema로 생성합니다.
[따라하기] 시스템 함수 생성, 조회하기
/* 시스템 함수 생성하기 */
-- 시스템 테이블을 직접 수정할 수 있도록 설정합니다.
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
GO
-- 함수는 master 데이터베이스에 생성하고,
-- 소유자는 system_function_schema로 지정합니다.
USE master
GO
CREATE FUNCTION system_function_schema.fn_greatest (@x bigint, @y bigint)
RETURNS bigint
AS
BEGIN
RETURN(CASE WHEN @x>@y THEN @x ELSE @y END)
END
GO
CREATE FUNCTION system_function_schema.fn_least (@x bigint, @y bigint)
RETURNS bigint
AS
BEGIN
RETURN(CASE WHEN @x<@y THEN @x ELSE @y END)
END
GO
-- 시스템 테이블을 직접 수정할 수 없도록 0으로 변경합니다. (반드시 수행 요망)
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO
-- 생성한 시스템 함수는 모든 데이터베이스에서 호출 가능합니다.
USE Northwind
GO
SELECT fn_greatest(989, 998), fn_least(989, 998)
GO
/* 시스템 함수 조회하기 */
USE master
GO
SELECT name FROM sysobjects
WHERE uid=USER_ID('system_function_schema')
AND (OBJECTPROPERTY(id, 'IsScalarFunction')=1
OR OBJECTPROPERTY(id, 'IsTableFunction')=1
OR OBJECTPROPERTY(id, 'IsInlineFunction')=1)
GO
INFORMATION 스키마 뷰 생성하기
SQL Server 2000에서 메타데이터 정보를 가져오는 방법은 시스템 저장 프로시저를 사용하는 방법과 INFORMATION 스키마 뷰를 사용하는 방법의 두 가지가 있습니다.
[참고] 메타데이터를 가져오기 위해서는 시스템 저장 프로시저, 시스템 함수 또는 시스템 제공 뷰를 사용할 것을 권고합니다. 시스템 테이블을 직접 쿼리하는 경우, 시스템 테이블이 이후 버전에서 변경될 때 정확한 정보를 제공하지 못할 수도 있습니다. 정보 스키마 뷰를 참조할 때는 다음과 같이 사용자 이름을 지정하는 위치에 INFORMATION_SCHEMA를 지정해야 합니다.
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
[SQL Server 2000에서 제공하는 INFORMATION 스키마 뷰 목록]
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
[따라하기]
CHECK 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰는 있지만 DEFAULT 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰는 기본적으로 제공되지 않습니다. 이 예제에서는 기존의 CHECK_CONSTRAINTS라는 INFORMATION 스키마 뷰의 소스를 수정하여 DEFAULT 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰를 만들어 보고자 합니다.
-- 기본으로 제공되는 INFORMATION 스키마 뷰 활용하기
USE Pubs
GO
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
GO
-- 사용자가 INFORMATION 스키마 뷰 생성하기
USE master
GO
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_MS_upd_sysobj_category 1
GO
CREATE VIEW INFORMATION_SCHEMA.DEFAULT_CONSTRAINTS
AS
SELECT db_name() as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name as CONSTRAINT_NAME
,com.text as DEFAULT_CLAUSE
FROM sysobjects c_obj, syscomments com
WHERE c_obj.uid = user_id()
AND c_obj.id = com.id
AND c_obj.xtype = 'D'
GO
EXEC sp_MS_upd_sysobj_category 2
GO
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
-- 생성한 INFORMATION 스키마 뷰 활용하기
USE pubs
GO
SELECT *
FROM INFORMATION_SCHEMA.DEFAULT_CONSTRAINTS
GO
사용자 관리
로그인과 사용자 이해하기
SQL Server에 접근하기 위해서는 로그인 계정이 필요하고, 데이터베이스에 접근하기 위해서는 사용자(user)가 필요합니다.
권한 이해하기
사용자는 권한을 받아 작업을 수행할 수 있습니다. 권한에는 문장을 실행할 수 있는지에 따라 권한을 제한하는 명령문(Statement) 사용권한과 테이블, 색인, 뷰, 프로시저에 따라 권한을 제한하는 개체(Object) 사용권한이 있습니다.
역할 이해하기
역할은 로그인 또는 사용자들의 집합이고, 역할에 권한이 설정되어 있습니다. 그러므로, 각 사용자에게 권한을 주는 것이 아니라, 그 권한이 있는 역할에 사용자를 추가시키는 것이 좋습니다. 시스템에서 미리 정의된 역할에는 로그인에게 사용할 수 있는 서버 역할과 사용자에게 사용할 수 있는 데이터베이스 역할 이 있습니다.
로그인 계정 생성하고 권한 부여하기
[따라하기]
SQL Server 계정을 생성하여, Sample 데이터베이스에 사용자를 등록하고, 데이터베이스내의 테이블 읽는 권한을 주고, 서버내의 서버 차원의 설정을 구성할 수 있는 권한을 설정합니다. 고정 데이터베이스 역할 db_datareader은 데이터베이스내의 테이블을 읽는 권한을 가지고 있고, 서버 데이터베이스 역할 serveradmin은 서버차원을 설정을 구성합니다.
EXEC sp_addlogin 'dbadmin','ad1234','sample'
GO
EXEC sp_addsrvrolemember 'dbadmin', 'serveradmin'
GO
USE Sample
GO
EXEC sp_adduser 'dbadmin', 'dbadmin', 'db_datareader'
GO
NT 로그인 계정을 생성하여, sample 데이터베이스에 사용자로 등록하고, 데이터베이스내의 테이블 읽는 권한을 설정합니다.
NT 로그인 계정은 윈도우의 [내 컴퓨터 관리]나 [액티브 디렉터리 사용자 및 컴퓨터]를 이용하여 생성합니다. 생성된 계정은 Admin\winadmin이라고 가정합니다.
EXEC sp_grantlogin 'Admin\winadmin'
GO
EXEC sp_defaultdb 'Admin\winadmin', 'sample'
GO
USE Sample
GO
EXEC sp_grantdbaccess 'Admin\winadmin', 'winadmin'
GO
EXEC sp_addrolemember 'db_datareader', 'winadmin'
GO
SQL Server 로그인은 'backupAdmin', 암호는 '1234'로 설정하여, sample 데이터베이스의 사용자로 등록하고, 데이터베이스 백업을 수행할 수 있는 권한을 부여합니다. 그 후, backupAdmin 로그인 계정을 삭제합니다. 고정 데이터베이스 역할 중에서 db_backupoperator 역할은 데이터베이스 백업을 수행할 수 있는 권한을 가지고 있습니다.
EXEC sp_addlogin 'backupAdmin','1234','sample'
GO
USE Sample
GO
EXEC sp_adduser 'backupAdmin', 'backupAdmin', 'db_backupoperator'
GO
-- 로그인 계정 삭제
EXEC sp_droprolemember 'db_backupoperator', 'backupAdmin'
GO
EXEC sp_revokedbaccess 'backupAdmin'
GO
EXEC sp_droplogin 'backupAdmin'
GO
NETDOMAIN의 John. Sarah, Diane 계정을 courses 데이터베이스의 사용자로 등록하고, Student 역할에 등록합니다. 그 후, Student 역할에서 Diane 계정을 삭제하고, 로그인 계정에서도 Diane을 삭제합니다.
USE master
GO
EXEC sp_grantlogin 'NETDOMAIN\John'
GO
EXEC sp_defaultdb 'NETDOMAIN\John', 'courses'
GO
EXEC sp_grantlogin 'NETDOMAIN\Sarah'
GO
EXEC sp_defaultdb 'NETDOMAIN\Sarah', 'courses'
GO
EXEC sp_grantlogin 'NETDOMAIN\Diane'
GO
EXEC sp_defaultdb 'NETDOMAIN\Diane', 'courses'
GO
USE courses
GO
EXEC sp_grantdbaccess 'NETDOMAIN\John'
GO
EXEC sp_grantdbaccess 'NETDOMAIN\Sarah'
GO
EXEC sp_grantdbaccess 'NETDOMAIN\Diane'
GO
EXEC sp_addrole 'Student'
GO
EXEC sp_addrolemember 'Student','NETDOMAIN\John'
GO
EXEC sp_addrolemember 'Student','NETDOMAIN\Sarah'
GO
EXEC sp_addrolemember 'Student','NETDOMAIN\Diane'
GO
EXEC sp_droprolemember 'Student','NETDOMAIN\Diane'
GO
EXEC sp_revokedbaccess 'NETDOMAIN\Diane'
GO
EXEC sp_revokelogin 'NETDOMAIN\Diane'
GO
[참고]
A. sp_revokelogin
sp_grantlogin 또는 sp_denylogin으로 만든 NT 사용자 또는 로그인 항목을 SQL Server에서 제거합니다. NT 로그인 계정이 SQL Server에 연결하는 것이 금지됩니다.
B. sp_denylogin
NT 로그인 계정이 SQL Server에 연결하는 것을 금지합니다.
기존 로그인과 사용자의 확인하기
[따라하기]
EXEC sp_helplogins
GO
EXEC sp_helpuser
GO
암호 변경하기
[따라하기]
로그인 계정 'dbadmin'의 암호 'ad1234'를 'ad5678'로 변경합니다.
EXEC sp_password 'ad1234','ad5678', 'dbadmin'
GO
서버 및 데이터베이스의 정보 확인
서버 이름, 버전, Edition 확인하기
[구문] SERVERPROPERTY ( propertyname )
[따라하기]
SELECT SERVERPROPERTY('ServerName') AS ServerName
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('InstanceName') AS InstanceName
, SERVERPROPERTY('Edition') AS Edition
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') AS ProductLevel
GO
서버의 옵션 확인하기
서버의 구성 옵션과 구성 옵션의 최소값과 최대값, 설정된 대상 값, 설정값을 확인합니다.
[구문] sp_configure [ [ @configname = ] 'name' ]
[ , [ @configvalue = ] 'value' ]
[따라하기]
1. 서버 구성 옵션 확인하기
EXEC sp_configure
GO
2. 서버의 고급 구성 옵션 확인하기
서버의 일부 옵션은 고급으로 지정되어 있고, 이 값들도 변경할 수 있습니다. 고급 구성 옵션을 보기 위해서는 다음을 실행합니다.
USE master
GO
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
데이터베이스 파일에 대한 I/O 통계 정보 확인하기
fn_virtualfilestats 함수는 시스템에서 기본으로 제공하는 함수로서, I/O에 대한 통계 정보를 제공합니다. 사용자들이 어떤 파일에 대하여 읽기나 쓰기를 수행하기 위하여 기다린 시간을 제공하므로 이 함수를 사용하면 어떤 파일들에 대하여 I/O가 많이 발생하는지 확인할 수 있습니다. IO로 인한 성능 저하가 의심되는 경우에는 fn_virtualfilestats 함수가 반환하는 IOStallMS를 점검할 것을 권고합니다.
[구문] fn_virtualfilestats ( [@DatabaseID=] database_id
, [ @FileID = ] file_id )
[따라하기]
1. 모든 데이터베이스의 파일들의 I/O 정보 확인하기
SELECT * FROM ::fn_virtualfilestats(-1, -1)
GO
2. 모든 데이터베이스의 파일들의 I/O 정보 확인하기 (시스템 함수 활용)
SELECT DB_NAME(Dbid) AS DBName
, FileId
, FILE_NAME(FileId) AS FileName
, IoStallMS, NumberReads, NumberWrites, BytesRead
, BytesWritten
FROM ::fn_VirtualFileStats (-1, -1)
ORDER BY IoStallMS DESC
GO
3. 특정 데이터베이스의 모든 파일들에 대한 I/O 정보 확인하기 (예제: tempdb)
SELECT * FROM ::fn_virtualfilestats(2, -1)
GO
4. 특정 데이터베이스의 특정 파일의 I/O 정보 확인하기 (Tempdb의 Primary Data File)
SELECT * FROM ::fn_virtualfilestats(2, 1)
GO
5. 특정 데이터베이스의 특정 파일의 I/O 정보 확인하기 (Tempdb의 로그 파일)
SELECT * FROM ::fn_virtualfilestats(2, 2)
GO
기본적인 파일 정보 확인하기
현재 데이터베이스와 연관된 파일의 물리적 이름과 특징을 반환합니다. 파일 이름을 지정하지 않으면, 데이터베이스내의 모든 파일에 대한 정보를 확인할 수 있습니다.
[구문] sp_helpfile [ [ @filename = ] 'name' ]
[따라하기]
USE Sample
EXEC sp_helpfile
-- 또는
EXEC Sample..sp_help
기본적인 파일 그룹 정보 확인하기
현재 데이터베이스와 연관된 파일그룹의 이름과 특징을 반환합니다. 파일그룹의 이름을 지정하지 않으면, 데이터베이스내의 모든 파일그룹에 대한 정보를 확인할 수 있습니다.
[구문] sp_helpfilegroup [ [ @filegroupname = ] 'name' ]
기본적인 데이터베이스의 정보 확인하기
지정된 데이터베이스 또는 모든 데이터베이스 정보를 반환합니다.
[구문] sp_helpdb [ [ @dbname= ] 'name' ]
데이터베이스를 지정하지 않았을 경우, 결과 집합입니다. 컬럼명 내용
Name 데이터베이스 이름
Db_size 데이터베이스의 총 크기
Owner 데이터베이스의 소유자
Dbid 데이터베이스의 ID
Created 데이터베이스의 생성일자
Status 옵션의 값을 쉼표로 분리하여 나열한 것
Compatibility_level 호환성 수준(60,65,70,80)
데이터베이스를 지정했을 경우, 위의 결과 집합에 다음의 결과 집합이 추가로 나타납니다. Name 논리적 파일 이름
Fileid 파일 ID
Filename 파일의 물리적 경로와 이름
Filegroup 파일이 속한 그룹
Size 파일 크기
Maxsize 파일이 증가할 수 있는 최대 크기
Growth 파일의 증가량
Usage 파일의 사용법
데이터베이스 옵션 또는 현재 설정 확인하기
지정한 Property에 따른 결과값이 반환됩니다. property의 값 목록과 그에 따른 결과의 종류는 BOL 참조해 주십시오.
[구문] DATABASEPROPERTYEX(database_name, property)
[따라하기]
1. Northwind 데이터베이스의 통계 자동 업데이트 옵션 설정 확인하기
SELECT DATABASEPROPERTYEX ('Northwind','IsAutoUpdateStatistics')
AS IsAutoUpdateStatistics
GO
2. Northwind 데이터베이스의 복구 모델 설정 확인하기
SELECT DATABASEPROPERTYEX ('Northwind','Recovery') AS Recovery
GO
데이터베이스 옵션 설정 확인하기
[구문] sp_dboption [ [ @dbname = ] 'database' ]
[ , [ @optname = ] 'option_name' ]
[ , [ @optvalue = ] 'value' ]
[따라하기]
A. 설정 가능한 옵션의 목록 확인하기
인수를 지정하지 않습니다.
EXEC sp_dboption
GO
B. 지정한 데이터베이스의 설정 옵션 목록 확인하기
데이터베이스만을 지정합니다. Northwind데이터베이스의 설정 옵션 목록을 확인합니다.
EXEC sp_dboption 'Northwind'
GO
C. 지정한 데이터베이스의 해당 옵션의 설정 상태 확인하기
데이터베이스와 해당 옵션을 설정합니다. Northwind데이터베이스의 자동 통계 갱신 옵션의 설정을 확인합니다.
EXEC sp_dboption 'Northwind', 'auto update statistics'
GO
데이터베이스 옵션의 종류 Auto create statistics
Auto update statistics
Autoclose
Autoshrink
ANSI null default
ANSI nulls
ANSI padding
ANSI warnings
Arithabort
concat null yields null
cursor close on commit
Dbo use only
default to local cursor
merge publish
numeric roundabort
Offline
Published
quoted identifier
read only
Recursive triggers
select into/bulkcopy
single user
Subscribed
Torn page detection
trunc. Log on chkpt.
Tempdb에 대한 공간 사용 정보 확인하기
[구문] sp_tempdbspace
테이블 크기 확인하기
[구문] sp_spaceused [[@objname =] 'objname']
[,[@updateusage =] 'updateusage']
[따라하기] Orders 테이블의 크기 확인하기
EXEC sp_spaceused 'Orders'
[참고] 테이블을 지정하지 않으면, 해당 데이터베이스의 크기가 반환됩니다.
로그 공간의 사용 정보 확인하기
서버 내에 존재하는 모든 데이터베이스의 로그 공간의 사용에 관한 통계를 확인합니다.
[구문] DBCC SQLPERF ( LOGSPACE )
테이블의 조각화 정보 확인하기
테이블의 조각화는 INSERT, UPDATE, DELETE문 등의 데이터를 수정할 때에 발생합니다. 이러한 수정들은 각 페이지의 채움 정도를 다르게 만들고, 테이블의 일부 또는 전부를 스캔하는 쿼리의 경우, 테이블 조각으로 인한 성능 저하가 발생할 가능성이 있습니다.
[구문] DBCC SHOWCONTIG
[ ( { table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
)
]
[ WITH { ALL_INDEXES
| FAST [ , ALL_INDEXES ]
| TABLERESULTS [ , { ALL_INDEXES } ]
[ , { FAST | ALL_LEVELS } ]
}
]
[따라하기]
A. 지정한 테이블의 조각화 정보 확인하기
Orders 테이블의 조각화 정보를 확인합니다.
USE Northwind
GO
DBCC SHOWCONTIG ('Orders')
GO
B. 지정한 테이블의 지정한 인덱스의 조각화 정보 확인하기
Orders 테이블의 CustomerID 인덱스에 대한 조각화 정보를 확인합니다.
USE Northwind
GO
DBCC SHOWCONTIG ('Orders', 'CustomerID')
GO
C. 테이블 ID와 인덱스 ID로 조각화 정보 확인하기
테이블 이름과 인덱스 이름 대신, 테이블 ID와 인덱스 ID를 사용합니다.
USE Northwind
GO
DECLARE @ObjectId int, @IndexId int
SELECT @ObjectId = OBJECT_ID ('Orders')
SELECT @IndexId = indid FROM sysindexes
WHERE id = @ObjectId
DBCC SHOWCONTIG (@ObjectId, @IndexId)
GO
D. 데이터베이스내의 모든 테이블의 모든 인덱스에 대한 조각화 정보 확인하기
USE Northwind
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
GO
E. 데이터베이스에 수행 결과를 저장하기
-- 결과를 저장할 테이블 생성하기
USE DBAdmin
GO
CREATE TABLE ShowContig_Pubs
(
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId tinyint,
Level tinyint,
Pages int,
Rows bigint,
MinimumRecordSize smallint,
MaximumRecordSize smallint,
AverageRecordSize smallint,
ForwardedRecords bigint,
Extents int,
ExtentSwitches numeric(10,2),
AverageFreeBytes numeric(10,2),
AveragePageDensity numeric(10,2),
ScanDensity numeric(10,2),
BestCount int,
ActualCount int,
LogicalFragmentation numeric(10,2),
ExtentFragmentation numeric(10,2),
CheckDate smalldatetime DEFAULT (GETDATE())
)
GO
-- 시스템 저장 프로시저 생성하기
USE master
GO
CREATE PROCEDURE sp_DBCCSHOWCONTIG
AS
DBCC SHOWCONTIG WITH TABLERESULTS
GO
-- 실행 예제
USE Pubs
GO
INSERT INTO DBAdmin..ShowContig_Pubs (
ObjectName, ObjectId, IndexName, IndexId, Level, Pages,
Rows, MinimumRecordSize, MaximumRecordSize,
AverageRecordSize, ForwardedRecords, Extents,
ExtentSwitches, AverageFreeBytes, AveragePageDensity,
ScanDensity, BestCount, ActualCount, LogicalFragmentation,
ExtentFragmentation)
EXEC sp_DBCCSHOWCONTIG
GO
-- 실행 결과 확인하기
SELECT * FROM DBAdmin..ShowContig_Pubs
WHERE ObjectName = 'Authors'
GO
SELECT * FROM DBAdmin..ShowContig_Pubs
WHERE LogicalFragmentation > 30
OR ExtentFragmentation > 30
GO
[참고] WITH FAST 옵션을 사용하면, 인덱스의 잎 또는 데이터 수준 페이지를 읽지 않기 때문에, 생략된 정보를 빠르게 반환합니다.
[주의] 크기가 큰 테이블에 대해서는 서비스 중에 DBCC SHOWCONTIG를 수행하면 성능 저하를 유발할 수 있으므로 유의합니다.
[권고사항] 수행 결과를 확인하고 단편화가 심한 테이블들에 대해서는 인덱스 재구성 작업을 수행합니다.
대기 정보 확인하기
각 waittype별 대기 시간을 확인합니다. 가장 대기가 많은 유형을 확인하고, 작업 부하에 따른 대기 유형의 변화도 확인합니다.
[구문] DBCC SQLPERF (WAITSTATS)
인덱스 재구성하기
인덱스 조각화로 인한 성능 저하를 방지하기 위해서는 주기적으로 인덱스 조각화가 진행된 테이블들에 대한 조각화 제거 작업이 필요합니다. 가능한 한 인덱스 재구성 작업을 자동화하여 주기적으로 용이하게 수행할 수 있는 체계를 갖출 것을 권고합니다.
[주의] DBCC DBREINDEX 작업은 서비스 휴지 시간에 수행해야 합니다.
[예제 스크립트] 다음은 인덱스 조각화 제거를 위해 활용할 수 있는 예제 스크립트입니다. 아래 예제 스크립트를 시스템의 환경에 적합하도록 수정 보완하여 활용하기 바랍니다.
----------------------------------------------------------------------------------------------------------------
-- Written by Kimberly L. Tripp - all rights reserved.
-- For more scripts, sample code and Kimberly's schedule check out
-- www.SQLSkills.com
-- For "More than Just Training," see the Industry Experts at
-- www.SolidQualityLearning.com
-- Disclaimer - Thoroughly test this script, execute at your own risk.
----------------------------------------------------------------------------------------------------------------
/*
Execute this whole script to create the sp_RebuildIndexes stored procedure in Master.
Best Viewed with Courier New 12pt. and Tabs saved as 4 spaces not 8. (Tools, Options, Editor)
To use the sp_RebuildIndexes procedure once created use:
sp_RebuildIndexes
To Rebuild All Indexes on All Tables for all that have a Scan Density < 100%
sp_RebuildIndexes @ScanDensity = 80
To Rebuild All Indexes on All Tables with a Scan Density of < 80%
sp_RebuildIndexes 'Authors'
To Rebuild All Indexes on the authors table - for a Scan Density of < 100%
sp_RebuildIndexes 'Authors', 80
To Rebuild All Indexes on the authors table - for a Scan Density of < 80%
Object Name and ScanDensity are both optional parameters.
ScanDensity must be a whole number between 1 and 100.
*/
USE master
GO
IF OBJECTPROPERTY(object_id('sp_RebuildClusteredIndex'), 'IsProcedure') = 1
DROP PROCEDURE sp_RebuildClusteredIndex
GO
IF OBJECTPROPERTY(object_id('sp_RebuildIndexes'), 'IsProcedure') = 1
DROP PROCEDURE sp_RebuildIndexes
GO
CREATE PROCEDURE sp_RebuildClusteredIndex
(
@TableName sysname = NULL,
@IndexName sysname = NULL
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- NOTE: If your clustered index is NOT unique then rebuilding the clustered
-- index will cause the non-clustered indexes to be rebuilt.
-- If the nonclustered indexes were fragmented
-- then this series of scripts will build them again.
IF @TableName IS NOT NULL
BEGIN
IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
AND OBJECTPROPERTY(object_id(@TableName), 'IsView') = 0)
BEGIN
RAISERROR('Object: %s exists but is NOT a User-defined Table.
This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
ELSE
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
BEGIN
RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?).
This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
END
END
IF @IndexName IS NOT NULL
BEGIN
IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') = 0
BEGIN
RAISERROR('Index: %s exists but is a Clustered Index.
This procedure only accepts valid table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
RETURN
END
ELSE
BEGIN
IF INDEXPROPERTY(object_id(@TableName),
@IndexName, 'IsClustered') IS NULL
BEGIN
SELECT @TableName, @IndexName,
INDEXPROPERTY(object_id(@TableName),
@IndexName, 'IsClustered')
RAISERROR('There is no index with name:%s on this table.
Please check the table name and index name as well as location (which database?).
This procedure only accepts existing table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
RETURN
END
END
END
-- So now we have a valid table, a valid CLUSTERED index and we're ready to rebuild.
-- Here's a quick overview of what this code will do:
-- Get the Column List and Index Defintion (Use the output from sp_helpindex)
-- Figure out if it's UNIQUE - to specify in CREATE INDEX statement
-- Build and Execute the CREATE INDEX command through dynamic string execution
DECLARE @ExecStr nvarchar(4000)
-- more than enough even if 16 cols of 128 chars,
-- Tablename of 128 and Indexname of 128...
-- but if this is the case you have other problems :).
, @ColList nvarchar(3000)
, @Unique nvarchar(7)
-- Will be either '' or 'Unique ' and added to CR Index String
, @FillFactor nvarchar(100)
CREATE TABLE #IndexInfo
(
IndexName sysname,
IndexDesc varchar(210),
IndexKeys nvarchar(2126)
)
INSERT INTO #IndexInfo EXEC sp_helpindex @TableName
SELECT @ColList = IndexKeys
, @Unique = CASE
WHEN IndexDesc LIKE 'clustered, unique%'
THEN 'Unique '
ELSE ''
END --CASE Expression
, @FillFactor = ', FILLFACTOR = ' + NULLIF(convert(nvarchar(3)
, (SELECT OrigFillFactor
FROM sysindexes
WHERE id = object_id(@TableName) AND Name = @IndexName)), 0)
FROM #IndexInfo
WHERE IndexName = @IndexName
SELECT @ExecStr = 'CREATE ' + @Unique + 'CLUSTERED INDEX '
+ QUOTENAME(@IndexName, ']') + ' ON '
+ QUOTENAME(@TableName, ']') + '(' + @collist
+ ') WITH DROP_EXISTING ' + ISNULL(@FillFactor, '')
-- For testing the String
-- SELECT @ExecStr
-- Create the Clustered Index
EXEC(@ExecStr)
GO
CREATE PROCEDURE sp_RebuildIndexes
(
@TableName sysname = NULL,
@ScanDensity tinyint = 100
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- This procedure will get the Fragmentation information for all tables and indexes
-- within the database.
-- Programmatically it will then walk the list rebuilding all indexes that have a scan
-- density less than the value passed in - by default any less than 100% contiguous.
-- Use this script as a starting point. Modify it for your options, ideas, etc.
-- and then schedule it to run regularly.
-- NOTE - This gathers density information for all tables and all indexes.
-- This might be time consuming on large databases.
-- DISCLAIMER - Execute at your own risk. TEST THIS FIRST.
SET NOCOUNT ON
IF @ScanDensity IS NULL
SET @ScanDensity = 100
IF @ScanDensity NOT BETWEEN 1 AND 100
BEGIN
RAISERROR('Value supplied:%i is not valid. @ScanDensity is a percentage.
Please supply a value for Scan Density between 1 and 100.', 16, 1, @ScanDensity)
RETURN
END
IF @TableName IS NOT NULL
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
BEGIN
RAISERROR('Object: %s exists but is NOT a User-defined Table.
This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
ELSE
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
BEGIN
RAISERROR('Object: %s does not exist within this database.
Please check the table name and location (which database?).
This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
END
END
-- Otherwise the Object Exists and it is a table so we'll continue from here.
-- First thing to do is create a temp location for the data returned from
-- DBCC SHOWCONTIG
CREATE TABLE #ShowContigOutput
(
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId tinyint,
[Level] tinyint,
Pages int,
[Rows] bigint,
MinimumRecordSize smallint,
MaximumRecordSize smallint,
AverageRecordSize smallint,
ForwardedRecords bigint,
Extents int,
ExtentSwitches numeric(10,2),
AverageFreeBytes numeric(10,2),
AveragePageDensity numeric(10,2),
ScanDensity numeric(10,2),
BestCount int,
ActualCount int,
LogicalFragmentation numeric(10,2),
ExtentFragmentation numeric(10,2)
)
IF @TableName IS NOT NULL
-- then we only need the showcontig output for that table
INSERT #ShowContigOutput
EXEC('DBCC SHOWCONTIG (' + @TableName + ') WITH FAST, ALL_INDEXES, TABLERESULTS')
ELSE
-- All Tables, All Indexes Will be processed.
INSERT #ShowContigOutput
EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS')
PRINT N' '
-- Quick test to see if everything is getting here correctly
-- SELECT * FROM #ShowContigOutput
-- Walk the showcontig output table skipping all replication tables
-- as well as all tables necessary for
-- the UI. This is also where you can list large tables
-- that you don't want to rebuild all at one time.
-- NOTE: If you take out a large table from rebuilding this script may have
-- already checked density
-- meaning that the expense in terms of time may have been
-- expensive.
-- Also, you should use a different procedure to rebuild a large table
-- specifically.
-- Even when you pass in the tablename it will be avoided here if
-- MANUALLY added to the list by you.
-- Test, Test, Test!
DECLARE @ObjectName sysname,
@IndexName sysname,
@QObjectName nvarchar(258),
@QIndexName nvarchar(258),
@IndexID tinyint,
@ActualScanDensity numeric(10,2),
@InformationalOutput nvarchar(4000),
@StartTime datetime,
@EndTime datetime
DECLARE TableIndexList CURSOR FAST_FORWARD FOR
SELECT ObjectName, IndexName, IndexID, ScanDensity
FROM #ShowContigOutput AS sc JOIN sysobjects AS so ON sc.ObjectID = so.id
WHERE sc.ScanDensity < @ScanDensity
AND (OBJECTPROPERTY(sc.ObjectID, 'IsUserTable') = 1
OR OBJECTPROPERTY(sc.ObjectID, 'IsView') = 1)
AND so.STATUS > 0
AND sc.IndexID BETWEEN 1 AND 250
AND sc.ObjectName NOT IN ('dtproperties')
-- Here you can list large tables you do not WANT rebuilt.
ORDER BY sc.ObjectName, sc.IndexID
OPEN TableIndexList
FETCH NEXT FROM TableIndexList INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @QObjectName = QUOTENAME(@ObjectName, ']')
SELECT @QIndexName = QUOTENAME(@IndexName, ']')
SELECT @InformationalOutput = N'Processing Table: '
+ RTRIM(UPPER(@QObjectName))
+ N' Rebuilding Index: ' + RTRIM(UPPER(@QIndexName))
PRINT @InformationalOutput
IF @IndexID = 1
BEGIN
SELECT @StartTime = getdate()
EXEC sp_RebuildClusteredIndex @ObjectName, @IndexName
SELECT @EndTime = getdate()
SELECT @InformationalOutput = N'Total Time to process = '
+ convert(nvarchar, datediff(ms, @StartTime, @EndTime))
+ N' ms'
PRINT @InformationalOutput
END
ELSE
BEGIN
SELECT @StartTime = getdate()
EXEC('DBCC DBREINDEX(' + @QObjectName + ', '
+ @QIndexName + ') WITH NO_INFOMSGS')
SELECT @EndTime = getdate()
SELECT @InformationalOutput = N'Total Time to process = '
+ convert(nvarchar, datediff(ms, @StartTime, @EndTime))
+ N' ms'
PRINT @InformationalOutput
END
PRINT N' '
FETCH NEXT FROM TableIndexList
INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity
END
END
PRINT N' '
SELECT @InformationalOutput = N'** All Indexes have been rebuilt. ** '
PRINT @InformationalOutput
DEALLOCATE TableIndexList
GO
오류 로그 보기
SQL Server에서는 이벤트를 SQL Server 오류 로그 및 응용 프로그램 로그에 기록합니다. 문제가 발생하였을 경우, 오류 로그에 기록되어 있는 정보를 사용하여, 문제의 원인을 찾을 수 있습니다.
SQL Server 오류 로그 보기
SQL Server 로그는 응용 프로그램의 상태 정보를 알기 위한 훌륭한 자료입니다. SQL Server 로그는 서비스가 시작할 때부터 서비스가 중지될 때까지 계속 메시지를 기록합니다.
모니터링 관리의 효율을 위하여 모니터링 담당자가 SQL Server 로그에서 찾아야 할 것을 정의합니다. 이 로그는 심각도 수준 19~25의 값을 가진 모든 오류를 기록합니다. 모니터링할 때, SQL Server 로그에서 심각도 수준 19~25 사이의 값을 가진 오류는 반드시 체크해야 합니다. 이 심각도 수준을 가진 오류는 트랜잭션을 실패하게 하고 응용 프로그램이 적절하게 운영되지 않도록 합니다. 심각도 수준 20에서 25사이의 오류는 치명적입니다. 만일 이 오류가 발생되면, 클라이언트 연결은 오류 메시지를 받은 후에 종료됩니다.
또한, RAISERROR…WITH LOG 문법을 사용하여 각각의 오류를 발견해 낼 수 있습니다. 이것은 오류 정보를 SQL Server 로그에 나타나게 합니다.
[따라하기]
1. EM에서 원하는 데이터베이스 서버를 선택합니다.
2. [관리]폴더를 클릭하고, [SQL Server 로그]를 클릭합니다.
3. 원하는 로그파일을 클릭하면, 오른쪽 창에 로그가 나타납니다.
[참고] 로그 파일의 정보 확인 및 개수 변경하기
SQL Server 로그 파일의 개수는 현재 기록하고 있는 로그와 이전의 6개의 로그에 대한 백업을 가지고 있습니다. 이 로그 파일의 수를 크게 설정하면 SQL Server 재시작으로 인하여 문제를 진단하는데 단서가 될 수 있는 ERRORLOG 파일이 overwrite되어 유실되는 것을 방지할 수 있습니다. 이 로그의 개수는 레지스트리 값을 수정하여 변경할 수 있습니다.
-- 오류 로그 파일들의 파일 정보 확인
EXEC master..xp_enumerrorlogs
GO
-- 3번째 로그 파일 내용 보기
EXEC sp_readerrorlog 3
GO
-- 로그 파일의 개수 20으로 변경하기
EXEC master..xp_regwrite 'HKEY_LOCAL_MACHINE'
, 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
, 'NumErrorLogs'
, 'REG_DWORD'
, 20
GO
-- 확인
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE'
, 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
, 'NumErrorLogs'
GO
[참고] SQL Server 재시작 없이 새 오류 로그 생성하기
새 오류 로그는 SQL Server가 시작되는 경우에 생성되지만, SQL Server의 재시작 없이도 새 로그를 생성할 수 있습니다. 어떤 이유로 ERRORLOG 파일의 크기가 지나치게 커진 경우에는 sp_cycle_errorlog를 사용하여 새로운 ERRORLOG 파일을 생성할 것을 권고합니다.
-- 새 로그 생성
EXEC sp_cycle_errorlog
GO
-- 오류 로그 파일별 정보 조회
EXEC master..xp_enumerrorlogs
GO
응용 프로그램 로그 보기
이벤트 뷰어는 사용자가 응용 프로그램, 보안, 시스템 로그에 기록되는 이벤트를 모니터링할 수 있도록 합니다. 이 로그는 SQL Server 로그와 SQL 에이전트 로그로 분리시켜 추가적인 정보를 제공합니다. SQL Server 메시지는 응용 프로그램 로그에서 발견됩니다.
SQL Server 메시지는 "MSSQLSERVER" 또는 "SQLSERVERAGENT"라는 원본을 가진 메시지로 구별될 수 있습니다. RAISERROR 메시지도 여기에서 볼 수 있습니다.
[따라하기]
1. [시작] → [설정] → [관리도구] → [이벤트 뷰어]를 선택합니다. (OS마다 다름)
2. 디폴트로 로컬컴퓨터의 로그가 나타난다. 다른 컴퓨터의 [연결]을 클릭하여 원격 컴퓨터의 로그를 확인합니다.
3. 원하는 사항을 더블 클릭하면, 자세한 정보를 얻을 수 있습니다.
SQL Server 에이전트 오류 로그 보기
SQL Server 에이전트 서비스는 작업 및 복제를 관리합니다.
1. EM에서 원하는 데이터베이스 서버를 선택합니다.
2. [관리] 폴더를 클릭합니다.
3. [SQL Server 에이전트]에 마우스를 대고 오른쪽 버튼을 클릭하고, [오류 로그 표시]를 선택합니다.
4. 오류를 선택하여 목록을 확인합니다.
성능 모니터링
성능 모니터링 하기
1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.
2. 상단 그래픽 메뉴에서 [+]를 클릭하여 카운터 추가화면이 나타나면, 원하는 모니터 카운터를 추가합니다.
3. [로컬 컴퓨터 카운터 사용] 또는 [다른 컴퓨터에서 카운터 선택]을 선택합니다.
4. [성능개체]를 선택합니다.
5. [모든 카운터]를 선택하거나, [다음 목록에서 카운터 선택]를 선택한 후, 원하는 카운터를 선택합니다.
6. [모든 인스턴스]를 선택하거나, [다음 목록에서 인스턴스 선택]을 선택한 후, 원하는 인스턴스를 선택합니다.
7. [추가]를 클릭합니다.
8. 카운터 추가를 완료 한 후, [닫기]를 클릭합니다.
9. 선택한 카운터가 하단에 나타납니다.
10. 카운터를 제거할 경우에는, 제거하기를 원하는 카운터를 선택한 후, 상단 그래픽 메뉴에서, [x] (삭제키)를 클릭합니다.
성능 로그 생성하기
[따라하기]
1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.
2. [성능 로그 및 경고]의 더하기 기호(+)를 클릭합니다.
3. 카운터 로그를 선택합니다.
4. 오른쪽 창에 마우스를 대고 오른쪽 버튼을 클릭하여, [새 로그 설정]을 선택합니다.
5. [새 로그 설정]에 원하는 로그 이름을 입력하고, [확인]을 클릭합니다.
6. Counter_Log화면에 사용 정보와 파일 포맷을 설정합니다.
7. [개체추가]를 선택하여, 원하는 개체를 추가합니다.
8. [카운터추가]를 선택하여, 원하는 카운터를 추가합니다.
9. [데이터 샘플 간격]의 [간격]과 [단위]를 선택합니다.
10. [로그파일]탭을 선택합니다.
11. [로그 파일 종류]를 선택합니다.
12. 구성을 클릭하면, 로그 파일 구성 화면이 나타납니다.
13. 구성을 클릭하면, 로그 파일 구성 화면이 나타납니다.
14. [찾아보기]를 클릭하여 로그 파일을 저장할 위치를 선택합니다.
15. 파일이름을 입력합니다.
16. 로그 파일 크기를 선택합니다. [다음으로 제한]할 경우, 제한 파일 크기를 입력합니다.
17. [확인]을 클릭합니다.
18. 파일명의 마지막 부분을 어떻게 설정할 것인지를 선택합니다.
19. 파일명의 마지막 부분을 일련 번호로 할 경우, 시작번호를 설정합니다.
20. 로그 파일의 설명을 입력합니다.
21. [일정]탭을 선택합니다.
22. 로그 시작 시간을 설정합니다.
23. 로그 중지 시간을 설정합니다.
24. 로그 파일을 닫을 때 실행할 명령이 있다면 선택합니다.
25. [확인]을 클릭합니다.
26. 오른쪽 창에 추가한 로그 파일의 목록이 나타납니다.
27. 새로 추가한 성능 로그의 이름위에서 마우스의 오른쪽 버튼을 클릭한 후 [다른 이름으로 설정 저장]으로 설정을 저장해 놓으면, 설정 파일을 재사용할 수 있습니다.
[참고]
성능 카운터에서 서버의 많은 정보를 얻을 수 있습니다. 문제를 확인할 수 있도록, 충분한 시간 동안 필요한 카운터를 수집합니다.
로그 파일 종류를 csv로 선택하여 수집하면, 분석 또는 집계하기에 편리합니다.
수집 시간을 고려하여 데이터 샘플 간격을 설정합니다. 샘플 간격이 커질수록, 그래프의 정확도는 떨어지고, 샘플 간격이 작다면, 데이터의 크기가 커집니다. 수집 시간 샘플 간격
2시간 4 초
1일 30 초
5일 180 초
1일 15초
SQL Server를 모니터링하기 위하여 어떤 서버를 사용할지 결정합니다. 원격으로 모니터링할 수 있으나 장기간 동안 네트워크를 연결하여 카운터를 사용하는 것은 네트워크 트래픽을 가중시킵니다. 만일 SQL Server에 성능 모니터링 로그를 위한 공간이 있다면, 성능 로그 정보를 로컬로 기록합니다.
수집 파일 크기는 적정한 값으로 제한합니다. 수집 파일이 너무 커지면, 파일이 열리지 않는 경우가 있습니다.
기존의 설정 파일(HTM)이 있는 경우에는 [기존 설정에서 새 로그 설정]을 사용하면 쉽게 구성이 가능합니다.
성능 로그의 재생
[따라하기]
1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.
2. [로그 데이터 보기] 버튼을 클릭하면, [시스템모니터 등록 정보] 창이 나타납니다.
3. 로그 파일을 선택하고, 추가 버튼을 클릭하여, 원하는 파일을 추가합니다.
4. [시간 범위]를 클릭하여, 원하는 시간대를 조절합니다.
5. 데이터 탭을 클릭합니다.
6. [추가]를 클릭하여, 원하는 개체를 추가합니다.
7. [확인]을 클릭합니다.
프로필러
성능 문제의 디버깅은 문제의 원인을 알아내는 것으로 시작합니다. 많은 경우, 성능 문제는 비효율적인 SQL 문에서 기인합니다. 비효율적인 SQL 문이 문제의 원인이라고 의심될 때, SQL 프로필러는 특정 SQL 문을 찾는 것에 도움을 줄 수 있습니다. 문제의 원인이 되는 SQL 문을 찾아, 튜닝하여 성능에 많은 도움을 줄 수 있습니다.
추적 수행하기 - GUI 사용
[따라하기]
1. 다음 방법 중 하나를 이용하여 프로필러를 실행합니다.
[시작] → [프로그램] → [Microsoft SQL Server] → [프로필러] 또는 엔터프라이즈 관리자의 상단 메뉴에서 [도구] → [SQL 프로필러]를 선택합니다.
2. [파일] → [새로 만들기] → [추적]을 선택합니다.
3. 원하는 SQL 서버에 연결하면, [추적 속성]창이 나타납니다.
4. 추적이름을 입력합니다.
5. 추적할 SQL Server를 선택합니다.
6. 템플렛을 사용할 경우에 템플렛 이름을 선택합니다.
7. 파일에 저장하려면, [파일에 저장]을 선택하고, 저장할 위치와 파일명을 입력합니다.
8. 최대 파일 크기 설정을 합니다.
9. 이벤트 탭을 선택한 후, 원하는 이벤트를 추가하거나, 제거합니다.
10. 데이터 열 탭을 선택한 후, 수집할 데이터 열을 선택합니다.
11. 필터를 이용하고 싶다면, 필터 탭을 선택하여, 원하는 필터를 정의합니다. 예를 들어, LoginName이 Test인 것만 수집하고 싶다면 다음과 같이 설정합니다.
12. [실행]을 클릭하면, 수집이 시작됩니다.
13. 수집 중지를 위해서, 중지 버튼(붉은 네모)을 클릭합니다.
추적 수행하기 - SP 사용
[파일] → [추적 스크립팅]을 이용하면, 원하는 확장 프로시저를 생성할 수 있습니다.
[추적 스크립팅]을 이용하여 작성한 저장 프로시저를 첨부합니다.
[따라하기]
오래 실행되는 SQL 문 찾기
오래 실행되는 쿼리는 잘못 튜닝된 시스템, 잘못 작성된 응용 프로그램, 또는 단순히 많은 동작을 수행하는 작업등을 의미할 수 있습니다. 어떠한 경우건, 이러한 오래 실행되는 SQL 문을 찾아서 튜닝하는 것은 그 작업의 성능은 물론 전반적인 시스템 성능까지도 향상시킬 수 있습니다.
권장되는 추적 이벤트 : TSQL, SQL:BatchCompleted
정렬 기준 컬럼 : Duration
과도한 자원 사용자 찾기
과도한 자원을 사용하는 응용 프로그램이나 사용자를 찾는 추적은 DBA에게 유용한 도구가 될 수 있습니다. 이러한 추적 유형은 CPU와 I/O 자원 모두를 많이 사용하는 SQL 문을 살펴야 합니다. 프로세스나 사용자를 식별하여, 응용 프로그램을 튜닝할 수 있습니다.
권장되는 추적 이벤트 : TSQL, SQL:BatchCompleted
정렬 기준 컬럼 : CPU, Reads, 및 Writes
교착 상태 알아내기
사용자의 작업에 따라 교착상태는 시스템에서 문제가 될 수도 있고 또 그렇지 않을 수도 있습니다. 많은 경우에 있어 교착 상태는 심각한 문제일 수 있는데, 이 경우 원인을 알아내는 것은 성능을 향상시키는데 핵심이 됩니다. 그러나 이러한 이벤트를 프로파일 하는 것은 자원을 많이 사용하게 되므로 주의해야 합니다.
권장되는 추적 이벤트
TSQL, SQL:BatchStarting 동작하는 SQL 일괄 처리(batch)
Locks, Lock:Deadlock 교착 상태 자체의 이벤트
Locks, Lock:Deadlock Chain 교착 상태에 이르는 이벤트 순서
[참고]
불필요한 이벤트의 추가는 삼가합니다. 너무 많은 이벤트의 추가는 서버의 성능에 영향을 줄 수 있습니다.
일반적인 경우라면, 모든 컬럼들을 포함시킵니다. 어떤 이벤트들은 보조적인 항목을 반환하는 어떤 항목들에 의존합니다. 적어도 다음 컬럼들은 포함하는 것이 좋습니다.
- BinaryData
- ClientProcessID
- CPU
- Duration
- EndTime
- EventClass
- EventSubClass
- HostName
- IntegerData
- LoginName
- NTUserName
- Reads
- SPID
- StartTime
- TextData
- Writes
테이블에 추적을 직접 저장하는 것은 좋지 않습니다. 추적 파일을 생성한 후, fn_trace_gettable 함수를 사용하면 테이블에 저장할 수 있습니다.
추적에 관련된 저장 프로시저의 예제 스크립트
다음은 추적 스크립팅을 저장 프로시저화한 예제 스크립트입니다. 시스템의 환경에 적합하도록 수정 보완하여 활용하기 바랍니다.
추적을 시작하는 저장 프로시저의 예제 스크립트 : sp_trace_start USE master
GO
CREATE PROCEDURE sp_trace_start @TraceFileName sysname=NULL,
@TraceName sysname='trace',
@Options int=2, -- TRACE_FILE_ROLLOVER
@MaxFileSize bigint=5,
@StopTime datetime=NULL,
@Events varchar(300)=
'10,12',
-- 10 - RPC:Completed
-- 12 - SQL:BatchCompleted
@Cols varchar(300)=
'1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,',
-- 모든 컬럼
@IncludeFilter sysname=NULL,
@ExcludeFilter sysname=NULL
AS
SET NOCOUNT ON
-- 변수 선언
DECLARE @TraceId int
DECLARE @On bit
DECLARE @rc int
SET @On=1
-- 이벤트와 컬럼을 확인한다.
IF @Events IS NULL or @Cols IS NULL BEGIN
PRINT 'No Events or Coloumns.'
RETURN -1
END
-- 파일경로와 파일명을 설정한다.
IF @TraceFileName IS NULL
SELECT @TraceFileName = 'c:\Trace\trace_' + CONVERT(CHAR(8),getdate(),112)
-- 추적 큐를 만든다
EXEC @rc =sp_trace_create @TraceId OUT, @Options, @TraceFileName, @MaxFileSize, @StopTime
IF @rc<>0 BEGIN
PRINT 'Trace not started.'
RETURN @rc
END
PRINT 'Trace started.'
PRINT 'The trace file name is '+@TraceFileName+'.'
-- 추적할 이벤트 클래스들과 컬럼들을 지정한다
DECLARE @i int, @j int, @Event int, @Col int, @Colstring varchar(300)
IF RIGHT(@Events,1)<>',' SET @Events=@Events+','
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @Colstring=@Cols
IF RIGHT(@Colstring,1)<>',' SET @Colstring=@Colstring+','
SET @j=CHARINDEX(',',@Colstring)
WHILE @j<>0 BEGIN
SET @Col=CAST(LEFT(@Colstring,@j-1) AS int)
EXEC sp_trace_setevent @TraceId, @Event, @Col, @On
SET @Colstring=SUBSTRING(@Colstring,@j+1 ,300)
SET @j=CHARINDEX(',',@Colstring)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END
-- 필터를 설정한다
EXEC sp_trace_setfilter @TraceId, 10, 0, 7, N'SQL Profiler'
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, N'EXEC% sp_%trace%'
IF @IncludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 6, @IncludeFilter
IF @ExcludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, @ExcludeFilter
-- 추적을 활성화한다
EXEC sp_trace_setstatus @TraceId, 1
-- 추적을 기록한다. (테이블 사용)
IF OBJECT_ID('tempdb..TraceQueueList') IS NULL BEGIN
CREATE TABLE tempdb..TraceQueueList (TraceID int, TraceName varchar(20), TraceFile sysname)
END
IF EXISTS(SELECT * FROM tempdb..TraceQueueList WHERE TraceName = @TraceName) BEGIN
UPDATE tempdb..TraceQueueList
SET TraceID = @TraceId, TraceFile = @TraceFileName
WHERE TraceName = @TraceName
END
ELSE BEGIN
INSERT tempdb..TraceQueueList
VALUES(@TraceId, @TraceName, @TraceFileName)
END
RETURN 0
GO
/* 실행 하기 */
EXEC sp_trace_Start
[참고]
output 파일을 지정하지 않으면, "c:\Trace"밑에 추적 파일이 생성됩니다. 이 스크립트를 직접 실행하려면, "c:\Trace"를 생성합니다.
추적 파일이 커질 수도 있으므로, output 파일이 생성되는 곳의 공간을 충분히 확보합니다.
원하는 이벤트와 컬럼은 번호로 설정합니다. 이벤트와 컬럼 번호는 BOL을 참조하십시오.
추적을 중지하는 저장 프로시저의 예제 스크립트 : sp_trace_stop
USE master
GO
CREATE PROCEDURE sp_trace_stop @TraceName sysname='trace'
AS
SET NOCOUNT ON
-- 변수를 선언한다
DECLARE @TraceId int
DECLARE @TraceFileName sysname
-- 추적 목록을 확인하여, 추적을 중지한다
IF OBJECT_ID('tempdb..TraceQueueList') IS NOT NULL BEGIN
SELECT @TraceId = TraceID, @TraceFileName=TraceFile
FROM tempdb..TraceQueueList
WHERE TraceName = @TraceName
IF @@ROWCOUNT<>0 BEGIN
EXEC sp_trace_setstatus @TraceId, 0
EXEC sp_trace_setstatus @TraceId, 2
DELETE tempdb..TraceQueueList
WHERE TraceName = @TraceName
PRINT 'Trace is stopped. '
+ 'The trace output file name is '+@TraceFileName
END
ELSE
PRINT 'No active traces.'
END
ELSE
PRINT 'No active traces.'
RETURN 0
GO
/* 실행하기 */
EXEC sp_trace_stop
[참고] sp_trace_stop은 sp_trace_start로 실행한 추적(Trace)를 중지하는 저장 프로시저입니다.
추적 재생하기
1. 프로필러를 시작하고, [파일] → [열기] → [추적 파일]을 선택합니다.
2. 원하는 파일을 선택합니다.
유용한 유틸리티
PSSDIAG
PSSDIAG는 성능 분석에 필요한 여러가지 로그와 데이터를 수집할 수 있는 유틸리티입니다. 이 유틸리티를 사용하면 프로필러에 비해 부하를 덜 주면서 추적 데이터를 수집할 수 있으므로, 대용량 시스템의 경우에는 프로필러 대신 이 툴을 사용할 것을 권고합니다. 자세한 내용은 다음 url을 참조하십시오.
http://support.microsoft.com/?kbid=830232
Read80Trace
수집한 추적 정보를 분석하는데 매우 유용한 유틸리티입니다. 추적 데이터를 분석하여 로컬 데이터베이스에 분석한 정보를 저장해 주고, htm 파일 형태로 리소스를 많이 사용한 쿼리 또는 저
[따라하기]
Employees 테이블에 관련된 컬럼, 인덱스, 제약 조건 등의 정보를 반환합니다.
USE Northwind
GO
EXEC sp_help Employees
GO
테이블이 사용하는 공간 확인하기
[구문] sp_spaceused [[@objname =] 'objname']
[,[@updateusage =] 'updateusage']
[따라하기] 데이터베이스 내의 모든 테이블의 사용 공간 확인하기
/ 방법1. 기존의 시스템 SP를 단순히 활용한 예제 */
EXEC sp_MSforeachtable 'EXEC sp_spaceused [?], ''TRUE'''
GO
/* 방법2. 기존의 시스템 SP를 활용하여 결과를 테이블에 저장한 예제 */
USE DBAdmin
GO
CREATE TABLE spaceused_pubs (
TableName sysname,
Rows int,
Reserved varchar(20),
Data varchar(20),
Index_size varchar(20),
Unused varchar(20))
GO
USE pubs
GO
INSERT INTO DBAdmin..spaceused_pubs
EXEC sp_MSforeachtable 'EXEC sp_spaceused [?], ''TRUE'''
GO
SELECT * FROM DBAdmin..spaceused_pubs
GO
/* 방법3. sp_spaceused의 소스 코드를 수정하여 사용하기 */
CREATE PROCEDURE sp_spaceused_all
AS
/*
작성자 : 전현경
내용 : 데이터베이스내의 모든 테이블의 크기
계산 근거 :
reserved: sum(reserved) where indid in (0, 1, 255)
data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
indexsize: sum(used) where indid in (0, 1, 255) - data
unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
SET NOCOUNT ON
DECLARE @Low bigint
SELECT @Low = low FROM mASter.dbo.spt_values
WHERE number = 1 and type = 'E'
SELECT OBJECT_NAME(tmp.id) AS Name
,convert(char(11),sum(rows)) AS Rows
,ltrim(str(sum(tmp.reserved) * @Low / 1024.,15,0) + ' ' + 'KB') AS Reserved
,ltrim(str(sum(tmp.data) * @Low / 1024.,15,0) + ' ' + 'KB') AS Data
,ltrim(str((sum(tmp.used) - sum(tmp.data)) * @Low / 1024.,15,0) + ' ' + 'KB') AS Index_Size
,ltrim(str((sum(tmp.reserved) - sum(tmp.used)) * @Low / 1024.,15,0) + ' ' + 'KB') AS Unused
FROM (
SELECT obj.id AS id, sum(ind.rows) AS rows, sum(ind.reserved) AS reserved, sum(dpages) AS data,
isnull(sum(used), 0) AS used
FROM sysindexes ind JOIN sysobjects obj ON ind.id = obj.id
WHERE obj.xtype='U' AND ind.indid < 2
GROUP BY obj.id
UNION
SELECT obj.id AS id, 0 AS rows, sum(ind.reserved) AS reserved, isnull(sum(used), 0) AS data,
isnull(sum(used), 0) AS used
FROM sysindexes ind JOIN sysobjects obj ON ind.id = obj.id
WHERE obj.xtype='U' AND ind.indid = 255
GROUP BY obj.id ) tmp
GROUP BY tmp.id
ORDER BY sum(tmp.data) desc, sum(tmp.used) desc
SET NOCOUNT OFF
GO
테이블 옵션 설정하기
사용자 정의 테이블의 옵션 값을 설정합니다.
[구문] sp_tableoption [ @TableNamePattern = ] 'table'
[@OptionName = ] 'option_name'
[@OptionValue = ] 'value'
[따라하기]
A. Orders 테이블에 'text in row' 옵션 설정하기
'text in row' 옵션을 설정하면, Text, ntext, image 컬럼의 행에 저장할 최대 크기를 지정할 수 있습니다. 기본값은 256바이트이고, 값의 범위는 24에서 7000바이트입니다. 다음은 Orders 테이블의 text 컬럼에 저장할 데이터를 1000바이트로 지정합니다.
EXEC sp_tableoption 'orders', 'text in row', '1000'
GO
-- 설정값 확인
USE Northwind
GO
SELECT OBJECTPROPERTY(OBJECT_ID('orders'),'TableTextInRowLimit')
GO
B. Orders 테이블에 'pintable' 옵션 설정하기
'pintable' 옵션을 설정하면, 지정한 테이블의 데이터가 메모리에 상주합니다. 테이블 크기가 작고, 자주 사용하는 코드 테이블을 대상으로 사용할 수 있습니다.
USE Northwind
GO
EXEC sp_tableoption 'Orders', 'pintable', 'on'
GO
--메모리 고정 테이블 확인
SELECT OBJECTPROPERTY (OBJECT_ID('Orders'), 'TableIsPinned')
GO
[참고] 메모리에 테이블의 데이터를 상주시키기 위해 DBCC PINTABLE을 사용할 수도 있습니다.
-- 메모리 고정
DECLARE @objid int, @dbid int
SELECT @dbid = DB_ID('Northwind'), @objid = OBJECT_ID('Northwind..Orders')
DBCC PINTABLE (@dbid, @objid)
GO
-- 메모리 고정 해제
DECLARE @objid int, @dbid int
SELECT @dbid = DB_ID('Northwind'), @objid = OBJECT_ID(' Northwind..Orders')
DBCC UNPINTABLE (@dbid, @objid)
GO
-- 데이터베이스내의 메모리 고정 테이블의 전체 크기 확인
SELECT sum(i.used * 8) AS [pin table space used (KB)]
FROM sysindexes i JOIN sysobjects o ON i.id = o.id
WHERE o.status & 1048576 <> 0 AND indid < 2
GO
[주의]
이 기능은 성능을 향상시킬 수 있지만 주의해서 사용해야 합니다. 커다란 테이블을 고정할 경우 많은 용량의 버퍼 캐시를 사용하기 때문에 다른 테이블에서 사용할 캐시가 부족하게 됩니다. 버퍼 캐시보다 용량이 큰 테이블을 고정하면 전체 버퍼 캐시를 채울 수도 있습니다. 이런 경우 sysadmin 고정 서버 역할의 구성원이 SQL Server를 중지한 후 다시 시작한 다음, 테이블을 고정 해제해야 합니다. 너무 많은 테이블을 메모리에 고정해도 이와 같은 문제가 발생할 수 있습니다.
시스템 오브젝트 생성
시스템 저장 프로시저 생성하기
시스템 저장 프로시저는 master 데이터베이스에 있으며 이름이 sp_ 라는 접두사로 시작하며, 모든 데이터베이스에서 master 데이터베이스라고 지정하지 않고 저장 프로시저의 이름 만으로 실행이 가능합니다. 그리고 master 데이터베이스가 아닌 데이터베이스에서 실행하면 그 데이터베이스의 컨텍스트 내에서 수행되는 특징을 가지고 있습니다. 예를 들어 저장 프로시저가 sysobjects 테이블을 참조한다고 가정하면 이 프로시저는 실제로는 master에 있음에도 불구하고 그 프로시저를 실행할 때 연결되어 있던 데이터베이스에 있는 sysobjects를 액세스합니다.
[따라하기] 시스템 저장 프로시저를 생성하기
1. master 데이터베이스에 연결합니다.
2. sp_ 로 시작하는 이름으로 저장 프로시저를 생성합니다.
시스템 함수 조회 및 생성하기
SQL Server가 설치되는 동안 시스템 UDF(사용자 정의 함수)들이 생성되며, 이 시스템 함수는 모든 데이터베이스에서 함수의 이름만 사용하여 액세스가 가능합니다.
시스템 뷰를 만드는 것과 유사하게 master 데이터베이스에 존재하지만 모든 데이터베이스에서 데이터베이스를 지정하지 않고 쿼리를 수행할 수 있는 함수를 사용자가 직접 생성할 수 있습니다. 시스템 함수가 되기 위해서는 함수를 생성할 때 소유자를 system_function_schema로 생성합니다.
[따라하기] 시스템 함수 생성, 조회하기
/* 시스템 함수 생성하기 */
-- 시스템 테이블을 직접 수정할 수 있도록 설정합니다.
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
GO
-- 함수는 master 데이터베이스에 생성하고,
-- 소유자는 system_function_schema로 지정합니다.
USE master
GO
CREATE FUNCTION system_function_schema.fn_greatest (@x bigint, @y bigint)
RETURNS bigint
AS
BEGIN
RETURN(CASE WHEN @x>@y THEN @x ELSE @y END)
END
GO
CREATE FUNCTION system_function_schema.fn_least (@x bigint, @y bigint)
RETURNS bigint
AS
BEGIN
RETURN(CASE WHEN @x<@y THEN @x ELSE @y END)
END
GO
-- 시스템 테이블을 직접 수정할 수 없도록 0으로 변경합니다. (반드시 수행 요망)
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO
-- 생성한 시스템 함수는 모든 데이터베이스에서 호출 가능합니다.
USE Northwind
GO
SELECT fn_greatest(989, 998), fn_least(989, 998)
GO
/* 시스템 함수 조회하기 */
USE master
GO
SELECT name FROM sysobjects
WHERE uid=USER_ID('system_function_schema')
AND (OBJECTPROPERTY(id, 'IsScalarFunction')=1
OR OBJECTPROPERTY(id, 'IsTableFunction')=1
OR OBJECTPROPERTY(id, 'IsInlineFunction')=1)
GO
INFORMATION 스키마 뷰 생성하기
SQL Server 2000에서 메타데이터 정보를 가져오는 방법은 시스템 저장 프로시저를 사용하는 방법과 INFORMATION 스키마 뷰를 사용하는 방법의 두 가지가 있습니다.
[참고] 메타데이터를 가져오기 위해서는 시스템 저장 프로시저, 시스템 함수 또는 시스템 제공 뷰를 사용할 것을 권고합니다. 시스템 테이블을 직접 쿼리하는 경우, 시스템 테이블이 이후 버전에서 변경될 때 정확한 정보를 제공하지 못할 수도 있습니다. 정보 스키마 뷰를 참조할 때는 다음과 같이 사용자 이름을 지정하는 위치에 INFORMATION_SCHEMA를 지정해야 합니다.
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
[SQL Server 2000에서 제공하는 INFORMATION 스키마 뷰 목록]
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
[따라하기]
CHECK 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰는 있지만 DEFAULT 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰는 기본적으로 제공되지 않습니다. 이 예제에서는 기존의 CHECK_CONSTRAINTS라는 INFORMATION 스키마 뷰의 소스를 수정하여 DEFAULT 제약 조건에 관한 정보를 제공해 주는 INFORMATION 스키마 뷰를 만들어 보고자 합니다.
-- 기본으로 제공되는 INFORMATION 스키마 뷰 활용하기
USE Pubs
GO
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
GO
-- 사용자가 INFORMATION 스키마 뷰 생성하기
USE master
GO
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_MS_upd_sysobj_category 1
GO
CREATE VIEW INFORMATION_SCHEMA.DEFAULT_CONSTRAINTS
AS
SELECT db_name() as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name as CONSTRAINT_NAME
,com.text as DEFAULT_CLAUSE
FROM sysobjects c_obj, syscomments com
WHERE c_obj.uid = user_id()
AND c_obj.id = com.id
AND c_obj.xtype = 'D'
GO
EXEC sp_MS_upd_sysobj_category 2
GO
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
-- 생성한 INFORMATION 스키마 뷰 활용하기
USE pubs
GO
SELECT *
FROM INFORMATION_SCHEMA.DEFAULT_CONSTRAINTS
GO
사용자 관리
로그인과 사용자 이해하기
SQL Server에 접근하기 위해서는 로그인 계정이 필요하고, 데이터베이스에 접근하기 위해서는 사용자(user)가 필요합니다.
권한 이해하기
사용자는 권한을 받아 작업을 수행할 수 있습니다. 권한에는 문장을 실행할 수 있는지에 따라 권한을 제한하는 명령문(Statement) 사용권한과 테이블, 색인, 뷰, 프로시저에 따라 권한을 제한하는 개체(Object) 사용권한이 있습니다.
역할 이해하기
역할은 로그인 또는 사용자들의 집합이고, 역할에 권한이 설정되어 있습니다. 그러므로, 각 사용자에게 권한을 주는 것이 아니라, 그 권한이 있는 역할에 사용자를 추가시키는 것이 좋습니다. 시스템에서 미리 정의된 역할에는 로그인에게 사용할 수 있는 서버 역할과 사용자에게 사용할 수 있는 데이터베이스 역할 이 있습니다.
로그인 계정 생성하고 권한 부여하기
[따라하기]
SQL Server 계정을 생성하여, Sample 데이터베이스에 사용자를 등록하고, 데이터베이스내의 테이블 읽는 권한을 주고, 서버내의 서버 차원의 설정을 구성할 수 있는 권한을 설정합니다. 고정 데이터베이스 역할 db_datareader은 데이터베이스내의 테이블을 읽는 권한을 가지고 있고, 서버 데이터베이스 역할 serveradmin은 서버차원을 설정을 구성합니다.
EXEC sp_addlogin 'dbadmin','ad1234','sample'
GO
EXEC sp_addsrvrolemember 'dbadmin', 'serveradmin'
GO
USE Sample
GO
EXEC sp_adduser 'dbadmin', 'dbadmin', 'db_datareader'
GO
NT 로그인 계정을 생성하여, sample 데이터베이스에 사용자로 등록하고, 데이터베이스내의 테이블 읽는 권한을 설정합니다.
NT 로그인 계정은 윈도우의 [내 컴퓨터 관리]나 [액티브 디렉터리 사용자 및 컴퓨터]를 이용하여 생성합니다. 생성된 계정은 Admin\winadmin이라고 가정합니다.
EXEC sp_grantlogin 'Admin\winadmin'
GO
EXEC sp_defaultdb 'Admin\winadmin', 'sample'
GO
USE Sample
GO
EXEC sp_grantdbaccess 'Admin\winadmin', 'winadmin'
GO
EXEC sp_addrolemember 'db_datareader', 'winadmin'
GO
SQL Server 로그인은 'backupAdmin', 암호는 '1234'로 설정하여, sample 데이터베이스의 사용자로 등록하고, 데이터베이스 백업을 수행할 수 있는 권한을 부여합니다. 그 후, backupAdmin 로그인 계정을 삭제합니다. 고정 데이터베이스 역할 중에서 db_backupoperator 역할은 데이터베이스 백업을 수행할 수 있는 권한을 가지고 있습니다.
EXEC sp_addlogin 'backupAdmin','1234','sample'
GO
USE Sample
GO
EXEC sp_adduser 'backupAdmin', 'backupAdmin', 'db_backupoperator'
GO
-- 로그인 계정 삭제
EXEC sp_droprolemember 'db_backupoperator', 'backupAdmin'
GO
EXEC sp_revokedbaccess 'backupAdmin'
GO
EXEC sp_droplogin 'backupAdmin'
GO
NETDOMAIN의 John. Sarah, Diane 계정을 courses 데이터베이스의 사용자로 등록하고, Student 역할에 등록합니다. 그 후, Student 역할에서 Diane 계정을 삭제하고, 로그인 계정에서도 Diane을 삭제합니다.
USE master
GO
EXEC sp_grantlogin 'NETDOMAIN\John'
GO
EXEC sp_defaultdb 'NETDOMAIN\John', 'courses'
GO
EXEC sp_grantlogin 'NETDOMAIN\Sarah'
GO
EXEC sp_defaultdb 'NETDOMAIN\Sarah', 'courses'
GO
EXEC sp_grantlogin 'NETDOMAIN\Diane'
GO
EXEC sp_defaultdb 'NETDOMAIN\Diane', 'courses'
GO
USE courses
GO
EXEC sp_grantdbaccess 'NETDOMAIN\John'
GO
EXEC sp_grantdbaccess 'NETDOMAIN\Sarah'
GO
EXEC sp_grantdbaccess 'NETDOMAIN\Diane'
GO
EXEC sp_addrole 'Student'
GO
EXEC sp_addrolemember 'Student','NETDOMAIN\John'
GO
EXEC sp_addrolemember 'Student','NETDOMAIN\Sarah'
GO
EXEC sp_addrolemember 'Student','NETDOMAIN\Diane'
GO
EXEC sp_droprolemember 'Student','NETDOMAIN\Diane'
GO
EXEC sp_revokedbaccess 'NETDOMAIN\Diane'
GO
EXEC sp_revokelogin 'NETDOMAIN\Diane'
GO
[참고]
A. sp_revokelogin
sp_grantlogin 또는 sp_denylogin으로 만든 NT 사용자 또는 로그인 항목을 SQL Server에서 제거합니다. NT 로그인 계정이 SQL Server에 연결하는 것이 금지됩니다.
B. sp_denylogin
NT 로그인 계정이 SQL Server에 연결하는 것을 금지합니다.
기존 로그인과 사용자의 확인하기
[따라하기]
EXEC sp_helplogins
GO
EXEC sp_helpuser
GO
암호 변경하기
[따라하기]
로그인 계정 'dbadmin'의 암호 'ad1234'를 'ad5678'로 변경합니다.
EXEC sp_password 'ad1234','ad5678', 'dbadmin'
GO
서버 및 데이터베이스의 정보 확인
서버 이름, 버전, Edition 확인하기
[구문] SERVERPROPERTY ( propertyname )
[따라하기]
SELECT SERVERPROPERTY('ServerName') AS ServerName
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('InstanceName') AS InstanceName
, SERVERPROPERTY('Edition') AS Edition
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') AS ProductLevel
GO
서버의 옵션 확인하기
서버의 구성 옵션과 구성 옵션의 최소값과 최대값, 설정된 대상 값, 설정값을 확인합니다.
[구문] sp_configure [ [ @configname = ] 'name' ]
[ , [ @configvalue = ] 'value' ]
[따라하기]
1. 서버 구성 옵션 확인하기
EXEC sp_configure
GO
2. 서버의 고급 구성 옵션 확인하기
서버의 일부 옵션은 고급으로 지정되어 있고, 이 값들도 변경할 수 있습니다. 고급 구성 옵션을 보기 위해서는 다음을 실행합니다.
USE master
GO
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
데이터베이스 파일에 대한 I/O 통계 정보 확인하기
fn_virtualfilestats 함수는 시스템에서 기본으로 제공하는 함수로서, I/O에 대한 통계 정보를 제공합니다. 사용자들이 어떤 파일에 대하여 읽기나 쓰기를 수행하기 위하여 기다린 시간을 제공하므로 이 함수를 사용하면 어떤 파일들에 대하여 I/O가 많이 발생하는지 확인할 수 있습니다. IO로 인한 성능 저하가 의심되는 경우에는 fn_virtualfilestats 함수가 반환하는 IOStallMS를 점검할 것을 권고합니다.
[구문] fn_virtualfilestats ( [@DatabaseID=] database_id
, [ @FileID = ] file_id )
[따라하기]
1. 모든 데이터베이스의 파일들의 I/O 정보 확인하기
SELECT * FROM ::fn_virtualfilestats(-1, -1)
GO
2. 모든 데이터베이스의 파일들의 I/O 정보 확인하기 (시스템 함수 활용)
SELECT DB_NAME(Dbid) AS DBName
, FileId
, FILE_NAME(FileId) AS FileName
, IoStallMS, NumberReads, NumberWrites, BytesRead
, BytesWritten
FROM ::fn_VirtualFileStats (-1, -1)
ORDER BY IoStallMS DESC
GO
3. 특정 데이터베이스의 모든 파일들에 대한 I/O 정보 확인하기 (예제: tempdb)
SELECT * FROM ::fn_virtualfilestats(2, -1)
GO
4. 특정 데이터베이스의 특정 파일의 I/O 정보 확인하기 (Tempdb의 Primary Data File)
SELECT * FROM ::fn_virtualfilestats(2, 1)
GO
5. 특정 데이터베이스의 특정 파일의 I/O 정보 확인하기 (Tempdb의 로그 파일)
SELECT * FROM ::fn_virtualfilestats(2, 2)
GO
기본적인 파일 정보 확인하기
현재 데이터베이스와 연관된 파일의 물리적 이름과 특징을 반환합니다. 파일 이름을 지정하지 않으면, 데이터베이스내의 모든 파일에 대한 정보를 확인할 수 있습니다.
[구문] sp_helpfile [ [ @filename = ] 'name' ]
[따라하기]
USE Sample
EXEC sp_helpfile
-- 또는
EXEC Sample..sp_help
기본적인 파일 그룹 정보 확인하기
현재 데이터베이스와 연관된 파일그룹의 이름과 특징을 반환합니다. 파일그룹의 이름을 지정하지 않으면, 데이터베이스내의 모든 파일그룹에 대한 정보를 확인할 수 있습니다.
[구문] sp_helpfilegroup [ [ @filegroupname = ] 'name' ]
기본적인 데이터베이스의 정보 확인하기
지정된 데이터베이스 또는 모든 데이터베이스 정보를 반환합니다.
[구문] sp_helpdb [ [ @dbname= ] 'name' ]
데이터베이스를 지정하지 않았을 경우, 결과 집합입니다. 컬럼명 내용
Name 데이터베이스 이름
Db_size 데이터베이스의 총 크기
Owner 데이터베이스의 소유자
Dbid 데이터베이스의 ID
Created 데이터베이스의 생성일자
Status 옵션의 값을 쉼표로 분리하여 나열한 것
Compatibility_level 호환성 수준(60,65,70,80)
데이터베이스를 지정했을 경우, 위의 결과 집합에 다음의 결과 집합이 추가로 나타납니다. Name 논리적 파일 이름
Fileid 파일 ID
Filename 파일의 물리적 경로와 이름
Filegroup 파일이 속한 그룹
Size 파일 크기
Maxsize 파일이 증가할 수 있는 최대 크기
Growth 파일의 증가량
Usage 파일의 사용법
데이터베이스 옵션 또는 현재 설정 확인하기
지정한 Property에 따른 결과값이 반환됩니다. property의 값 목록과 그에 따른 결과의 종류는 BOL 참조해 주십시오.
[구문] DATABASEPROPERTYEX(database_name, property)
[따라하기]
1. Northwind 데이터베이스의 통계 자동 업데이트 옵션 설정 확인하기
SELECT DATABASEPROPERTYEX ('Northwind','IsAutoUpdateStatistics')
AS IsAutoUpdateStatistics
GO
2. Northwind 데이터베이스의 복구 모델 설정 확인하기
SELECT DATABASEPROPERTYEX ('Northwind','Recovery') AS Recovery
GO
데이터베이스 옵션 설정 확인하기
[구문] sp_dboption [ [ @dbname = ] 'database' ]
[ , [ @optname = ] 'option_name' ]
[ , [ @optvalue = ] 'value' ]
[따라하기]
A. 설정 가능한 옵션의 목록 확인하기
인수를 지정하지 않습니다.
EXEC sp_dboption
GO
B. 지정한 데이터베이스의 설정 옵션 목록 확인하기
데이터베이스만을 지정합니다. Northwind데이터베이스의 설정 옵션 목록을 확인합니다.
EXEC sp_dboption 'Northwind'
GO
C. 지정한 데이터베이스의 해당 옵션의 설정 상태 확인하기
데이터베이스와 해당 옵션을 설정합니다. Northwind데이터베이스의 자동 통계 갱신 옵션의 설정을 확인합니다.
EXEC sp_dboption 'Northwind', 'auto update statistics'
GO
데이터베이스 옵션의 종류 Auto create statistics
Auto update statistics
Autoclose
Autoshrink
ANSI null default
ANSI nulls
ANSI padding
ANSI warnings
Arithabort
concat null yields null
cursor close on commit
Dbo use only
default to local cursor
merge publish
numeric roundabort
Offline
Published
quoted identifier
read only
Recursive triggers
select into/bulkcopy
single user
Subscribed
Torn page detection
trunc. Log on chkpt.
Tempdb에 대한 공간 사용 정보 확인하기
[구문] sp_tempdbspace
테이블 크기 확인하기
[구문] sp_spaceused [[@objname =] 'objname']
[,[@updateusage =] 'updateusage']
[따라하기] Orders 테이블의 크기 확인하기
EXEC sp_spaceused 'Orders'
[참고] 테이블을 지정하지 않으면, 해당 데이터베이스의 크기가 반환됩니다.
로그 공간의 사용 정보 확인하기
서버 내에 존재하는 모든 데이터베이스의 로그 공간의 사용에 관한 통계를 확인합니다.
[구문] DBCC SQLPERF ( LOGSPACE )
테이블의 조각화 정보 확인하기
테이블의 조각화는 INSERT, UPDATE, DELETE문 등의 데이터를 수정할 때에 발생합니다. 이러한 수정들은 각 페이지의 채움 정도를 다르게 만들고, 테이블의 일부 또는 전부를 스캔하는 쿼리의 경우, 테이블 조각으로 인한 성능 저하가 발생할 가능성이 있습니다.
[구문] DBCC SHOWCONTIG
[ ( { table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
)
]
[ WITH { ALL_INDEXES
| FAST [ , ALL_INDEXES ]
| TABLERESULTS [ , { ALL_INDEXES } ]
[ , { FAST | ALL_LEVELS } ]
}
]
[따라하기]
A. 지정한 테이블의 조각화 정보 확인하기
Orders 테이블의 조각화 정보를 확인합니다.
USE Northwind
GO
DBCC SHOWCONTIG ('Orders')
GO
B. 지정한 테이블의 지정한 인덱스의 조각화 정보 확인하기
Orders 테이블의 CustomerID 인덱스에 대한 조각화 정보를 확인합니다.
USE Northwind
GO
DBCC SHOWCONTIG ('Orders', 'CustomerID')
GO
C. 테이블 ID와 인덱스 ID로 조각화 정보 확인하기
테이블 이름과 인덱스 이름 대신, 테이블 ID와 인덱스 ID를 사용합니다.
USE Northwind
GO
DECLARE @ObjectId int, @IndexId int
SELECT @ObjectId = OBJECT_ID ('Orders')
SELECT @IndexId = indid FROM sysindexes
WHERE id = @ObjectId
DBCC SHOWCONTIG (@ObjectId, @IndexId)
GO
D. 데이터베이스내의 모든 테이블의 모든 인덱스에 대한 조각화 정보 확인하기
USE Northwind
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
GO
E. 데이터베이스에 수행 결과를 저장하기
-- 결과를 저장할 테이블 생성하기
USE DBAdmin
GO
CREATE TABLE ShowContig_Pubs
(
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId tinyint,
Level tinyint,
Pages int,
Rows bigint,
MinimumRecordSize smallint,
MaximumRecordSize smallint,
AverageRecordSize smallint,
ForwardedRecords bigint,
Extents int,
ExtentSwitches numeric(10,2),
AverageFreeBytes numeric(10,2),
AveragePageDensity numeric(10,2),
ScanDensity numeric(10,2),
BestCount int,
ActualCount int,
LogicalFragmentation numeric(10,2),
ExtentFragmentation numeric(10,2),
CheckDate smalldatetime DEFAULT (GETDATE())
)
GO
-- 시스템 저장 프로시저 생성하기
USE master
GO
CREATE PROCEDURE sp_DBCCSHOWCONTIG
AS
DBCC SHOWCONTIG WITH TABLERESULTS
GO
-- 실행 예제
USE Pubs
GO
INSERT INTO DBAdmin..ShowContig_Pubs (
ObjectName, ObjectId, IndexName, IndexId, Level, Pages,
Rows, MinimumRecordSize, MaximumRecordSize,
AverageRecordSize, ForwardedRecords, Extents,
ExtentSwitches, AverageFreeBytes, AveragePageDensity,
ScanDensity, BestCount, ActualCount, LogicalFragmentation,
ExtentFragmentation)
EXEC sp_DBCCSHOWCONTIG
GO
-- 실행 결과 확인하기
SELECT * FROM DBAdmin..ShowContig_Pubs
WHERE ObjectName = 'Authors'
GO
SELECT * FROM DBAdmin..ShowContig_Pubs
WHERE LogicalFragmentation > 30
OR ExtentFragmentation > 30
GO
[참고] WITH FAST 옵션을 사용하면, 인덱스의 잎 또는 데이터 수준 페이지를 읽지 않기 때문에, 생략된 정보를 빠르게 반환합니다.
[주의] 크기가 큰 테이블에 대해서는 서비스 중에 DBCC SHOWCONTIG를 수행하면 성능 저하를 유발할 수 있으므로 유의합니다.
[권고사항] 수행 결과를 확인하고 단편화가 심한 테이블들에 대해서는 인덱스 재구성 작업을 수행합니다.
대기 정보 확인하기
각 waittype별 대기 시간을 확인합니다. 가장 대기가 많은 유형을 확인하고, 작업 부하에 따른 대기 유형의 변화도 확인합니다.
[구문] DBCC SQLPERF (WAITSTATS)
인덱스 재구성하기
인덱스 조각화로 인한 성능 저하를 방지하기 위해서는 주기적으로 인덱스 조각화가 진행된 테이블들에 대한 조각화 제거 작업이 필요합니다. 가능한 한 인덱스 재구성 작업을 자동화하여 주기적으로 용이하게 수행할 수 있는 체계를 갖출 것을 권고합니다.
[주의] DBCC DBREINDEX 작업은 서비스 휴지 시간에 수행해야 합니다.
[예제 스크립트] 다음은 인덱스 조각화 제거를 위해 활용할 수 있는 예제 스크립트입니다. 아래 예제 스크립트를 시스템의 환경에 적합하도록 수정 보완하여 활용하기 바랍니다.
----------------------------------------------------------------------------------------------------------------
-- Written by Kimberly L. Tripp - all rights reserved.
-- For more scripts, sample code and Kimberly's schedule check out
-- www.SQLSkills.com
-- For "More than Just Training," see the Industry Experts at
-- www.SolidQualityLearning.com
-- Disclaimer - Thoroughly test this script, execute at your own risk.
----------------------------------------------------------------------------------------------------------------
/*
Execute this whole script to create the sp_RebuildIndexes stored procedure in Master.
Best Viewed with Courier New 12pt. and Tabs saved as 4 spaces not 8. (Tools, Options, Editor)
To use the sp_RebuildIndexes procedure once created use:
sp_RebuildIndexes
To Rebuild All Indexes on All Tables for all that have a Scan Density < 100%
sp_RebuildIndexes @ScanDensity = 80
To Rebuild All Indexes on All Tables with a Scan Density of < 80%
sp_RebuildIndexes 'Authors'
To Rebuild All Indexes on the authors table - for a Scan Density of < 100%
sp_RebuildIndexes 'Authors', 80
To Rebuild All Indexes on the authors table - for a Scan Density of < 80%
Object Name and ScanDensity are both optional parameters.
ScanDensity must be a whole number between 1 and 100.
*/
USE master
GO
IF OBJECTPROPERTY(object_id('sp_RebuildClusteredIndex'), 'IsProcedure') = 1
DROP PROCEDURE sp_RebuildClusteredIndex
GO
IF OBJECTPROPERTY(object_id('sp_RebuildIndexes'), 'IsProcedure') = 1
DROP PROCEDURE sp_RebuildIndexes
GO
CREATE PROCEDURE sp_RebuildClusteredIndex
(
@TableName sysname = NULL,
@IndexName sysname = NULL
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- NOTE: If your clustered index is NOT unique then rebuilding the clustered
-- index will cause the non-clustered indexes to be rebuilt.
-- If the nonclustered indexes were fragmented
-- then this series of scripts will build them again.
IF @TableName IS NOT NULL
BEGIN
IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
AND OBJECTPROPERTY(object_id(@TableName), 'IsView') = 0)
BEGIN
RAISERROR('Object: %s exists but is NOT a User-defined Table.
This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
ELSE
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
BEGIN
RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?).
This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
END
END
IF @IndexName IS NOT NULL
BEGIN
IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') = 0
BEGIN
RAISERROR('Index: %s exists but is a Clustered Index.
This procedure only accepts valid table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
RETURN
END
ELSE
BEGIN
IF INDEXPROPERTY(object_id(@TableName),
@IndexName, 'IsClustered') IS NULL
BEGIN
SELECT @TableName, @IndexName,
INDEXPROPERTY(object_id(@TableName),
@IndexName, 'IsClustered')
RAISERROR('There is no index with name:%s on this table.
Please check the table name and index name as well as location (which database?).
This procedure only accepts existing table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
RETURN
END
END
END
-- So now we have a valid table, a valid CLUSTERED index and we're ready to rebuild.
-- Here's a quick overview of what this code will do:
-- Get the Column List and Index Defintion (Use the output from sp_helpindex)
-- Figure out if it's UNIQUE - to specify in CREATE INDEX statement
-- Build and Execute the CREATE INDEX command through dynamic string execution
DECLARE @ExecStr nvarchar(4000)
-- more than enough even if 16 cols of 128 chars,
-- Tablename of 128 and Indexname of 128...
-- but if this is the case you have other problems :).
, @ColList nvarchar(3000)
, @Unique nvarchar(7)
-- Will be either '' or 'Unique ' and added to CR Index String
, @FillFactor nvarchar(100)
CREATE TABLE #IndexInfo
(
IndexName sysname,
IndexDesc varchar(210),
IndexKeys nvarchar(2126)
)
INSERT INTO #IndexInfo EXEC sp_helpindex @TableName
SELECT @ColList = IndexKeys
, @Unique = CASE
WHEN IndexDesc LIKE 'clustered, unique%'
THEN 'Unique '
ELSE ''
END --CASE Expression
, @FillFactor = ', FILLFACTOR = ' + NULLIF(convert(nvarchar(3)
, (SELECT OrigFillFactor
FROM sysindexes
WHERE id = object_id(@TableName) AND Name = @IndexName)), 0)
FROM #IndexInfo
WHERE IndexName = @IndexName
SELECT @ExecStr = 'CREATE ' + @Unique + 'CLUSTERED INDEX '
+ QUOTENAME(@IndexName, ']') + ' ON '
+ QUOTENAME(@TableName, ']') + '(' + @collist
+ ') WITH DROP_EXISTING ' + ISNULL(@FillFactor, '')
-- For testing the String
-- SELECT @ExecStr
-- Create the Clustered Index
EXEC(@ExecStr)
GO
CREATE PROCEDURE sp_RebuildIndexes
(
@TableName sysname = NULL,
@ScanDensity tinyint = 100
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- This procedure will get the Fragmentation information for all tables and indexes
-- within the database.
-- Programmatically it will then walk the list rebuilding all indexes that have a scan
-- density less than the value passed in - by default any less than 100% contiguous.
-- Use this script as a starting point. Modify it for your options, ideas, etc.
-- and then schedule it to run regularly.
-- NOTE - This gathers density information for all tables and all indexes.
-- This might be time consuming on large databases.
-- DISCLAIMER - Execute at your own risk. TEST THIS FIRST.
SET NOCOUNT ON
IF @ScanDensity IS NULL
SET @ScanDensity = 100
IF @ScanDensity NOT BETWEEN 1 AND 100
BEGIN
RAISERROR('Value supplied:%i is not valid. @ScanDensity is a percentage.
Please supply a value for Scan Density between 1 and 100.', 16, 1, @ScanDensity)
RETURN
END
IF @TableName IS NOT NULL
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
BEGIN
RAISERROR('Object: %s exists but is NOT a User-defined Table.
This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
ELSE
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
BEGIN
RAISERROR('Object: %s does not exist within this database.
Please check the table name and location (which database?).
This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
END
END
-- Otherwise the Object Exists and it is a table so we'll continue from here.
-- First thing to do is create a temp location for the data returned from
-- DBCC SHOWCONTIG
CREATE TABLE #ShowContigOutput
(
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId tinyint,
[Level] tinyint,
Pages int,
[Rows] bigint,
MinimumRecordSize smallint,
MaximumRecordSize smallint,
AverageRecordSize smallint,
ForwardedRecords bigint,
Extents int,
ExtentSwitches numeric(10,2),
AverageFreeBytes numeric(10,2),
AveragePageDensity numeric(10,2),
ScanDensity numeric(10,2),
BestCount int,
ActualCount int,
LogicalFragmentation numeric(10,2),
ExtentFragmentation numeric(10,2)
)
IF @TableName IS NOT NULL
-- then we only need the showcontig output for that table
INSERT #ShowContigOutput
EXEC('DBCC SHOWCONTIG (' + @TableName + ') WITH FAST, ALL_INDEXES, TABLERESULTS')
ELSE
-- All Tables, All Indexes Will be processed.
INSERT #ShowContigOutput
EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS')
PRINT N' '
-- Quick test to see if everything is getting here correctly
-- SELECT * FROM #ShowContigOutput
-- Walk the showcontig output table skipping all replication tables
-- as well as all tables necessary for
-- the UI. This is also where you can list large tables
-- that you don't want to rebuild all at one time.
-- NOTE: If you take out a large table from rebuilding this script may have
-- already checked density
-- meaning that the expense in terms of time may have been
-- expensive.
-- Also, you should use a different procedure to rebuild a large table
-- specifically.
-- Even when you pass in the tablename it will be avoided here if
-- MANUALLY added to the list by you.
-- Test, Test, Test!
DECLARE @ObjectName sysname,
@IndexName sysname,
@QObjectName nvarchar(258),
@QIndexName nvarchar(258),
@IndexID tinyint,
@ActualScanDensity numeric(10,2),
@InformationalOutput nvarchar(4000),
@StartTime datetime,
@EndTime datetime
DECLARE TableIndexList CURSOR FAST_FORWARD FOR
SELECT ObjectName, IndexName, IndexID, ScanDensity
FROM #ShowContigOutput AS sc JOIN sysobjects AS so ON sc.ObjectID = so.id
WHERE sc.ScanDensity < @ScanDensity
AND (OBJECTPROPERTY(sc.ObjectID, 'IsUserTable') = 1
OR OBJECTPROPERTY(sc.ObjectID, 'IsView') = 1)
AND so.STATUS > 0
AND sc.IndexID BETWEEN 1 AND 250
AND sc.ObjectName NOT IN ('dtproperties')
-- Here you can list large tables you do not WANT rebuilt.
ORDER BY sc.ObjectName, sc.IndexID
OPEN TableIndexList
FETCH NEXT FROM TableIndexList INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @QObjectName = QUOTENAME(@ObjectName, ']')
SELECT @QIndexName = QUOTENAME(@IndexName, ']')
SELECT @InformationalOutput = N'Processing Table: '
+ RTRIM(UPPER(@QObjectName))
+ N' Rebuilding Index: ' + RTRIM(UPPER(@QIndexName))
PRINT @InformationalOutput
IF @IndexID = 1
BEGIN
SELECT @StartTime = getdate()
EXEC sp_RebuildClusteredIndex @ObjectName, @IndexName
SELECT @EndTime = getdate()
SELECT @InformationalOutput = N'Total Time to process = '
+ convert(nvarchar, datediff(ms, @StartTime, @EndTime))
+ N' ms'
PRINT @InformationalOutput
END
ELSE
BEGIN
SELECT @StartTime = getdate()
EXEC('DBCC DBREINDEX(' + @QObjectName + ', '
+ @QIndexName + ') WITH NO_INFOMSGS')
SELECT @EndTime = getdate()
SELECT @InformationalOutput = N'Total Time to process = '
+ convert(nvarchar, datediff(ms, @StartTime, @EndTime))
+ N' ms'
PRINT @InformationalOutput
END
PRINT N' '
FETCH NEXT FROM TableIndexList
INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity
END
END
PRINT N' '
SELECT @InformationalOutput = N'** All Indexes have been rebuilt. ** '
PRINT @InformationalOutput
DEALLOCATE TableIndexList
GO
오류 로그 보기
SQL Server에서는 이벤트를 SQL Server 오류 로그 및 응용 프로그램 로그에 기록합니다. 문제가 발생하였을 경우, 오류 로그에 기록되어 있는 정보를 사용하여, 문제의 원인을 찾을 수 있습니다.
SQL Server 오류 로그 보기
SQL Server 로그는 응용 프로그램의 상태 정보를 알기 위한 훌륭한 자료입니다. SQL Server 로그는 서비스가 시작할 때부터 서비스가 중지될 때까지 계속 메시지를 기록합니다.
모니터링 관리의 효율을 위하여 모니터링 담당자가 SQL Server 로그에서 찾아야 할 것을 정의합니다. 이 로그는 심각도 수준 19~25의 값을 가진 모든 오류를 기록합니다. 모니터링할 때, SQL Server 로그에서 심각도 수준 19~25 사이의 값을 가진 오류는 반드시 체크해야 합니다. 이 심각도 수준을 가진 오류는 트랜잭션을 실패하게 하고 응용 프로그램이 적절하게 운영되지 않도록 합니다. 심각도 수준 20에서 25사이의 오류는 치명적입니다. 만일 이 오류가 발생되면, 클라이언트 연결은 오류 메시지를 받은 후에 종료됩니다.
또한, RAISERROR…WITH LOG 문법을 사용하여 각각의 오류를 발견해 낼 수 있습니다. 이것은 오류 정보를 SQL Server 로그에 나타나게 합니다.
[따라하기]
1. EM에서 원하는 데이터베이스 서버를 선택합니다.
2. [관리]폴더를 클릭하고, [SQL Server 로그]를 클릭합니다.
3. 원하는 로그파일을 클릭하면, 오른쪽 창에 로그가 나타납니다.
[참고] 로그 파일의 정보 확인 및 개수 변경하기
SQL Server 로그 파일의 개수는 현재 기록하고 있는 로그와 이전의 6개의 로그에 대한 백업을 가지고 있습니다. 이 로그 파일의 수를 크게 설정하면 SQL Server 재시작으로 인하여 문제를 진단하는데 단서가 될 수 있는 ERRORLOG 파일이 overwrite되어 유실되는 것을 방지할 수 있습니다. 이 로그의 개수는 레지스트리 값을 수정하여 변경할 수 있습니다.
-- 오류 로그 파일들의 파일 정보 확인
EXEC master..xp_enumerrorlogs
GO
-- 3번째 로그 파일 내용 보기
EXEC sp_readerrorlog 3
GO
-- 로그 파일의 개수 20으로 변경하기
EXEC master..xp_regwrite 'HKEY_LOCAL_MACHINE'
, 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
, 'NumErrorLogs'
, 'REG_DWORD'
, 20
GO
-- 확인
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE'
, 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
, 'NumErrorLogs'
GO
[참고] SQL Server 재시작 없이 새 오류 로그 생성하기
새 오류 로그는 SQL Server가 시작되는 경우에 생성되지만, SQL Server의 재시작 없이도 새 로그를 생성할 수 있습니다. 어떤 이유로 ERRORLOG 파일의 크기가 지나치게 커진 경우에는 sp_cycle_errorlog를 사용하여 새로운 ERRORLOG 파일을 생성할 것을 권고합니다.
-- 새 로그 생성
EXEC sp_cycle_errorlog
GO
-- 오류 로그 파일별 정보 조회
EXEC master..xp_enumerrorlogs
GO
응용 프로그램 로그 보기
이벤트 뷰어는 사용자가 응용 프로그램, 보안, 시스템 로그에 기록되는 이벤트를 모니터링할 수 있도록 합니다. 이 로그는 SQL Server 로그와 SQL 에이전트 로그로 분리시켜 추가적인 정보를 제공합니다. SQL Server 메시지는 응용 프로그램 로그에서 발견됩니다.
SQL Server 메시지는 "MSSQLSERVER" 또는 "SQLSERVERAGENT"라는 원본을 가진 메시지로 구별될 수 있습니다. RAISERROR 메시지도 여기에서 볼 수 있습니다.
[따라하기]
1. [시작] → [설정] → [관리도구] → [이벤트 뷰어]를 선택합니다. (OS마다 다름)
2. 디폴트로 로컬컴퓨터의 로그가 나타난다. 다른 컴퓨터의 [연결]을 클릭하여 원격 컴퓨터의 로그를 확인합니다.
3. 원하는 사항을 더블 클릭하면, 자세한 정보를 얻을 수 있습니다.
SQL Server 에이전트 오류 로그 보기
SQL Server 에이전트 서비스는 작업 및 복제를 관리합니다.
1. EM에서 원하는 데이터베이스 서버를 선택합니다.
2. [관리] 폴더를 클릭합니다.
3. [SQL Server 에이전트]에 마우스를 대고 오른쪽 버튼을 클릭하고, [오류 로그 표시]를 선택합니다.
4. 오류를 선택하여 목록을 확인합니다.
성능 모니터링
성능 모니터링 하기
1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.
2. 상단 그래픽 메뉴에서 [+]를 클릭하여 카운터 추가화면이 나타나면, 원하는 모니터 카운터를 추가합니다.
3. [로컬 컴퓨터 카운터 사용] 또는 [다른 컴퓨터에서 카운터 선택]을 선택합니다.
4. [성능개체]를 선택합니다.
5. [모든 카운터]를 선택하거나, [다음 목록에서 카운터 선택]를 선택한 후, 원하는 카운터를 선택합니다.
6. [모든 인스턴스]를 선택하거나, [다음 목록에서 인스턴스 선택]을 선택한 후, 원하는 인스턴스를 선택합니다.
7. [추가]를 클릭합니다.
8. 카운터 추가를 완료 한 후, [닫기]를 클릭합니다.
9. 선택한 카운터가 하단에 나타납니다.
10. 카운터를 제거할 경우에는, 제거하기를 원하는 카운터를 선택한 후, 상단 그래픽 메뉴에서, [x] (삭제키)를 클릭합니다.
성능 로그 생성하기
[따라하기]
1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.
2. [성능 로그 및 경고]의 더하기 기호(+)를 클릭합니다.
3. 카운터 로그를 선택합니다.
4. 오른쪽 창에 마우스를 대고 오른쪽 버튼을 클릭하여, [새 로그 설정]을 선택합니다.
5. [새 로그 설정]에 원하는 로그 이름을 입력하고, [확인]을 클릭합니다.
6. Counter_Log화면에 사용 정보와 파일 포맷을 설정합니다.
7. [개체추가]를 선택하여, 원하는 개체를 추가합니다.
8. [카운터추가]를 선택하여, 원하는 카운터를 추가합니다.
9. [데이터 샘플 간격]의 [간격]과 [단위]를 선택합니다.
10. [로그파일]탭을 선택합니다.
11. [로그 파일 종류]를 선택합니다.
12. 구성을 클릭하면, 로그 파일 구성 화면이 나타납니다.
13. 구성을 클릭하면, 로그 파일 구성 화면이 나타납니다.
14. [찾아보기]를 클릭하여 로그 파일을 저장할 위치를 선택합니다.
15. 파일이름을 입력합니다.
16. 로그 파일 크기를 선택합니다. [다음으로 제한]할 경우, 제한 파일 크기를 입력합니다.
17. [확인]을 클릭합니다.
18. 파일명의 마지막 부분을 어떻게 설정할 것인지를 선택합니다.
19. 파일명의 마지막 부분을 일련 번호로 할 경우, 시작번호를 설정합니다.
20. 로그 파일의 설명을 입력합니다.
21. [일정]탭을 선택합니다.
22. 로그 시작 시간을 설정합니다.
23. 로그 중지 시간을 설정합니다.
24. 로그 파일을 닫을 때 실행할 명령이 있다면 선택합니다.
25. [확인]을 클릭합니다.
26. 오른쪽 창에 추가한 로그 파일의 목록이 나타납니다.
27. 새로 추가한 성능 로그의 이름위에서 마우스의 오른쪽 버튼을 클릭한 후 [다른 이름으로 설정 저장]으로 설정을 저장해 놓으면, 설정 파일을 재사용할 수 있습니다.
[참고]
성능 카운터에서 서버의 많은 정보를 얻을 수 있습니다. 문제를 확인할 수 있도록, 충분한 시간 동안 필요한 카운터를 수집합니다.
로그 파일 종류를 csv로 선택하여 수집하면, 분석 또는 집계하기에 편리합니다.
수집 시간을 고려하여 데이터 샘플 간격을 설정합니다. 샘플 간격이 커질수록, 그래프의 정확도는 떨어지고, 샘플 간격이 작다면, 데이터의 크기가 커집니다. 수집 시간 샘플 간격
2시간 4 초
1일 30 초
5일 180 초
1일 15초
SQL Server를 모니터링하기 위하여 어떤 서버를 사용할지 결정합니다. 원격으로 모니터링할 수 있으나 장기간 동안 네트워크를 연결하여 카운터를 사용하는 것은 네트워크 트래픽을 가중시킵니다. 만일 SQL Server에 성능 모니터링 로그를 위한 공간이 있다면, 성능 로그 정보를 로컬로 기록합니다.
수집 파일 크기는 적정한 값으로 제한합니다. 수집 파일이 너무 커지면, 파일이 열리지 않는 경우가 있습니다.
기존의 설정 파일(HTM)이 있는 경우에는 [기존 설정에서 새 로그 설정]을 사용하면 쉽게 구성이 가능합니다.
성능 로그의 재생
[따라하기]
1. [시작] → [설정] → [제어판] → [관리] → [성능]을 선택합니다.
2. [로그 데이터 보기] 버튼을 클릭하면, [시스템모니터 등록 정보] 창이 나타납니다.
3. 로그 파일을 선택하고, 추가 버튼을 클릭하여, 원하는 파일을 추가합니다.
4. [시간 범위]를 클릭하여, 원하는 시간대를 조절합니다.
5. 데이터 탭을 클릭합니다.
6. [추가]를 클릭하여, 원하는 개체를 추가합니다.
7. [확인]을 클릭합니다.
프로필러
성능 문제의 디버깅은 문제의 원인을 알아내는 것으로 시작합니다. 많은 경우, 성능 문제는 비효율적인 SQL 문에서 기인합니다. 비효율적인 SQL 문이 문제의 원인이라고 의심될 때, SQL 프로필러는 특정 SQL 문을 찾는 것에 도움을 줄 수 있습니다. 문제의 원인이 되는 SQL 문을 찾아, 튜닝하여 성능에 많은 도움을 줄 수 있습니다.
추적 수행하기 - GUI 사용
[따라하기]
1. 다음 방법 중 하나를 이용하여 프로필러를 실행합니다.
[시작] → [프로그램] → [Microsoft SQL Server] → [프로필러] 또는 엔터프라이즈 관리자의 상단 메뉴에서 [도구] → [SQL 프로필러]를 선택합니다.
2. [파일] → [새로 만들기] → [추적]을 선택합니다.
3. 원하는 SQL 서버에 연결하면, [추적 속성]창이 나타납니다.
4. 추적이름을 입력합니다.
5. 추적할 SQL Server를 선택합니다.
6. 템플렛을 사용할 경우에 템플렛 이름을 선택합니다.
7. 파일에 저장하려면, [파일에 저장]을 선택하고, 저장할 위치와 파일명을 입력합니다.
8. 최대 파일 크기 설정을 합니다.
9. 이벤트 탭을 선택한 후, 원하는 이벤트를 추가하거나, 제거합니다.
10. 데이터 열 탭을 선택한 후, 수집할 데이터 열을 선택합니다.
11. 필터를 이용하고 싶다면, 필터 탭을 선택하여, 원하는 필터를 정의합니다. 예를 들어, LoginName이 Test인 것만 수집하고 싶다면 다음과 같이 설정합니다.
12. [실행]을 클릭하면, 수집이 시작됩니다.
13. 수집 중지를 위해서, 중지 버튼(붉은 네모)을 클릭합니다.
추적 수행하기 - SP 사용
[파일] → [추적 스크립팅]을 이용하면, 원하는 확장 프로시저를 생성할 수 있습니다.
[추적 스크립팅]을 이용하여 작성한 저장 프로시저를 첨부합니다.
[따라하기]
오래 실행되는 SQL 문 찾기
오래 실행되는 쿼리는 잘못 튜닝된 시스템, 잘못 작성된 응용 프로그램, 또는 단순히 많은 동작을 수행하는 작업등을 의미할 수 있습니다. 어떠한 경우건, 이러한 오래 실행되는 SQL 문을 찾아서 튜닝하는 것은 그 작업의 성능은 물론 전반적인 시스템 성능까지도 향상시킬 수 있습니다.
권장되는 추적 이벤트 : TSQL, SQL:BatchCompleted
정렬 기준 컬럼 : Duration
과도한 자원 사용자 찾기
과도한 자원을 사용하는 응용 프로그램이나 사용자를 찾는 추적은 DBA에게 유용한 도구가 될 수 있습니다. 이러한 추적 유형은 CPU와 I/O 자원 모두를 많이 사용하는 SQL 문을 살펴야 합니다. 프로세스나 사용자를 식별하여, 응용 프로그램을 튜닝할 수 있습니다.
권장되는 추적 이벤트 : TSQL, SQL:BatchCompleted
정렬 기준 컬럼 : CPU, Reads, 및 Writes
교착 상태 알아내기
사용자의 작업에 따라 교착상태는 시스템에서 문제가 될 수도 있고 또 그렇지 않을 수도 있습니다. 많은 경우에 있어 교착 상태는 심각한 문제일 수 있는데, 이 경우 원인을 알아내는 것은 성능을 향상시키는데 핵심이 됩니다. 그러나 이러한 이벤트를 프로파일 하는 것은 자원을 많이 사용하게 되므로 주의해야 합니다.
권장되는 추적 이벤트
TSQL, SQL:BatchStarting 동작하는 SQL 일괄 처리(batch)
Locks, Lock:Deadlock 교착 상태 자체의 이벤트
Locks, Lock:Deadlock Chain 교착 상태에 이르는 이벤트 순서
[참고]
불필요한 이벤트의 추가는 삼가합니다. 너무 많은 이벤트의 추가는 서버의 성능에 영향을 줄 수 있습니다.
일반적인 경우라면, 모든 컬럼들을 포함시킵니다. 어떤 이벤트들은 보조적인 항목을 반환하는 어떤 항목들에 의존합니다. 적어도 다음 컬럼들은 포함하는 것이 좋습니다.
- BinaryData
- ClientProcessID
- CPU
- Duration
- EndTime
- EventClass
- EventSubClass
- HostName
- IntegerData
- LoginName
- NTUserName
- Reads
- SPID
- StartTime
- TextData
- Writes
테이블에 추적을 직접 저장하는 것은 좋지 않습니다. 추적 파일을 생성한 후, fn_trace_gettable 함수를 사용하면 테이블에 저장할 수 있습니다.
추적에 관련된 저장 프로시저의 예제 스크립트
다음은 추적 스크립팅을 저장 프로시저화한 예제 스크립트입니다. 시스템의 환경에 적합하도록 수정 보완하여 활용하기 바랍니다.
추적을 시작하는 저장 프로시저의 예제 스크립트 : sp_trace_start USE master
GO
CREATE PROCEDURE sp_trace_start @TraceFileName sysname=NULL,
@TraceName sysname='trace',
@Options int=2, -- TRACE_FILE_ROLLOVER
@MaxFileSize bigint=5,
@StopTime datetime=NULL,
@Events varchar(300)=
'10,12',
-- 10 - RPC:Completed
-- 12 - SQL:BatchCompleted
@Cols varchar(300)=
'1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44,',
-- 모든 컬럼
@IncludeFilter sysname=NULL,
@ExcludeFilter sysname=NULL
AS
SET NOCOUNT ON
-- 변수 선언
DECLARE @TraceId int
DECLARE @On bit
DECLARE @rc int
SET @On=1
-- 이벤트와 컬럼을 확인한다.
IF @Events IS NULL or @Cols IS NULL BEGIN
PRINT 'No Events or Coloumns.'
RETURN -1
END
-- 파일경로와 파일명을 설정한다.
IF @TraceFileName IS NULL
SELECT @TraceFileName = 'c:\Trace\trace_' + CONVERT(CHAR(8),getdate(),112)
-- 추적 큐를 만든다
EXEC @rc =sp_trace_create @TraceId OUT, @Options, @TraceFileName, @MaxFileSize, @StopTime
IF @rc<>0 BEGIN
PRINT 'Trace not started.'
RETURN @rc
END
PRINT 'Trace started.'
PRINT 'The trace file name is '+@TraceFileName+'.'
-- 추적할 이벤트 클래스들과 컬럼들을 지정한다
DECLARE @i int, @j int, @Event int, @Col int, @Colstring varchar(300)
IF RIGHT(@Events,1)<>',' SET @Events=@Events+','
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @Colstring=@Cols
IF RIGHT(@Colstring,1)<>',' SET @Colstring=@Colstring+','
SET @j=CHARINDEX(',',@Colstring)
WHILE @j<>0 BEGIN
SET @Col=CAST(LEFT(@Colstring,@j-1) AS int)
EXEC sp_trace_setevent @TraceId, @Event, @Col, @On
SET @Colstring=SUBSTRING(@Colstring,@j+1 ,300)
SET @j=CHARINDEX(',',@Colstring)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END
-- 필터를 설정한다
EXEC sp_trace_setfilter @TraceId, 10, 0, 7, N'SQL Profiler'
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, N'EXEC% sp_%trace%'
IF @IncludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 6, @IncludeFilter
IF @ExcludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, @ExcludeFilter
-- 추적을 활성화한다
EXEC sp_trace_setstatus @TraceId, 1
-- 추적을 기록한다. (테이블 사용)
IF OBJECT_ID('tempdb..TraceQueueList') IS NULL BEGIN
CREATE TABLE tempdb..TraceQueueList (TraceID int, TraceName varchar(20), TraceFile sysname)
END
IF EXISTS(SELECT * FROM tempdb..TraceQueueList WHERE TraceName = @TraceName) BEGIN
UPDATE tempdb..TraceQueueList
SET TraceID = @TraceId, TraceFile = @TraceFileName
WHERE TraceName = @TraceName
END
ELSE BEGIN
INSERT tempdb..TraceQueueList
VALUES(@TraceId, @TraceName, @TraceFileName)
END
RETURN 0
GO
/* 실행 하기 */
EXEC sp_trace_Start
[참고]
output 파일을 지정하지 않으면, "c:\Trace"밑에 추적 파일이 생성됩니다. 이 스크립트를 직접 실행하려면, "c:\Trace"를 생성합니다.
추적 파일이 커질 수도 있으므로, output 파일이 생성되는 곳의 공간을 충분히 확보합니다.
원하는 이벤트와 컬럼은 번호로 설정합니다. 이벤트와 컬럼 번호는 BOL을 참조하십시오.
추적을 중지하는 저장 프로시저의 예제 스크립트 : sp_trace_stop
USE master
GO
CREATE PROCEDURE sp_trace_stop @TraceName sysname='trace'
AS
SET NOCOUNT ON
-- 변수를 선언한다
DECLARE @TraceId int
DECLARE @TraceFileName sysname
-- 추적 목록을 확인하여, 추적을 중지한다
IF OBJECT_ID('tempdb..TraceQueueList') IS NOT NULL BEGIN
SELECT @TraceId = TraceID, @TraceFileName=TraceFile
FROM tempdb..TraceQueueList
WHERE TraceName = @TraceName
IF @@ROWCOUNT<>0 BEGIN
EXEC sp_trace_setstatus @TraceId, 0
EXEC sp_trace_setstatus @TraceId, 2
DELETE tempdb..TraceQueueList
WHERE TraceName = @TraceName
PRINT 'Trace is stopped. '
+ 'The trace output file name is '+@TraceFileName
END
ELSE
PRINT 'No active traces.'
END
ELSE
PRINT 'No active traces.'
RETURN 0
GO
/* 실행하기 */
EXEC sp_trace_stop
[참고] sp_trace_stop은 sp_trace_start로 실행한 추적(Trace)를 중지하는 저장 프로시저입니다.
추적 재생하기
1. 프로필러를 시작하고, [파일] → [열기] → [추적 파일]을 선택합니다.
2. 원하는 파일을 선택합니다.
유용한 유틸리티
PSSDIAG
PSSDIAG는 성능 분석에 필요한 여러가지 로그와 데이터를 수집할 수 있는 유틸리티입니다. 이 유틸리티를 사용하면 프로필러에 비해 부하를 덜 주면서 추적 데이터를 수집할 수 있으므로, 대용량 시스템의 경우에는 프로필러 대신 이 툴을 사용할 것을 권고합니다. 자세한 내용은 다음 url을 참조하십시오.
http://support.microsoft.com/?kbid=830232
Read80Trace
수집한 추적 정보를 분석하는데 매우 유용한 유틸리티입니다. 추적 데이터를 분석하여 로컬 데이터베이스에 분석한 정보를 저장해 주고, htm 파일 형태로 리소스를 많이 사용한 쿼리 또는 저
댓글목록
등록된 댓글이 없습니다.