Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Profiling Parameters Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 5:15 AM This worked for the OP Answer marked as solution
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 590, Visits: 1,579
Hi

I was trying to find the parameters of a query I suspect to be causing unnecessary logical read and eventually captured it in profiler, however parameters @p3 and @p4 are not listed. Is anyone able to explain was SQL server is doing here?

declare @p1 int
set @p1=1073764508
declare @p2 int
set @p2=180527133
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int',N'SELECT A.POPUP,A.EMAIL,A.CREATEDBY,A.BATCHJOBID,101090,B.EMAIL,B.EVENTPOPUPDISPLAYWHEN,B.EVENTEMAILALERTSWHEN,B.ID,101090,C.LANGUAGE,C.ID,101090,D.RECID,D.STATUS,D.CAPTION,D.COMPANY,D.BATCHJOBID,D.STARTDATETIME,D.STARTDATETIMETZID,D.ENDDATETIME,D.ENDDATETIMETZID,D.ORIGSTARTDATETIME,D.ORIGSTARTDATETIMETZID,D.CANCELEDBY FROM BATCHJOBALERTS A,SYSUSERINFO B,USERINFO C,BATCHJOBHISTORY D WHERE (A.CREATEDBY=B.ID) AND (C.ID=B.ID) AND (((D.ALERTSPROCESSED=@P1) AND (D.BATCHJOBID=A.BATCHJOBID)) AND ((((D.STATUS=@P2) AND (A.BATCHJOBENDED=@P3)) OR ((D.STATUS=@P4) AND (A.BATCHJOBERROR=@P5))) OR ((D.STATUS=@P6) AND (A.BATCHJOBCANCELED=@P7))))',@p5 output,@p6 output,@p7 output,1,4,1,3,1,8,1
select @p1, @p2, @p5, @p6, @p7

Many thanks
Post #1563775
Posted Tuesday, April 22, 2014 6:39 AM This worked for the OP Answer marked as solution


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 15,511, Visits: 27,891
Only the output parameters are listed, but if you look at the values at the end, there are 7, same as the number of parameters you have defined within the query.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1563817
Posted Tuesday, April 22, 2014 7:28 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
Parameter @p3 = A.BATCHJOBENDED=@P3 (which is equal to 1)
Parameter @p4 = D.STATUS=@P4 (which is equal to 3)




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1563845
Posted Tuesday, April 22, 2014 8:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 590, Visits: 1,579
Thanks both
Post #1563875
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse