C# Linq seeing Audit Logout after every read

clinqsql server

This is not SQL Connection Pooling and Audit Login/Logout.

I've got a C# .NET 3.5 app that updates about 30K records on SQL 2008 on a local database.

The logic is it first checks to see if the record exists SingleOrDefault(p => p.stock=stock && p.number=number) and either adds the record or updates.

I'm focusing only on the reads using SingleOrDefault.

I created the datacontext file using SQLMetal.exe.

After every read I see in SQL Profiler:
Audit Logout (1300 reads)
RPC:Completed sp_reset_connection
Audit Login
RPC:Completed sp_executesql 'select etc…'

I created a new project on another machine using 2005 and a different database but got different results.
Audit Login
RPC:Completed sp_execute sql …
RPC:Completed sp_execute sql …
RPC:Completed sp_execute sql …
…

Any ideas?

Best Answer

Wrapping everything in a TransactionState object solved this problem for me. For example:

using (TransactionScope ts = new TransactionScope())
{
   // do db stuff here

   ts.Complete();
}

Note that I did this even though I was only doing database reads.

Also note that you have to add a reference to System.Transactions to your project, if it's not there already.

Related Solutions

Sql-server – Diagnosing Deadlocks in SQL Server 2005

According to MSDN:

http://msdn.microsoft.com/en-us/library/ms191242.aspx

When either the READ COMMITTED SNAPSHOT or ALLOW SNAPSHOT ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change. The versions of modified rows are chained using a link list. The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

For short-running transactions, a version of a modified row may get cached in the buffer pool without getting written into the disk files of the tempdb database. If the need for the versioned row is short-lived, it will simply get dropped from the buffer pool and may not necessarily incur I/O overhead.

There appears to be a slight performance penalty for the extra overhead, but it may be negligible. We should test to make sure.

Try setting this option and REMOVE all NOLOCKs from code queries unless it’s really necessary. NOLOCKs or using global methods in the database context handler to combat database transaction isolation levels are Band-Aids to the problem. NOLOCKS will mask fundamental issues with our data layer and possibly lead to selecting unreliable data, where automatic select / update row versioning appears to be the solution.

ALTER Database [StackOverflow.Beta] SET READ_COMMITTED_SNAPSHOT ON

Sql-server – SQL Connection Pooling and Audit Login/Logout

While the MSDN article says that the event will only be raised for non-reused connections, the SQL Server documentation contradicts this statement:

"The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool."

The best way to measure the effectiveness of pooling is to collect the time spent in connecting with and without pooling. With pooling, you should see that the first connection is slow and the subsequent ones are extremely fast. Without pooling, every connection will take a lot of time.

If you want to track the Audit Logon event, you can use the EventSubClass data column to whether the login is with a reused connection or a new connection. The value will be 1 for a real connection and 2 for a reused connection from the pool.application.

Best Answer

Related Solutions

Sql-server – Diagnosing Deadlocks in SQL Server 2005

Sql-server – SQL Connection Pooling and Audit Login/Logout

Related Topic