[MSSQL] SQL Server 점검 시 유용한 쿼리 모음

DBA라고 하기 부끄러운 수준이지만, 그래도 다른 사람보다 DB를 조금 더 자주 접하면서 MSSQL에서 가지고 있으면 유용한 쿼리를 아래에 정리해 본다. 모두 sysadmin의 Server-level role을 가지고 수행하였다.

 

참고로, 아래 모든 쿼리들이 서버에 부하를 얼마나 유발하는 지 확인해 보지 않았고, 매우 심각한 상황(쿼리 하나가 서버를 죽이든,살리든 하는 상황)에서는 한 번도 사용해보지 않았으므로, 반드시 유의해서 사용해야 한다...!

 

부하를 최소화한다든지, 새로운 쿼리를 알아내게 되면 이 포스팅에 계속 업데이트할 예정이므로, 이 포스팅 내용은 계속 변할 수 있다!

---

1. 현재 MSSQL의 상태 조회 쿼리

MSSQL 인스턴스에 connect 되어 있는 세션 정보 확인

SELECT s.login_time
	, s.host_name
	, s.session_id
	, s.program_name
	, [DB Name] = db_name(st.dbid)
	, s.login_name
	, s.status
	, [CPU Time used by session (ms)] = s.cpu_time
	, [# of Pages In MEM] = s.memory_usage
	, [Total Scheduled Time (ms)] = s.total_scheduled_time
	, [Since Session established Time (ms)] = s.total_elapsed_time
	, s.last_request_start_time
	, s.last_request_end_time
	, [Physical Reads from Disk (Pages)] = s.reads
	, [Logical Reads from MEM (Pages)] = s.logical_reads
	, s.is_user_process
	, s.transaction_isolation_level
	, [해당 세션에서 현재까지 return된 행 수] = s.row_count
	, [가장 최근 수행한 sql문] = st.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c 
	ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
ORDER BY s.cpu_time DESC, s.reads DESC

 

 

각 DB별 연결되어 있는 Connection 개수 및 Login 계정 확인

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame
;

 

 

Running 상태가 아닌, 문제가 있거나 Blocking 걸려 있는 트랜잭션 조회

SELECT req.session_id
	, ses.host_name
	, ses.login_name
	, req.status
	, req.blocking_session_id	-- 이 값이 0이면, block되고 있지 않거나, blocking session 정보를 불러올 수 없는 상태.
	, req.wait_type
	, SUBSTRING(sql_text.text,req.statement_start_offset/2, 
		(CASE 
			WHEN req.statement_end_offset = -1
			THEN LEN(CONVERT(NVARCHAR(max), sql_text.text)) * 2
			ELSE req.statement_end_offset 
			END - req.statement_start_offset)/2
	) AS [Query_Text] --- statement of batch or proc executing right now
	, DB_NAME(sql_text.dbid) AS [DB명]
	, CAST(DATEDIFF(ss, req.start_time, GETDATE()) AS NVARCHAR)+ N'초' AS [Request 경과시간]
	, object_name(sql_text.objectid) AS [SP명]	-- ad-hoc 또는 prepared SQL statement 라면, Null 로 나옴
	, req.cpu_time AS [CPU_Time (ms단위)]
	, req.total_elapsed_time
	, req.reads
	, req.writes 
	, req.logical_reads 
	, req.scheduler_id
FROM sys.dm_exec_requests AS req WITH (NOLOCK)
INNER JOIN sys.dm_exec_sessions AS ses WITH (NOLOCK) ON req.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sql_text
WHERE req.session_id > 50 AND wait_type <> 'WAITFOR'
ORDER BY req.scheduler_id, req.status, req.cpu_time, req.logical_reads

 

 

'STATS' 키워드를 넣지 못한 Backup 또는 Restore 진행 시, 현재 진행상황 확인

SELECT ER.session_id
	, ER.command
	, ER.start_time
	, ER.percent_complete
	, ER.total_elapsed_time
	, ER.status
	, ER.last_wait_type
	, ER.estimated_completion_time /1000/60 AS [남은 시간(분)]	-- ms 단위로 저장되므로 분 단위 표시를 위해 (1000*60)으로 나눠주는 것임
	, EST.text
FROM sys.dm_exec_requests AS ER WITH(NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS EST
WHERE ER.command = 'BACKUP DATABASE' OR ER.command = 'RESTORE DATABASE'
	AND ER.percent_complete > 0;

 

 

현재 Temp DB를 사용하고 있는 세션별 각 resource 사용량 확인하는 쿼리

SELECT
	es.session_id AS [SESSION ID]
  , DB_NAME(su.database_id) AS [DATABASE Name]
  , es.host_name AS [System Name]
  , es.program_name AS [Program Name]
  , est.text AS [Query_Text]
  , es.login_name AS [USER Name]
  , er.status
  , es.cpu_time AS [CPU TIME (in milisec)]
  , es.total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  , es.total_elapsed_time AS    [Elapsed TIME (in milisec)]
  , (es.memory_usage * 8)      AS [Memory USAGE (in KB)]
  , (su.user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  , (su.user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  , (su.internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  , (su.internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  , CASE is_user_process
		WHEN 1	THEN 'user session'
		WHEN 0  THEN 'system session'
		END         
	AS [SESSION Type], er.row_count AS [ROW COUNT]
FROM 
	sys.dm_db_session_space_usage AS su
INNER JOIN sys.dm_exec_sessions AS es
	ON es.session_id = su.session_id
INNER JOIN sys.dm_exec_requests AS er
	ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS est
ORDER BY [Total Scheduled TIME (in milisec)] DESC, [Elapsed TIME (in milisec)] DESC

 

SQL Server Instacne의 Uptime 확인 쿼리

USE master
go
SELECT sqlserver_start_time AS [SQL Server Instance Uptime] 
FROM sys.dm_os_sys_info

 

평균 Blocking 당한 시간이 긴 쿼리 Top100 조회

sys.dm_exec_query_stats: SQL Server에서 캐시된 쿼리 계획에 대한 집계 성능 통계를 반환하는 DMV

• 이 DMV에서 반환되는 Row의 유효 기간은 쿼리 플랜 자체와 연결되어 있다.
  따라서, 해당 플랜이 캐시에서 제거되면, 이 DMV 결과로도 확인할 수 없다는 점을 유의하자.
• 이 DMV의 total_elapsed_time 은 해당 플랜의 쿼리 실행이 완료되기까지 소요된 총 경과 시간을 [μs](마이크로 초) 단위로 보여준다.

USE [DATABASE_NAME]
SELECT TOP(100)
	[Average Time Blocked (초)] = (total_elapsed_time - total_worker_time) / qs.execution_count / 1000000 -- 초 단위로 보여주기 위해 나눔
	, [Total Time Blocked (초)] = total_elapsed_time - total_worker_time / 1000000 -- 초 단위로 보여주기 위해 나눔
	, [Execution count] = qs.execution_count
	, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
			ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
	, [Parent Query] = qt.text
	, [DatabaseName] = DB_NAME(qt.dbid)
  , [마지막 실행 시점] = qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked (초)] DESC, [Execution count] DESC;

---

2. 권한 관리 관련 쿼리 (주로 Audit 또는 보안 상태 점검 시 사용)

Public을 제외한 다른 서버 고정 역할을 가지고 있는 SQL Login의 Role, Default Database 조회

SELECT [Login Name] = B.name 
	, [Principal ID] = B.principal_id
	, [Role] = A.name
	, B.type_desc
	, B.default_database_name
FROM sys.server_role_members rm
INNER JOIN sys.server_principals A
	ON A.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals  B 
	ON B.principal_id = rm.member_principal_id
WHERE B.name = '조회를 원하는 SQL Login Name'

 

고정 데이터베이스 역할 리스트 조회

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;

 

특정 유저의 user mapping DB 및 해당 DB에서의 Database-level roles 확인

DECLARE @command nvarchar(4000) 
DECLARE @DBRole TABLE (DBName sysname, UserName sysname, RoleName varchar(255))
SELECT @command = '
USE [?]
SELECT ''?'' AS DB_Name
	, (CASE prin.name
		WHEN ''dbo'' THEN (
			SELECT SUSER_SNAME(owner_sid)
			FROM master.sys.databases
			WHERE name = ''?'')
			ELSE prin.name
			END
		) AS UserName
	, ISNULL(USER_NAME(mem.role_principal_id), '''')  AS AssociatedRole
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem
	ON prin.principal_id = mem.member_principal_id
'

INSERT INTO @DBRole
EXEC sp_MSforeachdb @command 

SELECT * FROM @DBRole t 
WHERE t.UserName LIKE '%특정 유저 네임%'

위 쿼리를 수행하면 아래와 같은 결과를 얻을 수 있다.

 

특정 유저가 해당 오브젝트에 대해서 어떤 권한을 가지고 있는 지 확인

fn_my_permissions의 경우, 임시 테이블을 Insert, Delete 하는 과정이 수반되므로, Audit Scope에 해당하는 DB에 대해서 사용하는 것에는 각별한 주의가 필요하다.

EXECUTE AS LOGIN = 'LOGIN USER NAME';

SELECT * 
FROM fn_my_permissions('[DATABASE NAME].[SCHEMA NAME].[TABLE | VIEW NAME]', 'OBJECT')
ORDER BY subentity_name, permission_name;

REVERT;

 

 

유저가 생성한 Stored Procedure에 대한 권한에 대해 특정 유저가 어떠한 권한을 가지고 있는 지 확인

SELECT 
	[Permission_State] = dp.state_desc
	, [Permission_Name] = dp.permission_name
	, [Schema_Name] = s.name
	, [Object_Name] = o.name
FROM 
    sys.database_permissions dp
JOIN sys.objects o 
	ON dp.major_id = o.object_id
JOIN sys.schemas s 
	ON o.schema_id = s.schema_id
WHERE 
    dp.grantee_principal_id = USER_ID('%USER_NAME%')
    AND o.type = 'P' -- 'P'는 저장 프로시저를 의미
ORDER BY [Object_Name]

위 쿼리 수행 결과 예시

---

3. Size 또는 Index 관련 쿼리

현재 MSSQL 인스턴스 내 모든 DB의 용량 확인

USE master
GO
 
SELECT sys.databases.name
	, [Disk Usage (MB)] = CONVERT(VARCHAR,SUM(CAST(mf.size AS bigint))*8/1024) +' MB'
	, [Disk Usage (GB)] = CONVERT(VARCHAR,SUM(CAST(mf.size AS bigint))*8/1024/1024) +' GB'
FROM sys.databases
JOIN sys.master_files AS mf
	ON sys.databases.database_id = mf.database_id
WHERE sys.databases.name != 'tempdb'	-- sys.master_files 뷰에서의 tempdb 의 사이즈는 initial size를 의미한다. 그러므로 현재 tempdb 사이즈가 아니기 때문에 제외하였다.
GROUP BY sys.databases.name
ORDER BY SUM(CAST(mf.size AS bigint))*8/1024 DESC		-- DB 총 사이즈별로 내림차순 정렬

 

해당 DB의 mdf, ldf 전체 사이즈 및 실제 남은 공간 확인

USE [DB_NAME]
SELECT [DB NAME] = DB_NAME() 
	, [Logical File Name] = name
	, type_desc
	, [Current File Size (MB)] = size / 128.0
	, [Used Space (MB)] = CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0
	, [Free Space (MB)] = size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0
	, [사용률] = (CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0) / (size / 128.0) * 100.00
FROM sys.database_files
WHERE type IN (0,1);

-- Log space만 확인하고 싶을때
DBCC SQLPERF(LOGSPACE)

 

 

해당 DB의 테이블별 사이즈 확인

USE [DB_NAME]
SELECT
    OBJECT_SCHEMA_NAME(a2.object_id) AS SchemaName,
    a2.name AS TableName,
    a1.rows as [RowCount],
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
    CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB
FROM
    (SELECT 
        ps.object_id,
        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
    GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204)
     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
WHERE a2.type <> N'S' and a2.type <> N'IT' 
--	AND a2.name = N'TABLE NAME'  -- 원하는 테이블 있으면 해당 조건 넣기
ORDER BY SchemaName, TableName, ReservedSize_MB DESC

 

특정 테이블 내의 인덱스의 사용 빈도 확인

SELECT a.object_id
	, [Table Name] = object_name(a.object_id)
	, a.index_id
	, [Index Name] = b.name
	, a.user_seeks									-- 사용자 쿼리별 Seek 연산 횟수
	, a.last_user_seek								-- 사용자 쿼리에 따른 마지막 Seek 연산 수행 시간
	, a.user_scans									-- 사용자 쿼리별 Scan 연산 횟수
	, a.last_user_scan								-- 사용자 쿼리에 따른 마지막 Scan 연산 수행 시간
FROM sys.dm_db_index_usage_stats AS a
INNER JOIN sys.indexes AS b
	ON a.object_id = b.object_id
	AND a.index_id = b.index_id
WHERE a.database_id = DB_ID(N'DATABASE_NAME')
	AND OBJECT_NAME(a.object_id) = N'TABLE_NAME'  -- 스키마명 제외한 순수 테이블명만 넣을 것.

 

특정 테이블 내 인덱스들의 조각화 정도 확인

SELECT a.object_id
	, [Table Name] = object_name(a.object_id)
	, a.index_id
	, [Index Name] = b.name
	, avg_page_space_used_in_percent
	, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
	DB_ID (N'DB_NAME')
	, OBJECT_ID(N'TABLE_NAME')     -- 스키마 네임은 빼고 순수 테이블명만 넣기
	, NULL
	, NULL
	, 'SAMPLED'
	) AS a
INNER JOIN sys.indexes AS b
	ON a.object_id = b.object_id
	AND a.index_id = b.index_id

 

Compression Backup 시에, 압축 비율 및 현재 백업 파일 사이즈 확인

SELECT name 
	, backup_start_date
	, [compressed_backup_size (MB)] = CONVERT(DECIMAL(10, 2), compressed_backup_size / 1024. / 1024.)
	, [compression ratio] = compressed_backup_size/backup_size * 100
FROM msdb..backupset
WHERE backup_start_date >= '2023-12-11'  -- 확인이 필요한 특정 날짜를 조건절에 꼭 넣어서 쿼리할 것.
ORDER BY backup_start_date DESC

 

---

4. 기타 등등?

프로시저 조회

USE [데이터베이스명]

-- 전체 프로시저 오브젝트 조회
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'

-- 프로시저 이름, 프로시저 내용만 조회
SELECT [ProcedureName] = ROUTINE_NAME
	, [ProcedureContents] = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'

 

---

참고

https://99lib.tistory.com/541

문제상황시 유용한 sql쿼리들

https://sharedstoryit.tistory.com/entry/MSSQL-%EC%BF%BC%EB%A6%AC-%ED%8A%9C%EB%8B%9D%EC%9D%84-%EC%9C%84%ED%95%9C-%EC%BF%BC%EB%A6%AC-%EB%B0%8F-%EC%8B%9C%EC%8A%A4%ED%85%9C-%ED%85%8C%EC%9D%B4%EB%B8%94

[MSSQL] 쿼리 튜닝을 위한 쿼리 및 시스템 테이블