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

Profiler shows long duration for sp but actually it is not Expand / Collapse
Posted Friday, June 07, 2013 5:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 360, Visits: 840
I have a sp which is timing out on my website. When i run a trace on it, duration comes ~ 80000 (ms)
So it times out as it is set 30s for site. but when I run the same call after copying from trace in SSMS, it hardly takes 2-3 secs. Why profiler show long durations?
I tries same thing for 5-10 calls to be sure that it is not because of cached data. but they are always fast on SSMS.
Is duration column in profiler accounts for Network time or any other times in it?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1461042
Posted Friday, June 07, 2013 7:10 AM



Group: General Forum Members
Last Login: Yesterday @ 4:02 AM
Points: 177, Visits: 789
The version that you are running may well have a different query plan. You may have an issue with parameter sniffing.

Every day is a school day, and don't trust anyone who tells you any different.
Post #1461066
Posted Saturday, June 08, 2013 3:17 PM



Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
You could also be experiencing blocking. Run one proc now and a statement within it might be blocked for some reason and the proc could take 30 seconds. Run that same proc 60 seconds later and it might complete with no issues in 2-3 seconds.

There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1461304
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse