Stack Overflow
Stack Overflow

Questions tagged [parameter-sniffing]

45 questions
36
votes
11 answers

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 the # sign and then runs a cursor through these tables to generate a 'running total' into a third…
Malik Daud Ahmad Khokhar
  • 13,470
  • 24
  • 79
  • 81
30
votes
3 answers

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…
Junior
  • 11,602
  • 27
  • 106
  • 212
24
votes
2 answers

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…
mindlessgoods
  • 1,135
  • 1
  • 11
  • 22
13
votes
2 answers

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…
user3104183
  • 408
  • 1
  • 9
  • 26
12
votes
3 answers

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…
Justin
  • 84,773
  • 49
  • 224
  • 367
7
votes
2 answers

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…
Mike
  • 7,500
  • 8
  • 44
  • 62
6
votes
2 answers

Multi-tenant SQL Server databases and parameter sniffing

I have a multi-tenant database in SQL Server 2012 where each tenant's rows are identified by a tenant_id column (aka the Shared Database, Shared Schema approach). Some tenants, particularly the newer ones, have very few rows, while others have…
Mike
  • 7,500
  • 8
  • 44
  • 62
5
votes
4 answers

At some point in your career with SQL Server does parameter sniffing just jump out and attack?

Today again, I have a MAJOR issue with what appears to be parameter sniffing in SQL Server 2005. I have a query comparing some results with known good results. I added a column to the results and the known good results, so that each month, I can…
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
5
votes
2 answers

Parameter sniffing on table valued parameters

I'm fairly certain that adding parameter sniffing to table valued parameters is of little or no value however I was wondering if someone could confirm this? (INT_LIST is a user defined table type which is a single column of type INT) CREATE…
Señor Cardgage
  • 93
  • 2
  • 8
4
votes
2 answers

Should you always anticipate problems caused by parameter sniffing?

Using sql server 2008, I have a simple stored procedure, the contents of which are DELETE FROM [ABC].[dbo].[LookUpPermissions] WHERE Code = @Code In a recent code review, the DBA said I should "add parameter sniffing" which I believe means I…
earthling
  • 5,084
  • 9
  • 46
  • 90
4
votes
2 answers

Is there any way to overcome parameter sniffing in SQL Server?

I came across parameter sniffing when one of my queries took a much longer time to execute than expected. When I delved a little deeper into this problem I came to know that: When first time query gets executed it (SQL Server) creates execution…
Naim Halai
  • 355
  • 1
  • 8
  • 27
4
votes
1 answer

Can I make Linq-To-Sql embed the actual values into the query instead of passing them as parameters?

I have the following piece of code: IQueryable allItems = ...; var selectedItems = allItems.Where( item => ( item.State == 1 || item.State == 3 ) ); Console.WriteLine( selectedItems.ToString() ); and I get the following query…
sharptooth
  • 167,383
  • 100
  • 513
  • 979
4
votes
2 answers

Does Sql Server 2014's "Hekaton" compiled stored procedures address parameter sniffing issues?

SQL Server 2014's "Hekaton" in-memory table optimization proclaims, "Native compilation of business logic in stored procedures." Because of issues with "parameter sniffing" (see here and here) in SQL Server 2012 and earlier, however, I have always…
Mike
  • 7,500
  • 8
  • 44
  • 62
3
votes
2 answers

sql execution latency when assign to a variable

The following query will be ran in about 22 seconds: DECLARE @i INT, @x INT SET @i = 156567 SELECT TOP 1 @x = AncestorId FROM dbo.tvw_AllProjectStructureParents_ChildView a WHERE ProjectStructureId = @i AND a.NodeTypeCode…
Mahmoud Moravej
  • 8,705
  • 6
  • 46
  • 65
3
votes
1 answer

Table valued parameter "sniffing"

I have a stored procedure (SP) in which a table-valued parameter (TVP) is being passed in. The same code in the SP executes a lot slowly than it does outside the SP. I took a look at the execution plans and they are very different. At first this…
Umair
  • 3,063
  • 1
  • 29
  • 50
1
2 3