There are 2 types of DBCC TRACEON (session or global). Session trace is turned on that session only and global trace is visible for all connections on the server. Trace#: 3502 – This trace writes information to SQL Server log every time a check point occurs SQLServer 2008 R2 trace flags SQLServer 2005 trace flags…
In SQLServer Managment Studio to the execution plan one can use one of the following SET commands “SET SHOWPLAN_XML ON” – SQLServer will not execute the SQL and it will return execution plan and has estimated row counts and stats in form of a XML document. The output displayed in SQL Server management studio can…
— Display SQL statement (SQLServer 2005 and SQLServer 2008), it displays creation time, last exeuction time, max time it ran for in microseconds, average execution time, # of times sql statement executed SELECT j.plan_handle , sqltext , deqs.creation_time , deqs.last_execution_time , deqs. , deqs.total_worker_time/deqs.execution_count avg_exec_time , deqs.execution_count FROM (SELECT decp.plan_handle, st.text sqltext FROM sys.dm_exec_cached_plans decp…
To list locks held by a current session select convert (smallint, req_spid) As spid, rsc_dbid As dbid, rsc_objid As ObjId, object_name(rsc_objid) tablename, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x,…
List sessions that are being blocked select spid , blocked being_blocked , db_name(dbid) , login_time , last_batch , program_name from master..sysprocesses where blocked <> 0 Output: spid being_blocked login_time last_batch program_name —— ————- ——————————————————————————————————————————– ———————– ———————– ——————————————————————————————————————————– 53 52 test 2010-10-04 20:40:41.780 2010-10-04 20:45:50.357 Microsoft SQL Server Management Studio – Query
SET STATISTICS TIME [ON|OFF] – Tells how much CPU time and time taken to parse and compile and also the CPU time and elapsed time taken for execution of the SQL statement. Sample Output: SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU…
