First, you state that:
Yes, this is part of statement in stored procedure.
Is this the only statement in the stored procedure? While not impossible if testing, it would otherwise seem odd to have a stored procedure that sets a variable and then never uses it or does anything else. So, what then of the other statements in the stored procedure? Have all of their times been accounted for?
RPC:Starting — the Remote Procedure Call to exec the proc (i.e. not an ad hoc query batch)
SP:Starting — the stored procedure starts (if it's not in the cache, time will be spent compiling it)
- One or more sets of:
If you want to check for latency, be sure to capture the
EndTime and compare the values between:
- The final
- If there are a lot of result rows being sent back and/or the calling app isn't consuming them quickly enough, that might show up as a larger than expected time difference between the final
- Compile time might be difference between
StartTime of first
- What other statements are in this proc and what are there durations?
Next, are we really talking about seconds here? 40 seconds for a
SELECT of a single column given two
INT (or similar) predicates? Even without an index to help I would expect such a query to return well before 40 seconds, unless there's a lot of contention and/or the table has billions of rows, or something like that. Is it possible that you divided by the wrong number? I think you're supposed to divide by
1000000. If these numbers are correct, then I would be more concerned about why that simple
SELECT is taking 40 seconds than I would be about whatever else is taking up the remaining 24 seconds.