Dynamic Management Functions

Question

Post reply

Dynamic Management Functions

steve.jacobs

SSCommitted

Points: 1830
More actions
January 11, 2014 at 11:14 pm

#281090

Comments posted to this topic are about the item Dynamic Management Functions
January 13, 2014 at 12:37 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

Simple, yet good question, thank you Steve.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 2

Good question, foiled by haste.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Easy question to start the week, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 4

For SQL Server 2008 R2 there is no valid answer, as sys.dm_exec_sessions does not contain the open tran count. But thanks to this, I have learned that this column is new in SQL Server 2012 🙂

Best Regards,

Chris Büttner

steve.jacobs SSCommitted Points: 1830 More actions · Answer 5

Christian Buettner-167247 (1/13/2014)
For SQL Server 2008 R2 there is no valid answer, as sys.dm_exec_sessions does not contain the open tran count. But thanks to this, I have learned that this column is new in SQL Server 2012 🙂

Chris:

You are correct. 2012 is where the open transaction field\count was added to the dm_exec_sessions. But you can get the transaction count in 2008 by joining (cross apply, join, etc.) with exec_requests and cross applying sql_text and query_plan to generate a "monitoring" tool.

However, this is a question I had on one of my tests and believe it or not, they took it directly from BOL and TechNet and I got it wrong because of the open tran count per session.:ermm:

Thomas Abraham SSChampion Points: 10761 More actions · Answer 6

Another "easy" question that still managed to teach us something. Thanks Steve!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Dana Medley SSCertifiable Points: 6764 More actions · Answer 7

Thanks for the question Steve. Good way to start the week.

Everything is awesome!

Ken SSCoach Points: 17191 More actions · Answer 8

Christian Buettner-167247 (1/13/2014)
For SQL Server 2008 R2 there is no valid answer, as sys.dm_exec_sessions does not contain the open tran count. But thanks to this, I have learned that this column is new in SQL Server 2012 🙂

+ 1. Nice question. Thanks!

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 9

Great Question for a Monday. I do have to say that I had to research if Open Tran count was part of any version of sys.dm_exec_sessions contained Tran count before selecting an answer. 😎

sneumersky SSCertifiable Points: 7667 More actions · Answer 10

Sweat that one out. I must stop thinking everyone is out to trick me!

Revenant SSC-Forever Points: 42467 More actions · Answer 11

Revenant

SSC-Forever

Points: 42467

January 14, 2014 at 10:10 am

#1679982

Nice headscratcher, Steve - thanks!

sqlnaive SSCoach Points: 17437 More actions · Answer 12

KWymore (1/13/2014)
Christian Buettner-167247 (1/13/2014)
For SQL Server 2008 R2 there is no valid answer, as sys.dm_exec_sessions does not contain the open tran count. But thanks to this, I have learned that this column is new in SQL Server 2012 🙂
+ 1. Nice question. Thanks!

+1

Gristley SSCommitted Points: 1595 More actions · Answer 13

Might be useful to state which SQL version, as some estates have several versions available.

Btw, isn't sys.dm_exec_cursors the only DMF, the others are DMV's?

I went with the only function as it looked like a trick question!

Thanks.