.NET Core 8迁移后SQL连接延迟问题排查求助

背景与问题描述 #

关于SQL Server审核登录与注销事件的说明：现有内容描述正确但不够完整，它指出了SQL Profiler中可识别连接池工作状态的EventSubClass，但实际识别效果不佳。

正在将应用从Classic ASP迁移到.NET Core 8，对比新旧应用连接SQL Server 2019数据库的性能：两者调用同一数据库的相同存储过程，但新应用每次调用都出现10-30秒的连接延迟，且每次调用都会触发审核登录与注销事件；旧Classic ASP应用无延迟，仅在初始连接时触发一次审核登录与注销事件。两者的EventSubClass均显示连接池正常工作。

连接字符串对比 #

Classic ASP连接字符串

strConnect = "Driver={SQL Server};Server=192.168.50.123,25123;Database=mydb_2;Uid=yyyyy;Pwd=xxxxx;pooling=true"

.NET Core 8配置（appsettings.json）

"default": "Data Source=192.168.50.123,25123;Initial Catalog=mydb_2; uid=yyyyy;pwd=xxxxx;pooling=true"

已意识到可能存在非连接字符串相关问题，但未发现异常，调用数据库时已遵循.NET Core最佳实践。

更新1：代码细节 #

最初以为是连接池问题，但已确认连接池工作正常。以下是典型的SQL Command调用代码（已使用using块）：

核心服务代码 #

