Collect SQL Server Query Execution time in seconds
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
sql-server sql-server-2016
New contributor
add a comment |
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
sql-server sql-server-2016
New contributor
add a comment |
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
sql-server sql-server-2016
New contributor
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
sql-server sql-server-2016
sql-server sql-server-2016
New contributor
New contributor
New contributor
asked Mar 21 at 11:39
user153917user153917
212
212
New contributor
New contributor
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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.
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.
– user153917
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
– user153917
Mar 21 at 22:07
add a comment |
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
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
user153917 is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232707%2fcollect-sql-server-query-execution-time-in-seconds%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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.
– user153917
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
– user153917
Mar 21 at 22:07
add a comment |
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.
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.
– user153917
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
– user153917
Mar 21 at 22:07
add a comment |
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.
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.
edited Mar 21 at 14:31
answered Mar 21 at 11:56
ForrestForrest
2,4861820
2,4861820
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.
– user153917
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
– user153917
Mar 21 at 22:07
add a comment |
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.
– user153917
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
– user153917
Mar 21 at 22:07
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.
– user153917
Mar 21 at 12:11
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.
– user153917
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
@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
– user153917
Mar 21 at 22:07
Many thanks guys for your swift responses. I shall check this out further based on feedback
– user153917
Mar 21 at 22:07
add a comment |
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
add a comment |
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
add a comment |
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
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
answered Mar 21 at 23:53
KASQLDBAKASQLDBA
5,57261944
5,57261944
add a comment |
add a comment |
user153917 is a new contributor. Be nice, and check out our Code of Conduct.
user153917 is a new contributor. Be nice, and check out our Code of Conduct.
user153917 is a new contributor. Be nice, and check out our Code of Conduct.
user153917 is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232707%2fcollect-sql-server-query-execution-time-in-seconds%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown