SQL Performance Tuning common statements (excerpt online)

1. Import the trace file into the table

SELECT IDENTITY (BIGINT11 as RowNumber,* into TableNamefrom fn_trace_gettable ('trace.trc'default)

2. Querying for statements with large CPU overhead

SELECT Top  -Total_worker_time/Execution_count asAvg_cpu_cost, Plan_handle, Execution_count, (SELECT SUBSTRING(text, Statement_start_offset/2 + 1,       ( Case  whenStatement_end_offset= -1           Then LEN(CONVERT(nvarchar(Max),text))* 2          ELSEStatement_end_offsetEND -Statement_start_offset)/2)     fromsys.dm_exec_sql_text (sql_handle)) asQuery_text,creation_time fromsys.dm_exec_query_statswhereCreation_time> '2014-11-01' and(Total_worker_time/Execution_count)> 146341ORDER  by [Avg_cpu_cost] DESC

3. Query blocking

SELECTA.*  fromSys.[sysprocesses]aWHEREA.[spid]> -  and db_name(A.[dbid])='DBName'  andA.spid<> @ @SPID  andA.blocked> 0

4. Query the number of connections

SELECT COUNT (*from[sys]. [dm_exec_sessions] WHERE [session_id] >  -

SQL Performance Tuning common statements (excerpt online)