cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Report - Query Statistics by Day

Description

This procedure, when executed, will return all SQL statements for the given date range along with key statistics like executions, disk reads, logical reads and others ordered by the metric of choice. See comments inside the procedure for more details on the parameters and methods for calling. Install the stored procedure into the Ignite repository database.

Stored Procedure

IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'QueryStatsbyDay') = 1

DROP PROC QueryStatsbyDay

GO

-- QueryStatsbyDay PROC

--

-- PARMS '@FROM_DATE', '@TO_DATE', '@DBNAME' , '@HASHVALUE'

-- EXAMPLE:

--

-- EXEC QueryStatsbyDay '2008-12-23','2008-12-30','WT10A_DEMO','4011593629'

CREATE PROC QueryStatsbyDay

(@FROM_DATE VARCHAR(10), @TO_DATE VARCHAR(10), @DBNAME VARCHAR(50), @HASHVALUE VARCHAR(30))

AS

DECLARE

@SQL VARCHAR(4000),

@DBID VARCHAR(3)

BEGIN

SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME

SELECT @SQL = 'SELECT CONVERT(VARCHAR,D,101) "Day", ' +

  'SUM(DREADS) "Disk_Reads", ' +

  'SUM(BGETS) "Logical Reads", ' +

  'SUM(EXECS) "Executions", ' +

  'SUM(PARSES) "Logical Writes" ' +

'FROM CONSS_' + @DBID +

' WHERE H= CAST(' + '''' + @HASHVALUE + '''' + ' AS NUMERIC) ' +

'AND D BETWEEN ' +

'CONVERT(DATETIME,' + '''' + @FROM_DATE + ' 00:00:00.000' + '''' + ', 101) AND ' +

'CONVERT(DATETIME,' + '''' + @TO_DATE + ' 23:59:00.000' + '''' + ', 101) ' +

'GROUP BY CONVERT(VARCHAR,D,101) ' +

'ORDER BY CONVERT(VARCHAR,D,101) '

EXEC (@SQL)

END

GO

Labels (1)
Comments

Hi All!

CONSS_ table doesn't have statistics on Events; Cosw_ table does have all statistics but we save only monthly data.

However I am able to  create report Top waits for a single sql on historical data from 2013. Which table in ignite_repository contains historical information on events for a single sql?

DPA has basically two star schema's the one you are looking in with CONSW_ in the center is the detail data. The long term storage data has CON_SAMPLE_SUM_ in the center.

summary data for CONSS_ is in the table CON_SQL_SUM_  and summary data for Events would be CON_EVENT_SUM_

here is a good example on the joins from thwack. Custom Report - Top SQL by Any Metric

Thank you , you answer was very helpful. con_sql_sum_ table is exactly what i was looking for.

Version history
Revision #:
1 of 1
Last update:
‎12-31-2013 12:39 AM
Updated by: