SQL Trace: How to aggregate data by stored procedure without ClearTrace
February 23, 2014 7:24 PM   Subscribe

I'm trying to put together an automated SQL server monitoring and diagnostics solution, including SQL Trace data. I want to be able to group RPC:Completed event data by stored procedure. Unfortunately, the formatting and parameter values in each specific call make this kind of aggregation impossible. For instance "Select * From qryLinksByNewsletterID Where NewsletterID=19" and "Select * From qryLinksByNewsletterID Where NewsletterID=234" would be considered different remote procedure calls. ClearTrace does that for you. But, ClearTrace can't run as an automated SQL job. Some kind of script would be ideal, and someone by the name of Andrew Zanevsky made a post describing one back in 2004, but the link to the actual script is dead. I could create my own with Regexes and such, but it seems like someone out there must have done it already.

What CleartTrace does:
Normalized SQL Statements
ClearTrace "normalizes" the SQL statements to eliminate transient differences in SQL statements. For example, the following statement

Select * From qryLinksByNewsletterID Where NewsletterID=19

is normalized to

SELECT * FROM QRYLINKSBYNEWSLETTERID WHERE NEWSLETTERID={##}

This allows the common SQL statements to be grouped together to determine their impact on the system. Other steps include:

- All numeric, string and date constants are converted to a placeholder
- Prepared SQL is displayed as the actual statement executed
- Server-side cursors are prefixed with "{CURSOR}" so they can be identified and removed
- sp_executesql displays the actual SQL statement executed
- Stored Procedure names are pulled from the RPC:Completed event



Anybody manage to automate this? Currently, the support team at my company is logging onto servers, querying Profiler data saved to a table, and manually looking through a list of RPC:Completed entries ordered by duration. We then find the longest duration for each Stored Procedure call that took over 4 seconds and type it into an Excel Spreadsheet. I want to kill myself.
posted by UrbanEye to Computers & Internet (1 answer total)
 
I don't know the answer. You might get a decent answer here. But for this kind of question, I think you'll get a better answer, faster, on Stack Overflow.
posted by Kwine at 9:44 PM on February 23, 2014 [1 favorite]


« Older Recent anime recommendations   |   Cost/Quality tradeoff of cellular shades? Newer »
This thread is closed to new comments.