7

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?

Nate Cook
  • 8,395
  • 5
  • 46
  • 37

4 Answers4

18

In Column Filters set TextData Not Like exec sp_reset_connection

Arnie
  • 181
  • 1
  • 3
  • Thanks for your input @Arnie, but my question was not **how** to exclude it, rather **should** it be excluded. – Nate Cook Aug 30 '12 at 02:26
  • Yes!! That's it! Maybe not directo answer this question, but in my case - yes – Chaki_Black Feb 27 '15 at 10:31
  • 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. https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/filter-a-trace?view=sql-server-ver15 – Mahmoud-Abdelslam Dec 23 '19 at 07:11
2

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

Mahmoud-Abdelslam
  • 615
  • 6
  • 15
0

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.

Nate Cook
  • 8,395
  • 5
  • 46
  • 37
  • 1
    You should mark the person who answered your question, not your own. – Ray Lu Mar 04 '13 at 04:40
  • 2
    @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. – Nate Cook May 13 '13 at 22:41
0

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

Tohid
  • 6,175
  • 7
  • 51
  • 80
  • 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. – Nate Cook Apr 05 '12 at 17:39
  • 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?](http://stackoverflow.com/questions/4004279/can-i-stop-sp-reset-connection-being-called-to-improve-performance/9623715#9623715). – Tohid Apr 06 '12 at 01:01