Solved

the sql query relating to exec sp_execute

Posted on 2012-04-10
1
545 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax: How to force case sensitive query? 2 56
point in time restore in SQL server 26 45
Replication failure 1 23
Need to trim my database size 9 26
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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