Itzik Ben-Gan describes a method of analyzing trace files in his book "Inside Microsoft SQL Server 2005: T-SQL Querying" He provides a couple functions for stripping the literals from queries in the TextData column. If you do not use one of these functions or one of your own similar tools, you are not being very effective in your trace analyisis. The first one is from Microsoft PSS and can be downloaded here. The second one is a CLR function that uses regex. It performs much better. His book is worth it just for this function.
Here is the concept: Tuning single queries can be insane ineffective for multiple reasons. When facing a performance problem, you should be looking at query patterns that way to can get the biggest bang in the shortest amount of time.
Here is a simple trace analysis query using the PSS function.
-- Generate pattern and order by highest CPU
-- This is long running so you may want to dump it into a table.
--http://statisticsio.com/files/patterns/fn_tsqlsig.sql
SELECT dbo.fn_SQLSigTSQL(textdata, 4000)
, SUM(CPU)
, COUNT(*)
FROM dbo.trace_table
GROUP BY dbo.fn_SQLSigTSQL(textdata, 4000)
order by CPU DESC
Check out Itzak's book for more info on trace analysis. In this series on blog's, we will look at applying this concept to data in the DMV.
There is a charity coding event coming up here in Dallas. I have volunteered. I recommend you do so as well if you are local. They need .net developers, DBA's and graphic designers.
http://wearemicrosoft.com
"
Are you a .NET developer, database administartor or graphics designer? Do you want to spend 3 fun-filled days competing with your peers to build the best application?