Ent lib 3.0 Data Access Block

Topics: Data Access Application Block
Aug 8, 2007 at 2:10 PM
I am using Data Access Application Block as

SqlDatabase database = new SqlDatabase(connectionstring); //integrated authentication
database.executeReader(command);

when i load test the code and check the memory with CLR Profiler it shows me a large number of Finalization for "System.Transactions.SafeIUnknown" and the allocation for these objects is shown as
Database.ExecuteReader(dbcommand)--->TransactionScopeConnection.GetConnection()---Transaction.Get_Current()---->

I am not using any transactions in my code. DO i have to explicitly disable Transactions???

Also it shows a large number of finalization for "Microsoft.Win32.SafeHandle.SafeTokenHandle", with allocation by

Database.ExecuteReader()---->SqlConnection.Open()----->DbConnectionPoolIdentity.GetCurrentWindowsIdentity()

is there anything i have to write to avoid Finalization and do an early Dispose?

Aug 8, 2007 at 3:47 PM
Hi,

You should close the reader you get from Database.ExecuteReader to close the connection. As for the SafeIUnknown it's really strange, as the allocation as you describe it occurs while getting a property; I'm not sure what could be done about it.

Regards
Aug 9, 2007 at 6:57 AM
I am closing the reader(returned from Database.ExecuteReader), also i am closing connection of the command instance,

but till i am seeing all those finalizations getting executed.

Regards,
Aug 9, 2007 at 12:43 PM
Hi,

Can you try creating an isolated repro? A short console app is usually enough.

Regards,
Fernando
Aug 9, 2007 at 5:34 PM
Ok, i will create an app to reproduce this. How do i send the app to you?

Regards,
Aug 9, 2007 at 6:56 PM
Hopefully the repro should be short enough to post it here.

Regards,
Fernando
Aug 10, 2007 at 7:22 PM
please find a sample code. The actual code is an ASP.NET(which was load tested for 30 users for 20 mins)
to simulate same behavior, i have used loop

Strange thing for the demo app is that
1. If i execute loop for 300 times, CLR Profiler does not show any Finalization
2. if i execute loop for 3000 times, CLR Profiles start showing large number of Finalization for "Microsoft.Win32.SafeHandles.SafeTokenHandle" and "System.Threading.ReaderWriterLock", both from the DAAB code allocation
3. Also in case of 300 or 3000 it shows a large number of
"GC handles surving"
major of whose allocation is from DAAB code. Could this be possible that the time at which data was collected GC might have not executed.
4. If I use
SqlParameter param = new SqlParameter(name, SqlDBType.Int)
param.SqlValue = 10; // any performance hit due to boxing, should i use int.ToString();


static void Main(string[] args)
{
for (int count = 0; count < loadCount; count++)
{

SqlDatabase dataBase = new SqlDatabase(connectionString);

DbCommand command = dataBase.GetStoredProcCommand(storedProcName);
IDataReader reader = null;

try
{
reader = dataBase.ExecuteReader(command);

ReadData(reader);
}
finally
{
if (reader != null)
{
if (!reader.IsClosed)
{
reader.Close();
}
}

DbConnection connection = command.Connection;
if (connection != null)
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
}
}

//some business logic
private static void ReadData(IDataReader reader)
{
//business
}


private static string connectionString = "Server=.; database=pkcd; Integrated Security=SSPI;";
private static string storedProcName = "GetEmployee";
private static Int32 loadCount = 3000;

Aug 10, 2007 at 7:48 PM
Thanks,

I'll try and take a look at it.

Regards,
Fernando
Aug 11, 2007 at 10:09 AM
Thanks for looking into it

few more points
1# FInalization for "Microsoft.Win32.SafeHandles.SafeTokenHandle" is happening only for Windows Integrated Security; NO finalization happens if i use User name and Password in my connection string.

2#. SqlConnectionFactory.Constructor---->SqlPerformanceCounter.Constructor() is showing Finalization for "System.Threading.ReaderWriterLock". Is there any way i can turn off this Performance counters.

Regards,
Aug 13, 2007 at 3:47 PM
Hi,

Did a quick run to your repro. You can see on the CLR profiler that the allocations are performed by ADO.NET code, so there's little you can do about them. Even changing your repro to use just raw ADO.NET shows the same behavior. As you appropriately notice on #1 only using integrated security causes the finalization of the SafeTokenHandles; the handle is actually created when retrieving the current windows identity.

As for #2, I don't know. You can ask in MSDN's ADO.NET forum.


I didn't get any finalization of the System.Transactions.SafeIUnknown class in this repro, and only 5 instances were allocated. However, this is also instantiated by the .NET framework, so I don't think there's much to be done here.

Are you facing any particular performance issue?

Regards,
Fernando