The output of @p1 we’ll asume is 73. Each call to sp_prepexec will get a new @p1 OUTPUT value from SQL Server. Note that the client is trying to assign @P1 to be 1, and SQL Server can choose to honor that request or not. It probably won’t.
Subsequent calls when using Prepared Execution will look something like this:
exec sp_execute 1,’Something’
All subsequent calls will look like this until either the client or SQL Server loses the @P1 mapping. For instance, if either is low on memory the cache may be cleared. On the client side the solution is simple…another sp_prepexec statement is sent. On the SQL Server, if the cache was cleared then the sp_execute call from the client will generate an error which will force the client to send a new sp_prepexec call.
The problem is that the @P1 cached call may remain in cache for hours or even days. When it comes to troubleshooting a client call, how do we get anything useful from exec sp_execute 1,’Something’, such as a stored procedure name?
One Possible Solution
I’ve worked on a project where I knew prepared execution was going to be a nightmare. We mandated that the last parameter to EVERY stored procedure was called @ParameterName with a DEFAULT of NULL. Our data API knew to append this value to all database queries. This made our calls look like this:
exec sp_execute 1,’Something’, ‘MyProc’
Problem solved. Of course, designing a system like this takes forethought.
Profiler can still be used with RPC:Completed events if you also add SP:CacheHit. The two events will have the same StartTime so we can match the two events. Note that you can filter for specific things as normal (database id for instance), but SP:CacheHit will add some overhead on a production system, and it cannot be filtered. If you enable it you will see instance-wide SP:CacheHit events. Use with caution.
Here is the SQLTrace definition:
-- declare variables
declare @rc int
declare @TraceID int
declare @databaseid int
declare @piOptions int
declare @filecount int
declare @maxfilesize bigint
declare @stoptime datetime
-- set variables
-- @TraceID is output from trace initializataion
set @databaseid =(SELECTTOP 1 DATABASE_ID FROMSYS.DATABASESWHERE NAME LIKE'%TND%')
set @piOptions = 2
set @filecount = 20
set @maxfilesize = 2000
set @stoptime =(SELECTDATEADD(HOUR, 8,GETDATE()));
-- Set this to Null to include subprocedures in the cachehit results.
-- Set to 8272 to filter out cache hits of subprocedures
declare @ObjectType INT
set @ObjectType = 8272
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has