posts - 218, comments - 410, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Importing SQL Server 2005 Trace Files

I previously wrote a utility called ClearTrace that monitored trace files and automatically loaded them into SQL Server.  I've started a similar program for SQL Server 2005.  Right now this utility doesn't automatically import trace files on rollover.  That functionality should come soon though.  I wasn't planning on releasing the tool but it looks like we're not going to see Read90Trace anytime soon.  This utility does normalize SQL statements in a way similar to Read80Trace.  For example it takes this SQL:

select       topic_id
from forum_topics
where   topic_id = 3467

and normalizes it to:

SELECT TOPIC_ID FROM FORUM_TOPICS WHERE TOPIC_ID = {##}

It should handle dynamic SQL, stored procedures, cursors, prepared SQL statements and other oddities I've run across.  It will prefix server-side cursors with a {CURSOR} so that you can identify them.  I'm planning on using the normalized SQL statement to build a performance data warehouse.

This utility requires SQL Server 2005 to be installed on the machine where it runs.  It uses SMO to read the trace files.  It's very, very beta with limited error handling and functionality.  I've also tested it with SQL Server 2000 trace files and it appears to work fine.  I'm currently storing the the actual SQL statement in an ntext column and the normalized SQL in an nvarchar column.  In the future I'll have an option to use nvarchar(max) columns (actually it may work now).

There is a CREATE TABLE SQL statement included in the download.  That's the only table structure I've really tested with.  It's basically a subset of the table that would be created if you imported a trace file using Profiler.  It does have three extra columns though:

  • TextDataNormalized - the normalized version of the SQL statement
  • TextDataHashCode - the .NET hash code of the normalized SQL statement
  • FileName - the name of the file that was loaded.

This table is cleared out by the utility when it runs.  The idea is to use this utility to load this table and then a series of T-SQL statements to store the data as needed.  It will handle any event but only normalizes SQL for the RPC:Completed and SQL:BatchCompleted events at this point.  You can download it here.  I'm curious to get any feedback you might have.  I'd specifcally like to know if you find any SQL that it doesn't normalize properly.  Please email me the original SQL and what it generated and tell me what it should have done.

Print | posted on Sunday, February 26, 2006 8:04 PM | Filed Under [ ClearTrace ]

Feedback

Gravatar

# re: Importing SQL Server 2005 Trace Files

What happens if the database collation is case-sensitive?
2/27/2006 8:38 AM | Arnold Fribble
Gravatar

# re: Importing SQL Server 2005 Trace Files

Arnold,

At this point I'm not sure. I'll test that in a future release.
2/27/2006 6:26 PM | Bill
Gravatar

# re: Importing SQL Server 2005 Trace Files

Ah sorry, I think the point of my question got lost. Really, what I was saying is that if your normalization of SQL includes changing case of identifiers, then those normalized queries won't necessarily work on case-sensitive databases.
2/28/2006 6:19 AM | Arnold Fribble
Gravatar

# re: Importing SQL Server 2005 Trace Files

Arnold,

That's what I understood you to mean. The normalized SQL is rarely runnable. It's mostly identify and group common queries.
2/28/2006 8:23 AM | Bill
Gravatar

# Bill Graziano - Importing SQL Server 2005 Trace Files

3/1/2006 12:26 PM | Professional Association for SQL
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET