SQL 语句在查询分析器执行很快，程序 Dapper 参数化查询就很慢（parameter-sniffing）

这个问题困扰我好长时间了，使用SQLSERVER 事务探查器找到执行超时的SQL语句，参数查询都是通过执行exe sp_executesql 的存储过程调用，因为它能够分析并缓存查询计划，从而优化查询效率，但是现在反而很慢。本地调试没有问题，开始上线也没有问题，但是运行一个月左右有时候会出现超时现象：
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

                var param = new DynamicParameters();
                param.Add("@StDateNum", Convert.ToInt32(dStartDate.ToString("yyyyMMdd")), dbType: DbType.Int32);
                param.Add("@EndDateNum", Convert.ToInt32(dEndDate.ToString("yyyyMMdd")), dbType: DbType.Int32);
                param.Add("@StDate", dStartDate, dbType: DbType.DateTime);
                param.Add("@EdDate", dEndDate, dbType: DbType.DateTime);
                param.Add("@HotelCd", sHotelCd, dbType: DbType.AnsiString, size: sHotelCd.Length);
                return new SqlConnection(DBSetting.PriceAndRmFlow).Query<PriceAndRmFlow>(cmdText, param).ToList();

临时解决办法

1.就是把代码DbType重新指定不同类型，重新部署就好了，但根本原因还是没有找到。网上也看了好多文章，说是是参数类型不正确，必须设定为数据库一致的参数类型。另外Size也有影响，使用参数化后,因为字段类型错误导致了表扫描。已做修改，还在观察中。

2.看了几篇文章，初步分析是因为执行计划的问题，后面在SQL后面强制禁用了重用执行计划( OPTION (RECOMPILE))，本来想分析了SQL的执行计划，但是之前同样慢的SQL，查询起来又很快了。

3.可能是缓存不好的缓存计划，可以执行 DBCC FREEPROCCACHE 清除。

4.在这篇文章 http://*.com/questions/10933366/sp-executesql-is-slow-with-parameters 中并且也用的是Dapper，解决方式是加了 WITH RECOMPILE 语句

parameter-sniffing

• Query runs fast, but runs slow in stored procedure
• SQL Server: Query fast, but slow from procedure
• Query times out when executed from web, but super-fast when executed from SSMS

SQL Server Management Studio 的默认 ARITHABORT 设置为 ON。 客户端应用程序将 ARITHABORT 设置为 OFF 可以接收不同的查询计划，使得对性能较差的查询进行故障排除变得困难。 即，同一个查询可以在 Management Studio 中快速执行，但在应用程序中却比较慢。 使用 Management Studio 排除查询故障时始终与客户端 ARITHABORT 设置匹配。

查询执行计划

select * from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle) t ORDER BY sys.dm_exec_cached_plans.usecounts DESC

System.Data.DbType映射关系

AnsiString:VarChar
Binary:VarBinary
Byte:TinyInt
Boolean:Bit
Currency:Money
Date:DateTime
DateTime:DateTime
Decimal:Decimal
Double:Float
Guid:UniqueIdentifier
Int16:SmallInt
Int32:Int
Int64:BigInt
Object:Variant
Single:Real
String:NVarChar
Time:DateTime
AnsiStringFixedLength:Char
StringFixedLength:NChar
Xml:Xml
DateTime2:DateTime2
DateTimeOffset:DateTimeOffset

=========== System.Data.SqlClient.SqlDbType =========

System

.Data.SqlClient.SqlDbType.BigInt(0) = System.Data.DbType.Int64(12)

System

.Data.SqlClient.SqlDbType.Binary(1) = System.Data.DbType.Binary(1)

System

.Data.SqlClient.SqlDbType.Bit(2) = System.Data.DbType.Boolean(3)

System

.Data.SqlClient.SqlDbType.Char(3) = System.Data.DbType.AnsiStringFixedLength(22)

System

.Data.SqlClient.SqlDbType.DateTime(4) = System.Data.DbType.DateTime(6)

System

.Data.SqlClient.SqlDbType.Decimal(5) = System.Data.DbType.Decimal(7)

System

.Data.SqlClient.SqlDbType.Float(6) = System.Data.DbType.Double(8)

System

.Data.SqlClient.SqlDbType.Image(7) = System.Data.DbType.Binary(1)

System

.Data.SqlClient.SqlDbType.Int(8) = System.Data.DbType.Int32(11)

