Have you ever been asked to get involved in diagnosing a problem for a .Net application only to find that your diagnostic tool of choice shows “.Net SqlClient Data Provider” for all applications running on the database?  I’ve encountered this frequently and it makes filtering out activity to the application of interest difficult.  Fortunately there is a very simple fix for this.  You can have your developers make a small innocuous change to their application which will display a more appropriate application name.

Below is the output from a profiler trace:

Below is the output from sys.dm_exec_sessions:

select * from sys.dm_exec_sessions s where s.session_id != @@spid and s.is_user_process = 1

By specifying the “Application Name” property in an ADO.Net connection string, you can set the application name to something more meaningful than “.Net SqlClient Data Provider”.  Most developers store the connection string in an app.config or web.config file, so generally speaking this is a simple configuration change.

This is what the connection string would look like without the application name specified:

"Integrated Security = true;server=localhost\\sql2012;"

This is what the connection string would look like with the application name specified:

"Integrated Security = true;server=localhost\\sql2012; Application Name=MyDotNetApplication"

Below is the output of a profiler trace after setting the application name to “MyDotNetApplication”:

Below is the output from sys.dm_exec_sessions:

select * from sys.dm_exec_sessions s where s.session_id != @@spid and s.is_user_process = 1

This is something simple that your developers are likely unaware of that can help when diagnosing database issues.

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store:

Free you say?  How can that be?