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

/*

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

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

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)
		, te.name AS [EventType]
		, [EventCount] = COUNT(te.name)
		, [ReadCount] = SUM(Reads)
FROM [YOUR_TRACE_TABLE_SCHEMA].[YOUR_TRACE_TABLE_NAME] AS d

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

declare @dbid int
–To get Datbase ID
set @dbid = db_id()

select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*

bartread

--get most used tables
SELECT 
	db_name(ius.database_id) AS DatabaseName,
	t.NAME AS TableName,
	SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id
WHERE database_id = DB_ID('YOUR_DATABASE_HERE')
GROUP BY database_id, t.name
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

bartread

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
Plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

bartread

USE master;  
GO  
SELECT * FROM sys.dm_exec_query_plan (YOUR_PLAN_HANDLE_HERE);  
GO