Stack Overflow
Stack Overflow

Questions tagged [parameter-sniffing]

45 questions
1
vote
2 answers

How can I cure parameter sniffing on SQL Server 2000?

I am having an issue where an update query with about 70 parameters times-out on occasion. Based on some research, I believe this is due to packet sniffing. I saw that in newer versions of SQL Server, I can use the Option(recompile) clause, but that…
PFranchise
  • 6,642
  • 11
  • 56
  • 73
0
votes
0 answers

Filter the data inside a stored procedure using Table-Value parameters

I have a stored procedure which receives multiple table-type parameters and I want to filter the data using those parameters. The problem with that is those parameters can contain no rows and in that case a join will not work properly. CREATE TYPE…
Paul Muresan
  • 1
  • 3
0
votes
2 answers

Why does wrong constant defeat parameter sniffing?

I have a table Credit: create table Credit (ID_Credit int identity, ID_PayRequestStatus int, ... 20 more fields) create nonclustered index Credit_ix_PayRequestStatus ON dbo.Credit(ID_PayRequestStatus) The table has about 200k rows. The…
AngryHacker
  • 59,598
  • 102
  • 325
  • 594
0
votes
1 answer

EXECUTE AS USER in DB2

We are trying to debug a very old web application that uses DB2. I would like to run a trace to see what happens when I click on a button but as soon as I try I receive this error: create event monitor ........ for statement where AUTH_ID='.......'…
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
0
votes
1 answer

Stored procedure has different plans in different databases, can't replicate better plan in main database

Can you point me in the right direction on where to look to figure out why one plan cache is different from the other one in another database. One database is older and has less data but the schema should be intact, along with the compatibility…
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
0
votes
1 answer

EF Core Parameter Sniffing in SQL Server

I've been watching Brent Ozar's training videos (the SQL Guru for me) and he talks about parameter sniffing and say EF does this, but for the life of me I cant get an example working. I was expecting to see parameters but it just creates the SQL…
Andrew
  • 2,571
  • 2
  • 31
  • 56
0
votes
1 answer

Can Stats per partition prevent parameter sniffing problem when data varies by wide margin in partitions?

Currently we have a Datawarehouse that is holding data from multiple tenants. SQL server is on version 2019. Same schema for all the tenant databases and the data from all the tenants is consolidated in the Datawarehouse. Data is partitioned in the…
PraveenDS
  • 51
  • 3
0
votes
1 answer

Prevent parameter sniffing in stored procedures SQL Server 2008?

I have started creating a stored procedure that will search through my database table based on the passed parameters. So far I already heard about potential problems with kitchen sink parameter sniffing. There are a few articles that helped…
espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
0
votes
1 answer

Why SSRS reports works in development but not in Production

I have a SELECT statement (NOT a Stored Procedure) that I am using to create a report in SSRS (Visual Studio 2010). Parameter @ClassCode is the one that causing a trouble. But in Development it works fine, but when I deploy it to Production it…
Serdia
  • 4,242
  • 22
  • 86
  • 159
0
votes
1 answer

Does query form have an influence on getting into parameter sniffing?

Recently, one of my colleague working in SQL development got into a problem like this: a procedure ran fine on all environments, but production, which has the most resources. Typical case of parameter sniffing, but the profiler indicated that only…
Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
0
votes
2 answers

Getting around cached plans in SQL Server 2005

Can someone please explain why this works. Here is the scenerio. I have a stored proc and it starts to run slow. Then I pick a parameter and declare a variable to house its value and in the proc use the declared variable instead of the parameter.…
Mike Malter
  • 1,018
  • 1
  • 14
  • 38
0
votes
1 answer

Parameter sniffing degrades stored procedure performance

I'm using SQL Server 2012. Recently I'm have performance issue on all my stored procedures, the code inside the procedures work very fast though. I found something about parameter sniffing so I used defining local variables technique as workaround…
FLICKER
  • 6,439
  • 4
  • 45
  • 75
0
votes
1 answer

SSRS Parameter Sniffing with multiple values

I have a sql query that takes about 15 seconds to run in ssms, but when I run it in ssrs, it takes 5-7 minutes. From everything I've read this appears to be from "parameter sniffing" so I've declared variables inside the query to bypass this,…
Timothy
  • 1,198
  • 3
  • 10
  • 30
0
votes
1 answer

SQL Server sp_recompile on a table

When sp_recompile is run against a table, I understand that all stored procedures and triggers dependent on that table will be recompiled. What I don't understand is what parameters SQL Server uses for this recompile. I can't see how parameter…
PseudoToad
  • 1,504
  • 1
  • 16
  • 34
0
votes
2 answers

strange SQL server report performance problem related with update statistics

I got a complex report using reporting service, the report connect to a SQl 2005 database, and calling a number of store procedure and functions. it works ok initially, but after a few months(data grows), it run into timeout error. I created a few…
peanut
  • 1,406
  • 3
  • 14
  • 21
1 2
3