History of executed query

• mah_j

  SSCrazy

  Points: 2931

  More actions

  November 21, 2012 at 2:21 am

  #285274

  Hi Experts,

  I want to see if there is a way to find the spid,block by witch session,what is the blocking session,the time of a particular query and the text of query?

  By this script i can find these information in the moment but i want the history.

  SELECT p.spid, db_name(p.dbid) AS DBName, p.hostname, p.nt_username,

  p.loginame, p.login_time, p.blocked as blocked_by,(SELECT TEXT

  FROM sys.dm_exec_sql_text(sql_handle))AS [QUERY], p.waittype, p.waittime, p.cpu,

  p.[status],p.program_name, p.cmd, p.nt_domain

  FROM sys.sysprocesses p

  WHERE p.spid>50

  AND DB_NAME(p.dbid)='dbname'

  I also use these DMVs to find the executed query in the past ,since the last service restart(and also if the plan has not removed) but i can't find if there was a blocking session.

  Actualy i want to find the blocking session of insert statement,but i don't want to use sql profiler because of it's cost on performance.

  SELECT plan_handle, st.text,*

  FROM sys.dm_exec_cached_plans

  CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

  WHERE text LIKE N'%part of a query%' AND TEXT NOT LIKE '%plan_handle%'

  select * from sys.dm_exec_query_stats qs

  cross apply sys.dm_exec_query_plan(qs.plan_handle)

  WHERE plan_handle in(0x06000700D23E1B36B8A08211000000000000000000000000)

• bleroy

  Hall of Fame

  Points: 3052

  More actions

  November 21, 2012 at 3:45 am

  #1561169

  You should possibly use server side trace (SQL Trace) which has a relatively low impact on performance.

  See the following article which compares SQL Trace v Profiler (v nothing):

  http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

  also this article (which then links to the relevant MSDN articles):

  http://weblogs.sqlteam.com/tarad/archive/2008/08/06/SQL-Profiler-best-practices.aspx

  HTH,

  B

  Edit: fixed IFCode