/*
Get list of all queries hitting any specific tables
*/
SELECT DISTINCT TOP 100
ProcedureName = OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)
,SQLStatement = SUBSTRING(
sqlTxt.Text
,(QueryState.statement_start_offset/2)+1
,CASE QueryState.statement_end_offset
WHEN -1 THEN DATALENGTH(sqlTxt.text)
ELSE QueryState.statement_end_offset
END - (QueryState.statement_start_offset/2) + 1
)
,DiskReads = QueryState.total_physical_reads --- Disk reads
,MemoryReads = QueryState.total_logical_reads --–Logical Reads are memory reads
,ExecutionCount = QueryState.execution_count --Execution Count
,CPUTime = QueryState.total_worker_time --CPU Time
,DiskWaitAndCPUTime = QueryState.total_elapsed_time
,MemoryWrites = QueryState.max_logical_writes
,DateCached = QueryState.creation_time
,DatabaseName = DB_Name(sqlTxt.dbid) --Database name
,LastExecutionTime = QueryState.last_execution_time
,cte.*
FROM sys.dm_exec_query_stats AS QueryState
CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) AS sqlTxt
CROSS APPLY sys.dm_sql_referenced_entities(
OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)
, 'OBJECT'
) cte
WHERE sqlTxt.dbid = db_id() --Get detail for current database
AND cte.referenced_schema_name + '.' + cte.referenced_entity_name = 'dbo.tblEmployee'
