SELECTTOP50(total_logical_reads+total_logical_writes)AStotal_logical_io,(total_logical_reads/execution_count)ASavg_logical_reads,(total_logical_writes/execution_count)ASavg_logical_writes,(total_physical_reads/execution_count)ASavg_phys_reads,substring(st.text,(qs.statement_start_offset/2)+1,((CASEqs.statement_end_offsetWHEN-1THENdatalength(st.text)ELSEqs.statement_end_offsetEND-qs.statement_start_offset)/2)+1)ASstatement_text,*FROMsys.dm_exec_query_statsASqsCROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)ASst
ORDERBYtotal_logical_ioDESC
READ_COMMITED方法开启
SQL Server开启READ_COMMITTED_SNAPSHOT
--查询数据库状态select name,user_access,user_access_desc, snapshot_isolation_state,snapshot_isolation_state_desc, is_read_committed_snapshot_onfrom sys.databases--设置数据库为SINGLE_USER模式,减少锁定时间ALTERDATABASEprjidbzzhSETSINGLE_USERWITHROLLBACKIMMEDIATEALTERDATABASEprjidbzzhSETALLOW_SNAPSHOT_ISOLATIONONALTERDATABASEprjidbzzhSETREAD_COMMITTED_SNAPSHOTONALTERDATABASEprjidbzzhSETMULTI_USER