Questions tagged [hints]
The hints tag has no summary.
66 questions
2 votes
1 answer
133 views
Why does FORCE_LEGACY_CARDINALITY_ESTIMATION not match ('ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES', 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')?
Assume the StackOverflow2010 database under SQL Server 2022 and compatibility level 160. Consider the following two queries: SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u JOIN dbo.Posts AS p ...
- 1,342
7 votes
2 answers
417 views
Does OPTIMIZE FOR UNKNOWN do anything for table-valued variables/parameters?
I have a query that is both prone to parameter sensitivity and is suffering from its table-valued parameter. I'm lazy and just want to solve this with query hints. When I'm lazy, I can solve parameter ...
- 1,342
3 votes
1 answer
294 views
Hiding an index on a view to other queries in SQL Server
I have a View that has an Index that I'd like to use for known queries. The issue is the existence of this Indexed View causes other queries to perform poorly. Is there a way to hide the Index or ...
- 63
1 vote
1 answer
379 views
SQL Server management Studio does not show Index recommendation
I have installed SQL Server management studio version 20 (and also tried with 19 and 18) and surprisingly unable to see the Missing Index Hints while explaining a SQL Query. Whenever i receive a SQL ...
- 11
1 vote
1 answer
1k views
Is it possible to add a query hint to an entire stored procedure in SQL Server 2022?
SQL Server 2022 has a new feature called Parameter Sensitive Plan Optimization. I don't want to turn it on for my entire database, but I have four stored procedures that I believe would benefit from ...
- 291
0 votes
0 answers
403 views
Resolving deadlock between an UPDATE and a SELECT with query hint
I bumped into a page lock deadlock on a table when two queries are running at the same time in two separate sessions, one is selecting from the table (holds S lock on page x and waits for S lock on ...
- 151
3 votes
0 answers
229 views
A hint changes query results. Why is this OK? [duplicate]
I have a query where OPTION(NO_PERFORMANCE_SPOOL) changes the resultset. I understood that hints affected the physical operations, not the values returned. I'd like help understanding why this is ...
- 25.3k
6 votes
1 answer
473 views
Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan
I have a query which I force a plan for in Query Store (the plan is the one SQL Server compiled for this query) If I run the query immediately afer forcing the plan, I get the NO_PLAN ...
- 2,256
5 votes
1 answer
311 views
What's the difference between these three versions of TSQL snippet?
Version 1 DECLARE @key INTEGER = 33, @val INTEGER = 44; BEGIN TRANSACTION; INSERT dbo.t([key], val) SELECT @key, @val WHERE NOT EXISTS ( SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE) ...
- 1,239
0 votes
1 answer
180 views
Query plan XML: one stmt's DegreeOfParallelism is 1 for any maxdop other than 1 (maxdop 1 leads to DegreeOfParallelism = 0 with reason MaxDopSetToOne)
I have a procedure with two SELECTs. One statement respects the server's/database's/query hint's maxdop setting and uses parallelism, the other is being difficult and never going parallel. The 8 core ...
- 95
0 votes
1 answer
200 views
Ensure that SQL server "avoids" the clustered index when a non-clustered index can be used
I have a query, the short of which looks like the following. While this is not exact, it should show how the problematic behavior is introduced and what I am looking to prevent. And "prevent"...
- 205
2 votes
1 answer
1k views
Why does adding a WHERE clause break my working query, with error "Query processor could not produce a query plan because of the hints defined..."?
I have the following (stupidly-simplified) query that leverages two CTEs that refer to the same table and joins them to each other: WITH CTE1 AS ( SELECT dbo.RemoveNonNumericCharacters(PhoneNumber)...
- 41.1k
2 votes
1 answer
2k views
Does it make sense to use OPTION(RECOMPILE) in dynamic SQL?
I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries? There are a few points to keep in mind: There will be hundreds (maybe even thousands) of ...
- 23
7 votes
4 answers
918 views
Using a filtered index when setting a variable
I know from other questions and posts that when SQL compiles a query plan it can only use a filtered index if the filtered index is guaranteed to be able to be used every time the query runs. This ...
- 3,292
2 votes
3 answers
2k views
Is it a good practice to use index hints in production code?
I came across a situation where one of my MySQL (5.6) views (type - MERGE) was performing quite bad in order to fetch records (~3500 rows) for an entity id say 123. This view performs inner joins ...
- 41