That is very useful for dba to keep track of the total load per stored procedure or a common query. You can catch some long running queries using filters in profiler but it would be much more interesting to see the total impact per stored procedure, i.e 

proc name, number of calls, total duration, avg duration time, total reads, total cpu, total writes...

Here is described a method which will give you the ability to make such reports.

First of all you will have to run profiler with no filter on your sql server(s) .

Then you have to import the trace data into a table of sql server database. For this purpose you need to have the trace files stored on a sql server local disk.

select *, spname = convert(varchar(100), null), srvnum=3

into trace22march

from ::fn_trace_gettable('C:\traces\sql3_march_22.trc',1)

where trace22march is the destination table name which will be automatically created containing all the necessary fields. You can omit srvnum column if you plan to analyze only 1 server trace

select *, spname = convert(varchar(100), null)

into trace22march

from ::fn_trace_gettable('C:\traces\sql3_march_22.trc',1)

If you used the first query and want to load the other server traces run

insert into trace22march

select *, spname = convert(varchar(100), null), srvnum=1

from ::fn_trace_gettable('C:\traces\sql1_march_22.trc',1)

Changing srvnum for each server trace.

Now that you have uploaded the data you need to parse the traced queries to find a corresponding stored procedure. You will need a sample database that contains all these stored procedures

update t set

spname = o.name

from trace22march t

  inner join sample.sys.objects o on t.textdata like 'execute ' + o.name  + '' collate database_default

where t.spname is null

and o.type = 'P'

update t set

spname = o.name

from trace22march t

  inner join sample.sys.objects o on t.textdata like 'execute ' + o.name  + ' %' collate database_default

where t.spname is null

and o.type = 'P'

update t set

spname = o.name

from trace22march t

  inner join sample.sys.objects o on t.textdata like '% execute ' + o.name  + ' %' collate database_default

where t.spname is null

and o.type = 'P'

update t set

spname = o.name

from trace22march t

  inner join sample.sys.objects o on t.textdata like '%' + o.name  + '%' collate database_default

where t.spname is null

and o.type = 'P'

Here database sample contains all the stored procedures so we can find the corresponding calls. Consider the fact that this query runs quite slowly (1 hour +) and loads the sql server very much. The query may be changed if it is more popular to use exec instead of execute inside your application code.

After this query has been executed you can run analyzing queries

select srvnum, avg(duration)

from dbo.trace22march

group by srvnum

order by avg(duration) desc

this one shows avg execution time per server.

select isnull(spname, 'not defined') as spname, sum(duration) as sumduration, avg(duration) as avgduration, count(*) as calls

from trace22march

where srvnum = 4

group by spname

order by sum(duration) desc

This one shows avg execution time per stored procedure on server4. Skip the where clause and you will see the total stats.

select top 10 *

from trace22march

where spname = 'sp_getusefuldata'

order by duration desc

This one will give you the worst executions of sp_getusefuldata sp.

If you want to compare the traces for 2 different days

select isnull(fri.spname, 'not defined') as [stored procedure], frid as [sum execution time on friday],friad as [friday avg execution time],fricalls as [calls # on friday], mond as [today sum duration],monad as [today avg execution time], moncalls [calls # today]

from (

select spname, sum(duration) as mond, avg(duration) monad, count(*) as moncalls

from traces1hour

group by spname

) mon

inner join (

select spname, sum(duration)as frid, avg(duration) as friad, count(*) as fricalls

from traceinfo10min

group by spname

) fri on isnull(fri.spname, -1) = isnull(mon.spname, -1)

order by frid desc

where  traces1hour is the table containing the second trace, traceinfo10min – first trace.

The top resuts of the queries results are the stored procedures that are subject to optimize.