Can "exec sp_reset_connection" be removed from SQL Server Profiler trace data before running the tuning advisor?

Question

Based on information I have read about the "exec sp_reset_connection" which appears in SQL Server Profiler trace data when connection pooling exists, it seems safe--or logical, rather--to remove/exclude it from trace data to be considered by the Database Tuning Advisor. Any thoughts or objections?

Wraith

Answer 1

I successfully ran the Database Tuning Advisor on a bunch of Profiler trace data that excluded the "exec sp_reset_connection" and everything worked fine. So, as far as I can tell, there don't seem to be any negative side effects of excluding that data.

Wraith

You should mark the person who answered your question, not your own. – Nightstick

@codemeit Who answered my question? I'll gladly delete my answer and award this to someone who answers the actual question I asked. I didn't ask how to exclude "exec sp_reset_connection" from Profiler and I didn't ask whether or not running sp_reset_connection affects performance. I asked: "Can 'exec sp_reset_connection' be removed from SQL Server Profiler trace data before running the tuning advisor?", and the answer is: "Yes, with no negative side effects." The downvote is unmerited. I suggest that people read more carefully before downvoting. – Wraith

Answer 2

In Column Filters set TextData Not Like exec sp_reset_connection

Dahabeah

Thanks for your input @Arnie, but my question was not how to exclude it, rather should it be excluded. – Wraith

Yes!! That's it! Maybe not directo answer this question, but in my case - yes – Shun

Referring to your answer you should append % after the text, so you should filter with exec sp_reset_connection%. > If tracing events from the osql utility or the sqlcmd utility, always > append % to filters on the TextData data column. , I will post an answer stating this so that others can benefit. If you updated your answer with this recommendation I will remove mine. learn.microsoft.com/en-us/sql/relational-databases/sql-trace/… – Rhiana

Answer 3

Referring to @Arnie answer you should append % after the text, so you should filter with exec sp_reset_connection%.

If tracing events from the osql utility or the sqlcmd utility, always append % to filters on the TextData data column.

https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/filter-a-trace?view=sql-server-ver15

Rhiana

Answer 4

If you want to use ADO.NET Connection Pooling , the answer is: Absolutely not!

Lune

I know the connection must be reset between calls when using connection pooling, but are you sure this matters to the Database Tuning Advisor? The link you posted doesn't mention trace data or database tuning. – Wraith

Nate- No. It doesn't bother sql performance at all. Some of db guys just fear of several "sp_reset_connection" and "audit logout/login" when they look at trace report of "sql server profiler". But it's not something that affects sql performance, it's the normal events of "sql connection pooling". I did a simple test to show it, read it in my answer to Can I stop sp_reset_connection being called to improve performance?. – Lune