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별 페이지수

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 

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'

• 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

• 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

-- 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'; 

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;

-- 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;  

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 
 

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
 

11 spin lock #

 SELECT * FROM sys.dm_os_spinlock_stats
 ORDER BY [spins] DESC;
 GO 
 

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

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

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

SELECT [object_name], [counter_name],  
   [instance_name], [cntr_value] 
FROM sys.[dm_os_performance_counters] 
WHERE [object_name] = 'SQLServer:Buffer Manager'; 

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

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

--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

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

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

  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;