CODEWITHSUNDEEP
sqlsql-server
Andrew Hill
Andrew Hill

Reputation: 2030

SQL Server : how do I add a hint to every query against a table?

I have a transactional database. one of the tables is almost empty (A). it has a unique indexed column (x), and no clustered index.

2 concurrent transactions:

begin trans
insert into A (x,y,z) (1,2,3)
WAITFOR DELAY '00:00:02'; -- or manually run the first 2 lines only
select * from A where x=1; -- small tables produce a query plan of table scan here, and block against the transaction below.
commit

begin trans
insert into A (x,y,z) (2,3,4)
WAITFOR DELAY '00:00:02';
-- on a table with 3 or less pages this hint is needed to not block against the above transaction
select * from A with(forceseek) -- force query plan of index seek + rid lookup
    where x=2;
commit

My problem is that when the table has very few rows the 2 transactions can deadlock, because SQL Server generates a table scan for the select, even though there is an index, and both wait on the lock held by the newly inserted row of the other transaction.

When there are lots of rows in this table, the query plan changes to an index seek, and both happily complete.

When the table is small, the WITH(FORCESEEK) hint forces the correct query plan (5% more expensive for tiny tables).

  1. is it possible to provide a default hint for all queries on a table to pretend to have the 'forceseek' hint?

  2. the deadlocking code above was generated by Hibernate, is it possible to have hibernate emit the needed query hints?

  3. we can make the tables pretend to be large enough that the query optimizer selects the index seek with the undocumented features in UPDATE STATISTICS http://msdn.microsoft.com/en-AU/library/ms187348(v=sql.110).aspx . Can anyone see any downsides to making all tables with less than 1000 rows pretend they have 1000 rows over 10 pages?

Upvotes: 1

Views: 1502

Answers (3)

Stoleg
Stoleg

Reputation: 9300

Just to add another option you may consider.

You can lock entire table on update by using 

ALTER TABLE MyTable SET LOCK_ESCALATION = TABLE

This workaround is fine if you do not have too many updates that will queue and slow performance.

It is table-wide and no updates to other code is needed.

Upvotes: 0

Bohemian
Bohemian

Reputation: 425033

You could create a view that is a copy of the table but with the hint and have queries use the view instead:

create view A2 as
select * from A with(forceseek)

If you want to preserve the table name used by queries, rename the table to something else then name the view "A":

sp_rename 'A', 'A2';
create view A as
select * from A2 with(forceseek)

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294287

You can create a Plan Guide.

Or you can enable Read Committed Snapshot isolation level in the database.

Better still: make the index clustered.

For small tables that experience high update ratio, perhaps you can apply the advice from Using tables as Queues.

Can anyone see any downsides to making all tables with less than 1000 rows pretend they have 1000 rows over 10 pages?

If the table appears in a another, more complex, query (think joins) then the cardinality estimates may cascade wildly off and produce bad plans.

Upvotes: 4

Related Questions