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... Continue Reading →


How to execution plan on SQLServer

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... Continue Reading →

SQL Statement in cache, it’s execution time

-- 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... Continue Reading →

List locks held by a session

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,... Continue Reading →

List blocked sessions in SQLServer

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... Continue Reading →

Create a free website or blog at WordPress.com.

Up ↑