Custom Report - Top SQL for Database User

Question

Description

This procedure, when executed, will return all SQL statements for the given date range and database user 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 'TopSQLforDBUser') = 1

DROP PROC TopSQLforDBUser

GO

-- TopSQLforDBUser PROC

--

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

-- The @DBNAME can be found from the following query: Select NAME from ignite.cond

-- EXAMPLE:

--

-- EXEC TopSQLforDBUser '2014-2-23 00:00','2014-12-30 00:00','DBNAMEHERE','sa'

create PROC TopSQLforDBUser

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

AS

DECLARE

@SQL VARCHAR(4000),

@DBID VARCHAR(3)

BEGIN

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

SELECT @SQL = 'SELECT ISNULL(N.NAME, SW.IZHO) HASH_OR_NAME, SUM(SW.QP/100) TIMESECS, ST.ST SQLTEXT ' +

'FROM CONSW_' + @DBID + ' SW LEFT OUTER JOIN CON_SQL_NAME N ON SW.IZHO = N.HASH ' +

'LEFT OUTER JOIN CONST_' + @DBID + ' ST ON SW.IZHO = ST.H ' +

'INNER JOIN CONU_' + @DBID + ' U ON SW.XCUW = U.ID ' +

'WHERE SW.D BETWEEN ' +

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

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

'AND UPPER(U.NAME) = UPPER(' + '''' + @DBUSER + '''' + ') ' +

'GROUP BY ISNULL(N.NAME, SW.IZHO), ST.ST ' +

'ORDER BY SUM(SW.QP/100) DESC '

EXEC (@SQL)

END

GO

dpaadmin · Answer

Thank you for posting this code. It was super useful. I just modified it as I see that all tables in DPA database have schema called "ignite". It's working as of 8/6/2021.

declare

@from_date varchar(16) = '2021-05-03 00:00',
@to_date varchar(16) = '2021-08-07 00:00',
@iname varchar(50) = '<INSERT SERVER NAME>',
@dbname varchar(50) = '<INSERT DATABASE NAME>',
@sql varchar(4000),
@dbid varchar(3)

if object_id('tempdb..#topsqlalldata') is not null
begin
drop table #topsqlalldata
end

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 ignite.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 ignite.consw_' + @dbid + ' sw ' +
'inner join ignite.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'
print @sql
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set dbuser = (select name from ignite.conu_' + @dbid + ' u ' +
'where dbuser_id = u.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set machine = (select name from ignite.conm_' + @dbid + ' m ' +
'where machine_id = m.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set program = (select name from ignite.conpr_' + @dbid + ' p ' +
'where program_id = p.id)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set sql_text = (select substring(st,1,500) from ignite.const_' + @dbid + ' s ' +
'where sql_hash = s.h and s.p = 0)'
exec (@sql)

select @sql = 'update #topsqlalldata ' +
'set executions = (select sum(execs) from ignite.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 ignite.con_sql_name n ' +
'where sql_hash = n.hash) ' +
'where sql_hash in (select hash from ignite.con_sql_name)'
exec (@sql)

select sql_hash,timesecs,dbuser,machine,program,sql_text,last_date,executions
from #topsqlalldata
order by last_date desc
--order by timesecs desc

if object_id('tempdb..#topsqlalldata') is not null
begin
drop table #topsqlalldata
end
--end
go