In trace details we don't see System.Data.SqlClient.SqlConnection.close()

Hi Team,

Currently, in trace details of any transactions, we are able to see System.Data.SqlClient.SqlConnection.open() calls, but we are not seeing System.Data.SqlClient.SqlConnection.close() calls. How to get close calls on trace details page?
Please find the attached screenshot for reference.

I would suggest that, with connection pooling, the close method is rarely closed?

1 Like

As @stefan_garnham already mentioned, it depends on how you’re using the sqlconnection.

1 Like

We’re using MySQL with entity framework core provider and disabled connection pooling.
I just checked you also wouldn’t see the close call. But this could be related to how entity frameworks handles it.
Nevertheless, I think the worst case is to manually call close every time or override the close call and track it somehow, but that depends on the project.

@stefan_garnham we could see the close method is called in our code level just like the open method. But, we need to track whether the number open() calls are equal to close() calls so that developer can fix it.

is there any way that we can custom instrument from outside code to track close() calls?

So, you’re calling it manually (in code)?
Or is it e.g. handled by a using statement?

The only thing I can currently think of is to somehow override or wrap the close call in another method. Then you could use the [Trace] annotation to get the information to new relic.
From my understanding it should then also be visible in the transaction details.

1 Like

close() is handled in the developer code.

We are not allowed to write any annotations to the code. we can only instrument through xml.

For this you could also record custom events.

1 Like

@broder.peters Thank you so much. Will try this and update this thread with results

3 Likes

There are a standard set of instrumentation files that are deployed as part of the agent. You could look up the connection open instrumentation in those files, copy it to your custom instrumentation file and change the method to close.

4 Likes

Let us know how you got on following Stefan & Broder’s advice :smiley:

3 Likes

Apologies for the delay in response. hank you so much for the follow up. Stefan approach worked for me. create a new instrumentation file with close() method and it is able to capture the function calls. T

2 Likes

Glad you found your solution @praveen.ps. Would you mind posting the snippet from the custom instrumentation file to help anyone else who may be looking for the same monitoring?

2 Likes

Hi @stefan_garnham,

Please find the below steps to get the close method in trace details view.

  1. Locate the file “NewRelic.Providers.Wrapper.Sql.Instrumentation.xml” under C:\ProgramData\New Relic.NET Agent\Extensions\netframework . This file is created as part of NR .net core agent installation.
  2. Open the file in edit mode and update the file like below (i have only updated for MS SQL server) and save.
  <match assemblyName="System.Data" className="System.Data.SqlClient.SqlConnection,System.Data.Odbc.OdbcConnection,System.Data.OleDb.OleDbConnection">
    <exactMethodMatcher methodName="Open" />
	<exactMethodMatcher methodName="Close" />
  </match>

  <!-- built in MS SQL driver (core / nuget) -->
  <match assemblyName="System.Data.SqlClient" className="System.Data.SqlClient.SqlConnection">
    <exactMethodMatcher methodName="Close" />
  </match>

  <!-- MS SQL flagship data access driver -->
  <match assemblyName="Microsoft.Data.SqlClient" className="Microsoft.Data.SqlClient.SqlConnection">
    <exactMethodMatcher methodName="Close" />
  </match>
  1. Restart the IIS and we should see “close” method in trace details

Note: If the “close” method is still not visible, try to create a new custom instrumentation file (as per NR standards) and copy the above custom instrumentation.

Regards,
Praveen

2 Likes

Thank you for sharing @praveen.ps :smiley: