July 2, 2012 at 11:59 pm
Comments posted to this topic are about the item <A HREF="/scripts/Finding+Top+N+worst+performaing+queries/91698/">Finding Top N worst performaing queries</A>
Everything is mentioned in the code .But be careful while executing it. This is doing lots of sorts to get the ranking and it is on different columns. Thus it will do lots of sorts and if your system is alredy under pressure this query might add to your perf issue. I would suggest that you start using one of the counter and comment others and then proceed to make sure that it doesnt put the pressure on the system.
Also, this query will work on SQL server 2008 and compability 100 and above.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 7:50 pm
I must be missing something, when I cut-and-paste the query into SQL, I get the following:
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near '.'.
tried on Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
July 5, 2012 at 9:37 pm
I copied the script and paste in my ssms and it worked fine. I am putting it back here..It should not cause any issues. I took out the comments and repaste the query here.
;with PerformanceMetrics
as
(
select
substring
(
dest.text,
statement_start_offset/2,
case when statement_end_offset = -1 then LEN(dest.text)
else statement_end_offset
end /2
) as 'Text of the SQL' ,
deqs.plan_generation_num as 'Number of times the plan was generated for this SQL',
execution_count as 'Total Number of Times the SQL was executed',
DENSE_RANK() over(order by execution_count desc) as 'Rank of the SQL by Total number of Executions',
total_elapsed_time/1000 as 'Total Elapsed Time in ms consumed by this SQL',
DENSE_RANK() over(order by total_elapsed_time desc) as 'Rank of the SQL by Total Elapsed Time',
Max_elapsed_time/1000 as 'Maximum Elapsed Time in ms consumed by this SQL',
min_elapsed_time/1000 as 'Minimum Elapsed Time in ms consumed by this SQL',
total_elapsed_time/1000*nullif(execution_count,0) as 'Average Elapsed Time in ms consumed by this SQL',
DENSE_RANK() over(order by total_elapsed_time/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Elapsed Time',
total_worker_time as 'Total CPU Time in ms consumed by this SQL',
DENSE_RANK() over(order by total_worker_time desc) as 'Rank of the SQL by Total CPU Time',
Max_worker_time as 'Maximum CPU Time in ms consumed by this SQL',
min_worker_time as 'Minimum CPU Time in ms consumed by this SQL',
total_worker_time/nullif(execution_count,0) as 'Average CPU Time in ms consumed by this SQL',
DENSE_RANK() over(order by total_worker_time/nullif(execution_count,0) desc) as 'Rank of the SQL by Average CPU Time',
total_logical_reads as 'Total Logical Reads Clocked by this SQL',
DENSE_RANK() over(order by total_logical_reads desc) as 'Rank of the SQL by Total Logical reads',
Max_logical_reads as 'Maximum Logical Reads Clocked by this SQL',
min_logical_reads as 'Minimum Logical Reads Clocked by this SQL',
total_logical_reads/nullif(execution_count,0) as 'Average Logical Reads Clocked by this SQL',
DENSE_RANK() over(order by total_logical_reads/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Logical reads',
total_physical_reads as 'Total Physical Reads Clocked by this SQL',
DENSE_RANK() over(order by total_physical_reads desc) as 'Rank of the SQL by Total Physical Reads',
Max_physical_reads as 'Maximum Physical Reads Clocked by this SQL',
min_physical_reads as 'Minimum Physical Reads Clocked by this SQL',
total_physical_reads/nullif(execution_count,0) as 'Average Physical Reads Clocked by this SQL',
DENSE_RANK() over(order by total_physical_reads/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Physical Reads',
total_logical_writes as 'Total Logical Writes Clocked by this SQL',
DENSE_RANK() over(order by total_logical_writes desc) as 'Rank of the SQL by Total Logical Writes',
Max_logical_writes as 'Maximum Logical Writes Clocked by this SQL',
min_logical_writes as 'Minimum Logical Writes Clocked by this SQL',
total_logical_writes/nullif(execution_count,0) as 'Average Logical Writes Clocked by this SQL',
DENSE_RANK() over(order by total_logical_writes/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Logical Writes',
deqp.query_plan as 'Plan of Query'
--similarly you can add the ranks for maximum values as well.That is quite useful in finding some of the perf issues.
from
sys.dm_exec_query_stats deqs
/*F0C6560A-9AD1-448B-9521-05258EF7E3FA*/ --use a newid so that we could exclude this query from the performanc emetrics output
outer apply sys.dm_exec_query_plan(deqs.plan_handle) deqp --sometimes the plan might not be in the cache any longer.So using outer apply
outer apply sys.dm_exec_sql_text(deqs.sql_handle) dest --Sometimes the text is not returned by the dmv so use outer apply.
where
dest.text not like '%F0C6560A-9AD1-448B-9521-05258EF7E3FA%'
)
select
*
from
PerformanceMetrics
where
1=1
--apply any of these where clause in any combinations or one by one..
--and [Rank of the SQL by Average CPU Time] <= 20 --Use this to find the top N queries by avg CPU time.
--and [Rank of the SQL by Average Elapsed Time] <= 20 --Use this to find the top N queries by avg elspsed time.
--and [Rank of the SQL by Average Logical reads] <= 20 --Use this to find the top N queries by avg logical reads.
--and [Rank of the SQL by Average Physical Reads] <= 20 --Use this to find the top N queries by avg physical reads.
and [Rank of the SQL by Total CPU Time] <= 20 --Use this to find the top N queries by total CPU time.
and [Rank of the SQL by Total Elapsed Time] <= 20 --Use this to find the top N queries by total elapsed time.
and [Rank of the SQL by Total Logical reads] <= 20 --Use this to find the top N queries by Total Logical reads.
and [Rank of the SQL by Total Physical Reads] <= 20 --Use this to find the top N queries by Total Physical Reads.
and [Rank of the SQL by Total number of Executions] <= 20 --Use this to find the top N queries by Total number of Executions.
--and [Rank of the SQL by Average Logical Writes] <= 20 --Use this to find the top N queries by Average Logical Writes.
and [Rank of the SQL by Total Logical Writes] <= 20 --Use this to find the top N queries by Total Logical Writes.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 6, 2012 at 10:32 am
Hello,
Still no luck; I copied and pasted the script you posted here. SQL 2008 R2 displays the following:
Msg 102, Level 15, State 1, Line 51
Incorrect syntax near '.'.
Tried to remove all comments -- but just seems to generate more errors; sorry.
July 6, 2012 at 10:43 am
It just run fine on my machione I have 2008R2 as well.
Try to search all the '.' and check if there is any issue specially while copying and pasting.It is difficult for me to provide answer unless i can produce it on my machine.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 6, 2012 at 11:01 am
Ok -- here is the script I tried -- (basically, I just removed ALL comments):
;with PerformanceMetrics
as
(
select
substring
(
dest.text,
statement_start_offset/2,
case when statement_end_offset = -1 then LEN(dest.text)
else statement_end_offset
end /2
) as 'Text of the SQL' ,
deqs.plan_generation_num as 'Number of times the plan was generated for this SQL',
execution_count as 'Total Number of Times the SQL was executed',
DENSE_RANK() over(order by execution_count desc) as 'Rank of the SQL by Total number of Executions',
total_elapsed_time/1000 as 'Total Elapsed Time in ms consumed by this SQL',
DENSE_RANK() over(order by total_elapsed_time desc) as 'Rank of the SQL by Total Elapsed Time',
Max_elapsed_time/1000 as 'Maximum Elapsed Time in ms consumed by this SQL',
min_elapsed_time/1000 as 'Minimum Elapsed Time in ms consumed by this SQL',
total_elapsed_time/1000*nullif(execution_count,0) as 'Average Elapsed Time in ms consumed by this SQL',
DENSE_RANK() over(order by total_elapsed_time/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Elapsed Time',
total_worker_time as 'Total CPU Time in ms consumed by this SQL',
DENSE_RANK() over(order by total_worker_time desc) as 'Rank of the SQL by Total CPU Time',
Max_worker_time as 'Maximum CPU Time in ms consumed by this SQL',
min_worker_time as 'Minimum CPU Time in ms consumed by this SQL',
total_worker_time/nullif(execution_count,0) as 'Average CPU Time in ms consumed by this SQL',
DENSE_RANK() over(order by total_worker_time/nullif(execution_count,0) desc) as 'Rank of the SQL by Average CPU Time',
total_logical_reads as 'Total Logical Reads Clocked by this SQL',
DENSE_RANK() over(order by total_logical_reads desc) as 'Rank of the SQL by Total Logical reads',
Max_logical_reads as 'Maximum Logical Reads Clocked by this SQL',
min_logical_reads as 'Minimum Logical Reads Clocked by this SQL',
total_logical_reads/nullif(execution_count,0) as 'Average Logical Reads Clocked by this SQL',
DENSE_RANK() over(order by total_logical_reads/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Logical reads',
total_physical_reads as 'Total Physical Reads Clocked by this SQL',
DENSE_RANK() over(order by total_physical_reads desc) as 'Rank of the SQL by Total Physical Reads',
Max_physical_reads as 'Maximum Physical Reads Clocked by this SQL',
min_physical_reads as 'Minimum Physical Reads Clocked by this SQL',
total_physical_reads/nullif(execution_count,0) as 'Average Physical Reads Clocked by this SQL',
DENSE_RANK() over(order by total_physical_reads/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Physical Reads',
total_logical_writes as 'Total Logical Writes Clocked by this SQL',
DENSE_RANK() over(order by total_logical_writes desc) as 'Rank of the SQL by Total Logical Writes',
Max_logical_writes as 'Maximum Logical Writes Clocked by this SQL',
min_logical_writes as 'Minimum Logical Writes Clocked by this SQL',
total_logical_writes/nullif(execution_count,0) as 'Average Logical Writes Clocked by this SQL',
DENSE_RANK() over(order by total_logical_writes/nullif(execution_count,0) desc) as 'Rank of the SQL by Average Logical Writes',
deqp.query_plan as 'Plan of Query'
from sys.dm_exec_query_stats deqs
outer apply sys.dm_exec_query_plan(deqs.plan_handle) deqp
outer apply sys.dm_exec_sql_text(deqs.sql_handle) dest
where
dest.text not like '%F0C6560A-9AD1-448B-9521-05258EF7E3FA%'
)
select
*
from
PerformanceMetrics
where
1=1
and [Rank of the SQL by Total CPU Time] <= 20
and [Rank of the SQL by Total Elapsed Time] <= 20
and [Rank of the SQL by Total Logical reads] <= 20
and [Rank of the SQL by Total Physical Reads] <= 20
and [Rank of the SQL by Total number of Executions] <= 20
and [Rank of the SQL by Total Logical Writes] <= 20
SQL Response:
Msg 102, Level 15, State 1, Line 48
Incorrect syntax near '.'.
Line 48 -> outer apply sys.dm_exec_sql_text(deqs.sql_handle) dest
July 9, 2012 at 9:24 am
Any chance you have your compatibility mode set at 80 for the database you are using?
July 9, 2012 at 10:02 am
Wesley Norton ...
Yes! -- the database I was checking was inherited (from SQL 2000) and I did not know the compatibility mode was still at 80 !!!
Thanks!!
July 9, 2012 at 12:23 pm
My apologies. I have not mentioned the version on which It will work. I have tested this on SQL Server 2008 and Compatibility Level 100 only.Also, CTE is not supported in previous version so It wont work on older versions or 2008 Server with compatibility level 100.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 10, 2012 at 2:13 pm
i ran into a situation tempdb was full and terminated after a long run. It seems very heavy to run.
July 11, 2012 at 2:54 am
Yes, as it has to do a number of sorts to get the dense rank so it could be slow and will consume resources.Thus based on the data it could be quite heavy on memory,CPU and tempdb for sorts.
You can comment the ranking based on averages if you are looking at the total or comment the part which you would think is not necessary.
Also, it looks like either your tempdb and memory is quite small or dmv's are having lots of data which means lots of queries ran on your system( it could mean either you have that much normal queries or you have a very heavy adhoc workload).
Thus to filter it further add an extra filter say total logicalread should be more than say 10000 or elapsed time or cpu time should be more than 5 sec etc.. This will reduce the size of the data set and thus query will put the less pressure on your system. Thus start with one or 2 counters like logical read and say CPU time and use a good enough filter.
Can you post the output of the following query?
select execution_count,count(*) from sys.dm_exec_query_stats group by execution_count order by 2 desc
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 11, 2012 at 1:58 pm
When I executed the query on our production database it returned multiple insert statements that were executed 633466,
3004769,
2735369, and so on number of times and I know that they are supposed to do so many inserts.
The total physical reads clocked by these SQL are
2004076,
1344409,
1271453, and so on
The total time elapsed in ms consumed by this sql is as shown
20370469,
16103583,
15422630 and so on
I know there are some indexes on the tables on which these inserts are happening. It could be them that are making the inserts take longer time to do the insert. But I need those indexes to speed up things for other queries. So what is the best way of improving performance in this scenario?
Thanks for your inputs.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
July 11, 2012 at 9:48 pm
It looks like that yours is OLTP system. Do you know when was the last time server got restarted?
These are accumulated since last server start.
If you see you have some phsyical reads and these doesnt sounds bad but it might be possible that some of indexes on your table 's data is not in the memory and thus it has to read from the disk.For the first query on an average it is 3 phsyical reads and if it is normal insert into then it is much more physical IO then it should have it could be possible that some of the index's leaf pages are not in memory and those needs to be updated.
These 3 queries on average takes around 30ms,5ms and 6ms in the order that you mentioned. Main reason could be that you have so many inserts so you have lots of log generated and it has to be written to disk before committing the transaction. Writing log is a sequentila process and thus some of the processes has to wait. Look at the write log wait types and see what they suggest.High values means there could be some contention. Also, as you are doing so many inserts and you mentioned you have a number of indexes and thus the page splitting should be happening.Page splitting is
also expensive and it generates lots of log and thus more log has to be written to the disk and thus it could add to your write log wait types. There could be lots of other reasons and you have to look at those as well.Like you could have high level of contention for pages due to high level of insertions.
It could be possible that you have so many queries being executed on the server against same table(I am not sure for how many day's data DMV has,it will be more clear after that). Thus check the following
find the pagelatch and pageiolatch waits values from sys.dm_db_index_opertaion_stats DMV. It will be useful to tell if the issue is due to some kind of contention or not.
Also, check the sys.dm_db_index_usage_stats view against these tables to find their usage in select ,update etc..
Also, what is the size of the fillfactor you have specified. Check the fragmentation on pages using the sys.dm_db_index_physical_stats for these tables(do not run it for whole db it is time consuming). It might be possible with this large amount of inserts that you might have quite a lot of fragmentation.
Look at the following acg page spaced used in percent and avg fragmentation in percent column from the output of this index.
Check your CPU usage as well because when you run so many tasks then your query might need to wait before being executed as there are so many other tasks already in queue.
Hopefully, it will provide you some kind of information which would be useful for you. But again I would suggest that check when the server was restarted and find per day inserts for these tables and then start tuning.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 12, 2012 at 12:24 pm
The last time the server was rebooted was 3 weeks back
Currently the fragmentation is below 20% on the database. Please find attached screenshot of my top waittypes. I am still not quite sure if index optimization is the right solution.
Thanks for you feedback
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
July 12, 2012 at 12:51 pm
I could see following issue.
1. I could see lots of waits on cxpackate which could be very much normal but that indicates that some of your queries are using parallel plan which usually is considered when you have high cost query either due to lack of indexes or you actually need lots of data to be processed. Thus these queries usually do table scan or index scans and thus brings lots of data into memory from disk and this could be cause of high pageiolatch as well as high physical reads. Also once the pages of these tables or indexes are in cache and when your inserts run they bring the pages into memory and thus clock some physical IO's and thus pageiolatch waits. Thus you have to check whethr you have these kind of eother adhoc or other queries.
Use the query i provided but this time do not use the totall* values but instead use the average values or maybe max values to check some queries which actually takes lots of resources for each run but are ran for few times a day. This should give you some idea on which queries to look further.
2. The pageiolatch* accounts for around 27% of your total waits and this means that it is using a lots of physical IO.It can say two things either you have less memory or some of your queries are not efficient and thus might be causing the physical IO's. Most of these are exlained in above as well.
e.g. One of your insert was executed 633K in 3 weeks which is around 30 times a minutes and I am assuming your system is buys all 24 hours.and each of those execution uses 30 physical reads.This must be causing the pagelaatchIO*. Try to tune this query to reduce the physical IO's to reduce overall physical IO
3. Latch_EX is around 13% this means that there is lots of contention on some of your structures in memory.Do you have lots of heap tables i.e. tables without any clustered indexes? It could be that these tables where you are inserting are heap tables and that is why so many latch_ex is happening.Do you have any varchar(max) columns which actually are storing more than 8000 bytes?
4. writelog waits seems very less as compared to other waits but these are quite high as well and which is kind of expected as you are doing so many inserts. It is around 11 hours wait in last 3 weeks and it is cummulative but still you just chekc that you have your log on separate disk than your data and it is a fast disk as well.
5. You have very high ait time for lck_m_rs_s and it signifies that your queries are using avery high isolation level.Usually, the range shared locks are acquired when you use serilizable isolation level.Then you have othe rlock waits as welll but those do not look bad as you have very high insert activity.But this range share one you should look as it could be avoided by using less restricted isolation level if possible.
Can you post the plan of those 3 inserts if possible?Are these inserts getting data as values or are these getting data using some select statement?
Also, post the data from view sys.dm_db_index_operational_stats for these tables.
You can refer to this whitepaper as well but it is more related to page latches and page io latches and very less towards latch_ex.Even if you get your pageiolatch waits will be reduced,It will imrove the perf overall.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply