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

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.

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.

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.

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