Skip to main content

Questions tagged [hints]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
27 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 ...
Parth's user avatar
  • 1
1 vote
1 answer
532 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 ...
CB_Ron's user avatar
  • 291
0 votes
0 answers
194 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 ...
user1589188's user avatar
3 votes
0 answers
150 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 ...
Michael Green's user avatar
6 votes
1 answer
316 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 ...
SE1986's user avatar
  • 2,000
5 votes
1 answer
289 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) ...
Fajela Tajkiya's user avatar
0 votes
1 answer
288 views

Mysql choosing really poor index without IGNORE INDEX hint

Problem The following query produces a really inefficient query plan: select `event`.`id` as `eventId` from `alert` inner join `event` on `event`.`id` = `alert`.`eventId` inner join `property` on `...
Sam Shiles's user avatar
0 votes
1 answer
131 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 ...
Bobogator's user avatar
0 votes
1 answer
163 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"...
user2864740's user avatar
2 votes
1 answer
979 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)...
J.D.'s user avatar
  • 38.3k
1 vote
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 ...
Jan Martin's user avatar
7 votes
4 answers
677 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 ...
Greg's user avatar
  • 3,272
2 votes
3 answers
1k 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 ...
Kushan Sen's user avatar
1 vote
3 answers
15k views

Use of Parallel hints in a SQL query

What's difference between parallel (4) and parallel 4 with and without brackets? For eg : select /*+parallel(4) */ * from table_name; select /*+parallel 4 */ * from table_name;
yash singh's user avatar
3 votes
1 answer
1k views

Add query hints to a CROSS APPLY [duplicate]

I need to apply a query hint (like NOWAIT or NOLOCK) to the CROSS APPLY in this query. How do I do it: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM sys.objects obj WITH (NOLOCK) ...
Brent Ozar's user avatar
0 votes
1 answer
2k views

Receiving "Query processor could not produce a query plan because of the hints defined in this query" when using FORCESEEK but can use FORCESCAN?

I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total). One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that ...
J.D.'s user avatar
  • 38.3k
2 votes
0 answers
270 views

Sql Server 2019 Query Hint Placement confusion

I'm using SQL Server 2019 (CU4) and I'm encountering some strange issues when trying to use a query hint for the legacy cardinality estimation due to a poorly performing Table Value Function. I'm not ...
Scott Hodgin - Retired's user avatar
5 votes
0 answers
740 views

Hash join hint returns an error when using local variable

The query below is using the StackOverflow2010 sample database: DECLARE @Id INT = 18471 SELECT c.UserId FROM dbo.Comments AS c INNER HASH JOIN dbo.Users AS u ON c.UserId = u.Id AND c.UserId = @Id ...
JieLong's user avatar
  • 167
0 votes
2 answers
623 views

why are estimated rows exponentially increasing with each join?

summary We have an Object-Oriented database schema that we query with Hibernate with: 1 parent table (NAL_Actions) 7 child tables (NAL_*) If I want to query a couple of these "action"-...
JohannesB's user avatar
  • 101
0 votes
4 answers
1k views

When is it ok to use an Index Hint on a query in production code?

I've recently been working with decently sized tables (between 100s of million of rows to 10s of billions), and have ran into a few cases where I was forced into a corner to use query hints, which I ...
J.D.'s user avatar
  • 38.3k
1 vote
1 answer
2k views

Set FORCESEEK for only one of hinted indexes

Is there a way to use a TABLE HINT with two indexes on the same table and set the FORCESEEK for only one of them? The following statement causes an error because the first index is columnstore: ...
Yevgeni Grinberg's user avatar
2 votes
2 answers
1k views

Can the FORCESEEK query hint be used on tables being queried against on a linked server? (I.e. as part of a distributed query)

I have a query that uses the FORCESEEK hint and runs fine on the server where the database I'm querying lives. If I try to run the same exact query remotely from a different server (targeting the ...
J.D.'s user avatar
  • 38.3k
1 vote
2 answers
365 views

Why won't SQL Server 2012 use my "better" execution plan when provided via the USE PLAN hint or through a stored query plan?

I have a 3rd party application that I cannot change that runs a "bad" select query that can run very slowly in some circumstances (we have killed it after 6 hrs on occasion). This is after we've done ...
Guest's user avatar
  • 23
0 votes
1 answer
174 views

Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints?

I know there's a couple of alternate ways to write this query, but I'm more curious if there's another approach that I could use to improve the performance by helping guide the optimizer in the right ...
J.D.'s user avatar
  • 38.3k
6 votes
1 answer
813 views

Why SQL does not use the indexed view?

I have the following version of SQL Server: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) I created ...
Francisco G's user avatar
6 votes
1 answer
295 views

Query/Table Hints for the History Table when Using the FOR SYSTEM_TIME Clause

Probably a pretty quick question, but is there any way to specify Query/Table hints for the History Table when using the FOR SYSTEM_TIME statement when querying temporal tables? I suspect not, but I ...
John Eisbrener's user avatar
2 votes
1 answer
2k views

Does using a read committed isolation level and a NOLOCK query hint impact performance?

Can having both the SET TRANSACTION ISOLATION LEVEL READ COMMITTED and WITH (NOLOCK) on all tables in SP cause conflicts and downgrade performance on the SP? I just noticed some changes that a dev ...
Melinda's user avatar
  • 129
0 votes
1 answer
716 views

How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

There is a third party app which generates a high-cost query that has a join on multiple tables. I created a materialized view on some of the tables using the same join that is originated in the ...
Rupesh's user avatar
  • 1
8 votes
1 answer
686 views

What cases benefit from the Reduce, Replicate, and Redistribute join hints?

The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms: Reduce Replicate Redistribute However there does not seem to be much information ...
crokusek's user avatar
  • 2,120
3 votes
2 answers
1k views

What is the difference between "OPTION (OPTIMIZE FOR UNKNOWN)" and "OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))"

Can you tell me what is difference between OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING')) and OPTION (OPTIMIZE FOR UNKNOWN)?
arif's user avatar
  • 31
2 votes
1 answer
699 views

Is it possible to ignore query hints in SQL Server 2012?

I know this sounds a bit odd... the thing is we have a piece of critical code in production which issues a query with a Hash Join hint which is causing high CPU usage on SQL Server. Owing to some ...
Mark's user avatar
  • 899
10 votes
1 answer
742 views

Why does the READPAST hint cause indexed views to be ignored?

I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem. It seemed like a good way to go because financial transaction records are only ever ...
Mars's user avatar
  • 123
1 vote
1 answer
3k views

Hinting to Redshift how to distribute a join?

If I am joining between two tables, A and B, with multiple equality constraints, is there any way that I can hint to Redshift which it should use for distributing the join? For example, in: FROM A ...
Fabio Beltramini's user avatar
0 votes
2 answers
5k views

Oracle "insert /*+APPEND*/" into empty table

I have following code in script: execute immediate 'truncate table tab_a drop storage'; insert /*+APPEND*/ into tab_a select (...) I am wondering if this APPEND hint can cause any performance ...
Michał Herman's user avatar
0 votes
1 answer
936 views

Stored Procedure with HINT "WITH RECOMPILE" loses performance, solved with DBCC FLUSHPROCINDB [duplicate]

I have a Stored Procedure with the hint WITH RECOMPILE, so the Stored Procedure do not store in the cache a Query Plan for the Stored Procedure. The average time the Stored Procedure resolve the Query ...
Santiago's user avatar
0 votes
1 answer
330 views

Inheritance and performance in database [closed]

I have 3 table linked by an "inheritance" with Entity Framework. Sadly, I have poor performance with those query because I can't make any good index. Here is the tables : CREATE TABLE [dbo].[...
Guillaume Philipp's user avatar
7 votes
2 answers
2k views

SQL Server INNER REMOTE JOIN returns more rows than INNER JOIN

I'm trying to join a few rows from a remote view to a local table. The view has about 300 million rows, so I want to use the REMOTE query hint so that all 3 million rows don't have to be transferred ...
xyzzy's user avatar
  • 71
34 votes
3 answers
52k views

What does OPTION FAST in SELECT statement do?

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and I'm still confused about it. According to MSDN: Specifies that the query is optimized for fast ...
Matthew Verstraete's user avatar
1 vote
0 answers
58 views

Very different query plans with and without LIMIT in PostgreSQL.

I have the following query which I am using to create vector tiles for a web map. I'm pulling together just the geometries within the selected tile using a CTE, then joining it to get additional ...
John Reiser's user avatar
7 votes
2 answers
4k views

Fixed query plan for a stored procedure

I am exploring the plan guides topic. Sounds easy, as far as we go through the examples in documentation. The problem is that I cannot find any working example (nor did I manage to figure it out by ...
Limonka's user avatar
  • 338
5 votes
2 answers
1k views

Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

I have a really busy function that I need to optimize the best way I can. This function is just a nested select statement that is requested several times a sec by a legacy application. The indexes ...
Pivobispo's user avatar
  • 111
0 votes
1 answer
878 views

Append hint with autotrace

I'm doing a direct-path insert which executes successfully. When I try to autotrace the execution, I get the ORA-12838: cannot read/modify an object after modifying it in parallel. SQL> insert ...
Robotron's user avatar
  • 139
13 votes
3 answers
3k views

NOLOCK hint changes order of records returned

There is a clustered index on a table Client field LastName. When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in ...
ajeh's user avatar
  • 911
3 votes
1 answer
680 views

Problem trying to force the use of a filtered index

I was trying to do some testing on filtered indexes yesterday and created a fairly simple situation: CREATE TABLE IndexTest ( ID INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_IndexTest PRIMARY KEY, ...
Kenneth Fisher's user avatar
1 vote
1 answer
761 views

Using of OPTIMIZE FOR hints in a sql query

As far as I know, the OPTIMIZE FOR hint helps to avoid parameter sniffing. For such a stored procedure: SELECT * FROM Table WHERE param=@param1 We have two parameters S1 with high selectivity and S2 ...
Kaja's user avatar
  • 143
0 votes
1 answer
94 views

Using oracle hints

I am trying the following query to list the first 10 rows from fisgen table: SELECT /*+FIRST_ROWS(10)*/ salute, gender, fid FROM fisgen; The hint is not executed. All the rows from the table are ...
Lakshmi's user avatar
8 votes
3 answers
9k views

Grant users privilege to use QUERYTRACEON 9481

Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120. In my situation, "degraded" ...
poke's user avatar
  • 527
1 vote
1 answer
1k views

Why would I not specify ROWLOCK to get rid of page locks?

I have a query that goes something like this: SELECT TOP (1) * FROM MyTable WITH (UPDLOCK, READPAST) ORDER BY SomeColumn ASC; This answer says that it's hard to say for sure but it will probably ...
sharptooth's user avatar
  • 1,301
2 votes
1 answer
1k views

Making MySQL use primary index without hinting or forcing

I have the following db structure: CREATE TABLE `question` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level_id` int(11) NOT NULL, `video_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, ...
altunyurt's user avatar
  • 133
3 votes
1 answer
4k views

How to speed up looped INSERT INTO statements?

I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO ...
Abel's user avatar
  • 145