Contents
- 1 쿼리 모니터링
- 2 현재 cpu 사용율
- 3 latch wait
- 4 쿼리의 반환된 row수
- 5 버퍼 풀
- 6 버퍼 풀(table단위)
- 7 버퍼 풀(db단위)
- 8 실행계획보기
- 9 세션 모니터링
- 10 big wait stats
- 11 spin lock
- 12 Latency
- 13 IO Bottlenecks
- 14 Page IO Latch
- 15 CPU Bottlenecks
- 16 블록킹
- 17 dm_os_performance_counters
- 18 복구 Tracking
- 19 세션 모니터링
- 20 현재 cpu 사용율
- 21 Transaction/sec
- 22 대기유형들
- 23 각 데이터베이스에 대해 캐시된 페이지 수 반환
- 24 현재 데이터베이스의 각 개체에 대해 캐시된 페이지 수
- 25 데이터베이스, 파일, pagetype별 페이지수
[edit]
1 쿼리 모니터링 #
select
a.session_id
, b.status
, b.blocking_session_id
, b.wait_type
, wait_resource
, b.wait_time / (1000.0) wait_sec
, b.cpu_time
, requested_memory_kb * 1.0 / 1024 / 1024 request_memory_gb
, b.logical_reads
, b.reads
, b.writes
, b.total_elapsed_time / (1000.0) elaps_sec
, substring(c.text,(b.statement_start_offset / 2) + 1, ((case b.statement_end_offset when -1 then datalength(c.text) else b.statement_end_offset end - b.statement_start_offset) / 2) + 1) as statement_text
, coalesce(quotename(db_name(c.dbid)) + N'.' + quotename(object_schema_name(c.objectid,c.dbid)) + N'.' + quotename(object_name(c.objectid,c.dbid)), '') as command_text
, b.command
, a.login_name
, a.host_name
, a.program_name
, a.last_request_end_time
, a.login_time
, b.open_transaction_count
from sys.dm_exec_sessions as a
inner join sys.dm_exec_requests as b
on b.session_id = a.session_id
cross apply sys.dm_exec_sql_text(b.sql_handle) as c
inner join sys.dm_exec_query_memory_grants d
on a.session_id = d.session_id
where 1=1
and b.session_id != @@spid
order by
b.cpu_time desc
, b.status
, b.blocking_session_id
, a.session_id
[edit]
2 현재 cpu 사용율 #
select
max(a.cntr_value * 1.0 / b.cntr_value) cpu_usage
from sys.dm_os_performance_counters a
inner join sys.dm_os_performance_counters b
on a.object_name = b.object_name
and a.object_Name = 'SQLServer:Resource Pool Stats'
and a.counter_name = 'CPU usage %'
and b.counter_name = 'CPU usage % base'
[edit]
3 latch wait #
- http://www.sqlskills.com/BLOGS/PAUL/post/Advanced-performance-troubleshooting-waits-latches-spinlocks.aspx
- http://feedproxy.google.com/~r/PaulSRandal/~3/QKhxouMV__w/post.aspx
WITH Latches AS
(SELECT
latch_class,
wait_time_ms / 1000.0 AS WaitS,
waiting_requests_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_latch_stats
WHERE latch_class NOT IN (
'BUFFER')
AND wait_time_ms > 0
)
SELECT
W1.latch_class AS LatchClass,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(14, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S
FROM Latches AS W1
INNER JOIN Latches AS W2
ON W2.RowNum <= W1.RowNum
WHERE W1.WaitCount > 0
GROUP BY W1.RowNum, W1.latch_class, W1.WaitS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO
[edit]
4 쿼리의 반환된 row수 #
- sql server 2008 r2 sp1 이상
- http://www.sqler.com/415263#0
SELECT TOP 50 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" ,total_rows,last_rows,min_rows,max_rows FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash,total_rows,last_rows,min_rows,max_rows ORDER BY 2 DESC; GO
[edit]
5 버퍼 풀 #
http://feedproxy.google.com/~r/PaulSRandal/~3/1p2yduALcpE/post.aspx
-- db buffer-pool
SELECT
(CASE WHEN ([database_id] = 32767)
THEN 'Resource Database'
ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
GO
-- table buffer-pool
EXEC sp_MSforeachdb
N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]
FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
BEGIN
SELECT
''?'' AS [Database],
OBJECT_NAME (p.[object_id]) AS [Object],
p.[index_id],
i.[name] AS [Index],
i.[type_desc] AS [Type],
--au.[type_desc] AS [AUType],
--DPCount AS [DirtyPageCount],
--CPCount AS [CleanPageCount],
--DPCount * 8 / 1024 AS [DirtyPageMB],
--CPCount * 8 / 1024 AS [CleanPageMB],
(DPCount + CPCount) * 8 / 1024 AS [TotalMB],
--DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
--CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
FROM
(SELECT
allocation_unit_id,
SUM (CASE WHEN ([is_modified] = 1)
THEN 1 ELSE 0 END) AS [DPCount],
SUM (CASE WHEN ([is_modified] = 1)
THEN 0 ELSE 1 END) AS [CPCount],
SUM (CASE WHEN ([is_modified] = 1)
THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],
SUM (CASE WHEN ([is_modified] = 1)
THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
FROM sys.dm_os_buffer_descriptors
WHERE [database_id] = DB_ID (''?'')
GROUP BY [allocation_unit_id]) AS buffers
INNER JOIN sys.allocation_units AS au
ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than 100MB
ORDER BY [FreeSpacePC] DESC;
END';
[edit]
6 버퍼 풀(table단위) #
http://www.sqler.com/384271#2
USE 데이터베이스;
GO
;WITH src AS
(
SELECT
[Object] = o.name,
[Type] = o.type_desc,
[Index] = COALESCE(i.name, ''),
[Index_Type] = i.type_desc,
p.[object_id],
p.index_id,
au.allocation_unit_id
FROM
sys.partitions AS p
INNER JOIN
sys.allocation_units AS au
ON p.hobt_id = au.container_id
INNER JOIN
sys.objects AS o
ON p.[object_id] = o.[object_id]
INNER JOIN
sys.indexes AS i
ON o.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE
au.[type] IN (1,2,3)
AND o.is_ms_shipped = 0
)
SELECT
src.[Object],
src.[Type],
src.[Index],
src.Index_Type,
buffer_pages = COUNT_BIG(b.page_id),
buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
src
INNER JOIN
sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE
b.database_id = DB_ID()
GROUP BY
src.[Object],
src.[Type],
src.[Index],
src.Index_Type
ORDER BY
buffer_pages DESC;
[edit]
7 버퍼 풀(db단위) #
http://www.sqler.com/384271#2
-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
[edit]
8 실행계획보기 #
http://www.yangdb.com/
SELECT
db_name(qt.dbid) AS 'db_name'
, OBJECT_NAME(qt.objectid,qt.dbid) as sp_name
, qp.query_plan
, execution_count as execution_count
, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS 'Calls/Second'
, DATEDIFF(Minute, qs.cached_time, GetDate()) AS 'Age in Cache(min)'
, qs.cached_time
, qs.last_execution_time
, ISNULL(qs.total_elapsed_time/qs.execution_count, 0) /1000.0 AS 'avg_Elapsed_Time(ms)'
, qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'
, max_elapsed_time /1000.0 AS 'max_Elapsed_Time(ms)'
, ISNULL(qs.total_worker_time/qs.execution_count, 0)/1000.0 AS 'avg_worker_time(ms)'
, total_worker_time/1000.0/1000.0 as 'total_worker_time(sec)'
, qs.max_worker_time/1000.0 as 'max_worker_time(msdb)'
, ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'avg_logical_reads'
, total_logical_reads as total_logical_reads
, qs.max_logical_reads as max_logical_reads
, ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'avg_physical_reads'
, total_physical_reads as total_physical_reads
, qs.max_physical_reads as max_physical_reads
, ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'avg_physical_writes'
, qs.total_logical_writes as total_logical_writes
, qs.max_logical_writes as max_logical_writes
, qs.plan_handle
FROM
sys.dm_exec_procedure_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) qp
where
db_name(qt.dbid) is not null
AND OBJECT_NAME(qt.objectid,qt.dbid) = @ProcName
AND qt.dbid = DB_ID(@DBName)
[edit]
9 세션 모니터링 #
SELECT
owt.session_id,
owt.wait_duration_ms,
owt.wait_type,
owt.blocking_session_id,
owt.resource_description,
es.program_name,
est.text,
est.dbid,
eqp.query_plan,
es.cpu_time,
es.memory_usage
FROM sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es
ON owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
WHERE es.is_user_process = 1;
GO
[edit]
10 big wait stats #
--리셋
--DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR);
;WITH Latches AS
(SELECT
latch_class,
wait_time_ms / 1000.0 AS WaitS,
waiting_requests_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_latch_stats
WHERE latch_class NOT IN ('BUFFER')
)
SELECT
W1.latch_class AS LatchClass,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(14, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S
FROM Latches AS W1
INNER JOIN Latches AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.latch_class, W1.WaitS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO
[edit]
12 Latency #
SELECT
--virtual file latency
ReadLatency = CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
WriteLatency = CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
Latency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,
--avg bytes per IOP
AvgBPerRead = CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,
AvgBPerWrite = CASE WHEN io_stall_write_ms = 0
THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,
AvgBPerTransfer = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read + num_of_bytes_written) /
(num_of_reads + num_of_writes)) END,
LEFT (mf.physical_name, 2) AS Drive,
DB_NAME (vfs.database_id) AS DB,
--vfs.*,
mf.physical_name
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
--WHERE vfs.file_id = 2 -- log files
-- ORDER BY Latency DESC
-- ORDER BY ReadLatency DESC
ORDER BY WriteLatency DESC;
GO
http://feedproxy.google.com/~r/PaulSRandal/~3/kjZ7zRrkCmI/post.aspx[edit]
14 Page IO Latch #
SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name ,i.name as index_name ,page_io_latch_wait_count ,page_io_latch_wait_in_ms ,CAST(1. * page_io_latch_wait_in_ms / NULLIF(page_io_latch_wait_count ,0) AS decimal(12,2)) AS page_io_avg_lock_wait_ms FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1 ORDER BY 5 DESC
[edit]
15 CPU Bottlenecks #
![[http]](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAsAAAALCAMAAACecocUAAAA51BMVEUPDxEQEBQPECEPFTAeGiIjIygkJCwyLSorNFI1MC5DNzBIOylQSTkQLnAeP41MUXxTapt4WiCVbxuUglGZhEVXWV1lY2VpYFxxbV15cGBtanCAblx8cWmAe3aRgHeQg3QbQ6AiT7VIcatpg7Rojbdpl8Z8kbQ7/w+bscvAdBq9cR6zcym5jxq5jyDAkSnKkR7Rmh7ZfhDiiA/bmxfimBupko7/cA/vlBD6mw/bpSTbsSDHnjbpqxHvqRflshfotBn8rQ//nw/7wA//yw//pw//ww//0Q//0g//2A+vs7jV1N692frV6P/+fUWVAAAAKHRSTlP///////////////////////////////////////////////////8AvqouGAAAAAFiS0dETPdvEPMAAABySURBVHjaY1BXV5eTVZEHUuoM6uqepmr8xjpgto+XtKahkasQkK3hLWygreXgYi6mzqBqbS9jaeJoZsGuzqBk4+xkpS+ly8WtzqAsYOdhK6HIwSIO1Ksg4qbHx8vMADaTx12Qk4kRYr66JBurKNQuOAAA/ZkSIzXnQk8AAAAASUVORK5CYII=)
select plan_handle,
sum(total_worker_time) as total_worker_time,
sum(execution_count) as total_execution_count,
count(*) as number_of_statements
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time), sum(execution_count) desc
[edit]
16 블록킹 #
SELECT
DB_NAME() AS database_name,
GETDATE() AS audit_time,
s.spid AS process_id,
s.blocked AS blocking_process_id,
s.hostname,
s.loginame,
s.program_name,
blocking_s.hostname AS blocking_hostname,
blocking_s.loginame AS blocking_loginame,
blocking_s.program_name AS blocking_program_name,
REPLACE(REPLACE(buffer.[text], CHAR(10), ''),
CHAR(9), '') AS sql_statement,
SUBSTRING (buffer.[text], request.statement_start_offset/2,
(CASE
WHEN request.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), buffer.[text])) * 2
ELSE request.statement_end_offset
END - request.statement_start_offset)/2) AS specific_sql,
REPLACE(REPLACE(blocking_buffer.[text], CHAR(10), ''),
CHAR(9), '') AS blocking_sql_statement,
o.[name] AS blocking_object,
blocking_tr_locks.request_mode
FROM
sys.sysprocesses s INNER JOIN
sys.dm_exec_connections conn
ON
s.spid = conn.session_id CROSS APPLY
sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS buffer
LEFT JOIN sys.dm_exec_requests request
ON
conn.session_id = request.session_id INNER JOIN
sys.dm_exec_connections blocking_conn
ON
s.blocked = blocking_conn.session_id CROSS APPLY
sys.dm_exec_sql_text(blocking_conn.most_recent_sql_handle)
AS blocking_buffer INNER JOIN
sys.dm_tran_locks blocking_tr_locks
ON
s.blocked = blocking_tr_locks.request_session_id INNER JOIN
sys.objects o
ON
blocking_tr_locks.resource_associated_entity_id = o.object_id
INNER JOIN sys.sysprocesses blocking_s
ON
s.blocked = blocking_s.spid
WHERE
s.blocked <> 0
[edit]
17 dm_os_performance_counters #
SELECT [object_name], [counter_name], [instance_name], [cntr_value] FROM sys.[dm_os_performance_counters] WHERE [object_name] = 'SQLServer:Buffer Manager';
[edit]
18 복구 Tracking #
USE <db_name>
GO
DROP TABLE [dbo].[tbl_recovery_tracking]
GO
DROP TABLE [dbo].[tbl_dm_tran_database_transactions]
GO
CREATE TABLE [dbo].[tbl_recovery_tracking](
[runtime] [datetime] NOT NULL,
[command] [nvarchar](256) NOT NULL,
[session_id] [smallint] NOT NULL,
[database_id] [smallint] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[percent_complete] [real] NOT NULL,
[estimated_completion_time] [bigint] NOT NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[wait_time] [int] NOT NULL,
[wait_type] [nvarchar](60) NULL,
[blocking_session_id] [smallint] NULL,
[reads] [bigint] NOT NULL,
[writes] [bigint] NOT NULL,
[cpu_time] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_dm_tran_database_transactions](
[runtime] [datetime] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[database_id] [int] NOT NULL,
[database_transaction_log_record_count] [bigint] NOT NULL,
[database_transaction_log_bytes_used] [bigint] NOT NULL,
[database_transaction_log_bytes_reserved] [bigint] NOT NULL,
[database_transaction_next_undo_lsn] [numeric](25, 0) NULL
) ON [PRIMARY]
GO
-- collect the information in a loop
WHILE 1 = 1
BEGIN
INSERT INTO [dbo].[tbl_recovery_tracking]
SELECT GETDATE() as runtime, command,
session_id, database_id, total_elapsed_time,
percent_complete, estimated_completion_time,
wait_resource, wait_time, wait_type, blocking_session_id,
reads, writes, cpu_time
FROM sys.dm_exec_requests
WHERE command = 'DB STARTUP' -- may need to change this if troubleshooting recovery as part of attach database or restore
INSERT INTO tbl_dm_tran_database_transactions
SELECT GETDATE() as runtime,
transaction_id, database_id,
database_transaction_log_record_count, database_transaction_log_bytes_used,
database_transaction_log_bytes_reserved, database_transaction_next_undo_lsn
FROM sys.dm_tran_database_transactions
WAITFOR DELAY '00:00:01' -- change this capture interval
END
GO
-- after you collect information for some time, you can analyze the information to understand the progress of recovery
SELECT runtime, command,
session_id, database_id, total_elapsed_time,
percent_complete, estimated_completion_time,
wait_resource, wait_time, wait_type, blocking_session_id,
reads, writes, cpu_time
FROM [dbo].[tbl_recovery_tracking]
WHERE session_id = 25 -- change this
ORDER BY runtime
GO
SELECT
runtime, transaction_id, database_id,
database_transaction_log_record_count,
database_transaction_log_bytes_used, database_transaction_log_bytes_reserved,
database_transaction_next_undo_lsn
FROM tbl_dm_tran_database_transactions
WHERE database_id = 11 and transaction_id = 1452239 -- change this
ORDER BY runtime
GO
http://blogs.msdn.com/b/psssql/archive/2010/12/29/tracking-database-recovery-progress-using-information-from-dmv.aspx[edit]
19 세션 모니터링 #
select spid , hostname , cpu , physical_io , status , login_time , last_batch , st.text from sys.sysprocesses a cross apply sys.dm_exec_sql_text(sql_handle) st --where status = 'runnable' order by physical_io desc
[edit]
20 현재 cpu 사용율 #
--sql server 2008 r2
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks) FROM sys.dm_os_sys_info
SELECT record_id,
DATEADD(ms, 0 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
FROM (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
TIMESTAMP
FROM (
SELECT TIMESTAMP, CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '% %') AS x
) AS y
ORDER BY record_id DESC
[edit]
21 Transaction/sec #
SET NOCOUNT ON SET STATISTICS IO OFF DECLARE @cntr_value bigint , @min bigint , @sec bigint DECLARE @Result TABLE ( tps bigint ) SET @sec = 1 SET @min = 1 --10 minutes WHILE(@sec <= @min*60) BEGIN SELECT @cntr_value = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Transactions/sec%' AND instance_name LIKE '_Total%' WAITFOR DELAY '00:00:01' INSERT @Result(tps) SELECT cntr_value - @cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Transactions/sec%' AND instance_name LIKE '_Total%' SET @sec = @sec + 1 END SELECT AVG(tps) [Avg. Transaction/Sec] FROM @Result
[edit]
22 대기유형들 #
http://feedproxy.google.com/~r/PaulSRandal/~3/CVziN8ve91M/post.aspx
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
--HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO
[edit]
23 각 데이터베이스에 대해 캐시된 페이지 수 반환 #
select count(*)as cached_pages_count
,case database_id
when 32767 then 'resourcedb'
else db_name(database_id)
end as database_name
from sys.dm_os_buffer_descriptors
group by db_name(database_id) ,database_id
order by cached_pages_count desc
[edit]
24 현재 데이터베이스의 각 개체에 대해 캐시된 페이지 수 #
select count(*)as cached_pages_count
,name ,index_id
from sys.dm_os_buffer_descriptors as bd
inner join
(
select object_name(object_id) as name
,index_id ,allocation_unit_id
from sys.allocation_units as au
inner join sys.partitions as p
on au.container_id = p.hobt_id
and (au.type = 1 or au.type = 3)
union all
select object_name(object_id) as name
,index_id, allocation_unit_id
from sys.allocation_units as au
inner join sys.partitions as p
on au.container_id = p.hobt_id
and au.type = 2
) as obj
on bd.allocation_unit_id = obj.allocation_unit_id
where database_id = db_id()
group by name, index_id
order by cached_pages_count desc
[edit]
25 데이터베이스, 파일, pagetype별 페이지수 #
select case
when grouping(dbname) = 1 then '--- total ---'
else dbname
end as dbname,
case
when grouping(fileid) = 1 then '--- total ---'
else fileid
end as fileid,
case
when grouping(pagetype) = 1 then '--- total ---'
else pagetype
end as pagetype,
count(* ) as countpages,
sum(row_count*1.0) as sumrowcount,
avg(row_count*1.0) as avgrowcount,
sum(freespacebytes*1.0) as sumfreespacebytes,
avg(freespacebytes*1.0) as avgfreespacebytes
from (select case
when database_id = 32767 then 'resourcedb'
else cast(db_name(database_id) as varchar(25))
end as dbname,
cast(file_id as varchar(10)) as fileid,
cast(page_type as varchar(25)) as pagetype,
row_count as row_count,
free_space_in_bytes as freespacebytes
from sys.dm_os_buffer_descriptors bufferdescriptor with (nolock)) tmp
group by dbname,fileid,pagetype with rollup
order by case
when grouping(dbname) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
else dbname
end,
case
when grouping(fileid) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
else fileid
end,
case
when grouping(pagetype) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
else pagetype
end;
댓글 없음:
댓글 쓰기