A stored procedure that usually runs fast ran for a long time yesterday

  • An ADO.NET application calls a stored procedure. The response time is usually fast. However, yesterday the response time was very slow. And it caused timeout errors

    I checked activity monitor and there was blocking with wait type PAGELATCH_UP and LATCH_EX as WaitType. Please find the screenshot attached

  • The wait durations in the screenshot are all around 15ms, so probably not significant (timeout is 30 seconds). They're also from all different processes and you haven't indicated which process in the screenshot was the slow running procedure.

    Run the procedure, look at the execution plan, investigate where it's different from the usual execution plan.

    Could have been blocking, but there's no blocking showing in the screenshot. Could be parameter sniffing, could have been statistics, could have been a few other things too.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks a lot for your reply. Actually, there was blocking, self-blocking. I am still not able to understand why the response was very slow only yesterday and not in the other days. I look forward to your valuable inputs

  • That's parallelism waits, not actual blocking

    Have you looked at the execution plans?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Do i need to run the stored procedure now to view the execution plan? It is in production and I cannot run the stored proc now. How can i get the execution plan?

  • You can get the actual plan from when the application runs it with a server-side trace. You can run it manually within a transaction and roll it back, make sure that 'include actual execution plan' is included.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I manurally ran it. Please find the execution plans attached. I am an entry level junior dba and I request your valuable advise.

  • The two plans are for different queries. I've looked at the second one because it causes an optimiser timeout - that is, it's too convoluted for SQL Server to guarantee that the way it's executed the query is as good as it can get. The results will be accurate but it's likely to be slow.

    Both the code and the database exhibit some nasty design flaws. Indexing in the db is haphazard and this is as fine an example of cowboy coding as I've ever seen:

    SELECT

    CabID,

    JobID,

    BidID,

    CabNo,

    BidStatus,

    CASE ISNULL(LongestWaitingTime,'Processing') WHEN 'Processing' THEN 'NA'

    ELSE LongestWaitingTime END AS LongestWaitingTime,

    ISNULL(ShortestDistance,0) AS ShortestDistance,

    SubscriberName,

    SubscriberMobileNo,

    JobDispatch,

    PickUpTime,

    SubscriberID

    FROM ( -- d

    SELECT

    CabID,

    JobID,

    BidID,

    CabNo,

    BidStatus,

    LongestWaitingTime1+':'+LongestWaitingTime2 AS LongestWaitingTime,

    ShortestDistance,

    SubscriberName,

    SubscriberMobileNo,

    JobDispatch,

    PickUpTime,

    Subscriber_ID AS SubscriberID

    FROM ( -- c

    SELECT

    CASE ISNULL(CabID,0) WHEN 0 THEN 0 ELSE CabID END AS CabID,

    JobID,

    BidID,

    CabNo,

    CASE ISNULL(BidStatus,'Processing') WHEN 'Processing' THEN 'Bid Not Sent'

    WHEN 'Bid' THEN 'Bid Accepted'

    WHEN 'Timeout' THEN 'No Bidding'

    WHEN 'AAJ' THEN 'Already Job Awarded'

    ELSE BidStatus END AS BidStatus,

    CONVERT(VARCHAR(30),LongestWaitingTime1/60)+':'+(

    CASE WHEN LEN(CONVERT(VARCHAR(30),LongestWaitingTime1%60))= 1 THEN '0' + CONVERT(VARCHAR(30),LongestWaitingTime1%60)

    ELSE CONVERT(VARCHAR(30),LongestWaitingTime1%60) END )AS LongestWaitingTime1,LongestWaitingTime2,

    CASE ISNULL(ShortestDistance,'Processing') WHEN 'Processing' THEN 'NA' ELSE ShortestDistance END AS ShortestDistance,

    SubscriberName,

    SubscriberMobileNo,

    JobDispatch,

    PickUpTime,

    Subscriber_ID

    FROM ( -- b

    SELECT

    CabID,

    JobID,

    BidID,

    CabNo,

    CASE BidStatus WHEN 'Bid Reply' THEN ReplyStatus

    ELSE BidStatus END AS BidStatus,

    ReplyStatus,

    LongestWaitingTime,

    CONVERT(VARCHAR(30),LongestWaitingTime/60) AS LongestWaitingTime1,

    CASE WHEN LEN(CONVERT(VARCHAR(30),LongestWaitingTime%60))= 1 THEN '0' + CONVERT(VARCHAR(30),LongestWaitingTime%60)

    ELSE CONVERT(VARCHAR(30),LongestWaitingTime%60) END AS LongestWaitingTime2,

    ShortestDistance,

    SubscriberName,

    SubscriberMobileNo,

    JobDispatch,

    PickUpTime,

    Subscriber_ID

    FROM ( -- a

    SELECT

    CabID,

    JobID2 AS JobID,

    BidID,

    CabNo,

    BidStatus,

    ReplyStatus,

    IdleTime AS LongestWaitingTime,

    ShortestDistance,

    '' AS SubscriberName,

    '' AS SubscriberMobileNo,

    JobDispatch,

    PickUpTime,

    Subscriber_ID

    FROM vBiddingDetails_Oncall

    WHERE 1 = 1

    AND CityID = 15

    AND CAST(CONVERT(VARCHAR,CAST(PickUpTime AS DATETIME),101)+' '+CONVERT(VARCHAR,CAST(PickUpTime AS DATETIME),108) AS DATETIME) >=

    CAST('07/24/2015 10:20:51' AS DATETIME)

    AND CAST(CONVERT(VARCHAR,CAST(PickUpTime AS DATETIME),101)+' '+CONVERT(VARCHAR,CAST(PickUpTime AS DATETIME),108) AS DATETIME) <=

    CAST('07/24/2015 18:20:51' AS DATETIME)

    )a

    )b

    )c

    )d

    ORDER BY JobID DESC, JobDispatch ASC

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks a lot for replying. Actually, the stored proc calls another stored proc and hence there are 2 execution plans.Could you please advise on how to improve this proc.

  • I don't know how big is tblBidMaster tables, but an index on CityID should help. You can also try to create an index as suggested by optimizer itself:CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

    ON [dbo].[tblBidMaster] ([CityID])

    INCLUDE ([BidID],[JobID],[PickUpTime])

    But also I see this awful condition:and cast(convert(varchar,cast(PickUpTime as datetime),101)+' '+convert(varchar,cast(PickUpTime as datetime),108) as datetime) >= cast('07/24/2015 10:20:51' as datetime)

    and cast(convert(varchar,cast(PickUpTime as datetime),101)+' '+convert(varchar,cast(PickUpTime as datetime),108) as datetime) <= cast('07/24/2015 18:20:51' as datetime)

    If you put a function around you column name then it becomes non-SARGable (read here) as a result server cannot use indexes and in most of the cases cannot even correctly estimate the number of records that qualify the condition.

    What is the type of PickUpTime? If it's datetime then why do you need to use "cast(convert(cast". If it's (n)varchar then consider converting it to datetime permanently in the table.


    Alex Suprun

  • Thanks for the reply. My doubt is that why did it timeout only on that day and not on the other days?

  • Hi Alex,

    Here is the type of column PickUpTime:

    PickUpTime(udt_Date(datetime),not null)

    udt_Date is user defined type and here is its definition:

    CREATE TYPE [dbo].[udt_Date] FROM [datetime] NOT NULL

    tblBidMaster table size is 84.5 MB and it has 245625 rows.

    Please suggest.

  • Hi Alex,

    Here is the type of column PickUpTime:

    PickUpTime(udt_Date(datetime),not null)

    udt_Date is user defined type and here is its definition:

    CREATE TYPE [dbo].[udt_Date] FROM [datetime] NOT NULL

    tblBidMaster table size is 84.5 MB and it has 245625 rows.

    I look forward to suggestions/inputs

  • coolchaitu (7/26/2015)


    Hi Alex,

    Here is the type of column PickUpTime:

    PickUpTime(udt_Date(datetime),not null)

    udt_Date is user defined type and here is its definition:

    CREATE TYPE [dbo].[udt_Date] FROM [datetime] NOT NULL

    tblBidMaster table size is 84.5 MB and it has 245625 rows.

    Please suggest.

    If it's a datetime then why do you need all this conversions?

    AND CAST(CONVERT(VARCHAR,CAST(PickUpTime AS DATETIME),101)+' '+CONVERT(VARCHAR,CAST(PickUpTime AS DATETIME),108) AS DATETIME) >= CAST('07/24/2015 10:20:51' AS DATETIME)

    AND CAST(CONVERT(VARCHAR,CAST(PickUpTime AS DATETIME),101)+' '+CONVERT(VARCHAR,CAST(PickUpTime AS DATETIME),108) AS DATETIME) <= CAST('07/24/2015 18:20:51' AS DATETIME)

    You can simply replace it with

    AND PickUpTime >= CAST('07/24/2015 10:20:51' AS DATETIME)

    AND PickUpTime <= CAST('07/24/2015 18:20:51' AS DATETIME)


    Alex Suprun

  • Thanks for the reply. My doubt is that why did it timeout only on that day and not on the other days?

Viewing 15 posts - 1 through 15 (of 48 total)

You must be logged in to reply to this topic. Login to reply