All geek questions in one place

How to stop using the database index for a specific query?

Is there any way to do this? I am currently faced with a problem as one index is used without the need for my query (as shown in the query execution plan in SQL Server 2008), and this reduces the query performance. This index cannot be dropped because it is useful for some other queries.

+3
source share
6 answers

You can give SQL Server a query hint, for example. force him to use a specific index - but as far as I know, there is no way to prevent the use of a specific index. Can you find another index that might use your query?

In addition, with SQL Server 2008, you have new additional query hints, called OPTIMIZE FOR, that can help you determine (and explain) the query optimizer why it should optimize.

See the MSDN documentation for tooltips for more information .

This is a typical and fundamental compromise that you must make when adding indexes - yes, it can increase the performance of one query that you are looking at, but at the same time it can lead to a decrease in others. There really is no easy solution - either the gain for another query is more than the pain for that query, and then save the index. Otherwise release it.

+1
source

hints, ( )

, : (INDEX (0)).

+2

, - SQL Server - . "" . / / , . . , .

+2

, , , .

where substring(mycol,1,16) = ?
+1

, ( ). , , ...

.

+1

, , , ( ), , .

SQL, - , , .

, .

FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))

: http://msdn.microsoft.com/en-us/library/ms187373.aspx

0

Source: https://habr.com/ru/post/1734510/

More articles:


All Articles