SQL Server Performance Forum – Threads Archive

Making profiler traces useful for dynamic SQL

Hi. I have performance issues with an application being run on our servers and it uses a lot of dynamic SQL. So I need some way to make the trace output from profiler easier to analyze. I need to be able to group similar statments together so that I can see how often they are called (compared to all calls made) and which are taking the longest to run on average etc. For example a query taking 10 seconds and being called once isn’t as important as a query taking 2 seconds and is run 1000 times in the trace. So I need to convert statments like these:
SELECT col1,col2 FROM MyTable WHERE variable = 1
SELECT col1,col2 FROM MyTable WHERE variable = 2
SELECT col1,col2 FROM MyTable WHERE variable = 3 to:
SELECT col1,col2 FROM MyTable WHERE variable = X Now with tons of different SQL queries it seems a lot of work to identify them all and then convert them like above. I don’t want to do this manually. So I’m wondering if anybody out there has done something like this before and maybe have some basic scripts that I could use as a starting point. /Argyle

one of several reasons i prefer to use stored procedures is that i can parse the profiler trace for the "exec " and the first blank that follows to get the unique stored proc calls
below is my script does so from a table name Trace SELECT wt=SUM(CPU),cnt=count(*),CPU=AVG(CPU),Reads=AVG(Reads),rData
FROM (SELECT CPU,Reads,SUBSTRING(sData,1,CHARINDEX(‘ ‘,sData,1)) AS rData
FROM (SELECT CPU,Reads,SUBSTRING(tData,CHARINDEX(‘exec’,tData,1)+5,100) AS sData
FROM (SELECT CPU,Reads,CONVERT(VARCHAR(7900),TextData) AS tData FROM Trace) t
) s ) r
GROUP BY rData
ORDER BY wt DESC i suppose you could try parsing for text between the "FROM" and "WHERE"
but i am not that will give you distinct queries SELECT wt=SUM(CPU),cnt=count(*),CPU=AVG(CPU),Reads=AVG(Reads),rData
FROM (SELECT CPU,Reads,SUBSTRING(sData,1,CHARINDEX(‘WHERE’,sData,1)) AS rData
FROM (SELECT CPU,Reads,SUBSTRING(tData,CHARINDEX(‘FROM’,tData,1)+5,100) AS sData
FROM (SELECT CPU,Reads,CONVERT(VARCHAR(7900),TextData) AS tData FROM Trace) t
) s ) r
GROUP BY rData
ORDER BY wt DESC

I save the trace data (only textdata and durations) in a table called ‘statements’. There is a stored procedure which looks this in the database that contains the statements table. /*********BEGIN PROCEDURE**********************************/
CREATE PROCEDURE CheckQueries
AS
Create Table #traces (query varchar(1000),duration int)
Insert #traces Select REPLACE(convert(varchar(1000),Textdata),’– Dynamic SQL’,”) as query,
duration from statements
where Textdata like ‘– Dynamic SQL%’ Select query,Count(query) as TimesCalled from #traces
group by query
order by query Select query,duration, count(query) as TimesCalled from #traces
group by query,duration
order by query
GO /****************END PROCEDURE********************************/
When I run the stored procedure it returns 2 resultsets
(1) A summary of no of times each query was called
(2) A expanded view with durations included
As a start this maybe help, some more coding is required for the consolidation of similar queries into a general one. Maybe later.

This is an update to the prevous post. The new stored procedure will consolidate all queries with the same general body. Current Limitation: Will only work with one ‘=’ comparison operator. I am sure you could modify it to include other operators (or even more than one parameter) The trace of SQL<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtStarting events is saved in a table called ‘statements’ and this stored procedure resides in the same database. The procedure will output 3 resultssets which might be able to satisfy you temporarily. /***************START STORED PROCEDURE******************************/ CREATE PROCEDURE CheckQueries AS SET NOCOUNT ON Declare @Paramquery varchar(1000),@QueryBody varchar(1000) Declare @QueryCount int,@LoopCount int Declare @Params varchar(1000) Create Table #traces ( queryid int identity,query varchar(1000),duration int) Create Table #queries(Query varchar(1000)) Insert #traces Select REPLACE(convert(varchar(1000),Textdata),’– Dynamic SQL’,”) as query, duration from statements where Textdata like ‘– Dynamic SQL%’ Select query,Count(query) as TimesCalled from #traces group by query order by query Select query,duration, count(query) as TimesCalled from #traces group by query,duration order by query SET @QueryCount=(Select Count(*) from #traces) SET @LoopCount=0 WHILE @LoopCount<[email protected] BEGIN SET @[email protected]+1 SET @ParamQuery=(SELECT query from #traces where [email protected]) SET @QueryBody=LEFT(@ParamQuery,PATINDEX(‘%=%’,@ParamQuery)) IF NOT @querybody IS NULL Insert #queries(query) VALUES (@QueryBody + ‘X’) END SELECT query,count(query) as TimesCalled from #queries group by query GO /***************END STORED PROCEDURE******************************/ I hope this helps.

Thx. I’ll give these scripts a try <img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ /> Currently I’ve been identifying the different SQL queries manually and then been doing replaces like: update mytrace set textdata = ‘select col1,col2 from mytable where variable = X' where textata like ‘select col1,col2 from mytable where variable = %' for each query I find wich is a hassle <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /> I’ll work with the scripts you supplied and see what I can come up with. joechang: I agree that SPs are the best but unfortunately it’s not always one as a DBA have an influence on how developers choose to implement their code <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /> /Argyle

Give us a shout if the scripts are of benefit. I know they can be improved upon. Side Note: The DBA should know what’s good for their DB. Influencing developers can only be done by showing them why SPs are good (If you truly believe that they are)

]]>