0

I have large C# application doing heavy I/O on our SQL 2008 R2 server. It's been running fine, but I've had to make some changes in the application, which has made the I/O load even heavier.

The application does a bunch of inserts to the DB as fast at the client system will allow, and the first 2-3 minutes it runs fast (1600 batch requests/sec), but slows down gradually and ends up around dragging along (>150 batch requests/sec). Wait time on logging is in the 70ms/sec range, spiking to <900 ms/sec when .Net SQLClient process suspends with a Wait Type : WRITELOG.

Any ideas?

Performance degrade 15 minutes

Following is the problematic code. Can't really refactor the code, and i know it's by far a good way to accomplish what i'm trying to do. This code has been running fine for 3+ years, but due to a new data source (not the one i'm inserting into), it's trying to catch up on 3+ years of data. As it's financial data, the issue here arises from inserting 3 years of daily interest transactions per case.

Again, it stars out fine by writing 50+ transactions per second, but quickly ends up with 1-2 per second over 15-20 minutes of degradation.

Data comes from another table in the same database, gets "sorted" in the below code, and exported as CSV from the target database when all transactions has finalized.

SqlCommand GTTrcmd = new SqlCommand(GTTr, BottasDBGTTr);
                SqlDataReader GTTrReader = GTTrcmd.ExecuteReader();
                while (GTTrReader.Read())
                {
                    string FS = GTTrReader.GetString(0);
                    string FR = GTTrReader.GetString(1);
                    string CB = GTTrReader.GetString(2);
                    string CO = GTTrReader.GetString(3);

                    if ((FS != "" && FS != "0.000"))
                    {
                        trID = "4" + Sag;
                        InsertLine("TR", "ETR_AccountID", AccountID);
                        UpdateField("TR", "ETR_DebtorPNo", Sag);
                        UpdateField("TRD", "ETR_DebtorID", DebtorID);
                        UpdateField("TR", "ETR_Date", Date);
                        UpdateField("TR", "ETR_TransactionDate", DateN);
                        UpdateField("TR", "ETR_Source", "SVE");
                        UpdateField("TR", "ETR_TransactinId", trID);
                        UpdateField("TR", "ETR_SSN", GetSSN());
                        UpdateField("TR", "ETR_ReferencNo", HSag);

                        UpdateField("TRD", "ETR_TransactionAmount", FS.Insert(0, "-"));
                        UpdateField("TR", "ETR_FeesSalaer", FS.Insert(0, "-"));
                        UpdateField("TR", "ETR_TransactionType", "2");
                        UpdateField("TR", "ETR_AdjustmentType", "5");

                        if (UniREST.StartsWith("-"))
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                            if (Double.Parse(FS.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
                            {
                                UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
                            }
                        }
                        else
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                        }
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
                        UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);

                        //-------------NEW LINE -------------

                        trID = "5" + Sag;
                        InsertLine("TR", "ETR_AccountID", AccountID);
                        UpdateField("TR", "ETR_DebtorPNo", Sag);
                        UpdateField("TRD", "ETR_DebtorID", DebtorID);
                        UpdateField("TR", "ETR_Date", Date);
                        UpdateField("TR", "ETR_TransactionDate", DateN);
                        UpdateField("TR", "ETR_Source", "SVE");
                        UpdateField("TR", "ETR_TransactinId", trID);
                        UpdateField("TR", "ETR_SSN", GetSSN());
                        UpdateField("TR", "ETR_ReferencNo", HSag);

                        UpdateField("TRD", "ETR_TransactionAmount", FS);
                        UpdateField("TR", "ETR_CostsCourt", FS);
                        UpdateField("TR", "ETR_TransactionType", "2");
                        UpdateField("TR", "ETR_AdjustmentType", "4");

                        if (UniREST.StartsWith("-"))
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                            if (Double.Parse(FS.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
                            {
                                UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
                            }
                        }
                        else
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                        }
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
                        UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
                    }


                    if (FR != "" && FR != "0.000")
                    {
                        trID = "6" + Sag;
                        InsertLine("TR", "ETR_AccountID", AccountID);
                        UpdateField("TR", "ETR_DebtorPNo", Sag);
                        UpdateField("TRD", "ETR_DebtorID", DebtorID);
                        UpdateField("TR", "ETR_Date", Date);
                        UpdateField("TR", "ETR_TransactionDate", DateN);
                        UpdateField("TR", "ETR_Source", "SVE");
                        UpdateField("TR", "ETR_TransactinId", trID);
                        UpdateField("TR", "ETR_SSN", GetSSN());
                        UpdateField("TR", "ETR_ReferencNo", HSag);


                        UpdateField("TRD", "ETR_TransactionAmount", FR.Insert(0, "-"));
                        UpdateField("TR", "ETR_FeesReminder", FR.Insert(0, "-"));
                        UpdateField("TR", "ETR_TransactionType", "2");
                        UpdateField("TR", "ETR_AdjustmentType", "6");

                        if (UniREST.StartsWith("-"))
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                            if (Double.Parse(FR.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
                            {
                                UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
                            }
                        }
                        else
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                        }
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
                        UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);

                        //-------------NEW LINE -------------

                        trID = "7" + Sag;
                        InsertLine("TR", "ETR_AccountID", AccountID);
                        UpdateField("TR", "ETR_DebtorPNo", Sag);
                        UpdateField("TRD", "ETR_DebtorID", DebtorID);
                        UpdateField("TR", "ETR_Date", Date);
                        UpdateField("TR", "ETR_TransactionDate", DateN);
                        UpdateField("TR", "ETR_Source", "SVE");
                        UpdateField("TR", "ETR_TransactinId", trID);
                        UpdateField("TR", "ETR_SSN", GetSSN());
                        UpdateField("TR", "ETR_ReferencNo", HSag);

                        UpdateField("TRD", "ETR_TransactionAmount", FR);
                        UpdateField("TR", "ETR_CostsCourt", FR);
                        UpdateField("TR", "ETR_TransactionType", "2");
                        UpdateField("TR", "ETR_AdjustmentType", "4");

                        if (UniREST.StartsWith("-"))
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                            if (Double.Parse(FR.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
                            {
                                UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
                            }
                        }
                        else
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                        }
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
                        UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
                    }

                    if (CB != "" && CB != "0.000")
                    {
                        trID = "8" + Sag;
                        InsertLine("TR", "ETR_AccountID", AccountID);
                        UpdateField("TR", "ETR_DebtorPNo", Sag);
                        UpdateField("TRD", "ETR_DebtorID", DebtorID);
                        UpdateField("TR", "ETR_Date", Date);
                        UpdateField("TR", "ETR_TransactionDate", DateN);
                        UpdateField("TR", "ETR_Source", "SVE");
                        UpdateField("TR", "ETR_TransactinId", trID);
                        UpdateField("TR", "ETR_SSN", GetSSN());
                        UpdateField("TR", "ETR_ReferencNo", HSag);

                        UpdateField("TRD", "ETR_TransactionAmount", CB.Insert(0, "-"));
                        UpdateField("TR", "ETR_CostsBailiff", CB.Insert(0, "-"));
                        UpdateField("TR", "ETR_TransactionType", "2");
                        UpdateField("TR", "ETR_AdjustmentType", "3");

                        if (UniREST.StartsWith("-"))
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                            if (Double.Parse(CB.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
                            {
                                UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
                            }
                        }
                        else
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                        }
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
                        UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);

                        //-------------NEW LINE -------------

                        trID = "9" + Sag;
                        InsertLine("TR", "ETR_AccountID", AccountID);
                        UpdateField("TR", "ETR_DebtorPNo", Sag);
                        UpdateField("TRD", "ETR_DebtorID", DebtorID);
                        UpdateField("TR", "ETR_Date", Date);
                        UpdateField("TR", "ETR_TransactionDate", DateN);
                        UpdateField("TR", "ETR_Source", "SVE");
                        UpdateField("TR", "ETR_TransactinId", trID);
                        UpdateField("TR", "ETR_SSN", GetSSN());
                        UpdateField("TR", "ETR_ReferencNo", HSag);

                        UpdateField("TRD", "ETR_TransactionAmount", CB);
                        UpdateField("TR", "ETR_CostsCourt", CB);
                        UpdateField("TR", "ETR_TransactionType", "2");
                        UpdateField("TR", "ETR_AdjustmentType", "4");

                        if (UniREST.StartsWith("-"))
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                            if (Double.Parse(CB.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
                            {
                                UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
                            }
                        }
                        else
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                        }
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
                        UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
                    }


                    if ((CO != "" && CO != "0.000"))
                    {
                        trID = "10" + Sag;
                        InsertLine("TR", "ETR_AccountID", AccountID);
                        UpdateField("TR", "ETR_DebtorPNo", Sag);
                        UpdateField("TRD", "ETR_DebtorID", DebtorID);
                        UpdateField("TR", "ETR_Date", Date);
                        UpdateField("TR", "ETR_TransactionDate", DateN);
                        UpdateField("TR", "ETR_Source", "SVE");
                        UpdateField("TR", "ETR_TransactinId", trID);
                        UpdateField("TR", "ETR_SSN", GetSSN());
                        UpdateField("TR", "ETR_ReferencNo", HSag);

                        UpdateField("TRD", "ETR_TransactionAmount", CO.Insert(0, "-"));
                        UpdateField("TR", "ETR_AdditionalVar1", CO.Insert(0, "-"));
                        UpdateField("TR", "ETR_TransactionType", "2");
                        UpdateField("TR", "ETR_AdjustmentType", "2");

                        if (UniREST.StartsWith("-"))
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                            if (Double.Parse(CO.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
                            {
                                UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
                            }
                        }
                        else
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                        }
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
                        UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);

                        //-------------NEW LINE -------------

                        trID = "11" + Sag;
                        InsertLine("TR", "ETR_AccountID", AccountID);
                        UpdateField("TR", "ETR_DebtorPNo", Sag);
                        UpdateField("TRD", "ETR_DebtorID", DebtorID);
                        UpdateField("TR", "ETR_Date", Date);
                        UpdateField("TR", "ETR_TransactionDate", DateN);
                        UpdateField("TR", "ETR_Source", "SVE");
                        UpdateField("TR", "ETR_TransactinId", trID);
                        UpdateField("TR", "ETR_SSN", GetSSN());
                        UpdateField("TR", "ETR_ReferencNo", HSag);

                        UpdateField("TRD", "ETR_TransactionAmount", CO);
                        UpdateField("TR", "ETR_CostsCourt", CO);
                        UpdateField("TR", "ETR_TransactionType", "2");
                        UpdateField("TR", "ETR_AdjustmentType", "4");

                        if (UniREST.StartsWith("-"))
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                            if (Double.Parse(CO.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
                            {
                                UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
                            }
                        }
                        else
                        {
                            UpdateField("TR", "ETR_CurrentBalance", UniREST);
                        }
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
                        UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
                        UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
                        UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
                        UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
                    }

                    UpdateField("SN", "ESN_CurrentBalance_CostCourt", UniUBEL);
                    Console.WriteLine("Transaction [ACCOUNT IO] written for: " + Sag);
                }
                BottasDBGTTr.Close();
                BottasDBGTTr.Open();
  • Please post the "heavy I/O" code (both before and after your changes) so that we can look and see what the problem might be and what you might do about it. Your question, as written, doesn't give much to work with. – Darrin Cullop May 28 at 22:07
  • Posted more info. Hope this clears up the question. – M.Whitefield May 28 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.