System

.Data.SqlClient.SqlDbType.Money(9) = System.Data.DbType.Currency(4)

System

.Data.SqlClient.SqlDbType.NChar(10) = System.Data.DbType.StringFixedLength(23)

System

.Data.SqlClient.SqlDbType.NText(11) = System.Data.DbType.String(16)

System

.Data.SqlClient.SqlDbType.NVarChar(12) = System.Data.DbType.String(16)

System

.Data.SqlClient.SqlDbType.Real(13) = System.Data.DbType.Single(15)

System

.Data.SqlClient.SqlDbType.UniqueIdentifier(14) = System.Data.DbType.Guid(9)

System

.Data.SqlClient.SqlDbType.SmallDateTime(15) = System.Data.DbType.DateTime(6)

System

.Data.SqlClient.SqlDbType.SmallInt(16) = System.Data.DbType.Int16(10)

System

.Data.SqlClient.SqlDbType.SmallMoney(17) = System.Data.DbType.Currency(4)

System

.Data.SqlClient.SqlDbType.Text(18) = System.Data.DbType.AnsiString(0)

System

.Data.SqlClient.SqlDbType.Timestamp(19) = System.Data.DbType.Binary(1)

System

.Data.SqlClient.SqlDbType.TinyInt(20) = System.Data.DbType.Byte(2)

System

.Data.SqlClient.SqlDbType.VarBinary(21) = System.Data.DbType.Binary(1)

System

.Data.SqlClient.SqlDbType.VarChar(22) = System.Data.DbType.AnsiString(0)

System

.Data.SqlClient.SqlDbType.Variant(23) = System.Data.DbType.Object(13)

System

.Data.SqlClient.SqlDbType.Xml(25) = System.Data.DbType.Xml(25)

System

.Data.SqlClient.SqlDbType.Udt(29) = System.Data.DbType.Object(13)

System

.Data.SqlClient.SqlDbType.Structured(30) = System.Data.DbType.Object(13)

System

.Data.SqlClient.SqlDbType.Date(31) = System.Data.DbType.Date(5)

System

.Data.SqlClient.SqlDbType.Time(32) = System.Data.DbType.Time(17)

System

.Data.SqlClient.SqlDbType.DateTime2(33) = System.Data.DbType.DateTime2(26)

System

.Data.SqlClient.SqlDbType.DateTimeOffset(34) = System.Data.DbType.DateTimeOffset(27) ===========System.Data.OleDb.OleDbType=========

System

.Data.OleDb.OleDbType.Empty(0) = System.Data.DbType.Object(13)

System

.Data.OleDb.OleDbType.SmallInt(2) = System.Data.DbType.Int16(10)

System

.Data.OleDb.OleDbType.Integer(3) = System.Data.DbType.Int32(11)

System

.Data.OleDb.OleDbType.Single(4) = System.Data.DbType.Single(15)

System

.Data.OleDb.OleDbType.Double(5) = System.Data.DbType.Double(8)

System

.Data.OleDb.OleDbType.Currency(6) = System.Data.DbType.Currency(4)

System

.Data.OleDb.OleDbType.Date(7) = System.Data.DbType.DateTime(6)

System

.Data.OleDb.OleDbType.BSTR(8) = System.Data.DbType.String(16)

System

.Data.OleDb.OleDbType.IDispatch(9) = System.Data.DbType.Object(13)

System

.Data.OleDb.OleDbType.Error(10) = System.Data.DbType.Int32(11)

System

.Data.OleDb.OleDbType.Boolean(11) = System.Data.DbType.Boolean(3)

System

.Data.OleDb.OleDbType.Variant(12) = System.Data.DbType.Object(13)

System

.Data.OleDb.OleDbType.IUnknown(13) = System.Data.DbType.Object(13)

System

.Data.OleDb.OleDbType.Decimal(14) = System.Data.DbType.Decimal(7)

System

.Data.OleDb.OleDbType.TinyInt(16) = System.Data.DbType.SByte(14)

System

.Data.OleDb.OleDbType.UnsignedTinyInt(17) = System.Data.DbType.Byte(2)

System

.Data.OleDb.OleDbType.UnsignedSmallInt(18) = System.Data.DbType.UInt16(18)

System

.Data.OleDb.OleDbType.UnsignedInt(19) = System.Data.DbType.UInt32(19)

System

.Data.OleDb.OleDbType.BigInt(20) = System.Data.DbType.Int64(12)

