We are using SQL Server Web Edition. We have a table which has around 120 million records (every second around 100 insertion). It has a AFTER INSERT trigger to update another table. There is following performance issue we are facing.
- High CPU Usage
- Insertion failed (Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=0; handshake=10046; [Login] initialization=0; authentication=0; [Post-Login] complete=3999;)
- Insertion Failed (A severe error occurred on the current command. The results, if any, should be discarded.)
- Insertion Failed (Some time we are getting connection pool error.. There is no limit set in the connection string)
I ran sp_who2 command and found a lot of queries are in suspended mode..
here is the "sys.dm_os_sys_info" result...
cpu_count hyperthread_ratio physical_memory_in_bytes virtual_memory_in_bytes8 8 12853739520 8796092891136
Can anyone please suggest the improvement steps...
- Edited by GaneshAgrawal Wednesday, March 25, 2015 8:50 AM
- Moved by Bob BeaucheminMVP Wednesday, March 25, 2015 7:24 PM Moving to a more appropriate forum
- Merged by Eric__ZhangMicrosoft contingent staff, Moderator Monday, March 30, 2015 7:21 AM same question from one OP