2022 Developer Survey is open! Take survey.

Questions tagged [hints]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
36 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 ...
user avatar
0 votes
1 answer
135 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"...
user avatar
2 votes
1 answer
176 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)...
user avatar
  • 24.3k
1 vote
1 answer
355 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 ...
user avatar
7 votes
4 answers
288 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 ...
user avatar
  • 3,152
2 votes
3 answers
227 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 ...
user avatar
1 vote
2 answers
439 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;
user avatar
4 votes
1 answer
481 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) ...
user avatar
  • 41.3k
0 votes
1 answer
530 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 ...
user avatar
  • 24.3k
2 votes
0 answers
135 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 ...
user avatar
5 votes
0 answers
216 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 ...
user avatar
  • 125
0 votes
2 answers
233 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"-...
user avatar
  • 101
0 votes
3 answers
206 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 ...
user avatar
  • 24.3k
1 vote
1 answer
441 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: ...
user avatar
2 votes
2 answers
674 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 ...
user avatar
  • 24.3k
1 vote
2 answers
197 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 ...
user avatar
  • 23
0 votes
1 answer
101 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 ...
user avatar
  • 24.3k
6 votes
1 answer
637 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 ...
user avatar
6 votes
1 answer
156 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 ...
user avatar
2 votes
1 answer
1k 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 ...
user avatar
  • 129
0 votes
1 answer
446 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 ...
user avatar
  • 1
8 votes
1 answer
441 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 ...
user avatar
  • 1,966
3 votes
1 answer
516 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)?
user avatar
  • 31
2 votes
1 answer
550 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 ...
user avatar
  • 819
10 votes
1 answer
638 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 ...
user avatar
  • 113
1 vote
1 answer
2k 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 ...
user avatar
0 votes
0 answers
24 views

Will hotfixing parameter sniffing with optimize for xy help?

I figured out a parameter sniffing issue and after doing experiments have decided to introduce a code (query) change in app code that uses optimize for unknown hint. Now, I need to make sure the ...
user avatar
  • 4,595
0 votes
2 answers
4k 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 ...
user avatar
0 votes
1 answer
769 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 ...
user avatar
0 votes
1 answer
270 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].[...
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 ...
user avatar
  • 71
34 votes
3 answers
45k 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 ...
user avatar
1 vote
0 answers
42 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 ...
user avatar
6 votes
2 answers
3k 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 ...
user avatar
  • 308
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 ...
user avatar
  • 111
0 votes
1 answer
772 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 ...
user avatar
  • 139
13 votes
3 answers
2k 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 ...
user avatar
  • 911
3 votes
1 answer
404 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, ...
user avatar
0 votes
1 answer
561 views

Using of OPTIMIZE FOR hints in a sql query

as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing. For such a stored procedure: SELECT * FROM Tabele WHERE param=@param1 We have two parameter S1 with high selectivity and s2 with low ...
user avatar
  • 133
0 votes
1 answer
71 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 ...
user avatar
7 votes
3 answers
7k 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" ...
user avatar
  • 507
1 vote
1 answer
881 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 ...
user avatar
  • 1,273
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, ...
user avatar
  • 133
2 votes
1 answer
3k 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 ...
user avatar
  • 135
2 votes
2 answers
2k views

Reducing deadlocks with update query hint or other method

I'm getting 20-30 deadlocks per day on the same table. (2 mil rows) Profiler DeadlockGraphs show KEY LOCK and PAGE LOCK (See attched jpg) Total datatype size per row = 114 bytes We have multiple ...
user avatar
2 votes
3 answers
2k views

SELECT lock only for inserts [closed]

I need to get the MAX aggregate of certain field in a transaction. I use this value further inside the transaction. I want to avoid inserts in the entire table from which I'm getting the MAX while the ...
user avatar
4 votes
1 answer
326 views

Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)?

This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question). Our ...
user avatar
  • 10.4k
7 votes
1 answer
5k views

How does OPTION (FAST 1) actually interact with a client?

This is question 1 of 2 related to OPTION (FAST 1); We've just upgraded our ERP database from SQL 2000 EE to 2008 R2 EE and we've been noticing increased blocking in the database. I've narrowed it ...
user avatar
  • 10.4k
3 votes
3 answers
254 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in its current form requires a regular truncation and recreation of a lot of rows (about 1 million). We're ...
user avatar
0 votes
1 answer
952 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
user avatar
  • 309