Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [parameter-sniffing]

Use this tag for questions involved a sudden degradation in query performance related to query plan changes based on supplied parameters.

Filter by
Sorted by
Tagged with
1
vote
1answer
85 views

Parameter Sniffing - Running query in code vs in management studio

I just had a problem at work where a stored proc, being called by some C# code, was very slow. When I ran the same stored proc with the same parameters in Management Studio, it was very fast. After a ...
1
vote
0answers
33 views

Different query plans for same In line Function [duplicate]

I am facing a potential parameter sniffing issue with an In Line function in sql server 2008. Basically Given the below three queries (same function): First Query: DECLARE @SQL NVARCHAR(1000) ...
4
votes
1answer
155 views

How to find inputs that caused different query plans in Query Store

Query store shows that a completely parameterized query has multiple query plans: How can I find the input that is causing such long response times? Why would the same parameterized query end up ...
7
votes
2answers
488 views

Parameter sniffing = On with Parameterization = forced. Which takes precedence?

These two settings seem to contradict each other. One forces plan parameterization so that only one plan gets created. The other allows for multiple plans If you have Parameterization = forced, ...
0
votes
1answer
35 views

Stored Proc Logical Reads Spiking Daily At The Same Time [closed]

I have a procedure that I optimized in a dev environment. It is a heavy lifter and started at 14 millions logical reads with a run time of 30-40 seconds. I got it down to 96k reads and 8 seconds. ...
0
votes
1answer
501 views

how to add option(recompile) within this stored procedure?

In my current environment, I have many instances of stored procedures, just like the one shown below, where a bunch of parameters are passed to the procedure, and then within the procedure a select ...
1
vote
3answers
117 views

Is it a parameter sniffing problem?

I have a stored procedure which is called by Console Application (C#), the sp do nothing much just checking the existing of the record and update it: ALTER PROCEDURE [dbo].[sp_Update_StaffTable] ...
1
vote
0answers
60 views

SQL Server 2008 ignoring QUERYTRACEON 4136 and ARITHABORT ON

A slow Stored Procedure is extremely faster (going from 2 secs to 8 millisecs) with QUERYTRACEON 4136 and ARITHABORT ON, on the local SQL Server 2008 I use to develop. The "fixed" SP, on another SQL ...
0
votes
0answers
35 views

Will Parameter Sniffing happen? [duplicate]

I recently learned about the concept of parameter sniffing and I am having a confusion about a specific scenario where I am not able to understand the behavior of SQL Server. Anybody who can help me ...
0
votes
0answers
648 views

SET ARITHABORT, .NET, and explanations inconsistency?

A few days ago I happen to have stumbled on a lot of cases in a new DB I've been given where ARITHABORT set to OFF literally devastate performances. Being my first time facing this specific problem I ...
1
vote
0answers
336 views

Poor Query performance when scalar variables used in Query [duplicate]

Facing a strange issue that a query runs incredibly slow (worse) when scalar variables are used as parameter. Actual Query (Faster) - 9 secs select sum(v.sale_value) as col_0_0_, sum(v.vat_value)...
2
votes
1answer
1k views

Same query different execution plans

I am trying to optimize performance for a server and this particular query was causing huge reads from database, in turn causing timeout for queries. This query is generated from EF6 in Asp.Net MVC. ...
76
votes
4answers
23k views

Why is my query suddenly slower than it was yesterday?

[Salutations] (check one) [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer, I have a (check all that apply) [ ] query [ ] stored procedure [ ] database thing maybe that ...
0
votes
1answer
683 views

Does oracle database suffer from parameter sniffing issue

Does oracle database suffer from parameter sniffing issue in stored procedures? Considering there are millions of pages written on parameter sniffing issue, I think that this is more of a bug than a ...
2
votes
1answer
715 views

Does parameter sniffing exist for views?

I've heard of and encountered parameter sniffing in stored procedures before. But today I ran into the first instance of what I can only assume is parameter sniffing in a view. I have a query that ...
7
votes
2answers
627 views

Why does this OPTIMIZE FOR UNKNOWN improve my query by several seconds?

Ok, so I have a non-stored-procedure query that we're using in an SSRS report. This query has been hellishly slow (I've had the original version of this query running for the past two hours, still not ...
4
votes
1answer
2k views

Disable parameter sniffing for better performance - good idea?

I have an optimized query in SQL Server 2012 that takes consistently ~1-3 seconds to run. It works fast when I run it in SSMS, but when application does it (through sp_executesql), it is really slow, ...
4
votes
1answer
192 views

Execution plan caching of ITVF called with inline vs dynamic SQL

I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function [dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (...
3
votes
1answer
329 views

Methods to identify/recognise 'bad' parameter sniffing in SQL Server

There's lots of superb information out there about the certain approaches that can be used to mitigate bad parameter sniffing, but there's not a lot of information about how to go about identifying a ...
8
votes
2answers
439 views

Parameter sniffing work arounds

I have used two methods to get around parameter sniffing issues in the past: 1) Use WITH RECOMPILE 2) Reassign parameter values to local variables and use those instead of the parameters From what ...