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;
현재 tempdb의 사용량 확인 (Extent 기준으로 표시됨)
USE [tempdb];
DBCC showfilestats
현재 tempdb를 사용하고 있는 세션별 각 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 '%특정 유저 네임%'위 쿼리를 수행하면 아래와 같은 결과를 얻을 수 있다.
특정 DB 내에서 특정 사용자가 어떠한 오브젝트들에 대해 어떤 권한들을 가지고 있는 지 확인
USE [Database Name]; -- DB Name
SELECT
dp.name AS UserName,
s.name AS SchemaName,
o.name AS ObjectName,
p.permission_name AS PermissionType,
p.state_desc AS PermissionState
FROM
sys.database_permissions p
JOIN
sys.objects o ON p.major_id = o.object_id
JOIN
sys.schemas s ON o.schema_id = s.schema_id
JOIN
sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE
dp.name = 'User Name' -- 특정 사용자명
ORDER BY
s.name, o.name, p.permission_name;특정 유저가 해당 오브젝트에 대해서 어떤 권한을 가지고 있는 지 확인
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
[SQL Login Name] = USER_NAME(dp.grantee_principal_id)
, [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
, [Physical File Path] = physical_name
, [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];
WITH PartitionStats AS (
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
),
InternalTableStats AS (
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
)
SELECT
[Schema Name] = OBJECT_SCHEMA_NAME(a2.object_id),
[Table Name] = a2.name,
[Rows] = FORMAT(a1.rows, '#,#'),
[Reserved Size (MB)] = FORMAT(CAST(ROUND(((a1.reserved + ISNULL(a4.reserved, 0)) * 8) / 1024.00, 2) AS NUMERIC(36,2)), '#,#'),
[Data Size (MB)] = FORMAT(CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)), '#,#'),
[Index Size (MB)] = FORMAT(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)), '#,#'),
[Unused Size (MB)] = FORMAT(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)), '#,#')
FROM PartitionStats a1
LEFT OUTER JOIN InternalTableStats 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 CAST(ROUND(((a1.reserved + ISNULL(a4.reserved, 0)) * 8) / 1024.00, 2) AS NUMERIC(36,2)) DESC;
-- FORMAT 함수 사용한 컬럼 기준으로 정렬 시, 숫자 기준으로 정렬하기 위해 계산식 그대로 사용특정 테이블 내의 인덱스의 사용 빈도 확인
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'
참고
문제상황시 유용한 sql쿼리들
--get the connections select st.text, c.connect_time, c.net_transport, c.auth_scheme, c.num_reads, c.num_writes, c.last_read, c.last_write, c.connection_id, c.parent_connection_id, st.dbid, st.objectid from sys.dm_exec_connections c cross apply sys.dm_exec
99lib.tistory.com
[MSSQL] 쿼리 튜닝을 위한 쿼리 및 시스템 테이블
데이터베이스별 누락된 인덱스SQL Server가 쿼리를 처리할 때 최적화 프로그램은 쿼리를 수행하기 위해 사용하려고 시도한 인덱스에 대한 기록을 보관합니다. 이러한 인덱스가 없는 경우 SQL Server
sharedstoryit.tistory.com
'DB > SQL Server (MSSQL)' 카테고리의 다른 글
| [MSSQL] 모든 유저 데이터베이스의 파일 사이즈 기록하는 Agent Job 만들기 (0) | 2024.06.21 |
|---|---|
| [MSSQL] 데이터베이스명, 논리적 파일명, 물리적 파일명(.mdf, .ldf)을 쿼리로 변경하기 (1) | 2023.12.27 |
| [MSSQL] SQL Server Linked Server(연결된 서버) 추가와 로그인 매핑정보 / 삭제 (1) | 2023.10.20 |
| [MSSQL] SQL Server의 메모리 관련 문제를 진단하는 1차적인 방법 (0) | 2023.08.21 |
| [MSSQL] SQL Server 호스트의 Disk Space Alert 설정하기 / Powershell 방식 VS MSSQL 쿼리 방식 (0) | 2023.03.31 |
최근댓글