0

I have a specific stored procedure that executes in a SQL Server in about 0.250 seconds, when executed from SQL Server Management Studio, although it takes half a minute to display the resulting dataset of about 100 records in a desktop application.

Is it possible for a DBA to find out when exactly that specific call, to execute this procedure, arrives at the server and thus determining if the problem is situated before or after it is taken up by the server and the execution starts?

Is it possible that a server puts the procedure on the end of his 'list' of procedures to execute and where and how can this be traced?

The procedure does have a history of excessive use of resources, since the original version took about a minute to execute. But the current version certainly does not.

Improve this question
9
  • 2
    You could use extended events to store details of statements executed. If, however, you want some kind of verbose logging, you'd be better off adding that logic to your procedure. If you're asking how to do that some an execution that already occured, you don't unless you have something in place to already store that information; but if you did you wouldn't be here. Commented May 9 at 14:21
  • 5
    FYI, SQL Server 2008 reached end of life almost 6 years ago; it is long past time you got onto a fully supported version of SQL Server (now only SQL Server 2022). Using such an outdated version is a significant concern for you, your employer, and especially your customers. Commented May 9 at 14:21
  • The statement is executed properly and the profiler can tell exactly when it does and how much time it takes. My question concerns determining the exact time the application call arrives on the server. Is it possible to set up a log for a specific stored procedure and a specific user with the time it arrived, the execution started and finaly ended? For your second comment: ican only confirm your thoughts about the version that is used, no further comment on that. Commented May 9 at 15:43
  • 1
    You could be fighting multiple issues here... 1. client libraries commonly have different plan-affecting @@options set than SSMS uses (e.g.: ARITHABORT) that can cause different execution plans to be created (and so perform well in SSMS but horribly in an application). 2. SSMS has tricks to limit the amount of data it retrieves for large binary/character/XML values so that it appears to run much faster than applications (which have to retrieve everything). 3. Network latency matters, especialy for extremely "chatty" protocols like MS-TDS. Commented May 9 at 23:08
  • 1
    wrt. the plan-affecting @@options, I highly recommend setting aside a day to read, fully understand, and experiment with Erland Sommarskog's Slow in the Application, Fast in SSMS? Understanding Performance Mysteries. Commented May 9 at 23:11
Related questions
4347
UPDATE from a SELECT
1869
Insert results of a stored procedure into a temporary table
1163
Search text in stored procedure in SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.