I am wondering if anyone can provide me with direction/and or information on the following issues regarding Sql Server 2005. I have a fair amount of experience with database systems in general (most especially Oracle and MySql) but I am having some severe performance performance problems with our company's server product and am at a loss when it comes to determining the cause.
For background: Our server performs approximately 3000 inserts and updates on a set of 4 or 5 tables once every 5 minutes. Our database connectivity layer is written in C# and we are using the SqlClient and related objects. We had issues with occasional "General Network Errors" killing our connections to the database and after some net scouring it seemed as if Connection Pooling on the .Net 1.1 framework was the core issue. I turned off connection pooling and haven't seen the General Network Errors again but now we have a new issue.
Our server maintains a small (1 or 2) set of connections for performing the aforementioned inserts and updates. It does not connect, query, and disconnect but instead just creates new command objects on the same connection. Everything runs find for 1 or 2 days but at some point, with some unknown catalyst, the inserts and updates start taking a lot longer and eventually our outbound queue of database updates gets very large (on the order of one million pending). I've seen internal debug logs from our server stating that some of the inserts and updates take 200 seconds (most are around a second). Initially the insert time is negligable.
The outbound queue -does- decrease, just not at a rate fast enough to stop it from growing. I am hoping someone can tell me where to look to find out what Sql Server is doing with its time. The server's processor isn't pegged and restarting gives us another 2 clean days of running before it clogs up again. The tablespaces are more than large enough (80% empty on 20 gigs).
I've tried taking one of the commands that seemed like it was sitting around (extracted from the Activity Monitor in Management Studio) and running it manually. It ran in about 2 or 3 seconds:
UPDATE count_history_data_details SET count_delta = count_delta + 1 WHERE count_history_data_details_id=1055492
count_history_data_details_id is the primary key. If I restart everything the exact same query runs in negligable time (reported as 0 seconds)
Additionally, the server's log is full of message similar to this one:
Process 53:0:0 (0xe8c) Worker 0x264A20E8 appears to be non-yielding on Scheduler 0. Thread creation time: 12791423374634. Approx Thread CPU Used: kernel 406 ms, user 359 ms. Process Utilization 2%. System Idle 90%. Interval: 130286 ms.
I'm not sure what to make of all of it. I've read a lot about deadlocking and whatnot on this forum and elsewhere but I can't see why that would be occurring in this situation. For this system, on this server, there is very little activity on the database outside of the activity being performed as outlined above (it is an internal testing server, not a production one).
So, given all of the above, I'm hoping that at the very least someone can aide me in figuring out what sqlserver is doing. I thank in advance anyone who may reply to this post.
George Kondiles
I haven't managed to solve this problem (I didn't locate a cause) but I found what appears to be a workaround that I wanted to share in case anyone else has the same or similar issues.
I've found that if I just reinitialize the database connection (close, dispose, re-instantiate, open) every 5000 commands or so that none of the behaviors I listed in my first post seem to manifest. I am still testing but so far we've been running for 3.5 days in what appears to be perfect condition (previously didn't make it past 2 days) so I'm optimistic.
I'm still interested in identifying the root cause of the problem, if possible, so if anyone knows anything please share!
George Kondiles
|||It sounds as though some resource from your queries is not getting released. Have you checked to make sure that you are closing all result sets from your queries?sql
No comments:
Post a Comment