Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to find the start time of query in the database Expand / Collapse
Author
Message
Posted Friday, July 24, 2009 2:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 26, 2009 12:01 PM
Points: 8, Visits: 13
I am creating a stored procedure to identify, and kill a long running Business Objects query in the SQL Server database. I am able to identify the BO SQL iin the database with the BO user id, and also able to check the status "RUNNABLE" in sys_processes table, but last_batch column keeps updating timestamp for EACH REQUEST sent to the DB for the same BO query. Hence, I am unable to identify the start time, and to find out how long the BO SQL has been running. Does anyone know, how can I accomplish this ?
Post #759513
Posted Saturday, July 25, 2009 1:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:43 AM
Points: 351, Visits: 406
What version of SQL Server are running?


Post #759586
Posted Saturday, July 25, 2009 1:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:43 AM
Points: 351, Visits: 406
Check this article. See if this helps.
http://www.sqlservercentral.com/scripts/T-SQL/66830/



Post #759589
Posted Monday, July 27, 2009 6:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 13,876, Visits: 28,274
For 2005/2008, take a look at sys.dm_exec_syessions. It will give you a basic breakdown of information for the connections in the system, including start times on the last batch. For more detailed information, take a look at sys.dm_exec_requests. That will give you very specific information about what a process is waiting for, what it's doing, what statement it's on within the batch, etc. In some cases, not all, not even most, it will also show a percent complete, but don't rely on that value in any way. It may or may not be accurate and useful.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #760017
Posted Monday, July 27, 2009 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 26, 2009 12:01 PM
Points: 8, Visits: 13
SQL 2005
Post #760059
Posted Monday, July 27, 2009 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 26, 2009 12:01 PM
Points: 8, Visits: 13
This SP gets the start time of the running SQL from system table dm_exec_* and system function dm_exec_sql.

My understanding is ->One SQL sends multiple requests to the db, the start time in dm_* tables all point to the start time of EACH REQUEST, thus at a given point of time, I am unable to determine when the SQL started.



Post #760066
Posted Monday, July 27, 2009 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 26, 2009 12:01 PM
Points: 8, Visits: 13
My understanding is ->One SQL sends multiple requests to the db, the start time in dm_exec_* tables all point to the start time of EACH REQUEST, thus at a given point of time, I am unable to determine when the SQL started.



Post #760067
Posted Monday, July 27, 2009 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 13,876, Visits: 28,274
When you say SQL, do you mean an individual SQL statement? One that is currently running? I'm not entirely sure, but you may not be able to drill down to that level without capturing statements in a trace. That's one way of doing it, but you would already have to have the trace in place. You couldn't turn it on in a moment's notice.

I'll have to test the DMV to see if it records a statement or a batch start time.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #760106
Posted Monday, July 27, 2009 9:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 26, 2009 12:01 PM
Points: 8, Visits: 13
I mean the Report SQL which is running in the db. I am trying to identify the start time of the report SQL.
Post #760197
Posted Monday, July 27, 2009 11:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 13,876, Visits: 28,274
I'm not trying to be obtuse, I just don't understand what you are referring to. Do you mean queries coming from Reporting Services?


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #760247
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse