In SQL Server 2000 you can monitor active processes by looking at the sql_handle column in sysprocesses. In SQL Server 2005 BOL says sql_handle ...Represents the currently executing batch or object.In SQL 2000 a non-zero value in sql_handle indicated an active process. You could not simply rely on the status column to check for an actively running process. This is not the case in 2005. I ran a profiler trace to confirm that just because sql_handle reports a non-zero value does not mean the corresponding process is active. Does anyone know why this has changed and how we are now to interpret sql_handle in 2005? Thanks, Dave
http://blogs.msdn.com/sqlcat/archive/2005/09/23/473367.aspx and http://sqlserver-qa.net/blogs/perft...-optimizer-reuses-for-better-performance.aspx fyi.
Unless I am misunderstanding the first link, it says ...you frequenty want to see the SQL statements and associated query plans that are active on the system... ...Both of these views provide a plan_handle for the XML plan and a sql_handle for the query text... This sounds to me like sql_handle should only be reporting active processes. Am I misunderstanding these links? Dave