4

I'm trying to find a way of collecting SQL server query response times over say for the last two days. Is there a way to achieve this? I know there are the query stats DMV's but I dont seem to be achieving what I required. I dont have access to query store therefore unable to use that at the moment either.

I'm looking for query response time from SQL server as I have people blaming the sql server but I can't see no memory pressure / cpu pressure or any bottlenecks. Therefore now trying to get the response times for queries ran against the server.

Thanks

6

There are indeed multiple methods.

Query Store

Since you have SQL Server 2016, may I recommend Query Store? You can enable it per database from the "properties" menu in SSMS, or by running something similar to the following script:

USE [master]
GO
ALTER DATABASE [TestDB] SET QUERY_STORE = ON
GO
ALTER DATABASE [TestDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO)
GO

Once it is turned on, you can look at query performance details.

SSMS view:

Gotchas: Fair warning - turning on Query Store will clear the plan cache! Query Store, as a newer feature, can have some quirks. I've run across several myself. As far as performance, I see it used in environments with 20k batches/sec and only a couple of percent CPU overhead.

DMVs

Another option is to use the dmvs. The one you are looking for in this case is sys.dm_exec_query_stats, specifically the "elapsed_time" columns. Here's a query to look at recent long-duration queries.

SELECT TOP 50
st.text,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(st.text)  
        ELSE qs.statement_end_offset 
    END - qs.statement_start_offset)/2) + 1
) AS statement_text,
qp.query_plan,
qs.execution_count,
qs.last_execution_time,
qs.last_worker_time/1000000.0 AS last_worker_time_s, --this is CPU time
qs.last_elapsed_time/1000000.0 AS last_elapsed_time_s --this is clock time
FROM sys.dm_exec_query_stats qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE last_execution_time > DATEADD(minute,-5,GETDATE()) --last 5 minutes
ORDER BY qs.last_elapsed_time DESC

Gotchas to be mindful of with this dmv are that it doesn't capture RECOMPILE queries, and it doesn't capture canceled queries (which is what happens with a query timeout from the application).

SSMS

Finally, if you know the exact query, you can run it in SSMS and look at the actual execution plan. Highlight the select, and look at the properties window on the right. You'll see extremely helpful information about CPU and duration (in milliseconds). Similar information exists for the operators as well.

Actual Plan

Gotchas with this method include the fact that queries submitted through SSMS do not necessarily reflect what happens with application queries. Canonical link here.

In short, I highly recommend Query Store.

  • Yes that is an option but is this likely to introduce other load problems? As its a heavy database with an average of 3k batches. – william345 Mar 21 at 12:11
  • @user153917 take a look at this blogpost that goes in depth on the possible performance side effects of enabling the query store.Also note Forrest's comment on batches: "I see it used in environments with 20k batches/sec and only a couple of percent CPU overhead". Changing settings in the query store might also flush the plan cache. Look at this link for more on that. Flushing the plan cache will temporarily increase your cpu usage as well. – Randi Vertongen Mar 21 at 12:42
  • Many thanks guys for your swift responses. I shall check this out further based on feedback – william345 Mar 21 at 22:07
1

In addition to great tips above I might suggest to check on light weight extended events.

You can check some of the light events and actions as described here

Depending upon the storage available and no of events being captured you can get the required data with some minimal performance impact.

At the end you need to test and find what's best and within performance range for your server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.