By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Finding prepared statement sql

P: n/a
Hi,
I'm running SQL Profiler on an SQL Server 2000 database. I see that one
stored procedure gets repeatedly executed having a handle of '1'. This
query takes a long time to complete.
How do I find what the text of the stored procedure is? I cant see any
handle being created (using sp_prepare) with an id of '1' in the
profiler. Is there any way to force the server to re-prepare all
statements so that I can see the statement text and its preparation in
SQL Profiler?

Cheers,
Birju

Jun 2 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
birju (fl*************@gmail.com) writes:
I'm running SQL Profiler on an SQL Server 2000 database. I see that one
stored procedure gets repeatedly executed having a handle of '1'. This
query takes a long time to complete.
How do I find what the text of the stored procedure is? I cant see any
handle being created (using sp_prepare) with an id of '1' in the
profiler. Is there any way to force the server to re-prepare all
statements so that I can see the statement text and its preparation in
SQL Profiler?


As far as I know, it should be enough to kill that connection. But it sounds
strange that there is no sp_prepare. It could be the case that the
sp_prepare is filtered out for some reason?

You should also include the Performance:Showplan event in the trace. The
you would at least see which tables the prepared query accesses.

By the way, if you don't see the query text, how do you know that it's
the execution of a stored procedure? Stored procedure calls should be
called through RPC, and RPC calls are not prepared...

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 2 '06 #2

P: n/a

Erland Sommarskog wrote:
As far as I know, it should be enough to kill that connection. But it sounds
strange that there is no sp_prepare. It could be the case that the
sp_prepare is filtered out for some reason?
The statement is prepared before I start the profiler and the client
keeps that connection - so I can't see its preparation without
restarting the server (it's a web application).
By the way, if you don't see the query text, how do you know that it's
the execution of a stored procedure? Stored procedure calls should be
called through RPC, and RPC calls are not prepared...


Sorry, my bad! It was a prepared statment I was referring to and not a
stored procedure.

What I had to do was copy the database somewhere else and run the
application there so could restart it at will and see the statement
getting prepared.

I never thought about killing the connection - it was a bit too obvious
for me!

Many thanks,
Birju

Jun 5 '06 #3

P: n/a
birju (fl*************@gmail.com) writes:
The statement is prepared before I start the profiler and the client
keeps that connection - so I can't see its preparation without
restarting the server (it's a web application).


Aha. Thanks for the clarification.

It occurred to me one thing that you could try, although I'm not sure
that it works with prepared statements, and that is the function fn_get_sql
that was introduced in SQL 2000 SP3.

From sysprocesses you get a handle, and two statement offsets, start
and end. You feed the handle to fn_get_sql, and get back a table with
object id for the current stored procedure (if applicable), and the text
of the current scope. Then you use the offsets to get the exact statement.

I have it all packaged in a stored procedure aba_lockinfo, that you can
download from http://www.sommarskog.se/sqlutil/aba_lockinfo.html. You
cannot correlate directly with Profiler, but long-running statements are
more likely to turn up in the snapshot it produces.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 5 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.