How to replace Cursor in SQL Server?

SQL Trace: How to aggregate data by stored procedure without ClearTrace

• 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. http://www.scalesql.com/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 http://technet.microsoft.com/en-us/library/aa175800(v=sql.80).aspx 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.

• Answer:

  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.