SP 耗时 15 分钟,但执行时相同的查询在 1-2 分钟内返回结果(SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes)

问题 所以基本上我有这个相对较长的存储过程。 基本的执行流程是它将一些数据SELECTS INTO到用#符号声明的临时表中,然后在这些表中运行游标,生成一个“运行总计”到使用CREATE创建的第三个临时表中。 然后这个生成的临时表与数据库中的其他表连接以在一些分组等之后生成结果。问题是这个 SP 一直运行良好,直到现在在 1-2 分钟内返回结果。 现在突然需要 12-15 分钟。 如果我从 SP 中提取查询并通过手动设置相同的参数在管理工作室中执行它,它会在 1-2 分钟内返回结果,但 SP 需要很长时间。 知道会发生什么。 我试图生成查询和 SP 的实际执行计划,但由于游标而无法生成它。 知道为什么 SP 需要这么长时间而查询却没有吗? 回答1 这是参数嗅探的足迹。 有关它的另一个讨论,请参见此处; SQL糟糕的存储过程执行计划性能——参数嗅探 有几种可能的修复方法,包括将 WITH RECOMPILE 添加到您的存储过程中,这大约可以在一半的时间内工作。 对于大多数情况(尽管这取决于查询和 sproc 的结构),建议的修复方法是不要直接在查询中使用参数,而是将它们存储到局部变量中,然后在查询中使用这些变量。 回答2 这是由于参数嗅探。 首先声明临时变量并将传入的变量值设置为临时变量,并在整个应用程序中使用临时变量,下面是一个示例。 ALTER PROCEDURE [dbo]

2021-06-04 08:06:25    分类:技术分享    sql   sql-server   stored-procedures   sql-server-2005   parameter-sniffing

SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes

So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' into a third temp table which is created using CREATE. Then this resulting temp table is joined with other tables in the DB to generated the result after some grouping etc. The problem is that this SP had been running fine until now returning results in 1-2 minutes. And now suddenly its taking 12-15 minutes. If I extract the query from the SP and executed it in the

2021-05-16 11:30:12    分类:问答    sql   sql-server   stored-procedures   sql-server-2005   parameter-sniffing

What are the main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE)?

I run into the classic Parameter Sniffing issues in SQL Server 2012. Based on some research I found multiple options around this problem. The two options that I need to understand the difference between are OPTION(OPTIMIZE FOR UNKNOWN) vs OPTION(RECOMPILE). I am hesitating to use OPTION(RECOMPILE) at the end of my queries that are having this issue because it will force the server to generate a new execution plan each time. If I call this query often this will spike up the CPU of that machine. So that I use he best available solution, what are the real differences between the two options? Will

2021-04-24 05:50:08    分类:问答    sql-server   tsql   sql-server-2012   parameter-sniffing

How do I control parameter sniffing and/or query hints in entity framework?

Update: I've created a suggestion to implement hint control in a future version of EF. Go here to vote for it. I have a problem where one of my Entity Framework (EF) queries is taking a very long time to execute in Sql Server, although when I copy-and-paste the generated TSQL into Sql Server Management Studio (SSMS) it runs extremely fast. After some investigation I found that I was experiencing a parameter sniffing issue, and the correct way to fix it is to insert one of many query hints (OPTIMIZE FOR, RECOMPILE, and so on). How do I insert these hints into my EF queries? Related questions

2021-04-11 06:03:17    分类:问答    sql-server   entity-framework   query-hints   parameter-sniffing

SQL Server-参数嗅探(SQL Server - parameter sniffing)

问题 我读过许多有关参数嗅探的文章,但尚不清楚这是好是坏。 谁能用一个简单的例子来解释这一点。 有没有一种方法可以自动检测到错误的计划已分配给特定的语句? 提前致谢。 回答1 很好,但有时可能不好。 参数嗅探是关于查询优化器的,它使用所提供参数的值来找出可能的最佳查询计划。 许多选择之一,一个很容易理解的选择是,应扫描整个表以获取值,还是使用索引查找更快。 如果参数中的值具有高度选择性,则优化器可能会使用搜索来构建查询计划,如果不是,则查询将对表进行扫描。 然后将查询计划缓存并重新用于具有不同值的连续查询。 参数嗅探的糟糕之处在于,对于这些值之一而言,缓存的计划不是最佳选择。 样本数据: create table T ( ID int identity primary key, Value int not null, AnotherValue int null ); create index IX_T_Value on T(Value); insert into T(Value) values(1); insert into T(Value) select 2 from sys.all_objects; T是一个具有数千行的表,其值上具有非聚集索引。 有一行的值为1 ,其余的行的值为2 。 查询样例: select * from T where Value = @Value

2021-04-07 13:34:25    分类:技术分享    sql-server   parameter-sniffing

SQL不良的存储过程执行计划性能-参数嗅探(SQL poor stored procedure execution plan performance - parameter sniffing)

问题 我有一个存储过程,如果没有传入任何值,该存储过程接受日期输入,该日期输入以后会设置为当前日期: CREATE PROCEDURE MyProc @MyDate DATETIME = NULL AS IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP -- Do Something using @MyDate 我遇到的问题是,如果在第一次编译存储过程时将@MyDate作为NULL传递,则对于所有输入值( NULL或其他),性能总是很糟糕,如果在传递日期/当前日期时传递了错误存储过程已编译,对于所有输入值( NULL或其他),性能都很好。 令人困惑的是,即使使用的@MyDate值实际上为NULL (并且IF语句未将其设置为CURRENT_TIMESTAMP ),生成的执行计划也不佳 我发现禁用参数嗅探(通过欺骗参数)可以解决我的问题: CREATE PROCEDURE MyProc @MyDate DATETIME = NULL AS DECLARE @MyDate_Copy DATETIME SET @MyDate_Copy = @MyDate IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP -- Do Something using @MyDate

2021-04-03 13:54:12    分类:技术分享    sql   sql-server   tsql   sql-execution-plan   parameter-sniffing

SQL poor stored procedure execution plan performance - parameter sniffing

I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in: CREATE PROCEDURE MyProc @MyDate DATETIME = NULL AS IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP -- Do Something using @MyDate I'm having problems whereby if @MyDate is passed in as NULL when the stored procedure is first compiled, the performance is always terrible for all input values (NULL or otherwise), wheras if a date / the current date is passed in when the stored procedure is compiled performance is fine for all input values (NULL or otherwise). What is also

2021-03-28 20:37:39    分类:问答    sql   sql-server   tsql   sql-execution-plan   parameter-sniffing

SQL Server - parameter sniffing

I've read many articles about parameter sniffing, but it's not clear if this is good or bad. Can anyone explain this with a simple example. Is there a way to automatically detect that wrong plan was assigned to a specific statement? Thanks in advance.

2021-03-28 05:30:47    分类:问答    sql-server   parameter-sniffing

Entity Framework 4.2 exec sp_executesql does not use indexes (parameter sniffing)

I'm encountering some major performance problems with simple SQL queries generated by the Entity Framework (4.2) running against SQL Server 2008 R2. In some situations (but not all), EF uses the following syntax: exec sp_executesql 'DYNAMIC-SQL-QUERY-HERE', @param1... In other situations is simply executes the raw SQL with the provided parameters baked into the query. The problem I'm encountering is that queries executed with the sp_executesql are ignoring all indexes on my target tables, resulting in an extremely poor performing query (confirmed by examining the execution plan in SSMS). After

2021-03-28 05:18:48    分类:问答    entity-framework   sql-server-2008-r2   sp-executesql   parameter-sniffing