DMV for capturing all SQL Statements instead of just the last

Question

DMV for capturing all SQL Statements instead of just the last

DanielP

SSCrazy

Points: 2302
More actions
January 9, 2013 at 10:43 am

#272546

Is there a way to get from a DMV not the last statement executed by a Session but all statements executed by that Session I am trying to capture what the Connection was running with blocking occurred earlier. It currently isn’t blocking and our MDW didn’t capture any transactions at that time.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

What you see with the DMVs is the input buffer, i.e. the last batch. If you did not have a Trace or Extended Events session running then the information is no longer available.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

DanielP SSCrazy Points: 2302 More actions · Answer 2

Thanks. I actually found it in our MDW report when drilling down on Locks.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Nice. Thanks for posting back. Do you know if that was part of the default collection set, or a custom one you setup?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

DanielP SSCrazy Points: 2302 More actions · Answer 4

We had to set up Management Data Warehouse (MDW) to monitor. I was able to trace the SQL Code that was causing the blocking by drilling down the Server Acitviy Report for that time frame. There was a spike in 'locks', just had to drill down on it.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

DanielP (1/10/2013)
We had to set up Management Data Warehouse (MDW) to monitor. I was able to trace the SQL Code that was causing the blocking by drilling down the Server Acitviy Report for that time frame. There was a spike in 'locks', just had to drill down on it.

Sorry if I was not clear. I meant within MDW, is this info available to you via the information collected by the Default Data Collector or do you have some Custom Data Collectors setup as well that makes this research possible?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

DanielP SSCrazy Points: 2302 More actions · Answer 6

DanielP

SSCrazy

Points: 2302

January 10, 2013 at 10:10 am

#1575883

Sorry, no just the defaults.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

Good to know. Thanks for posting back.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato