Linked Questions

3 votes
2 answers
1k views

Why does a parameterised query lead to an index scan but hardcoded values leads to index seek? [duplicate]

I'm getting significantly different performance from a query that uses parameters vs the same query where the same values are hardcoded in the where clause. It comes down to 2 child tasks in the ...
BenCr's user avatar
  • 151
0 votes
0 answers
28 views

Performance - where clause not using index due to null verification, how do i use the index seek in this code without doing code duplication? [duplicate]

question : how do i use the index seek in this code, without doing code duplication? I've got this code : CREATE procedure [dbo].[GetActiveEntitiesByRecord] ( @MainId bigint , @...
dexon's user avatar
  • 47
0 votes
0 answers
23 views

Local variables and their impact on execution plans in SQL Server Stored Procedure [duplicate]

If a parameter that is passed to stored procedure is assigned to a local variable and the local variable is used inside the stored procedure logic, will that affect the execution plan in any way? Is ...
lifeisajourney's user avatar
5 votes
1 answer
1k views

How can I improve the performance of where clause with LIKE %abc?

I had below select statement that turned out very slow (41s) SELECT DISTINCT a.Doc_Resource_ID ,a.Parent_ID ,a.Logical_Path ,a.lvl ,CAST(SUBSTRING(a.Security_Level, 21, ...
HChau Le's user avatar
10 votes
1 answer
618 views

Why does a plan with FULL optimization show simple parameterization?

I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized. Then why is this plan showing Full Optimization, and ...
Erik Darling's user avatar
  • 40.8k
5 votes
1 answer
829 views

Query plan not reused for identical queries sp_executesql vs adhoc

One of the queries in my application is timing out. With the SQL Server profiler I was able to get the exact faulty SQL code. It's an sp_executesql dynamic statement that runs in > 2 minutes in ...
guillaume31's user avatar
2 votes
2 answers
2k views

WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance

Earlier today I was working on a Reporting Stored Procedure which had poor performance. I was able to fix the issue by changing a static variable which held a string of ten characters or less from ...
Kirk Saunders's user avatar
0 votes
2 answers
1k views

SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?

Let me set the stage: SQL Server > 2016 Several databases with the same schemas Data is similar but not identical The same indexes exist etc., all systems but one work as expected Examining the ...
D-K's user avatar
  • 503
0 votes
2 answers
1k views

Unexpected index scan instead of seek on update

We have a third party application which is performing badly. An update statement is performing a scan instead of a seek, and is not suggesting a missing index. Why is this happening? I was expecting ...
Luke's user avatar
  • 129
1 vote
3 answers
147 views

SQL Server estimating badly

I have a heap with over a million inserts daily. This table is a "staging table" where messages are received and sent to differens queues that are processed by the application. A stored proc ...
Elton Kerber's user avatar
0 votes
1 answer
59 views

Average Density & cardinality estimation

In a large web application we had a query that was running slow in code, that seemed to be working blazing fast in SSMS. After checking the basics like the same SET options, we found that there was ...
Hugo Delsing's user avatar