Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [hints]

58 questions
34
votes
3 answers

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 retrieval of the first number_rows. This is a…
Matthew Verstraete
  • 915
  • 4
  • 12
  • 28
13
votes
3 answers

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 question. That hint changes the order of records.…
ajeh
  • 911
  • 4
  • 12
  • 29
10
votes
1 answer

OPTION FORCE ORDER improves performance until rows are deleted

I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds. Taking a look at the execution plan, it was pretty…
shannon
  • 406
  • 1
  • 4
  • 13
10
votes
1 answer

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 added, never updated or deleted. The only rows that…
Mars
  • 113
  • 3
8
votes
1 answer

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 on when it might become necessary to use…
crokusek
  • 1,956
  • 3
  • 21
  • 32
7
votes
3 answers

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" means going from a few seconds to several minutes…
poke
  • 507
  • 7
  • 15
7
votes
1 answer

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 down to what I believe to be the offending statement…
swasheck
  • 10,365
  • 3
  • 43
  • 88
7
votes
4 answers

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 means you can't use a variable in the where clause…
Greg
  • 3,132
  • 3
  • 28
  • 51
7
votes
2 answers

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 to my computer. SELECT R.Something, L.ID,…
xyzzy
  • 71
  • 2
6
votes
1 answer

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 the following view CREATE VIEW…
Francisco G
  • 163
  • 3
6
votes
1 answer

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 wanted to double-check before I throw in the…
John Eisbrener
  • 8,970
  • 5
  • 23
  • 55
6
votes
2 answers

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number of rows for one query is very small I think I…
crokusek
  • 1,956
  • 3
  • 21
  • 32
6
votes
2 answers

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 myself) for setting a fixed plan for a stored…
Limonka
  • 263
  • 2
  • 8
5
votes
0 answers

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 With a hash join hint, it returns the following…
JieLong
  • 115
  • 8
5
votes
3 answers

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) NULL, [COLUMN_B] [varchar](128) NULL, …
jimw
  • 53
  • 1
  • 1
  • 3
1
2 3 4