1

I have a table which has a PK on the ID column. A clustered index on the PK column causing huge cost when where clause has conditions for columns except the ID.

Can I manually disable the index by using a hint?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Meow Meow
  • 637
  • 6
  • 17
  • 2
    Huh??? What are you actually trying to do? You can't temporarily disable a clustered index for the duration of a single query. That just doesn't make any sense. – Sean Lange Nov 27 '17 at 15:25
  • 1
    Index the fields you are querying on if you don't have other indexes. Don't over index though. Analyze your data and determine where the indexes are needed. – SS_DBA Nov 27 '17 at 15:27
  • I try to reduce a cost. A clustered index scan hangs all query. I assume if i'll disable index then query works faster. But i don't know for sure. – Meow Meow Nov 27 '17 at 15:28
  • @WEI_DBA through your comment i catched that i can't disable index by hint. Is it right? – Meow Meow Nov 27 '17 at 15:34
  • No, you can drop it and recreate the clustered index on other more useful columns if needed. By default the clustered index is created on the PK. – SS_DBA Nov 27 '17 at 15:35
  • 3
    If its scanning the clustered index then there's obviously currently no better index it can use. Disabling the clustered index means... it has to scan the whole table anyway (which is what a clustered index scan is). Why you would think that would somehow improve performance is unclear. – Damien_The_Unbeliever Nov 27 '17 at 15:35
  • The clustered index *is* the table. It's not like any regular index that you can disable. Disabling the clustered index causes the entire table to be rewritten as a heap table, and that's never going to improve performance. You can create a covering, non-clustered index to improve performance if scanning the table is the bottleneck. Note that even for regular indexes, there is no hint that forces the optimizer to not consider one particular index -- there is only one to force it to use specific indexes only, if possible for the query. But that's almost certainly not what you need. – Jeroen Mostert Nov 27 '17 at 16:05
  • Post your table schema, your query, and your SQL Query Plan, and we can possibly help you optimize the query. Without that, it's difficult to even understand what it is you're asking or why. – pmbAustin Nov 27 '17 at 17:30
  • @Damien_The_Unbeliever i just checked a plan =) – Meow Meow Nov 28 '17 at 07:21
  • i have a query like: _select * from table1 t1 left join table2 t2 on t1.ID = t2.ID where t1.field1 <> 'exmpl1' and t1.field2 <> 'exmpl2';_ The optimazier thinks that checks conditions in where statement is cheaper than left join so the index impedes to query. – Meow Meow Nov 28 '17 at 07:28

1 Answers1

1

No

Read these answers

Since the data is actually stored in the order of the clustered index it is fundamental and intrinsic to how the query is processed. You cannot avoid the clustered index and nor should you want to.

I accept your query is slow but it is not because the query engine is using the clustered index. It may be true that you could have a better clustered index.

Hint: For a specific answer, more useful to you, you'll need to ask a more specific question.

Jodrell
  • 34,946
  • 5
  • 87
  • 124
  • @Jordell thank you a lot You answered on my question. If it'll be interesting than i add some additional information about query above. – Meow Meow Nov 28 '17 at 07:31