how to trace a given t-sql-statement?

#11

thanks fo far.

let us say, i would like to see over a long period of time, when i.e. a
stored procedure called xyz is crawled. this sp is doing an insert.

assuming i do run a profiler trace - what do i have to enable in order to
see just this sp used? is the aforementioned filter the only way? i would
like to avoid receiving a huge profiler trace costing gb of data....

or - if profiler is not the right idea - what to enable instead?
--
thanks in advance / danke im voraus, fritz
--
Fritz Theiss

"Erland Sommarskog" wrote:

Quote:

Fritz Theiss (fritz (AT) anywhere (DOT) com) writes:
in order to troubleshoot a given scenario i have to find out, which
stored procedure, etc. is firing as a specific piece of t-sql code
(which has been written by different people). how can i figure this out?
i am aware of sql profiler, but in case this is the right tool, what to
to do in detail/which event is to monitor and how can i trace only, if
the given t-sql statement is fired assuming tons of t-sqls are fired a
day?

You could set up a filter on TextData that includes some text from this
piece of code. Assuming that you know what code you are looking for
that is.

It could help if you describe you scenario in little more detail. Is this
T-SQL code a SELECT or an update action?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#12

Fritz Theiss (FritzTheiss (AT) discussions (DOT) microsoft.com) writes:

Quote:

thanks fo far.

let us say, i would like to see over a long period of time, when i.e. a
stored procedure called xyz is crawled. this sp is doing an insert.

assuming i do run a profiler trace - what do i have to enable in order to
see just this sp used? is the aforementioned filter the only way? i would
like to avoid receiving a huge profiler trace costing gb of data....

or - if profiler is not the right idea - what to enable instead?

Profiler is definitely wrong. If trace is the answer, you should run it
server-side. You can still use Profiler to set up the trace, and then
use Export function (under the File menu). If you have multiple filters
just check that the filters are export correctly with regards to AND and
OR. The 2005 Profiler tends to get this wrong.

Whether a server-side trace is the right thing, is hard to tell from
the meagre information you have provided. The thought of a long-
running trace with StmtCompleted in it, is not appealing. Even if you
filter well, generating the events still takes some load.

But I can't really think of any alternatives. You could set up a trigger
on the table that is being inserted to, but there is no way to retrieve
the call stack. You can call DBCC INPUTBUFFER ir you arrange the
permissions, but that may not be sufficient.

Which version of SQL Server are you on?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#13

thanks for this detailed explanation.

i agree, setting a trigger is not an option - due to missing callstack -
here, even it looks to be the best one.

unfortunately all the other ones do not seem to cover my needs either :-(.

to answer your question: i am on sql 2005, but also using sql 2000 from time
to time.

--
thanks in advance / danke im voraus, fritz
--
Fritz Theiss

"Erland Sommarskog" wrote:

Quote:

Fritz Theiss (FritzTheiss (AT) discussions (DOT) microsoft.com) writes:
thanks fo far.

let us say, i would like to see over a long period of time, when i.e. a
stored procedure called xyz is crawled. this sp is doing an insert.

assuming i do run a profiler trace - what do i have to enable in order to
see just this sp used? is the aforementioned filter the only way? i would
like to avoid receiving a huge profiler trace costing gb of data....

or - if profiler is not the right idea - what to enable instead?

Profiler is definitely wrong. If trace is the answer, you should run it
server-side. You can still use Profiler to set up the trace, and then
use Export function (under the File menu). If you have multiple filters
just check that the filters are export correctly with regards to AND and
OR. The 2005 Profiler tends to get this wrong.

Whether a server-side trace is the right thing, is hard to tell from
the meagre information you have provided. The thought of a long-
running trace with StmtCompleted in it, is not appealing. Even if you
filter well, generating the events still takes some load.

But I can't really think of any alternatives. You could set up a trigger
on the table that is being inserted to, but there is no way to retrieve
the call stack. You can call DBCC INPUTBUFFER ir you arrange the
permissions, but that may not be sufficient.

Which version of SQL Server are you on?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

how to trace a given t-sql-statement?

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools

Re: how to trace a given t-sql-statement? - 01-09-2009 , 08:52 AM

Re: how to trace a given t-sql-statement? - 01-09-2009 , 05:03 PM

Re: how to trace a given t-sql-statement? - 01-12-2009 , 03:11 AM