After saving Trace file to table , query which will help what query is taking what time and easy to find query taking longest time to run/return result

 SELECT MAX(ObjectName),

    ApplicationName,
    SPID,
    EventClass,
    MIN(RowNumber),
    MAX(RowNumber),
    Min(StartTime),
    MAX(EndTime),
    Convert(VARCHAR(max),TextData) TextData,
    COUNT(1) Count,
    SUM(Duration)/1000000.0 Sum,
    avg(Duration)/1000000.0 Avg,
    min(Duration)/1000000.0 Min,
    max(Duration)/1000000.0 Max,
    AVG(Reads) Reads,     AVG(RowCounts)   RowCounts
FROM Related  WITH (NOLOCK)
WHERE 1=1
AND EventClass <> 15
-- AND Duration > 1000
GROUP BY Convert(VARCHAR(max),TextData),EventClass, SPID, ApplicationName
 --ORDER BY AVG(RowCounts) DESC
ORDER BY SUM(Duration) desc




Query run against all DBs for any customer that has PaperSave for Dynamics GP, Financial Edge, Dynamics SL, Intacct, and Dynamics 365 Business Central that also have the auto entry module installed.

 Query run against all DBs for any customer that has PaperSave for Dynamics GP, Financial Edge, Dynamics SL, Intacct, and Dynamics 365 Business Central that also have the auto entry module installed.

 

Here is the query I have created which will run against all DBs for any customer that has PaperSave for Dynamics GP, Financial Edge, Dynamics SL, Intacct, and Dynamics 365 Business Central that also have the auto entry module installed.

 

use master

DECLARE @db_name nvarchar(max)

DECLARE @string nvarchar(max)

DECLARE @ID INT

DECLARE @sqlCommand NVARCHAR(4000)

if object_ID(N'tempdb..#report') is not null

begin

drop table #report

end

CREATE TABLE #report (Teanant nvarchar(max),HostaApplicationName nvarchar(max),CompnayName nvarchar(max))

DECLARE c_db_names CURSOR FOR

SELECT name FROM sys.databases 

--WHERE name IN ('PaperSaveSP11')

WHERE name not IN('master','model','msdb','tempdb','DBATools','PaperSaveContentSearchTest','PaperSaveGrantEdge','PaperSaveInvoiceReport','PaperSavemylesvcrm','PaperSaveTenants','papersave2') order by name --might need to exclude more dbs

OPEN c_db_names

FETCH c_db_names INTO @db_name WHILE @@Fetch_Status = 0

BEGIN

SET @sqlCommand = 'USE '+@db_name+' ;  select @ID=count(ID) from config where ConfigName =''Auto Entry enabled'' and ConfigValue=''yes'''

EXEC sp_executesql @sqlCommand, N'@ID INT OUTPUT',@ID = @ID OUTPUT

--print @ID

if (@ID>0)

begin

EXEC(' INSERT INTO #report(Teanant,HostaApplicationName,CompnayName) select ''' + @db_name + ''',h.Name,c.CompanyName from ' + @db_name + '..hostapplication h inner join ' + @db_name + '..Company c on h.ID=c.HostApplication_ID where Name = ''Dynamics GP'' or Name = ''Blackbaud''''s The Financial Edge'' or Name =''Microsoft Dynamics SL'' or Name = ''Dynamics 365 Business Central'' or Name = ''Intacct''')

--print @string

end

FETCH NEXT FROM c_db_names INTO @db_name

END

CLOSE c_db_names

DEALLOCATE c_db_names

SELECT * FROM #report