July 24, 2015 at 2:57 am
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
July 24, 2015 at 3:16 am
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
July 24, 2015 at 4:28 am
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
July 24, 2015 at 4:57 am
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
July 24, 2015 at 5:20 am
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?
July 24, 2015 at 5:26 am
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
July 24, 2015 at 5:57 am
I manurally ran it. Please find the execution plans attached. I am an entry level junior dba and I request your valuable advise.
July 24, 2015 at 7:14 am
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
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
July 24, 2015 at 9:07 am
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.
July 24, 2015 at 12:04 pm
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.
July 26, 2015 at 6:47 am
Thanks for the reply. My doubt is that why did it timeout only on that day and not on the other days?
July 26, 2015 at 10:40 am
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.
July 27, 2015 at 2:14 am
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
July 27, 2015 at 11:39 am
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)
July 27, 2015 at 12:25 pm
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