Is SQL Server Profiler showing Connection Pooling not working?

TL;DR: No – it’s just SQL Profiler not telling you the entire truth.

In evaluating Entity Framework 4.1 (aka EF Code-First or “Magic Unicorn” Edition) I’ve been keeping an eye on what SQL it’s actually executing against SQL Server (how’s that for a leaky abstraction?). Here I saw something slightly worrying which was that it appeared that the client was logging in, executing a query and then logging out as seemingly indicated by the Audit Login and Audit Logout events:

image

The same thing happens without EF using basic SqlCommand queries. To tell you the truth I’d noticed this a while ago but hadn’t got round to investigating.

Rather than assume that connection pooling was broken on my machine, I had a hunch that SQL Profiler was somewhat misrepresenting what was really going on.

Indeed Markus Erickson on StackOverflow mentions the EventSubClass column you can add to SQL Profiler’s output to see if those Audit Logon/Logout events are actually connections being pulled from the pool or fresh connections.

Here’s how you show the EventSubClass column in SQL Profiler (I’m running SQL 2005 on this machine, I can only assume it’s similar on 2008):

  • Go to the Trace Properties window and switch to the Events Selection tab.
  • Click on the Show all columns checkbox
  • Scroll to the right and locate the EventSubClass column and check both checkboxes:

  • Then go to Organize Columns and move the EventSubClass column up so that it’s next to EventClass:

image

Now you can re-run your trace and hopefully be reassured that connection pooling after all is functioning correctly!

image

Hope that helps!

About these ads

2 Responses to Is SQL Server Profiler showing Connection Pooling not working?

  1. Dan Douglass says:

    Great tip! I never could explain that either.

  2. Amir says:

    I don’t completely agree with you or I might not get what you tried to describe.

    If these audit events are nothing to be worried about and they are just representing the “going in” and “going out” from the connection pool on the client side.

    Then the question is how the server knows about what’s happening on the client’s space?

    What I think it is real login/logouts that are happening on a physical connection.

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: