Physical Operations

/*

Lack of Indexes or memory pressure results in high physical I/O .
To find queries with high physical I/O , run the following script

NOTE:  This may take considerable time , so use TOP operator
to  restrict number of rows

*/

select top 50 sql.text, p.query_plan,qs.execution_count
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(sql_handle) sql
cross apply sys.dm_exec_query_plan(plan_handle) p
where query_plan.exist('
declare default element namespace http://schemas.microsoft.com/sqlserver/2004/07/showplan;
/showplanxml/batchsequence/batch/statements//relop/@physicalop[. = sql:variable("@op")]') = 1
order by execution_count desc

go