sqlserverでのフルテキストインデックスについて（降順検索の場合異常に遅い）

Question

sqlserverを利用しています。

フルテキストインデックスを用意し、検索を行っています。
現在のレコード数は約1億レコードあります。

現在、テストを行っているのですが、対象レコードが多いキーワードにて検索すると、
10秒ほどかかっております。

対象レコードが少ないキーワードにて検索すると、即座にレスポンスがある状況です。

実行しているSQLは下記のような内容です。
select * from [TABLE_NAME] where CONTAINS([TITLE],'"[KEYWORD]"') ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

対象レコードで合致するものが、上記SQLであれば20件該当すれば抜けるとイメージしておりますが、
該当件数が多い場合に、レスポンスが遅くなる理由が解らず困っております。

環境
Windows Server 2016
SQL Server 2017
CPU E5-2650*2
MEMORY 192GB
DBサイズ　約120GB

Answer 1

identity_column_nameに関するインデックスはありますか？
また、desc指定していますか？

追記

select で使用されるインデックスは一つだけですので、そのインデックスに、whereやorder byでの項目が含まれている必要があります。
インデックスを分けて使用されるようにネストしてみましょう。

[identity_column_name] DESC　のインデックスが有る状態で、以下の問い合わせで改善されないでしょうか。

select * from [TABLE_NAME] 
where プライマリーキー in (
        select プライマリーキー from [TABLE_NAME]  where CONTAINS([TITLE],'"*[KEYWORD]*"')
      ) 
ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

但し、上記は、そもそも以下のSQLが単独で早くないと意味はありません。

select * from [TABLE_NAME] 
ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

select プライマリーキー from [TABLE_NAME]  where CONTAINS([TITLE],'"*[KEYWORD]*"')

2番目が遅いという事なので、検索結果が少なくなるように条件を工夫するようなアプローチを考えないと駄目かもしれませんね。

例えば、

select  * from [TABLE_NAME] where CONTAINS([TITLE],'"*[KEYWORD]*"')
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

select count(*) from [TABLE_NAME] where CONTAINS([TITLE],'"*[KEYWORD]*"')

などで（後者は遅いかもですが）、閾値を超える場合に、条件を追加するようにメッセージを出すとか。

Comments

[ycy]

identity_column_nameに関するINDEXは存在するのですが、
インデックスが利用されていない状況だとわかりました。

identity_column_nameを主キーとしており、自動的にPK_[table_name]_[identity_column_name](クラスター化)
が自動的に生成されています。
このインデックスのプロパティ内に並べ替え順序とあるのですが、昇順のまま変更出来ない状況です。

試しに、非クラスター化インデックスで[identity_column_name]の降順インデックスを作ってみましたが、効果なく、
フルテキストインデックスのプロパティー内　一意インデックスがPK_[table_name]_[identity_column_name]から変更出来ません。
そのため昇順のインデックスが使われているのは？と思います。

select TOP 100 * from [TABLE_NAME] where CONTAINS([TITLE],'"*[KEYWORD]*"')
上記のようなSQLですと一瞬で返ってきますので、DESC部分だと思うのですが・・

根本的に何か間違っているとは思うのですが、どの部分なのか把握できずにいます。

[ycy]

おかげさまで、
select * from [TABLE_NAME]
where プライマリーキー in (
select プライマリーキー from [TABLE_NAME] where CONTAINS([TITLE],'"*[KEYWORD]*"')
)
ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
で劇的に早くなりました。

ありがとうございます！