47 questions
0
votes
1
answer
138
views
Mysterious query running from where?
My application is issuing the following query:
(@orgId bigint,@contractNumber nvarchar(12))
Select top 1 *
From ReceivableCut c with (nolock) ...
- 11.4k
0
votes
1
answer
91
views
Any benefit in parameterizing the value of GETDATE() / GETUTCDATE()?
Is there any benefit in parameterizing the value of GETDATE()/GETUTCDATE()? The objective is to allow SQL Server to reuse execution plans, example:
SELECT [c].[ID], [c].[Name]
FROM [Customer] AS [c]
...
- 1,569
0
votes
0
answers
388
views
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 ...
1
vote
2
answers
113
views
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)
...
- 61.7k
0
votes
1
answer
768
views
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 ...
- 12.4k
0
votes
1
answer
185
views
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 ...
- 23.8k
1
vote
1
answer
2k
views
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 ...
- 2,979
0
votes
1
answer
107
views
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 ...
- 51
4
votes
4
answers
4k
views
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 ...
- 375
2
votes
1
answer
2k
views
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 ...
- 116
2
votes
0
answers
302
views
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, ...
- 714
1
vote
2
answers
355
views
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].[...
- 139
0
votes
1
answer
455
views
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 ...
- 6,120
3
votes
2
answers
158
views
sql execution latency when assign to a variable
The following query will be ran in about 22 seconds:
DECLARE @i INT, @x INT
SET @i = 156567
SELECT
TOP 1
@x = AncestorId
FROM
dbo.tvw_AllProjectStructureParents_ChildView a
WHERE
...
- 9,052
0
votes
1
answer
271
views
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 ...
- 4,438