using FFD.Core.DTOs;
using FFD.Core.Helper;
using FFD.Core.Interfaces;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace FFD.Core.Services
{
    public class RoboCheatService : IRoboCheatService
    {
        private readonly string _connectionString;
        public RoboCheatService(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("default");
        }
        public List<RoboCheatDTO> GetRoboCheatData(RoboCheatXmlDTO obj)
        {
            try
            {
                List<RoboCheatDTO> Test1 =new List<RoboCheatDTO>();
                int user_Number = obj.personId;
                bool bAll = false;
                bool bUpdate = false;
                bool bUpdateWithCheat = false;
                int uft = 0;
                int iRunIdCH = 0;
                int iRunbefore = 0;
                int iRunIdFC = 0;
                bool bUpdateNpoWithCheat = false;
                var sTmpNode = "";
                int iRunId2 = 0;
                int iRunId1 = 0;
                if (obj.startDate != obj.endDate)
                {
                    uft = 2;
                }
                if (obj.updateNPO == "true")
                {
                    bUpdate = true;
                }
                else if (obj.updateNPO == "cheat")
                {
                    sTmpNode = obj.updateNPO;
                    bUpdateWithCheat = false;
                }
                if (obj.runAll)
                {
                    bAll = true;
                }

                var bTemp = bCheckCache(user_Number,uft, "CH", ref iRunIdCH, ref iRunbefore);
                if (iRunbefore == 1)
                {
                    if (bUpdate)
                    {
                        bTemp = bCheckCache(user_Number,uft, "FC", ref iRunIdFC, ref iRunbefore);
                        bTemp = bUpdateNPO(user_Number,ref iRunIdFC);
                    }
                    else if (bUpdateWithCheat)
                    {
                        bTemp = bUpdateNPOWithCheat(user_Number);
                    }
                    var sXML = sXMLPlyrCh(user_Number,ref iRunIdCH);
                    return sXML;
                }
                else
                {
                    if (sTmpNode == "noCheat")
                    {
                        bTemp = bCheckCache(user_Number,uft, "FC", ref iRunIdFC, ref iRunbefore);
                    }
                    if (bTemp)
                    {
                        //response.write "<ffd><result>1</result><getPlayerForecast></getPlayerForecast></ffd>"
                    }
                }
                var tmpDate = obj.endDate;
                if (bAll)
                {
                    tmpDate = obj.startDate;
                }
                DataTable table = new DataTable();
                using (SqlConnection sql = new SqlConnection(_connectionString))
                {
                    try
                    {
                        sql.Open();
                        using (SqlCommand cmd = new SqlCommand("sp_scrForecast5", sql))
                        {
                            
                            cmd.Parameters.AddWithValue("@User_Number", 1953);
                            cmd.Parameters.Add("@runIdThisWk", SqlDbType.Int).Direction = ParameterDirection.Output;
                            cmd.Parameters.Add("@runIdThisSeason", SqlDbType.Int).Direction = ParameterDirection.Output;
                            using (var da = new SqlDataAdapter(cmd))
                            {
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.ExecuteNonQuery();
                                iRunId1 = Convert.ToInt32(cmd.Parameters["@runIdThisWk"].Value);
                                iRunId2 = Convert.ToInt32(cmd.Parameters["@runIdThisSeason"].Value);
                            
                            da.Fill(table);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        sql.Close();
                    }
                }
                DataTable table1 = new DataTable();
                using (SqlConnection sql = new SqlConnection(_connectionString))
                {
                    try
                    {
                        sql.Open();
                        using (SqlCommand cmd = new SqlCommand("usp_CacheCheatSheet", sql))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@personId", 1953);
                            cmd.Parameters.Add("@runId", SqlDbType.Int).Direction = ParameterDirection.Output;
                            using (var da = new SqlDataAdapter(cmd))
                            {
                                cmd.ExecuteNonQuery();
                                iRunIdCH = Convert.ToInt32(cmd.Parameters["@runId"].Value);
                                da.Fill(table);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        sql.Close();
                    }
                }
                if (bAll)
                {
                    bTemp = bCheckCache(user_Number,uft, "CH", ref iRunIdCH, ref iRunbefore);
                }
                if (bUpdate)
                {
                    bTemp = bUpdateNPO(user_Number,ref iRunId2);
                }
                else if (bUpdateWithCheat)
                {
                    bTemp = bUpdateNPOWithCheat(user_Number);
                }
                var sXMLCheat = sXMLPlyrCh(user_Number,ref iRunIdCH);
               
                return sXMLCheat ;
            }
            catch (Exception ex)
            {
                List<RoboCheatDTO> Test1 = new List<RoboCheatDTO>();
                return Test1;
            }
        }
        private bool bCheckCache(int user_Number,int uft, string sAnalysisCode, ref int iRunId, ref int iRunbefore)
        {
            try
            {
                using (SqlConnection sql = new SqlConnection(_connectionString))
                {
                    try
                    {
                        sql.Open();
                        using (SqlCommand cmd = new SqlCommand("sp_getSimpleRunIdV20", sql))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@sAnalysisCd", sAnalysisCode);
                            cmd.Parameters.AddWithValue("@User_Number", user_Number);
                            cmd.Parameters.AddWithValue("@ussForecastType", uft);
                            cmd.Parameters.Add("@runId", SqlDbType.Int).Direction = ParameterDirection.Output;
                            cmd.Parameters.Add("@iRunBefore", SqlDbType.Int).Direction = ParameterDirection.Output;
                            using (var da = new SqlDataAdapter(cmd))
                            {
                                cmd.ExecuteNonQuery();
                                iRunId = Convert.ToInt32(cmd.Parameters["@runId"].Value);
                                iRunbefore = Convert.ToInt32(cmd.Parameters["@iRunbefore"].Value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
                    finally
                    {
                        sql.Close();
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        private bool bUpdateNPO(int user_Number,ref int iRunId)
        {
            try
            {
                using (SqlConnection sql = new SqlConnection(_connectionString))
                {
                    try
                    {
                        sql.Open();
                        using (SqlCommand cmd = new SqlCommand("sp_moveFromCacheToNPO", sql))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@User_Number", user_Number);
                            cmd.Parameters.AddWithValue("@runId", iRunId);
                            using (var da = new SqlDataAdapter(cmd))
                            {
                             cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
                    finally
                    {
                        sql.Close();
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        private bool bUpdateNPOWithCheat(int user_Number)
        {
            try
            {
                using (SqlConnection sql = new SqlConnection(_connectionString))
                {
                    try
                    {
                        sql.Open();
                        using (SqlCommand cmd = new SqlCommand("sp_uftToNpo", sql))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@User_Number", user_Number);
                            using (var da = new SqlDataAdapter(cmd))
                            {
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
                    finally
                    {
                        sql.Close();
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public List<RoboCheatDTO> sXMLPlyrCh(int user_Number,ref int iRunId)
        {
            List<RoboCheatDTO> roboCheat = new List<RoboCheatDTO>();
            try
            {
                DataTable table = new DataTable();
                using (SqlConnection sql = new SqlConnection(_connectionString))
                {
                    try
                    {
                        sql.Open();
                        using (SqlCommand cmd = new SqlCommand("usp_getCheatFromCache", sql))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@User_Number", user_Number);
                            cmd.Parameters.AddWithValue("@runId", iRunId);
                            using (var da = new SqlDataAdapter(cmd))
                            {
                                cmd.ExecuteNonQuery();
                                da.Fill(table);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return roboCheat;
                    }
                    finally
                    {
                        sql.Close();
                    }
                }
                
                if (table.Rows.Count > 0)
                {
                    roboCheat = table.ConvertToList<RoboCheatDTO>();
                    return roboCheat;
                }
            }
            catch (Exception ex)
            {
                return roboCheat;
            }
            return roboCheat;
        }
    }
}

求助 #

需要排查.NET Core 8应用每次调用数据库时出现10-30秒连接延迟、且每次触发审核登录与注销事件的原因，尽管连接池状态显示正常，且已遵循.NET Core数据库调用最佳实践。

内容的提问来源于stack exchange，提问作者Tom McDonald