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

Custom Report - Top SQL for Database

Description

This procedure, when executed, will return all SQL statements for the given date range and database ordered by the wait time. The data also includes the database user, machine, program, SQL text, last execution date ad number of executions. 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 'TopSQLforDatabase') = 1

DROP PROC TopSQLforDatabase

GO

-- TopSQLforDatabase PROC

--

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

-- EXAMPLE:

--

-- EXEC TopSQLforDatabase '2008-12-23 00:00','2008-12-30 00:00','YM-SQL02','AppData'

CREATE PROC TopSQLforDatabase

(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @INAME VARCHAR(50), @DBNAME VARCHAR(50))

AS

DECLARE

@SQL VARCHAR(4000),

@DBID VARCHAR(3)

CREATE TABLE #TOPSQLALLDATA

  (SQL_HASH VARCHAR(100),

  TIMESECS BIGINT,

  DBUSER_ID VARCHAR(100),

  MACHINE_ID VARCHAR(100),

  PROGRAM_ID VARCHAR(100),

  DBUSER VARCHAR(100),

  MACHINE VARCHAR(100),

  PROGRAM VARCHAR(100),

  SQL_TEXT VARCHAR(500),

  LAST_DATE DATETIME,

  EXECUTIONS BIGINT)

BEGIN

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

SELECT @SQL = 'INSERT INTO #TOPSQLALLDATA ' +

  '(SQL_HASH,TIMESECS,DBUSER_ID,MACHINE_ID,PROGRAM_ID,LAST_DATE) ' +

  'SELECT SW.IZHO, SUM(SW.QP)/100 TIMESECS' +

  ', SW.XCUW,SW.PWMY,SW.UDPW,MAX(SW.D) ' +

  'FROM CONSW_' + @DBID + ' SW ' +

  'INNER JOIN cono_' + @dbid + ' o ON sw.ixoy = o.id ' +

  'WHERE SW.D BETWEEN ' +

  'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +

  'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +

  'AND UPPER(O.NAME) = UPPER(' + '''' + @DBNAME + '''' + ') ' +

  'GROUP BY SW.IZHO,SW.XCUW,SW.PWMY,SW.UDPW'

EXEC (@SQL)

SELECT @SQL = 'UPDATE #TOPSQLALLDATA ' +

  'SET DBUSER = (SELECT NAME FROM CONU_' + @DBID + ' U ' +

  'WHERE DBUSER_ID = U.ID)'

EXEC (@SQL)

SELECT @SQL = 'UPDATE #TOPSQLALLDATA ' +

  'SET MACHINE = (SELECT NAME FROM CONM_' + @DBID + ' M ' +

  'WHERE MACHINE_ID = M.ID)'

EXEC (@SQL)

SELECT @SQL = 'UPDATE #TOPSQLALLDATA ' +

  'SET PROGRAM = (SELECT NAME FROM CONPR_' + @DBID + ' P ' +

  'WHERE PROGRAM_ID = P.ID)'

EXEC (@SQL)

SELECT @SQL = 'UPDATE #TOPSQLALLDATA ' +

  'SET SQL_TEXT = (SELECT SUBSTRING(ST,1,500) FROM CONST_' + @DBID + ' S ' +

  'WHERE SQL_HASH = S.H AND S.P = 0)'

EXEC (@SQL)

SELECT @SQL = 'UPDATE #TOPSQLALLDATA ' +

  'SET EXECUTIONS = (SELECT SUM(EXECS) FROM CONSS_' + @DBID + ' SS ' +

  'WHERE SQL_HASH = SS.H ' +

  'AND SS.D BETWEEN ' +

  'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +

  'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101))'

EXEC (@SQL)

SELECT @SQL = 'UPDATE #TOPSQLALLDATA ' +

  'SET SQL_HASH = (SELECT NAME FROM CON_SQL_NAME N ' +

  'WHERE SQL_HASH = N.HASH) ' +

  'WHERE SQL_HASH IN (SELECT HASH FROM CON_SQL_NAME)'

EXEC (@SQL)

SELECT SQL_HASH,TIMESECS,DBUSER,MACHINE,PROGRAM,SQL_TEXT,LAST_DATE,EXECUTIONS

FROM #TOPSQLALLDATA

ORDER BY TIMESECS DESC

DROP TABLE #TOPSQLALLDATA

END

GO

Labels (1)
Tags (3)
Comments

Thank you for this code.  It serves as an excellent reference point for metadata and thus custom reporting from DPA!

Could you please add Total Total  ExecutionTime, Total CPU, Total IO etc?

can this procedure be used/called to get a custom report in DPA for a specific database?

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