Questions tagged [hints]
The hints tag has no usage guidance.
63
questions
1
vote
1
answer
89
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
79
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 ...
- 143
3
votes
0
answers
82
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 ...
- 24.6k
6
votes
1
answer
220
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 ...
- 1,898
5
votes
1
answer
279
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,041
0
votes
1
answer
177
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 `...
- 121
0
votes
1
answer
98
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 ...
- 83
0
votes
1
answer
160
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"...
- 195
2
votes
1
answer
714
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)...
- 34.2k
1
vote
1
answer
1k
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 ...
- 13
7
votes
4
answers
511
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,222
2
votes
3
answers
586
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 ...
- 31
1
vote
2
answers
6k
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;
- 11
3
votes
1
answer
821
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)
...
- 42.7k
0
votes
1
answer
1k
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 ...
- 34.2k
2
votes
0
answers
231
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 ...
- 23.7k
5
votes
0
answers
559
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
...
- 167
0
votes
2
answers
509
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"-...
- 101
0
votes
4
answers
705
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 ...
- 34.2k
1
vote
1
answer
1k
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:
...
- 161
2
votes
2
answers
968
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 ...
- 34.2k
1
vote
2
answers
317
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 ...
- 23
0
votes
1
answer
147
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 ...
- 34.2k
6
votes
1
answer
755
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 ...
- 163
6
votes
1
answer
233
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 ...
- 9,282
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 ...
- 129
0
votes
1
answer
650
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 ...
- 1
8
votes
1
answer
615
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 ...
- 2,076
3
votes
2
answers
864
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)?
- 31
2
votes
1
answer
636
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 ...
- 879
10
votes
1
answer
695
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 ...
- 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
...
- 186
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 ...
- 259
0
votes
1
answer
867
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 ...
- 1
0
votes
1
answer
315
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].[...
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 ...
- 71
34
votes
3
answers
50k
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 ...
1
vote
0
answers
51
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 ...
- 111
6
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 ...
- 328
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 ...
- 111
0
votes
1
answer
839
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 ...
- 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 ...
- 911
3
votes
1
answer
536
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,
...
- 24k
1
vote
1
answer
675
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 ...
- 143
0
votes
1
answer
80
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 ...
- 1
8
votes
3
answers
8k
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" ...
- 527
1
vote
1
answer
959
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 ...
- 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,
...
- 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 ...
- 145
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 ...
- 201