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…
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…
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…
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…