Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

7
votes
2answers
267 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
30 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
95 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
75 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
46 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
95 views

EF Query as RPC - is it parameter sniffing?

Somewhat similar to this -- Why does sp_executesql use a different query plan? -- i.e. a C# app, using EF to SQL, but with a much simpler query. Here's the sample, as captured by Profiler and XEvents:...
0
votes
0answers
31 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
266 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 ...
0
votes
0answers
271 views

Poor Query performance when scalar variables used in Query

Facing a strange issue that query runs incredibly slow (worse) when scalar variables where used as parameter. Actual Query (Faster) - 9 secs select sum(v.sale_value) as col_0_0_, sum(v.vat_value)...
2
votes
1answer
409 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. ...
62
votes
4answers
11k 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
306 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
335 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
311 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
769 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, ...
5
votes
1answer
113 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 (...
4
votes
1answer
257 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
339 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 ...