Link to home
Start Free TrialLog in
Avatar of sedostal
sedostal

asked on

Diagnosing if a MSSQL statement is doing something in MSSQL 2008R2

In MSSQL 2008R2, we have a program that  runs using dynamic sql statements. The program appears to stop processing and so does the MSSQL statement its using.  I'm looking for a diagnostic tool to determine if the SQL statement is processing or if its a code problem with the program (looping). Here are the indicators that I did use

This shows no increase in status
SELECT session_id,reads,writes,logical_reads,cpu_time FROM sys.dm_exec_sessions  where session_id = '157' order by logical_reads desc

dbcc opentran (157) shows no LSN movement
sp_lock shows locks exclusive to that spid (157) that is running. No deadlocks are indicated.
DBCC inputbuffer for that spid shows the single statement with no changes.
SQL Server profiler indicates the execution of the statement but no response after that.

Can anyone recommend a diagnostic method or tool to determine if the Query is doing anything at all in the instance.
I later determined by guessing that it was a statistics problem on a table and that corrected the problem but I sure would like to find some sort of indicator to know if MMSQL is actually doing anything at all?

Really appreciate any ideas.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Sedostal,

Have you heard of SQL monitor from RedGate. This is the best tool <I think> for your requirement.

url - http://www.red-gate.com/products/dba/sql-monitor/

I took demo of this, It is very good, provides all info, including stats, execution plan, blocking, deadlock, etc in tabular format. Also note that this can be used with multiple instances also.

Hope it helps !
Avatar of sedostal
sedostal

ASKER

Status column!!!    So is this the missing link maybe...  I'll go after that with a fever pitch.   I have a stored procedure for deadlocks and I'm not seeing any indication that there are any although it compares with other spids.  Have you seen a SPID Deadlock itself?
I'm thinking row_count might work along with your status Vitor, have you used it as well?
Have you seen a SPID Deadlock itself?
I need to ask if you really meant Deadlock or Blocking lock? They are distinct stuffs and people usually mix them.

I'm thinking row_count might work along with your status Vitor, have you used it as well?
How do you think row_count could help here?
Blocking lock... I'm sorry I was't clear.   The sp_lock2  that reads the master..sysprocesses showed no blocks other than its own SPID. Deadlocks were non-existent.
 The sp_lock2  that reads the master..sysprocesses showed no blocks other than its own SPID.
That's good. Means that no other processes are being affected.

Deadlocks were non-existent.
That's because you can't know when they are occurring (present). You will only have information that they occurred (past) and that's because deadlocks are resolved by the database engine and SQL Server only log that they occurred and automatically solved.
Thank you for your help!!   The status will give me some idea where we are at in the process.