This year I attended the PASS Community Summit for the 4th time. Every year, there is at least one session, or one moment in a session when I think "that alone was worth the price of admission." That happened a couple of times this year. The first was when Ken Henderson demonstrated the Read80Trace tool. Apparently this tool, formerly used internally only, was made available to the world last year. But it was never on my radar, which is too bad, because it is a big timesaver.
It is a tool that reads sql trace files into database tables normalizes
the queries in the textdata column and aggregates
the results. This may not sound like much, unless you are someone who has spent hours trying to get substring and charindex statements to do this. I happen to be one of those someones, so I was very happy to see this tool in action.
After processing the data, the tool spits out a web page that makes it easy to identify the worst-performing queries. The only canned result it doesn't have is a count of how many times each normalized query is run. But it was easy to write my own query to get this information from the database tables created by the tool.
It's also packaged with a tool called ostress that will basically replay the commands processed by read80trace as fast as possible, to generate load on a database.
Both of these tools are discussed here
in SQL Server Magazine and here
in the knowledge base. Finally, they are available for download here