Questions tagged [parameter-sniffing]
45 questions
3
votes
1 answer
Running SQL query with options(recompile) always faster
this is a possible duplicate of this question, though I think my example is a bit more in depth and I would appreciate more definite answers.
I'm trying to understand why running the simple query with option(recompile) performs better.
DECLARE @p9…

Alex M
- 2,410
- 1
- 24
- 37
3
votes
1 answer
Huge slowdown for C# stored procedure call, parameter sniffing/optimization issues?
I have the following code that runs a stored procedure repeatedly. It works pretty well when I run the SQL statement literally, so I created a stored procedure that encapsulated what I was doing.
foreach (string worker in workers)
{
…

Andrew Mao
- 35,740
- 23
- 143
- 224
2
votes
1 answer
Strangely, making copies of parameters drastically speeds up SP in SQL Server 2008
When running a sproc with SqlDataAdapter.fill(), I noticed it was taking upwards of 90 seconds when running the same sproc in management studio took only 1-2 seconds. I started messing around with the parameters to try to find the issue, and I…

JoeCool
- 4,392
- 11
- 50
- 66
2
votes
4 answers
Query is slow when using parameters but fast when using literal
I have a query that runs against a pretty large table, I need to do a count on it.
If I use a literal the query runs in a few seconds but when I put the values in as variables (which I need to do) the query takes forever and presumably does a full…

NoseBagUK
- 345
- 4
- 23
2
votes
1 answer
Parameter sniffing / bind peeking in PostgresSQL
The Prepare and Execute combination in PostgreSQL permit the use of bound parameters. However, Prepare does not produce a plan optimized for one set of parameter bindings that can be reused with a different set of parameters bindings. Does anybody…

Campbell Fraser
- 106
- 5
2
votes
1 answer
Getting Around Parameter Sniffing in SQL Server 2005
I have seen people suggest copying the parameters to a local variable to avoid parameter sniffing in a stored proc. Say you have
CREATE PROCEDURE List_orders_3 @fromdate datetime AS
DECLARE @fromdate_copy datetime
SELECT @fromdate_copy =…

Davis Dimitriov
- 4,159
- 3
- 31
- 45
2
votes
0 answers
Lots of query plans for stored procedure
I built a stored procedure which finds the shortest route in a company with many warehouses. The procedure is heavily used by the system. After many optimizations, including memory optimized tables, if I run sp_BlitzCache, I have 2…

Nițu Alexandru
- 714
- 1
- 11
- 33
2
votes
2 answers
Is there a way to delay compilation of a stored procedure's execution plan?
(At first glance this may look like a duplicate of Different execution plan when executing statement directly and from stored procedure or Why does the SqlServer optimizer get so confused with parameters?, but my actual question is a bit…

Ian Henry
- 22,255
- 4
- 50
- 61
1
vote
3 answers
T-SQL Process Design and Execution Plan (UDF Parameter Sniffing?)
On SQL Server 2005, I have a complex multi-level allocation process which looks like this (pseudo-SQL):
FOR EACH @LVL_NUM < @MAX_LVL:
INSERT INTO ALLOCS
SELECT 'OUT', *
FROM BALANCES(@LVL_NUM)
INNER JOIN ALLOCN_SUMRY(@LVL_NUM)
INSERT INTO…

Cade Roux
- 88,164
- 40
- 182
- 265
1
vote
2 answers
SQL query running slowly - parameter sniffing
I have a simple query where I return a list of orders by date range. This query is used in a report which feeds it parameters(Site, From Date, and To Date).
ALTER PROCEDURE [dbo].[Z_N_ECOM_ORDER_STATUS_DATERANGE]
@Site VARCHAR(5),
@FromDate…

Natan
- 139
- 2
- 13
1
vote
2 answers
SQL Parameter sniffing is it possible that recompile does not help but local variables do
I am using sp_executesql to pass a complicated selection with a few parameters. It is much slower doing it this way than it is by taking it out of the stored procedure and declaring the variables.
I have seen many questions about SQL parameter…

user2945722
- 1,293
- 1
- 16
- 35
1
vote
1 answer
Nested sp_executesql not working with output variable
I am trying to call a stored procedure (with output variable) using sp_executesql but within another stored procedure. I wrote the following, but still not able to get trhough what that error means
This would be called from webservice code:
exec sp1…

user3792812
- 155
- 1
- 4
- 13
1
vote
1 answer
NHibernate LINQ Parameter Sniffing issue
We are having performance issues due to parameter sniffing in SQL Server. Our application uses NHibernate with the LINQ provider.
I am searching for a way to add for example "OPTION(RECOMPILE)" to that certain SQL query. I now I could use…

Dommicentl
- 721
- 1
- 5
- 12
1
vote
1 answer
SQL Where clause parameter faster than string literal. How do I fix?
Created following script to load user accounts. Used a parameter string for username in initial testing in management studio. Odd thing is after I changed from parameter string to string literal the query slowed down by 20 seconds. Isn't it the…

user1777129
- 61
- 5
1
vote
2 answers
Copy Stored Proc Execution Plan to Another Database
Setup:
Using SQL Server 2008 R2.
We've got a stored procedure that has been intermittently running very long. I'd like to test a theory that parameter sniffing is causing the query engine to choose a bad plan.
Question:
How can I copy the query's…

Nick Vaccaro
- 5,428
- 6
- 38
- 60