I am trying to find the equivalent of DBTime (Oracle) in SQL Server

This is the time when the connections to the instance are ACTIVE

i.e. use CPU, wait for something (IO ...) (Idle wait are not computed)

Thanks for your help

September 21st, 2013 2:37am

Did you check the cpu_time column in sys.dm_exec_sessions?
Free Windows Admin Tool Kit Click here and download it now
September 21st, 2013 4:53am


but I need not only CPU, I need all wait events also
I need to know how much the instance worked

For exemple:
2 hours elapsed, 2 min dbtime => no db problem
2 hours elapsed, 1000 min dbtime (NB: more than 1 session can be active)  => db problem

Thanks for your help

September 21st, 2013 9:02am

You mean something like sys.dm_os_wait_stats?
Free Windows Admin Tool Kit Click here and download it now
September 21st, 2013 11:51am

sys.dm_exec_sessions.total_elapsed_time is exactly what you want.  Contrary to what the documentation says, it tracks the total elapsed time for each query issued by that session.  So it includes cpu_time and wait time, but not idle time. 

September 21st, 2013 12:07pm


There are a few issues I have with the assumptions here.

1. In SQL Server, there are connections and then there are sessions. Sessions can be re-used and often are when it comes to pooled connections. Thus a single connection may have had multiple different users or queries running on the same session.

2. In Oracle terminology, a database is akin to an instance in SQL Server - so you want this over the entire server?

3. The assumption that a large amount of CPU used is inherently bad. This may or may not be a correct assumption as parallel executions will always have cpu time that is greater than "connection" time for the time it has been running. Assuming parallel plans are not good to begin with is not correct IMHO.

4. Wait events are at the instance level, unless you're collecting this information over a period of time from multiple system tables you're not going to know ALL of the wait stats and which database they were in, queries run, etc. This can also cause issues if you aren't taking period snapshots as queries could be aged out of cache.

However, if you wanted to use cpu time and connection time as a method of detecting issues - which I would suggest against using them alone, then sys.dm_exec_connections and sys.dm_exec_sessions are what you'll want to look at. Just remember, as I pointed out, that things like pooled connections, etc, are going to skew this information and might not represent the whole picture.

If you want a complete look at this type of information, there are 3rd party monitoring and performance tracking applications for SQL Server such as SQL Sentry, Idera, Quest, Red Gate, Ignite, etc, and it would probably be worth investing in one of those.


Free Windows Admin Tool Kit Click here and download it now
September 21st, 2013 12:07pm

ok thanks

how to compute the dbtime  with sys.dm_exec_sessions.total_elapsed_time

between 10:00 and 11:00 for example ?

Thanks for your help

September 22nd, 2013 2:56am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics