bartread

-- Originally from http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

USE [master];
GO

SELECT
	[vfs].[num_of_reads] AS TotalReads,
    [ReadLatency / ms] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,

bartread

SELECT	[TimeSlice] =
			RIGHT(N'0' + CAST(DATEPART(hh, StartTime) AS NVARCHAR(2)), 2)
			+ N':'
			+ RIGHT(N'0' + CAST(DATEPART(n, StartTime) AS NVARCHAR(2)), 2)
			+ N':'
			+ RIGHT(N'0' + CAST(DATEPART(ss, StartTime) AS NVARCHAR(2)), 2)
		, te.name AS [EventType]
		, [EventCount] = COUNT(te.name)
		, [ReadCount] = SUM(Reads)
FROM [YOUR_TRACE_TABLE_SCHEMA].[YOUR_TRACE_TABLE_NAME] AS d

bartread

SELECT	[TimeSlice] =
			RIGHT(N'0' + CAST(DATEPART(hh, StartTime) AS NVARCHAR(2)), 2)
			+ N':'
			+ RIGHT(N'0' + CAST(DATEPART(n, StartTime) AS NVARCHAR(2)), 2)
		, te.name AS [EventType]
		, [EventCount] = COUNT(te.name)
		, [ReadCount] = SUM(Reads)
FROM [YOUR_TRACE_TABLE_SCHEMA].[YOUR_TRACE_TABLE_NAME] AS d
INNER JOIN sys.trace_events AS te
	ON te.trace_event_id = d.EventClass

bartread

SELECT	[TimeSlice] =
			RIGHT(N'0' + CAST(DATEPART(hh, StartTime) AS NVARCHAR(2)), 2)
			+ N':'
			+ RIGHT(N'0' + CAST(DATEPART(n, StartTime) AS NVARCHAR(2)), 2)
			+ N':'
			+ RIGHT(N'0' + CAST(DATEPART(ss, StartTime) AS NVARCHAR(2)), 2)
		, d.DatabaseName
		, [AuditDBCCEventCount] = COUNT(CASE WHEN te.Name = 'Audit DBCC Event' THEN 1 ELSE NULL END)
		, [ErrorLogCount] = COUNT(CASE WHEN te.Name = 'ErrorLog' THEN 1 ELSE NULL END)
		, [LockTimeoutCount] = COUNT(CASE WHEN te.Name = 'Lock:Timeout' THEN 1 ELSE NULL END)

bartread

/*

T-SQL QUERY FOR INDEX SCAN STATISTICS FROM SQL PROFILER TRACE TABLE

Author: Bart Read
Copyright © 2016 bartread.com Ltd
License: MIT

This query gets the number of index scans by object ID, along with the total reads, and
average reads for those scans, as well as showing the time period over which the scans

bartread

/*

This script maps object IDs retrieved from, for example, a SQL Profiler trace table
back onto their object names.

Many SQL Profiler events, such as Scan:Stopped, collect the object ID, but do not map
it back onto an object name. If your trace table isn't on the same instance (smart)
or the database to which the event pertains (again, smart).

The IDs in this script are for the purposes of illustration only.

bartread

SELECT DISTINCT te.name
FROM [YOUR_TRACE_SCHEMA_NAME].[YOUR_TRACE_TABLE_NAME] AS d
INNER JOIN sys.trace_events AS te
	ON te.trace_event_id = d.EventClass;

bartread

/*

This query allows you to relate activity in tempdb to activity in other databases
where that activity has been recorded in a SQL Profiler trace table.

This can come in useful when, for example, diagnosing the source of I/O activity
and possible bottlenecking in tempdb.

*/

bartread

SELECT  o.[object_id] AS [ObjectID],
		s.[name] AS [SchemaName],
		o.[name] AS [ObjectName],
		s.[name] + '.' + o.[name] AS [QualifiedName],
		o.[type]
FROM    sys.objects AS o
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
ORDER BY [QualifiedName];

bartread

SELECT [cpu_ticks] ,
       [ms_ticks] ,
       [cpu_count] ,
       [hyperthread_ratio] ,
       [physical_memory_kb] ,
       [virtual_memory_kb] ,
       [committed_kb] ,
       [committed_target_kb] ,
       [visible_target_kb] ,
       [stack_size_in_bytes] ,