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