The tag has no usage guidance.

learn more… | top users | synonyms (1)

11
votes
3answers
1k 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 am still confused on it. According to MSDN: Specifies that the query is optimized for fast ...
8
votes
3answers
879 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 ...
7
votes
1answer
8k views

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. ...
6
votes
1answer
3k 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 ...
6
votes
2answers
100 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 ...
5
votes
2answers
881 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" ...
5
votes
2answers
699 views

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 ...
4
votes
2answers
71 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 ...
4
votes
1answer
291 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 ...
4
votes
2answers
8k views

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) ...
4
votes
1answer
495 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
3
votes
3answers
175 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 it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
2
votes
5answers
6k views

Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK

See first query below. Can NOT combine table hint Index and forceseek with two joins and the joins are not on the PK. How to make the first query compile? Interesting - if just one join or the other ...
2
votes
1answer
593 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 ...
2
votes
1answer
65 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, ...
2
votes
1answer
128 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, ...
2
votes
2answers
20k views

Where all can the /*+ PARALLEL */ hint be used?

It works with SELECT and UPDATE statements. Are there some more statements with which it works? I was able to use it with create, but am not sure if the hint was actually considered. SQL> create ...
2
votes
2answers
587 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 ...
1
vote
3answers
1k 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 ...
1
vote
1answer
302 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
vote
1answer
155 views

Should I use table hints with a process-keyed table?

I read on stackoverflow that locking can be a problem when using process-keyed tables, and I was wondering if you had any suggestions on how to minimize or avoid unnecessary contention (e.g. table ...
1
vote
0answers
15 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 ...
1
vote
0answers
205 views

What overhead is there if I MERGE row by row?

I have a target table of about 2000 rows and I want to be able to MERGE (with HOLDLOCK) about 5-10 rows at a time into the target table. The target table doesn't get used heavily, with only 1-2 users ...
0
votes
1answer
69 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 ...
0
votes
1answer
79 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 ...
0
votes
1answer
42 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 ...
0
votes
1answer
797 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 ...