SQLSERVER查询超时问题

问题描述

公司有一个正常运行的winform小程序,具体功能是定时跑一个任务，任务内容是从第三方数据库中查询出数据，存入另一个库。已经正常运行了一个多月，最近发现，从三天前开始，一些任务一直报`”Timeout 时间已到。在操作完成之前超时时间已过或服务器未响应。” 。但实际上服务器的网络是能够正常连接上第三方数据库的，并且使用UI工具执行相同SQL是非常快的。

分析问题

导致查询超时的原因会有很多种，根据这个答案的描述：

This type of timeout can have three causes;

1. There’s a deadlock somewhere
2. The database’s statistics and/or query plan cache are incorrect
3. The query is too complex and needs to be tuned

首先排除了第一条，因为在查询工具中同样的查询语句是可以查询出数据的，不存在死锁、事务等问题。
其次第二条所描述的统计信息问题，因为数据库有定时任务进行统计信息的维护，所以不存在这个问题。
最后只剩下分析sql语句问题了，在工具中运行SQL执行计划分析，是可以走索引快速查询出数据的。这里因为程序使用的是ADO.NET的参数化查询，所以可能会存在实际生成的sql语句和数据库工具中的不一致。

问题定位

WinForm程序使用SqlClient对SQL Server数据库进行访问，其中使用到了参数化查询功能，进一步分析参数化查询片段代码是否正确。

问题代码

string IP="";
string SYS ="";
string sSql = @"select * FROM Table_User WHERE loginIP=@IP AND loginSys=@SYS";
SqlParameter[] param = new SqlParameter[]
{
    new SqlParameter("@IP",IP),
    new SqlParameter("@SYS",SYS)
};

using (SqlConnection conn = new SqlConnection(GetConnectString(_Type)))
{
    using (SqlCommand sqlCommand = new SqlCommand())
    {
        try
        {
            PrepareCommand(sqlCommand, conn, null, SQLString, cmdParms);
            int result = sqlCommand.ExecuteNonQuery();
            sqlCommand.Parameters.Clear();
            return result;
        }
        catch (SqlException ex)
        {
            foreach (SqlParameter sqlParameter in cmdParms)
            {
                SQLString = SQLString.Replace(sqlParameter.ParameterName, (sqlParameter.Value == null) ? "" : sqlParameter.Value.ToString());
            }

            throw new Exception(ex.Message + "  SQL:" + SQLString);
        }
    }
}

void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
    if (conn.State != ConnectionState.Open)
    {
        conn.Open();
    }

    cmd.Connection = conn;
    cmd.CommandText = cmdText;
    if (trans != null)
    {
        cmd.Transaction = trans;
    }

    cmd.CommandType = CommandType.Text;
    if (cmdParms == null)
    {
        return;
    }

    foreach (SqlParameter sqlParameter in cmdParms)
    {
        if ((sqlParameter.Direction == ParameterDirection.InputOutput || sqlParameter.Direction == ParameterDirection.Input) && sqlParameter.Value == null)
        {
            sqlParameter.Value = DBNull.Value;
        }

        cmd.Parameters.Add(sqlParameter);
    }
}

问题原因

根据微软文档描述：SqlParameter.DbType 属性，默认值为 NVarChar
在第三方库中的表结构字段类型都为VarChar类型，而SqlCommand中,因为没有设置参数类型，导致使用默认的NVarChar类型的SqlParameter进行参数化查询。
字段类型不匹配导致Sql查询从索引扫描变成了全表扫描，直接导致查询缓慢的问题。

问题修复

1. 使用SqlParameter(String, SqlDbType, Int32)替代SqlParameter(String, Object)，添加正确的DbType和size
2. 直接使用SqlServer的查询分析器的默认匹配类型特点，改造sql语句。【不推荐】

排查故障参考

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated
参数化查询比拼接字符串慢的原因
原因：

经过查阅资料和自己的分析后，得知原来是字符类型的不匹配造成的。因为这个数据表是客户数据库中的，我只是提取数据，而我一般建数据表都使用NVarchar类型,而客户的这个表使用的是Char(32)，在查询分析器中直接写字符串作为查询条件时，查询优化器认为条件中等号两边的字符类型是相同的，从而会选择聚集索引查询，而在ADO.NET中使用SqlParameter后，因为字段类型错误导致了Sql查询从索引扫描变成了表扫描… 所以为了避免在Sql查询中由于条件字段类型不匹配而导致表扫描，记得以后在创建SqlParameter时声明SqlDbType就解决问题了。