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