Current user:   guest    Change     Preferences 
   List directory   History   Similar   Print version 
   About XSolvo 

Articles > Performance analyzing of SQL Trace logs

rw-rw-r--   stga   wheel

Performance analyzing of SQL Trace logs

After logging the activity in SQL trace it's difficulty to assembly all information to se what's actually taking resources in the SQL Server. One way to do it is to remove all specific data from the sqltrace text such as parameters, procedure arguments etc. To do this we are going to use regular expressions to update the textdata in the trace file.


You must first extend the SQL Server with an an extended procedure that can process regular expression. Se Regular Expressions in SQL Server.

Analyzing the SQL Trace data

Save the trace data from SQL profiler into a table with the name CurrentTrace in a database created for analyzing trace files. In this example the regular expression procedures should be located in the same database.

Open the Query Analyzer and make the analyze database as current.

Now we update the textdata field in CurrentTrace table according to these rules.

1 Replace '''''.*?''''' -> # (all ''textvars'' in dyn sql
2 Replace 'N''((exec(ute)|select|update|delete) .*?)''' -> { text } to preserve dynsql
3 Replace 'in(\s)*\(([0-9\-,#\s])*?\)' -> IN (#) for dynsql in lists
4 Replace 'N?''.*?''' -> '#'
5 Replace '(([\-\+]?[0-9]+|null))' -> for all numeric/string values

We create a new table with the name xtrace

if exists (
select * from dbo.sysobjects where id = object_id(N'[xtrace]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [xtrace]

                          'N?''''.*?''''','#'), -- Rule 1
                'exec sp_executesql N''(.*?)''','$1'), -- Rule 7
                'N?''((exec(ute)?|select|update|delete) .*?)''',
                          '$1'), -- Rule 2
                'N?''.*?''','#'), -- Rule 3
                '([\-\+]?[0-9]+|null)','#'), -- Rule 4
                'in[\s]*?\([,#\s]*?\)','IN (#)'), -- Rule 5
                '^(declare|set)\s.*?$',''),  -- Rule 6
                '\s+',' '), -- multispaces -> single
                '^\s+','') -- Spaces in begin of row
    as newtextdata,
into xtrace
from CurrentTrace

Now the data is prepared so we will get much more adequate results when grouping the data.

This query will get te top 10 most cpu intensive queries from the trace.

select top 10 * from (
select TOP 100 PERCENT
    count(*) [count],
    sum(cpu) + count(*) as cpu_weight,
    avg(cpu) as avg_cpu,
    min(cpu) as min_cpu,
    max(cpu) as max_cpu,
    sum(cpu) as sum_cpu,
    avg(duration) as avg_duration,
    min(duration) as min_duration,
    max(duration) as max_duration,
    sum(duration) sum_duration,
    sum(duration)-sum(cpu) as diff_duration_cpu
from xtrace
where eventclass = 12
group by newtextdata
order by 3 desc
) as a
views: 111

Reference Performance analyzing of SQL Trace logs

Prev. Check if data changed by quering date columns data   Multiple recordsets Next