I'm using SQL Server 2008 R2 enterprise and this is my first foray into extended events. It's pretty cool stuff. I'm wanting to create a session that will run basically all the time to audit query times on our SQL Server. I will then be able to pipe that data into a product called Splunk and correlate that data with all sorts of other metrics.
Anyway, I'm having some odd things happen (odd to me … I'm sure there is a reason) with the sp_statement_completed event. The .xel file is growing SUPER fast … like 1GB in a minute fast. Looking at the data I see the same SQL statements duplicated sometimes 75-100 times. What can I do to deduplicate/filter this data prior to going into the file? Many of them have a cpu of zero and duration of zero. I think I might even be seeing a little duplication using sql_statement_completed. I've also noticed that I do see stored procedure end times using the sql_statement_completed event. So, is there even a need to use sp_statement_completed?
Any thoughts would be most welcomed and thanks in advance.
Best Answer
Looks like the issue is because my stored procedures and ad hoc queries might be running more than one sql statement ... thus creating multiple hits. Really what I need is batch complete. Alas, it does not exist in SQL Server 2008. Probably gonna have to resort to a server trace.