TextData found in Profiler

Question

TextData found in Profiler

M Hoss

Grasshopper

Points: 23
More actions
October 23, 2007 at 3:33 pm

#119026

I would like to pull possibly from the Dynamic Management Views (DMV) or system tables the same data that is displayed in SQL Profiler... such as TextData.
Select TextData
From DMV
Where EventClass = 'SQL:BatchStarting'
However, I don't want to invoke the Profiler and run a trace to get this data.
Thanks for you interest and your responses.

Viewing 6 posts - 1 through 5 (of 5 total)

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

martin-210793 Ten Centuries Points: 1083 More actions · Answer 1

Its the profiler that logs these events, now forgive me if I'm wrong and I could well be but I don't see any way of logging these events unless the profiler is running.

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 2

http://microsoft.apress.com/feature/71/sql-server-2005-dynamic-management-and-views

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You can always run a trace serverside if you don't want to use the profiler GUI (which you shouldn't on a production system) See sp_tracecreate in BoL.

As for getting the data from tables, you can, but not as easily as with profiler. A join between dm_exec_requests and dm_exec_sql_text will get you the currently executing commands, but won't get you anything historical.

A join between dm_exec_query_stats and dm_exec_sql_text will get you historical data without any indication of when the query ran (though you will get the date the query first ran and the latest execution)

Try these queries out and see if they are what you want.

-- Historical

SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

-- Currently executing

SELECT * FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

p.s. What you'll get from dm_exec_sql_text in the case of stored procs and functions is the creation script, not the code that executed the procedure which is what you will see in profiler.

So, in profiler you'd see

EXEC DoSomething 1,'abc'

but from the dmv you'll see

CREATE PROCEDURE DoSomething

@theID int, @other VARCHAR(10)

AS

SELECT * FROM MyTable Where tblID = @theID and SomeColumn = @other

Just something to be aware of.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

M Hoss Grasshopper Points: 23 More actions · Answer 5

Thanks for the reply...

I did see the difference in the way it was returned from Query Analyzer (QA) vs through the Stored Procedure (SP) that was using it... This is however where I wanted to use it and I wanted it to display what I saw in QA (similar to Profiler).

I have not finished reading from one of the other post (http://microsoft.apress.com/feature/71/sql-server-2005-dynamic-management-and-views) , but I plan on seeing if it will enlighten me.

Thanks to all who have responded so far.

Mike.