System

.Data.OleDb.OleDbType.UnsignedBigInt(21) = System.Data.DbType.UInt64(20)

System

.Data.OleDb.OleDbType.Filetime(64) = System.Data.DbType.DateTime(6)

System

.Data.OleDb.OleDbType.Guid(72) = System.Data.DbType.Guid(9)

System

.Data.OleDb.OleDbType.Binary(128) = System.Data.DbType.Binary(1)

System

.Data.OleDb.OleDbType.Char(129) = System.Data.DbType.AnsiStringFixedLength(22)

System

.Data.OleDb.OleDbType.WChar(130) = System.Data.DbType.StringFixedLength(23)

System

.Data.OleDb.OleDbType.Numeric(131) = System.Data.DbType.Decimal(7)

System

.Data.OleDb.OleDbType.DBDate(133) = System.Data.DbType.Date(5)

System

.Data.OleDb.OleDbType.DBTime(134) = System.Data.DbType.Time(17)

System

.Data.OleDb.OleDbType.DBTimeStamp(135) = System.Data.DbType.DateTime(6)

System

.Data.OleDb.OleDbType.PropVariant(138) = System.Data.DbType.Object(13)

System

.Data.OleDb.OleDbType.VarNumeric(139) = System.Data.DbType.VarNumeric(21)

System

.Data.OleDb.OleDbType.VarChar(200) = System.Data.DbType.AnsiString(0)

System

.Data.OleDb.OleDbType.LongVarChar(201) = System.Data.DbType.AnsiString(0)

System

.Data.OleDb.OleDbType.VarWChar(202) = System.Data.DbType.String(16)

System

.Data.OleDb.OleDbType.LongVarWChar(203) = System.Data.DbType.String(16)

System

.Data.OleDb.OleDbType.VarBinary(204) = System.Data.DbType.Binary(1)

System

.Data.OleDb.OleDbType.LongVarBinary(205) = System.Data.DbType.Binary(1) =========System.Data.Odbc.OdbcType===========

System

.Data.Odbc.OdbcType.BigInt(1) = System.Data.DbType.Int64(12)

System

.Data.Odbc.OdbcType.Binary(2) = System.Data.DbType.Binary(1)

System

.Data.Odbc.OdbcType.Bit(3) = System.Data.DbType.Boolean(3)

System

.Data.Odbc.OdbcType.Char(4) = System.Data.DbType.AnsiStringFixedLength(22)

System

.Data.Odbc.OdbcType.DateTime(5) = System.Data.DbType.DateTime(6)

System

.Data.Odbc.OdbcType.Decimal(6) = System.Data.DbType.Decimal(7)

System

.Data.Odbc.OdbcType.Numeric(7) = System.Data.DbType.Decimal(7)

System

.Data.Odbc.OdbcType.Double(8) = System.Data.DbType.Double(8)

System

.Data.Odbc.OdbcType.Image(9) = System.Data.DbType.Binary(1)

System

.Data.Odbc.OdbcType.Int(10) = System.Data.DbType.Int32(11)

System

.Data.Odbc.OdbcType.NChar(11) = System.Data.DbType.StringFixedLength(23)

System

.Data.Odbc.OdbcType.NText(12) = System.Data.DbType.String(16)

System

.Data.Odbc.OdbcType.NVarChar(13) = System.Data.DbType.String(16)

System

.Data.Odbc.OdbcType.Real(14) = System.Data.DbType.Single(15)

System

.Data.Odbc.OdbcType.UniqueIdentifier(15) = System.Data.DbType.Guid(9)

System

.Data.Odbc.OdbcType.SmallDateTime(16) = System.Data.DbType.DateTime(6)

System

.Data.Odbc.OdbcType.SmallInt(17) = System.Data.DbType.Int16(10)

System

.Data.Odbc.OdbcType.Text(18) = System.Data.DbType.AnsiString(0)

System

.Data.Odbc.OdbcType.Timestamp(19) = System.Data.DbType.Binary(1)

System

.Data.Odbc.OdbcType.TinyInt(20) = System.Data.DbType.Byte(2)

System

.Data.Odbc.OdbcType.VarBinary(21) = System.Data.DbType.Binary(1)

System

.Data.Odbc.OdbcType.VarChar(22) = System.Data.DbType.AnsiString(0)

System

.Data.Odbc.OdbcType.Date(23) = System.Data.DbType.Date(5)

