An application INSERTS a parent record then child objects. Both these procedure call different stored procedures. Parent can have many child objects. Typically both parent and child would be inserted in milliseconds.
I am finding the parent record is created ok but the child objects arent. The inserts are not bound by a transaction , they are also done individually - no batch inserting.
This problem manifests itself as a parent with no child rows or a parent with a single child row or a parent with no end row (or i guess missing child in the middle but this would be hard/impossible to identify).
To the end user they see the parent with no end/closing row generally i.e the first CHILD that arrives creates a new PARENT object based on its status.
I am using SQL profiler to examine/alert on deadlocks. There are a few but fall far short of the missing child objects.
I have reduced SQL cpu pressure , was bouncing off 100% , not rarely gets near 100%.
Using SPOTLIGHT i can monitor lock/waits but see nothing unusual , infact look better than normal. LATCHES are relatively low too.
Today our application logs are not showing any timeouts but they have appears in small quantities in the past particular when sql server cpu at 100% or during out of hours maintenance.
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
I monitor disk read/write speed. Not problems.
QUESTIONS
1)In regards to the TIMEOUT which timeout value should be amended on the connection string to potentially counter this error above ?
2)Regards failure to INSERT , can you suggest any processes i should be monitoring to quantify the problem ?
Thank you , I hope the above is clear enough. (SQL 2005 Standard) (Parent table 200m records) (Child table 1bn records)
