4

As we can’t trust our customers to update the index stats etc in sql server, we have in the past had to use index hints. (As some of our customers are still on Sql Server 2000, we also can’t depend on the better query optimizer in later version of Sql Server).

So how do I pass in index hints when using LinqToSql to build a query rather than raw Sql?

Ian Ringrose
  • 51,220
  • 55
  • 213
  • 317
  • 1
    Why do they need to keep the statistics updated? You should have them on auto update - and can easily run an update script every weekend. In 20 years I never had problems with statistics updates. – TomTom May 05 '10 at 15:45
  • I have to agree with @TomTom on this one... this isn't really the kind of problem that index hints were intended to solve, and I think you'd get a lot better mileage out of forcing a statistics update either via auto-update (which is enabled by default) or by having your own background process that runs `UPDATE STATISTICS` or `sp_updatestats` if you're worried that they'll turn off auto-update. – Aaronaught May 05 '10 at 15:54
  • @Tom, the systems need to run 24*7, so "update scripts" etc are very hard to get agreement to, and I don't know if the people that install the systems even understand what they are. The problem with out of data statistics may just be a "old wifes tale" but it is what I have been told when I have asked why "index hints" are used. I may get away without using any index hints, but I am tring to cover my bases hare. – Ian Ringrose May 05 '10 at 15:56
  • 1
    Why? SQL Server Enterprise (the ONLY one usable ina 24/7 scenario seriously, as it has all the nice high availability features) support regerenerating an index WITHOUT INTERRUPTION ;) – TomTom May 05 '10 at 16:25
  • @TomTom funny! not everyone has $15,000 laying around - especially not people using Linq2Sql ;-) – Simon_Weaver Sep 16 '17 at 07:17

2 Answers2

2

This is not something that is supported, you only options over a query is to set the transaction isolation level, but that doesn't help with this issue.

I would recommend one of two courses of action is this is something that you really need to do.

  1. You can write a stored procedure and handle this within your procedure and use LINQ to call it....

  2. Move away from LINQ to SQL

Personally, IF this is really necessary I would recommend option two. If in fact you must take that level of action/control over your database calls, auto-generated SQL is going to be problematic for you at later points as well.

Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173
0

Unfortunately this is not possible. The only thing you can do is influence the transaction isolation level. See an example of this here.

Community
  • 1
  • 1
Steven
  • 166,672
  • 24
  • 332
  • 435