How to find slowest queries
Using Sql Server 2005 Profiler, what events, columns, and filters do you trace to find your slowest queries and stored procedures?
Slow = greater than N seconds, 10 for sake of argument.
sql-server sql-server-2005 profiling
add a comment |
Using Sql Server 2005 Profiler, what events, columns, and filters do you trace to find your slowest queries and stored procedures?
Slow = greater than N seconds, 10 for sake of argument.
sql-server sql-server-2005 profiling
Define "slow". While a query may be slow, this is only relevant to the number of calls made and whether those are critical or not.
– Lucero
May 4 '09 at 13:59
add a comment |
Using Sql Server 2005 Profiler, what events, columns, and filters do you trace to find your slowest queries and stored procedures?
Slow = greater than N seconds, 10 for sake of argument.
sql-server sql-server-2005 profiling
Using Sql Server 2005 Profiler, what events, columns, and filters do you trace to find your slowest queries and stored procedures?
Slow = greater than N seconds, 10 for sake of argument.
sql-server sql-server-2005 profiling
sql-server sql-server-2005 profiling
asked May 4 '09 at 13:57
KM.KM.
83.2k27149189
83.2k27149189
Define "slow". While a query may be slow, this is only relevant to the number of calls made and whether those are critical or not.
– Lucero
May 4 '09 at 13:59
add a comment |
Define "slow". While a query may be slow, this is only relevant to the number of calls made and whether those are critical or not.
– Lucero
May 4 '09 at 13:59
Define "slow". While a query may be slow, this is only relevant to the number of calls made and whether those are critical or not.
– Lucero
May 4 '09 at 13:59
Define "slow". While a query may be slow, this is only relevant to the number of calls made and whether those are critical or not.
– Lucero
May 4 '09 at 13:59
add a comment |
3 Answers
3
active
oldest
votes
In SQL 2005 you can use management views to find slow running queries. A good script i found a while ago on SQL server performance will help get you started; it lists data with the slowest performing first.
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,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
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
1
do you know the unit of measure on the times: total_worker_time, total_elapsed_time, and avg_elapsed_time?
– KM.
May 4 '09 at 14:18
4
The times are in microseconds; MSDN has a good breakdown on the management view msdn.microsoft.com/en-us/library/ms189741.aspx
– u07ch
May 4 '09 at 14:37
1
Yes, those DMV are great - but they are DYNAMIC, too - as their name implies, e.g. they get flushed each time the server starts up. If your server gets rebooted every night, these might not represent a very reliable sample on any given day. So treat those measurements with care - they're dynamic, and might be based on a fairly small sample...
– marc_s
May 4 '09 at 16:42
How would I add a column to this query to give me the stored procedure name?
– Hades
Nov 29 '12 at 0:00
1
@Hades for procedures you are better starting from sys.dm_exec_procedure_stats
– u07ch
Nov 30 '12 at 18:16
|
show 2 more comments
Before I use the profiler, I check the built-in usage reports. Right click a database, Reports, Standard Reports, then Object Execution Statistics.
It lists the currently cached execution plans, along with the amount of resources and the number of times they've been run. This generally gives a very good idea about what's keeping the server busy.
1
@Andomar, Is ok to run that report on a production server? I tried running that report on one of my production servers, and it still said "retreiving data" after one minute. I stopped it to be safe.
– Bill Paetzke
May 6 '10 at 1:16
@Bill Paetzke: The report should be safe for production even if it runs quite long. If you don't trust it, you can check the process list for blocking issues!
– Andomar
May 6 '10 at 12:40
add a comment |
The duration column does it for me, but sometimes I look at the reads and writes columns too.
I use the TSQL:StmtCompleted filter to get the raw queries. You may want to add others like stored procedures to that, but the tsql is the 'base' you need to view. As the MSDN article says
"The execution of a stored procedure
can be monitored by the SP:Starting,
SP:StmtStarting, SP:StmtCompleted, and
SP:Completed event classes and all the
TSQL event classes."
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f820219%2fhow-to-find-slowest-queries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
In SQL 2005 you can use management views to find slow running queries. A good script i found a while ago on SQL server performance will help get you started; it lists data with the slowest performing first.
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,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
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
1
do you know the unit of measure on the times: total_worker_time, total_elapsed_time, and avg_elapsed_time?
– KM.
May 4 '09 at 14:18
4
The times are in microseconds; MSDN has a good breakdown on the management view msdn.microsoft.com/en-us/library/ms189741.aspx
– u07ch
May 4 '09 at 14:37
1
Yes, those DMV are great - but they are DYNAMIC, too - as their name implies, e.g. they get flushed each time the server starts up. If your server gets rebooted every night, these might not represent a very reliable sample on any given day. So treat those measurements with care - they're dynamic, and might be based on a fairly small sample...
– marc_s
May 4 '09 at 16:42
How would I add a column to this query to give me the stored procedure name?
– Hades
Nov 29 '12 at 0:00
1
@Hades for procedures you are better starting from sys.dm_exec_procedure_stats
– u07ch
Nov 30 '12 at 18:16
|
show 2 more comments
In SQL 2005 you can use management views to find slow running queries. A good script i found a while ago on SQL server performance will help get you started; it lists data with the slowest performing first.
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,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
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
1
do you know the unit of measure on the times: total_worker_time, total_elapsed_time, and avg_elapsed_time?
– KM.
May 4 '09 at 14:18
4
The times are in microseconds; MSDN has a good breakdown on the management view msdn.microsoft.com/en-us/library/ms189741.aspx
– u07ch
May 4 '09 at 14:37
1
Yes, those DMV are great - but they are DYNAMIC, too - as their name implies, e.g. they get flushed each time the server starts up. If your server gets rebooted every night, these might not represent a very reliable sample on any given day. So treat those measurements with care - they're dynamic, and might be based on a fairly small sample...
– marc_s
May 4 '09 at 16:42
How would I add a column to this query to give me the stored procedure name?
– Hades
Nov 29 '12 at 0:00
1
@Hades for procedures you are better starting from sys.dm_exec_procedure_stats
– u07ch
Nov 30 '12 at 18:16
|
show 2 more comments
In SQL 2005 you can use management views to find slow running queries. A good script i found a while ago on SQL server performance will help get you started; it lists data with the slowest performing first.
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,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
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
In SQL 2005 you can use management views to find slow running queries. A good script i found a while ago on SQL server performance will help get you started; it lists data with the slowest performing first.
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,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
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
edited Nov 15 '18 at 12:11
Martijn Pieters♦
719k14025092319
719k14025092319
answered May 4 '09 at 14:04
u07chu07ch
10.6k43544
10.6k43544
1
do you know the unit of measure on the times: total_worker_time, total_elapsed_time, and avg_elapsed_time?
– KM.
May 4 '09 at 14:18
4
The times are in microseconds; MSDN has a good breakdown on the management view msdn.microsoft.com/en-us/library/ms189741.aspx
– u07ch
May 4 '09 at 14:37
1
Yes, those DMV are great - but they are DYNAMIC, too - as their name implies, e.g. they get flushed each time the server starts up. If your server gets rebooted every night, these might not represent a very reliable sample on any given day. So treat those measurements with care - they're dynamic, and might be based on a fairly small sample...
– marc_s
May 4 '09 at 16:42
How would I add a column to this query to give me the stored procedure name?
– Hades
Nov 29 '12 at 0:00
1
@Hades for procedures you are better starting from sys.dm_exec_procedure_stats
– u07ch
Nov 30 '12 at 18:16
|
show 2 more comments
1
do you know the unit of measure on the times: total_worker_time, total_elapsed_time, and avg_elapsed_time?
– KM.
May 4 '09 at 14:18
4
The times are in microseconds; MSDN has a good breakdown on the management view msdn.microsoft.com/en-us/library/ms189741.aspx
– u07ch
May 4 '09 at 14:37
1
Yes, those DMV are great - but they are DYNAMIC, too - as their name implies, e.g. they get flushed each time the server starts up. If your server gets rebooted every night, these might not represent a very reliable sample on any given day. So treat those measurements with care - they're dynamic, and might be based on a fairly small sample...
– marc_s
May 4 '09 at 16:42
How would I add a column to this query to give me the stored procedure name?
– Hades
Nov 29 '12 at 0:00
1
@Hades for procedures you are better starting from sys.dm_exec_procedure_stats
– u07ch
Nov 30 '12 at 18:16
1
1
do you know the unit of measure on the times: total_worker_time, total_elapsed_time, and avg_elapsed_time?
– KM.
May 4 '09 at 14:18
do you know the unit of measure on the times: total_worker_time, total_elapsed_time, and avg_elapsed_time?
– KM.
May 4 '09 at 14:18
4
4
The times are in microseconds; MSDN has a good breakdown on the management view msdn.microsoft.com/en-us/library/ms189741.aspx
– u07ch
May 4 '09 at 14:37
The times are in microseconds; MSDN has a good breakdown on the management view msdn.microsoft.com/en-us/library/ms189741.aspx
– u07ch
May 4 '09 at 14:37
1
1
Yes, those DMV are great - but they are DYNAMIC, too - as their name implies, e.g. they get flushed each time the server starts up. If your server gets rebooted every night, these might not represent a very reliable sample on any given day. So treat those measurements with care - they're dynamic, and might be based on a fairly small sample...
– marc_s
May 4 '09 at 16:42
Yes, those DMV are great - but they are DYNAMIC, too - as their name implies, e.g. they get flushed each time the server starts up. If your server gets rebooted every night, these might not represent a very reliable sample on any given day. So treat those measurements with care - they're dynamic, and might be based on a fairly small sample...
– marc_s
May 4 '09 at 16:42
How would I add a column to this query to give me the stored procedure name?
– Hades
Nov 29 '12 at 0:00
How would I add a column to this query to give me the stored procedure name?
– Hades
Nov 29 '12 at 0:00
1
1
@Hades for procedures you are better starting from sys.dm_exec_procedure_stats
– u07ch
Nov 30 '12 at 18:16
@Hades for procedures you are better starting from sys.dm_exec_procedure_stats
– u07ch
Nov 30 '12 at 18:16
|
show 2 more comments
Before I use the profiler, I check the built-in usage reports. Right click a database, Reports, Standard Reports, then Object Execution Statistics.
It lists the currently cached execution plans, along with the amount of resources and the number of times they've been run. This generally gives a very good idea about what's keeping the server busy.
1
@Andomar, Is ok to run that report on a production server? I tried running that report on one of my production servers, and it still said "retreiving data" after one minute. I stopped it to be safe.
– Bill Paetzke
May 6 '10 at 1:16
@Bill Paetzke: The report should be safe for production even if it runs quite long. If you don't trust it, you can check the process list for blocking issues!
– Andomar
May 6 '10 at 12:40
add a comment |
Before I use the profiler, I check the built-in usage reports. Right click a database, Reports, Standard Reports, then Object Execution Statistics.
It lists the currently cached execution plans, along with the amount of resources and the number of times they've been run. This generally gives a very good idea about what's keeping the server busy.
1
@Andomar, Is ok to run that report on a production server? I tried running that report on one of my production servers, and it still said "retreiving data" after one minute. I stopped it to be safe.
– Bill Paetzke
May 6 '10 at 1:16
@Bill Paetzke: The report should be safe for production even if it runs quite long. If you don't trust it, you can check the process list for blocking issues!
– Andomar
May 6 '10 at 12:40
add a comment |
Before I use the profiler, I check the built-in usage reports. Right click a database, Reports, Standard Reports, then Object Execution Statistics.
It lists the currently cached execution plans, along with the amount of resources and the number of times they've been run. This generally gives a very good idea about what's keeping the server busy.
Before I use the profiler, I check the built-in usage reports. Right click a database, Reports, Standard Reports, then Object Execution Statistics.
It lists the currently cached execution plans, along with the amount of resources and the number of times they've been run. This generally gives a very good idea about what's keeping the server busy.
answered May 4 '09 at 14:10
AndomarAndomar
192k35293339
192k35293339
1
@Andomar, Is ok to run that report on a production server? I tried running that report on one of my production servers, and it still said "retreiving data" after one minute. I stopped it to be safe.
– Bill Paetzke
May 6 '10 at 1:16
@Bill Paetzke: The report should be safe for production even if it runs quite long. If you don't trust it, you can check the process list for blocking issues!
– Andomar
May 6 '10 at 12:40
add a comment |
1
@Andomar, Is ok to run that report on a production server? I tried running that report on one of my production servers, and it still said "retreiving data" after one minute. I stopped it to be safe.
– Bill Paetzke
May 6 '10 at 1:16
@Bill Paetzke: The report should be safe for production even if it runs quite long. If you don't trust it, you can check the process list for blocking issues!
– Andomar
May 6 '10 at 12:40
1
1
@Andomar, Is ok to run that report on a production server? I tried running that report on one of my production servers, and it still said "retreiving data" after one minute. I stopped it to be safe.
– Bill Paetzke
May 6 '10 at 1:16
@Andomar, Is ok to run that report on a production server? I tried running that report on one of my production servers, and it still said "retreiving data" after one minute. I stopped it to be safe.
– Bill Paetzke
May 6 '10 at 1:16
@Bill Paetzke: The report should be safe for production even if it runs quite long. If you don't trust it, you can check the process list for blocking issues!
– Andomar
May 6 '10 at 12:40
@Bill Paetzke: The report should be safe for production even if it runs quite long. If you don't trust it, you can check the process list for blocking issues!
– Andomar
May 6 '10 at 12:40
add a comment |
The duration column does it for me, but sometimes I look at the reads and writes columns too.
I use the TSQL:StmtCompleted filter to get the raw queries. You may want to add others like stored procedures to that, but the tsql is the 'base' you need to view. As the MSDN article says
"The execution of a stored procedure
can be monitored by the SP:Starting,
SP:StmtStarting, SP:StmtCompleted, and
SP:Completed event classes and all the
TSQL event classes."
add a comment |
The duration column does it for me, but sometimes I look at the reads and writes columns too.
I use the TSQL:StmtCompleted filter to get the raw queries. You may want to add others like stored procedures to that, but the tsql is the 'base' you need to view. As the MSDN article says
"The execution of a stored procedure
can be monitored by the SP:Starting,
SP:StmtStarting, SP:StmtCompleted, and
SP:Completed event classes and all the
TSQL event classes."
add a comment |
The duration column does it for me, but sometimes I look at the reads and writes columns too.
I use the TSQL:StmtCompleted filter to get the raw queries. You may want to add others like stored procedures to that, but the tsql is the 'base' you need to view. As the MSDN article says
"The execution of a stored procedure
can be monitored by the SP:Starting,
SP:StmtStarting, SP:StmtCompleted, and
SP:Completed event classes and all the
TSQL event classes."
The duration column does it for me, but sometimes I look at the reads and writes columns too.
I use the TSQL:StmtCompleted filter to get the raw queries. You may want to add others like stored procedures to that, but the tsql is the 'base' you need to view. As the MSDN article says
"The execution of a stored procedure
can be monitored by the SP:Starting,
SP:StmtStarting, SP:StmtCompleted, and
SP:Completed event classes and all the
TSQL event classes."
answered May 4 '09 at 14:03
gbjbaanbgbjbaanb
45.3k1089135
45.3k1089135
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f820219%2fhow-to-find-slowest-queries%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
Define "slow". While a query may be slow, this is only relevant to the number of calls made and whether those are critical or not.
– Lucero
May 4 '09 at 13:59