This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Query stopped showing in Historical Trend's after improvements were made

outersource over 9 years ago

Hi folks,

I recently stumbled upon a query whilst analyzing our database. I ended up making some tweaks to the underlining index taking it from 2000+ reads to 8 reads, speeding up the query significantly (it's a high execution stored procedure). Now when I look at the historical trend chart for this stored procedure it's dropped off my radar and not reporting anything for today. When I capture the stored procedure in SQL Profiler however, I can see still the stored procedure being hit.

Does DPA have a threshold that a query has to pass before it can be picked up for reporting? If so, where can I alter this threshold? I would like to show management the improvements being made to queries using the nice DPA charts/reports, however if queries just stop showing up once improvements are made I cannot do this nicely without putting together something myself (and that involves work ).

Thanks folks!

Michael Murray

P.S. DPA rocks!

Top Replies

0 roballen over 9 years ago

DPA does have a threshold for the number of queries that get summarized. By default, it only summarizes the top 500 queries based on wait time. This can be increased by changing the support option "TREND_MINRANK" to something higher. However, increasing this value also means that your repository will grow.
To change the TREND_MINRANK value, click Options, then the Administration tab, then click the Advanced Options button. On the System Options tab, check the "Support Options" check box and search for "TREND_MINRANK".
Cancel
Vote Up 0 Vote Down

Cancel
0 outersource over 9 years ago in reply to roballen

Great thanks! Is there anyway of figuring out where my query falls in the top xxx queries based on wait time before I adjust this value? is there a adhoc query I can run on the repository first to figure this out?
Cancel
Vote Up 0 Vote Down

Cancel
0 roballen over 9 years ago in reply to outersource

The following SQL will show you the current rank of your query for today. If the query doesn't make the top N for the day, it may get cleaned out of CONSW_X and the data will be no longer exist.
You will need to know the SQLHASH of the query you are interested in as well as the database id of the monitored database. Change CONSW_X to the database id (ie. CONSW_1).
SQL Server - ROW_NUMBER() only supported on SQL Server 2008+
select *
from (select SW.IZHO SQLHASH,
             sum(SW.QP)/100 TIMESECS,
             ROW_NUMBER() OVER(ORDER BY sum(SW.QP)/100 DESC) AS RANK
      from CONSW_X SW with (nolock)
      where SW.D between DATEADD(dd, DATEDIFF(dd,0, GETDATE()), 0) and GETDATE()
      and SW.IZHO > 0
      group by SW.IZHO
     ) r
where SQLHASH = ?
Oracle
select *
from (select SW.IZHO SQLHASH,
             sum(SW.QP)/100 TIMESECS,
             ROW_NUMBER() OVER(ORDER BY sum(SW.QP)/100 DESC) AS RANK
      from CONSW_X SW
      where SW.D between TRUNC(SYSDATE) and SYSDATE
      and SW.IZHO > 0
      group by SW.IZHO
     ) r
where SQLHASH = ?
Cancel
Vote Up +1 Vote Down

Cancel
0 outersource over 9 years ago in reply to roballen

Thanks again! My query is now ranked 2207... I wonder what it was prior to my change
Cancel
Vote Up 0 Vote Down

Cancel
0 roballen over 9 years ago in reply to outersource

You can find out by changing the dates in the between predicate:
where SW.D between '2014-10-03 00:00:00' and '2014-10-03 23:59:59'
If the query was in the top 500, the data should remain in the CONSW table for 30 days.
Cancel
Vote Up +1 Vote Down

Cancel
0 outersource over 9 years ago in reply to roballen

That's great! I missed that, this helps greatly
Cancel
Vote Up 0 Vote Down

Cancel