#title 성능측정 [[TableOfContents]] ==== 쿼리 모니터링 ==== {{{ 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 }}} ==== 현재 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' }}} ==== 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 }}} ==== 쿼리의 반환된 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 }}} ==== 버퍼 풀 ==== 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'; }}} ==== 버퍼 풀(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; }}} ==== 버퍼 풀(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; }}} ==== 실행계획보기 ==== 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) }}} ==== 세션 모니터링 ==== {{{ 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 }}} ==== 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 }}} ==== spin lock ==== {{{ SELECT * FROM sys.dm_os_spinlock_stats ORDER BY [spins] DESC; GO }}} ==== 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 ==== IO Bottlenecks ==== http://www.mssqltips.com/tip.asp?tip=2329 ==== 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 }}} ==== CPU Bottlenecks ==== [http://www.mssqltips.com/tip.asp?tip=2316 How to Identify CPU Bottlenecks in SQL Server] {{{ 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 }}} ==== 블록킹 ==== {{{ 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 }}} ==== 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'; }}} ==== 복구 Tracking ==== {{{ USE 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 ==== 세션 모니터링 ==== {{{ 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 }}} ==== 현재 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 }}} ==== 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 }}} ==== 대기유형들 ==== 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 }}} ==== 각 데이터베이스에 대해 캐시된 페이지 수 반환 ==== {{{ 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 }}} ==== 현재 데이터베이스의 각 개체에 대해 캐시된 페이지 수 ==== {{{ 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 }}} ==== 데이터베이스, 파일, 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; }}}