order of query processing to reduce flushing buffers NOLOCK, TABLOCK partitioned view fragmentation DBCC SHOWCONTIG DBCC INDEXDEFRAG DBCC DBREINDEX SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ------------------------------------- select sum(reads),sum(cpu),sum(duration) from trcCpu1Jul10 select count(*)'c',sum(reads)'reads',avg(reads),max(reads),sum(cpu)'cpu',avg(cpu),max(cpu),left(textData,50) from trcCpu1Jul10 group by left(textData,50) order by 2 desc select reads,cpu,textData from trcCpu1Jul10 where textData like 'SELECT "Name ID" ,"Short Name" ,"Display Name" ,"N%' order by 1 desc ------------------------------------- select top 100 reads,cpu,duration,textData from trcCpu1Jul10 order by 1 desc declare @traceFile varchar(200) set @traceFile = 'd:\home\logs\sqltrace\INET\2003100601_trcDeadlocks' set @traceFile = @traceFile+'.trc' SELECT EventClass, SPID , convert(varchar(24),StartTime,126)'StartTime', convert(varchar(24),EndTime,126)'EndTime' , convert(varchar(26),TextData)'TextData' -- , DatabaseID , Duration -- , convert(varchar(20),ApplicationName)'ApplicationName',convert(varchar(20),loginName)'loginName' FROM ::fn_trace_gettable(@traceFile, default) --order by rownumber desc