Solved

the sql query relating to exec sp_execute

Posted on 2012-04-10
1
525 Views
Last Modified: 2012-08-13
in profiler, how can we know the exactl select statement, from the sp_execute of the xml like below..

exec sp_execute 101,4655,N'<report xmlns................</reportName></report>'
0
Comment
Question by:25112
1 Comment
 
LVL 29

Accepted Solution

by:
Randy Downs earned 500 total points
ID: 37828222
Maybe this will help - http://santoshkmrsingh.blogspot.com/2010/05/sql-server-spexecute.html

SQL Server sp_execute
We commonly come across this statement when profiling SQL Server i have wondered a lot about what it is and there was some explanation i found for it in the BOL.

The ODBC API defines prepared execution as a way to reduce the parsing and compiling overhead associated with repeatedly executing a Transact-SQL statement. The application builds a character string containing an SQL statement and then executes it in two stages. It calls SQLPrepare once to have the statement parsed and compiled into an execution plan by the database engine. It then calls SQLExecute for each execution of the prepared execution plan. This saves the parsing and compiling overhead on each execution. Prepared execution is commonly used by applications to repeatedly execute the same, parameterized SQL statement.

thus sp_execute is a system stored procedure used with "prepared" statements from a client. The number you see is an internal pointer to the execution plan on the server. The values following that number are the parameters for a particular invocation of the prepared statement


Now when we see sp_execute in the profile and we need to know the text behind it then first thing we can do it to check if there is an "sp_prepare" with the same number in the thread, if there is then you include sql_handle in the trace and you look can up the text for the cache entry using the following statement

select text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(plan_handle)
where session_id = [spid]


you can also enable Prepare SQL event in the profiler to get the handle of the execution plan

I found this problem where a prepared sql was taking much longer to execute when i executed the same sql in the management studio it was actually fast so i am wondering what could make a unprepared sql faster then a prepared sql?

I thought maybe the statistics are out so created the statistics for this table
with a FULLSCAN option and that really helped still not convinced why? maybe i will have to study the execution plan in both the cases and spot the difference i believe that different execution plans are being used in these cases.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Update Query - What's wrong with this. 18 20
SQL compatability in SQL 2016 2 31
Implementing SQL Server Data Files in Azure 1 35
SQL Server stored proc 2 10
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now