Hi,
We have sql SP's being called from Java code.
The execution on sql server side comes in as a call to "sp_prepexec".
For some reason, whenever @p1 is passed as NULL, the SP goes for a toss..runs for more than 30 secs (our timeout period)..whenver proper value is passed, the execution time is in milli seocnds...Any any one throw ligh on this "sp_prepexec" and the @p1 parameter...like..when/why would it be passed as a NULL...any help would be appreciated...
Thanks
Sam
Below is how the SP code looks like...The plan is perfect..and it runs inside 1 sec 98% of times in the day..its only 2% of the time when it runs long..and in trace we get that value as NULL.
CREATE PROCEDURE [dbo].[MySPname]
@Days INT,
@id int,
@PhoneNumber NVARCHAR(50),
@Velocity int OUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @Startdate DATETIME
DECLARE @CompleteStatus_id INT
SET @StartDate = DATEADD( d , -(@Days) , GETDATE())
SET @Velocity = 0
--This is a call to another SP
EXEC dbo.selCompleteStatusID @CompleteStatus_id OUT
SELECT @velocity = count(distinct ship.id) FROM [dbo].table1 ship with (nolock)
JOIN [dbo].table2 o(nolock) ON ship.ID = o.id AND o.status_id = @CompleteStatus_id
WHERE
ship.modified_dtm >= @StartDate AND
PhoneNumber =@PhoneNumber
END
@P1 which is the @Days parameter in the stored proc does not have a default value. So if you were to call the stored procedure without providing that parameter, it would generate an error. My guess is that the prepared statement is not getting prepared because of that, and it hangs.
One thing you could try is to provide default value for the @Days parameter. For example, like this:
CREATE PROCEDURE [dbo].[MySPname]
@Days INT = NULL, ---<------------THIS
@id INT ,
@PhoneNumber NVARCHAR(50) ,
@Velocity INT OUT
AS
BEGIN
SET NOCOUNT ON
IF (@Days IS NULL) SET @Days = 7; -- look back 7 days. -----< THIS
DECLARE @Startdate DATETIME
DECLARE @CompleteStatus_id INT
.....
It could be that the optimizer compiled the proc for some set of values at the time it was first run, but is now presented with different parameters (@Days = null). You didn't post the whole proc so we can't see how @Days is used in the proc. The different parameter value could mean that the cached execution plan is no good when @Days is null.
We're taking about the @p1 (OUTPUT) parameter to sp_prepexec, right?
I'm surprised it cares whether it is NULL or not but my understanding is that it is to return a Handle to the prepared query - so you can just run sp_execute, which that handle, if you want to run the query again, but with (say) different parameters. You are supposed to them release the handle with sp_unprepare when you are done (AFAIK its session based, so presumably when you close your session etc. then the handle is released anyway, but sounds to me like a memory-leak opportunity, or a memory-hoarder at the least (e.g. if sessions die without being able to tell SQL that they are dead)
Perhaps if you call sp_prepexec with an existing handle it re-uses it but for the new query - i.e. the new query replaces the old. If that IS the case then it suggests that sp_prepexec is running under very strained circumstances - memory is being paged out (very slowly??) or somesuch.
Does it run slow with @P1=NULL immediately after a re-boot (when, presumably, memory would not be under any strain)?
How did JamesK come by the source code for MySPname? I'm sure I'm being thick ... again! ... but definitely curious as I must have missed something really REALLY obvious?!!!
Quite right about @p1. sp_prepexec should not care about it's initial value. In fact, the example in BOL shows it not being initialized -- so it is null in the example.
You are right. I read it as as the @p1 in the dynamic SQL. But he is in fact referring to the first parameter for the sp_prepexec. It can/should be set to null or not set at all (which would be equivalent to NULL)
That's the @p1 (same name ) in the parameter list for the (parametrised) dynamic SQL which sp_prepexec runs. Confusing though ...
It did cross my mind whether there might be a bug if the dynamic SQL happens to use @P1 and thus clashes with the @p1 parameter to sp_prepexec, but all the "auto-generated" code that I had seen which uses sp_prepexec sems to stick to @p1, @p2, ...@Pn naming for its parameters, so it will be a widespread problem if it is indeed a problem!
DECLARE @p1 INT
SET @p1 = NULL --<<<< Parameter to sp_prepexec
DECLARE @p9 NVARCHAR(max)
SET @p9 = N'0'
EXEC sp_prepexec @p1 OUTPUT
,N'@P0 nvarchar(4000)
, @P1 nvarchar(4000) --<<<< This is the @p1 definition for MySPname
, @P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000) OUTPUT'
--vvvv The parameterised call to MySPname including @p1
,N'EXEC MySPname @P0, @P1, @P2, @P3, @P4, @P5 OUT '
,N'7'
,N'0' --<<<< This is the @p1 value for MySPname
,N'12345'
,N'123454'
,NULL
,@p9 OUTPUT
SELECT @p1
,@p9
That's embarrassing and annoying. I'd give good money that that did was not displayed when I originally replied. I had clearly seen your reply though ... I am sure I scrolled back up to "where I must have missed it" and sure I never saw it .... but its quite possible it was there and I was just using Man Eyes, as Mrs K would say!
But I do wonder if the Auto-Refresh on this site might sometime fail to freshen up a post whilst a thread is being replied to. I open all New / Unread posts in fresh tabs before I start replying, so they sit in that tab (for some time if the phone rings ) until I am ready to reply. Perhaps if it was on a tab which was in the background it missed a refresh, and then when in foreground your, newer, reply appeared.
@SamMen : Always worries me to see NOLOCK in code posted on the forum. If you don't know why please ask, folk here would be happy to explain in order to try to reduce its widespread abuse, misues and the damage it causes to data and, sometimes critical, business decisions.
Its happened again, in a different thread. This time I managed to spot, from the messages, that there was one missing so I took a screen-print, refreshed, and took another screen print:
