Database optimization, performance analysis

Query 20 SQL statements that consume the highest CPU, sql2008

Select TOP A.creation_time,total_worker_time/execution_count as Avg_cpu_cost, Plan_handle,execution_count, (select SUBSTRING (text, STATEMENT_START_OFFSET/2 + 1, (case when statement_end_offset = -1then LEN (CONVERT (nvarchar (max), text)) * 2ELSE Statement_end_offsetend-statement_start_offset)/2) from Sys.dm_exec_sql_text (sql_handle) b) as Query_textFROM Sys.dm_exec_query_stats aorder by [Avg_cpu_cost] DESC

View Deadlocks

Use mastergodeclare @spid int, @bl intdeclare s_cur CURSOR for select  0, Blockedfrom (SELECT * FROM sysprocesses where< C1/>BLOCKED>0) A Where NOT EXISTS (SELECT * from sysprocesses where  blocked>0) b where a.blocked =SPID) union select spid,blocked from sysprocesses where  blocked>0open s_curfetch NEXT from S_cur into @spid, @blWHI LE @ @FETCH_STATUS = 0beginif @spid =0             Select ' causes a database deadlock: ' + CAST (@bl as VARCHAR (10) + ' process number, which executes the SQL syntax as follows ' else            Selec  T ' process number spid: ' + cast (@spid as varchar (10)) + ' by ' + ' process number spid: ' + cast (@bl as varchar (10) + ' block, its current process executes SQL syntax as follows ' DBCC InputBuffer (@bl) FETCH NEXT from S_cur to @spid, @blendCLOSE s_curdeallocate s_curexec Sp_who2