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.