System

.Data.Odbc.OdbcType.Time(24) = System.Data.DbType.Time(17) =========System.Data.OracleClient.OracleType===========

System

.Data.OracleClient.OracleType.BFile(1) = System.Data.DbType.Binary(1)

System

.Data.OracleClient.OracleType.Blob(2) = System.Data.DbType.Binary(1)

System

.Data.OracleClient.OracleType.Char(3) = System.Data.DbType.AnsiStringFixedLength(22)

System

.Data.OracleClient.OracleType.Clob(4) = System.Data.DbType.AnsiString(0)

System

.Data.OracleClient.OracleType.Cursor(5) = System.Data.DbType.Object(13)

System

.Data.OracleClient.OracleType.DateTime(6) = System.Data.DbType.DateTime(6)

System

.Data.OracleClient.OracleType.IntervalDayToSecond(7) = System.Data.DbType.Object(13)

System

.Data.OracleClient.OracleType.IntervalYearToMonth(8) = System.Data.DbType.Int32(11)

System

.Data.OracleClient.OracleType.LongRaw(9) = System.Data.DbType.Binary(1)

System

.Data.OracleClient.OracleType.LongVarChar(10) = System.Data.DbType.AnsiString(0)

System

.Data.OracleClient.OracleType.NChar(11) = System.Data.DbType.StringFixedLength(23)

System

.Data.OracleClient.OracleType.NClob(12) = System.Data.DbType.String(16)

System

.Data.OracleClient.OracleType.Number(13) = System.Data.DbType.VarNumeric(21)

System

.Data.OracleClient.OracleType.NVarChar(14) = System.Data.DbType.String(16)

System

.Data.OracleClient.OracleType.Raw(15) = System.Data.DbType.Binary(1)

System

.Data.OracleClient.OracleType.RowId(16) = System.Data.DbType.AnsiString(0)

System

.Data.OracleClient.OracleType.Timestamp(18) = System.Data.DbType.DateTime(6)

System

.Data.OracleClient.OracleType.TimestampLocal(19) = System.Data.DbType.DateTime(6)

System

.Data.OracleClient.OracleType.TimestampWithTZ(20) = System.Data.DbType.DateTime(6)

System

.Data.OracleClient.OracleType.VarChar(22) = System.Data.DbType.AnsiString(0)

System

.Data.OracleClient.OracleType.Byte(23) = System.Data.DbType.Byte(2)

System

.Data.OracleClient.OracleType.UInt16(24) = System.Data.DbType.UInt16(18)

System

.Data.OracleClient.OracleType.UInt32(25) = System.Data.DbType.UInt32(19)

System

.Data.OracleClient.OracleType.SByte(26) = System.Data.DbType.SByte(14)

System

.Data.OracleClient.OracleType.Int16(27) = System.Data.DbType.Int16(10)

System

.Data.OracleClient.OracleType.Int32(28) = System.Data.DbType.Int32(11)

System

.Data.OracleClient.OracleType.Float(29) = System.Data.DbType.Single(15)

System

.Data.OracleClient.OracleType.Double(30) = System.Data.DbType.Double(8)

Refer:
实例化SqlParameter时，如果是字符型，一定要指定size属性

http://bbs.csdn.net/topics/380155255

为什么@SN varchar类型的和@SN1 nvarchar类型会有这样的差别？

http://bbs.csdn.net/topics/380126782

参数化查询比拼接字符串慢的原因

http://www.cnblogs.com/fxwdl/archive/2008/07/31/1257409.html

同样的SQL语句在查询分析器执行很快，但是网站上执行超时的诡异问题

http://www.cnblogs.com/bluedoctor/archive/2011/03/04/1970866.html

高分求解！为什么拼接的SQL语句比参数化的查询快？？？

http://bbs.csdn.net/topics/390399686

Data Type Precedence (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms190309.aspx

what-is-parameter-sniffing

http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

How Data Access Code Affects Database Performance

http://msdn.microsoft.com/en-us/magazine/ee236412.aspx

http://*.com/questions/25159051/non-optimal-execution-plan-using-sp-executesql

Slow in the Application, Fast in SSMS ?

http://www.sommarskog.se/query-plan-mysteries.html

parameter-sniffing

https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing

http://pratchev.blogspot.be/2007/08/parameter-sniffing.html

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

http://*.com/search?q=Parameter+sniffing