0

I am trying to diagnose slow application performance on a client site.

A log file on the client machine tells me execution time for each query measured From the application side. It appears that many bare-bones simple queries to the remote DB are taking an exorbitant amount of time to complete. For example,

SELECT CONVERT(varchar, GETDATE(), 121)

This query is repeatedly taking over 5 seconds to execute as timed from the application. Other queries almost as simple (inserting one recordset into one table) are taking over a minute to complete. On my test system (with a copy of the client's database) I do not experience any of these problems.

I would suspect a slow network, except that the problem reliably disappears after running a report from Crystal Reports. Then after 1-2 hours the application slows down again.

For the sake of isolating the problem further, I would like to retrieve/log the execution time on the server side. I am trying to figure out what the best way of doing this is. I could use a variable to obtain the execution time for a single query, but I don't have the option of modifying every single query in my application.

sys.dm_exec_query_stats looked very promising for retrieving execution times for previous queries, but the millisecond values it reports for last_elapsed_time seem far too high.

Can anyone help me figure out how to obtain timing for my queries?

qianlong
  • 41
  • 6

1 Answers1

2

Here it is A way

set statistics time on
SELECT CONVERT(varchar, GETDATE(), 121)
set statistics time off

And it will report the time spend for the query as

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
leoh
  • 10,378
  • 7
  • 28
  • 39
  • Unfortunately I need to obtain these statistics for queries run by the application, not for queries that I am executing manually from ssms. – qianlong Feb 25 '14 at 22:06
  • 1
    Well, you can get the same message from your client app. For example: http://stackoverflow.com/questions/765142/access-to-sql-server-messages-via-ado-net And http://stackoverflow.com/questions/5008826/retrieve-set-statistics-io-and-set-statistics-time-values-via-ado-net – leoh Feb 25 '14 at 22:24