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.
我可以看到,每15米,我每2次都能得到100-200个记录/记录(同时由简介员报告)。 在15天之后,我再有一套每秒100-200张日志。
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.
因此,我的问题是,如果改进记录添加某种方式的话,因为公司装上了100 000份记录,进行日常规划,让苏丹解放军能够满足客户要求,作为统一档案令和一些大档案,并且必须处理10万份(迅速进口)。 进口60万人的4小时应减至30分钟。
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.
请就如何解决这一问题提出建议。 现在,我试图在一个单独的数据库中设置一个小幅记录和没有指数的分流表,我将试图将(男性)母子插入。 我认为,生产行有简单的恢复模式,但可以完全恢复。 如果目前由我的“网络”应用使用的“银”用户具有大宗行政作用,那么大宗加就意味着其数量很少。 我的理解是,如果一个表格已经分组,而且许多没有分类索引的表格,各行各行各有标记。
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