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.

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