Top 15 SQL Statements by wait time

Hi,

I need the same output as the Top SQL statements by wait time graph, but in csv/xls format.

To this end, I have been trying to extract the data directly from the database, but I am unsure of how to accomplish this.

I thought the numbers in CON_STATS_SUM_ were what I needed, but when extracting data based off of it for a certain date, the numbers I get in the query do not match the numbers I get in the graph from the DPA interface.

This is what I've tried:

with top_statements
as
(
     select
          SQLHASH,
          DATEHOUR,
          TIMESECS,
          ROW_NUMBER() over (partition by DATEHOUR order by TIMESECS desc) RN
     from
          ignite.CON_STATS_SUM_1
     where
               DATEHOUR between '19 Sep 2018' and '20 Sep 2018'
)
select
     DATEHOUR,
     SUM(TIMESECS)
from
     top_statements
where
     RN <= 15
group by
     DATEHOUR

I would like to use the exact same query that the graph is based on, as this will be submitted to a group that will be reviewing them along side the graphs (so the numbers must match).

I just don't know where that query is...

* This is for a Sybase ASE instance.

Any help with this would be great.

Thanks!

Find more posts tagged with

database performance analyser

sybase

ase

sql query*

Accepted answers

darichar

Your query looks at the top SQL statements for the one day you entered and then breaks the data out into hourly summaries, which is completely valid. However, the DPA chart does it differently in that it only uses the Top 15 from the 30 day trend view to keep the charts consistent. To match up data, you would have to get the topn queries using 30 days and then get the stats for the day. You may not have excluded any SQLs from the trend views, but this query also takes those into consideration.

Something like this is what DPA uses:

select ss.datehour, sum(ss.timesecs) as timesecs

from (

select top 15 alldata.sqlhash, sum(alldata.timesecs) as timesecs

from (

-- full days for up to 30 days ago

select SQLHASH, TIMESECS

from ignite.CON_STATS_DAY_SUM_2

where DATEHOUR between CONVERT(DATETIME, '2018-09-04 00:00:00', 121) and CONVERT(DATETIME, '2018-10-03 00:00:00', 121)

union all

-- the day so far today

select SQLHASH, TIMESECS

from ignite.CON_STATS_SUM_2 where DATEHOUR >= CONVERT(DATETIME, '2018-10-04 00:00:00', 121) and DATEHOUR < CONVERT(DATETIME, '2018-10-04 23:59:59', 121)

) ALLDATA

left outer join ignite.CON_EXCLUDED_SQL on CON_EXCLUDED_SQL.DBID=2 and ALLDATA.SQLHASH=CON_EXCLUDED_SQL.SQLHASH

where ALLDATA.SQLHASH > -10

and CON_EXCLUDED_SQL.SQLHASH is null

group by ALLDATA.SQLHASH

order by timesecs desc) as topn

inner join ignite.CON_STATS_SUM_2 ss on ss.sqlhash=topn.sqlhash

left outer join ignite.CON_SQL_NAME n on topn.sqlhash=n.HASH

where ss.datehour between '19 Sep 2018' and '20 Sep 2018'

group by ss.datehour

All comments

darichar

Something like this is what DPA uses:

select ss.datehour, sum(ss.timesecs) as timesecs

from (

select top 15 alldata.sqlhash, sum(alldata.timesecs) as timesecs

from (

-- full days for up to 30 days ago

select SQLHASH, TIMESECS

from ignite.CON_STATS_DAY_SUM_2

where DATEHOUR between CONVERT(DATETIME, '2018-09-04 00:00:00', 121) and CONVERT(DATETIME, '2018-10-03 00:00:00', 121)

union all

-- the day so far today

select SQLHASH, TIMESECS

from ignite.CON_STATS_SUM_2 where DATEHOUR >= CONVERT(DATETIME, '2018-10-04 00:00:00', 121) and DATEHOUR < CONVERT(DATETIME, '2018-10-04 23:59:59', 121)

) ALLDATA

left outer join ignite.CON_EXCLUDED_SQL on CON_EXCLUDED_SQL.DBID=2 and ALLDATA.SQLHASH=CON_EXCLUDED_SQL.SQLHASH

where ALLDATA.SQLHASH > -10

and CON_EXCLUDED_SQL.SQLHASH is null

group by ALLDATA.SQLHASH

order by timesecs desc) as topn

inner join ignite.CON_STATS_SUM_2 ss on ss.sqlhash=topn.sqlhash

left outer join ignite.CON_SQL_NAME n on topn.sqlhash=n.HASH

where ss.datehour between '19 Sep 2018' and '20 Sep 2018'

group by ss.datehour

ziv

Thank you very much!

While I ended up with some minor changes to the code (among the changes, we don't exclude anything) , your tip and code were exactly what I needed to get this done!!!

select
     SST.datehour,
     SNT.name,
     sum(SST.timesecs) as 'timesecs'
from
     (
     select top 15
          ADT.sqlhash,
          ADT.sum_secs as timesecs
     from (
          -- full days for up to 30 days ago
          select
               SQLHASH,
               sum(TIMESECS) as 'sum_secs'
          from
               ignite.CON_STATS_DAY_SUM_1
          where
               DATEHOUR < dateadd(dd,-30,getdate())
          group by
               SQLHASH
          ) ADT
     where
          ADT.sqlhash > -10
     order by
          sum_secs desc
     ) TNT
     join ignite.CON_STATS_SUM_1 SST
     on SST.sqlhash = TNT.sqlhash
     left outer join ignite.CON_SQL_NAME SNT
     on TNT.sqlhash = SNT.hash
where
     SST.datehour between '19 Sep 2018' and '19 Sep 2018 23:59:59'
group by
     SST.datehour,
     SNT.name
order by
     SST.datehour,
     sum(SST.timesecs) desc