SQL Server "Audit Logout" operation takes long.

Question

We have a stored procedure that is called about 300,000 times per day by 15 users throughout the day. I have poured through every line and it is about as efficient as I can get it.

The stored procedure is accessed through an ASP.NET page on 4.0 from a legacy VB6 application on basic Winterms.

When I look at the SQL trace file, I see the following:

1. exec sp_reset_connection (Using the connection pool)
2. Audit Login
3. Execution of the stored procedure
4. Audit Logout

I see on step 4, the read and writes are way high, which makes sense since it's an accumulation of the connection being reused in the pool.

What concerns me is how long it takes, sometimes at takes 50ms, and other times 400ms, it's totally random. From the docs I read "Audit Logout" is the entire duration for all three steps. But steps 1-3 were very quick, like 0-5ms. Why would the "Audit Logout" duration take so long?

Hyphen

Answer 1

I´m "dealing" with a similar issue right now and stumbled across this post: http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/84ecfe9e-ff0e-4fc5-962b-cffdcbc619ee

Maybe this (out of the above mentioned post) is the solution:

"One error in my analysis has been identified. When a connection is pulled out of the pool, the server is sent a sp_reset_connection. That reset invokes an audit_logout followed by an audit_login. The next audit_logout doesn’t occur until the next time the connection is pulled out of the pool… so the long intervals I am seeing include the time the application processes the results of a query, releases the connection to the connection pool, does whatever, and finally pulls the connection back out of the pool to start the next transaction."

Holpen