Can somebody shed some lights on how SQL Server 2005 deals with may request issued by a client using ADO.NET 2.0. Below is the shortend output of SQL Trace. I can see that connection pooling is working (I believe there is only one connection being pooled). What is not clear to me is why we have so many sp_reset_connection calls i.e a series of: Audit Login, SQL:BatchStarting, RPC:Starting and Audit Logout for each loop in for loop below. I can see that there is constant switching between tempdb and master database which leads me to conclude that we lost the context when next connection is created by fetching it from the pool based on ConectionString argument.
I can see that every 15ms I can get 100-200 login/logout per second (reported at the same time by Profiler). The after 15ms I have again a series fo 100-200 login/logout per second.
I need clarification on how this might affect much complex insert queries in production environment. I use Enterprise Library 2006, the code is compiled with VS 2005 and it is a console application that parses a flat file with 10 of thousand of rows grouping parent-child rows, runs on an application server and runs 2 stored procedure on a remote SQL Server 2005 inserting a parent record, retrieves Identity value and using it calls the second stored procedure 1, 2 or multiple times (sometimes several thousands) inserting child records. The child table has close to 10 million records with 5-10 indexes some of them being covering non-clustered. There is a pretty complex Insert trigger that copies inserted detail record to an archive table. All in all I only have 7 inserts per second which means it can take 2-4 hours for 50 thousand records. When I run Profiler on the test server (that is almost equivalent with production server) I can see that there is about 120ms between Audit Logout and Audit Login trace entries which almost give me chance to insert about 8 records.
So my question is if there is some way to improve inserting of records since the company loads 100 thousands of records and does daily planning and has SLA to fulfill client request coming as flat file orders and some big files > 10 thousands have to be processed(imported quickly). 4 hours to import 60 thousands should be reduced to 30 minutes.
I was thinking to use BatchSize of DataAdapter to send multiple stored procedure calls, SQL Bulk inserts to batch multiple inserts from DataReader or DataTable, SSIS fast load. But I don't know how to properly analyze re-indexing and stats population and maybe this has to take some time to finish. What is worse is that the company uses the biggest table for reporting and other online processing and indexes cannot be dropped. I manage transaction manually by setting a field to a value and do an transactional update changing that value to a new value that other applications are using to get committed rows.
Please advise how to approach this problem. For now I am trying to have a staging tables with minimal logging in a separate database and no indexes and I will try to do batched (massive) parent child inserts. I believe Production DB has simple recovery model, but it could be full recovery. If DB user that is being used by my .NET console application has bulkadmin role does it mean its bulk inserts are minimally logged. I understand that when a table has clustered and many non-clustered indexes that inserts are still logged for each row.
Connection pooling is working, but with many login/logouts. Why?
for (int i = 1; i <= 10000; i++){ using (SqlConnection conn = new SqlConnection("server=(local);database=master;integrated security=sspi;")) {conn.Open(); using (SqlCommand cmd = conn.CreateCommand()){ cmd.CommandText = "use tempdb"; cmd.ExecuteNonQuery();}}}
SQL Server Profiler trace:
Audit Login master 2010-01-13 23:18:45.337 1 - Nonpooled
SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.337
RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.337
Audit Logout tempdb 2010-01-13 23:18:45.337 2 - Pooled
Audit Login -- network protocol master 2010-01-13 23:18:45.383 2 - Pooled
SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.383
RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.383
Audit Logout tempdb 2010-01-13 23:18:45.383 2 - Pooled
Audit Login -- network protocol master 2010-01-13 23:18:45.383 2 - Pooled
SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.383
RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.383
Audit Logout tempdb 2010-01-13 23:18:45.383 2 - Pooled