XSolvo
Current user:   guest    Change     Preferences 
   List directory   History   Similar   Print version 
XSolvo 
   About XSolvo 
Articles 
html2xml 
tradufix 
TVProgramGrabber 
download 
Philosophy 
gallery 

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.

Requirements

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]
GO

select
    rowNumber,
    eventclass,
            dbo.rereplace(
            dbo.rereplace(
            dbo.rereplace(
            dbo.rereplace(
            dbo.rereplace(
            dbo.rereplace(
            dbo.rereplace(
            dbo.rereplace(
            dbo.rereplace(convert(varchar(8000),textdata),
                          '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,
    textdata,
    NTUserName,ClientProcessId,
    Applicationname,
    LoginName,
    SPID,
    duration,
    starttime,
    reads,
    writes,
    cpu
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
    newtextdata,
    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
http://www.xsolvo.com/Articles/PerformanceAnalyzingOfSQLTraceLogs


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