-- FIRST GOT THE TABLE, NO NEED TO RUN THIS TIME
RETURN
DECLARE @StartTime DATETIME = DATEADD(HOUR, -2, GETDATE()),
@EndTime DATETIME = GETDATE()
EXEC Util.dbo.ProfileImport
@StartTime = @StartTime,
@EndTime = @EndTime,
@ApplicationNameList = 'merge',
@ApplicationNameDelimiter = ',',
@ApplicationName_Equal_WildChar_CharIndex = 2,
@HostNameList = NULL,
@HostNameDelimiter = ',',
@HostName_Equal_WildChar_CharIndex = 2,
@LoginNameList = NULL,
@LoginNameDelimiter = ',',
@LoginName_Equal_WildChar_CharIndex = 2,
@DatabaseNameList = NULL,
@DatabaseNameDelimiter = ',',
@DatabaseName_Equal_WildChar_CharIndex = 2,
@MinDurationSec = NULL,
@TextDataList = NULL,
@TextDataDelimiter = ',',
@TextData_Equal_WildChar_CharIndex = 2,
@Tokenize = 1,
@ExecuteSQL = 1,
@PrintSQL = 0,
@ImportAuditSQLStmt = 0
GO
-- THEN GROUPED BY
SELECT ts.TokenSQL,
COUNT(*) AS COUNTER,
Util.dbo.StringConcat(DISTINCT SPID, ',') AS SPIDs,
SUM(Duration) AS SumDuration,
MIN(Duration) AS MinDuration,
MAX(Duration) AS MaxDuration,
AVG(Duration) AS AvgDuration,
SUM(READS) AS SumReads,
MIN(READS) AS MinReads,
MAX(READS) AS MaxReads,
AVG(READS) AS AvgReads,
SUM(Writes) AS SumWrites,
MIN(Writes) AS MinWrites,
MAX(Writes) AS MaxWrites,
AVG(Writes) AS AvgWrites,
SUM(CPU) AS SumCPU,
MIN(CPU) AS MinCPU,
MAX(CPU) AS MaxCPU,
AVG(CPU) AS AvgCPU,
MIN(StartTime) AS MinStart,
MAX(EndTime) AS MaxEnd
FROM TEMPORARY..ProfileReport_20111104_110745953
CROSS APPLY Util.dbo.GetProcedureFromExecuteSQL(textData) ps
CROSS APPLY (SELECT ISNULL (ps.ProcSQL, tokensql) AS TokenSQL) ts
GROUP BY ts.TokenSQL
ORDER BY SUM(Duration) DESC
GO
-- ANALYZE TRANSACTION LOADS
SELECT d.TokenSQL,
COUNT(*) AS COUNTER,
SUM(Duration) AS SumDuration,
MIN(Duration) AS MinDuration,
MAX(Duration) AS MaxDuration,
AVG(Duration) AS AvgDuration,
SUM(READS) AS SumReads,
MIN(READS) AS MinReads,
MAX(READS) AS MaxReads,
AVG(READS) AS AvgReads,
SUM(Writes) AS SumWrites,
MIN(Writes) AS MinWrites,
MAX(Writes) AS MaxWrites,
AVG(Writes) AS AvgWrites,
SUM(CPU) AS SumCPU,
MIN(CPU) AS MinCPU,
MAX(CPU) AS MaxCPU,
AVG(CPU) AS AvgCPU,
MIN(StartTime) AS MinStart,
MAX(EndTime) AS MaxEnd
FROM TEMPORARY..ProfileReport_20111104_110745953 a
CROSS APPLY (SELECT TOP 1
CntyCd,
datafolder,
DataFolderOrig,
DataSupplierId
FROM #DataFolder
WHERE a.applicationname LIKE datafolder) b
CROSS APPLY (SELECT CASE WHEN textData LIKE '%sp[_]executesql%' THEN textData ELSE TokenSQL END AS TokenSQL) c
CROSS APPLY (SELECT CASE WHEN b.DataFolder IS NOT NULL
THEN REPLACE(REPLACE(REPLACE(c.TokenSQL, b.cntycd, '@CNTY_CD'), DataFolderOrig, ''), DataSupplierId, '@DataSupplier')
ELSE c.TokenSQL
END AS TokenSQL) d
GROUP BY d.TokenSQL
ORDER BY SUM(Duration) DESC
GO