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 be stored with .sqlplan extension using “Save Results As..” which … Continue reading How to execution plan on SQLServer

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 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st) j, sys.dm_exec_query_stats deqs WHERE deqs.plan_handle … Continue reading SQL Statement in cache, it’s execution time

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 (, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (, 1, 8) As Mode, substring (, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' … Continue reading List locks held by a session

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 Continue reading List blocked sessions in SQLServer