All geek questions in one place

MS-SQL Server 2000 Slow Full-Text Indexing

We have a full text index on a rather large table of 633,569 entries. The index is rebuilt from scratch as part of the service plan every evening, after a group of DTS packages starts deleting / inserting records. Large chunks of data are deleted, then inserted (to keep track of updates and inserts), so incremental indexing is not possible. Changing packages only when necessary is not impossible, as this is an outdated application that will eventually be replaced.

FTI includes two columns: one is varchar (50), not null, and the value is varchar (255).

The primary key column has a clustered index, which is only an identity column. There is also a combined index for the integer column and varchar (50) column mentioned above. This latest index was added for performance reasons.

The problem is that reindexing is painfully slow - about 8 hours.

The server is quite reliable (dual-processor, 4 GB of RAM), and everything quickly goes beyond the scope of this reindexing.

Any tips on how to speed this up?

UPDATE

Our client has access to the sql window. Turns out they turned on table change tracking, which is part of the full text index. We turned it off, and the total population took less than 3 hours. Still not great, but better than 8.

UPDATE 2

FTI picks up ~ 8 hours again.

+3
7

SQL Server - .

  • " index in background ".

- "update index in background". (FTI, "" SQL, ), , . , , , . .

-, ( Enterprise Manager) :

sp_fulltext_getdata CatalogID, object_id

PK MSSearch, MSSearch sp_fulltext_getdata, . , 50 , 50 .

, , MSSearch :

sp_fulltext_getdata CatalogID, object_id

, . , 50 , 50 . ? MSSearch , , . , , , .

, , , , , .

. , , MSSearch :

sp_fulltext_getdata CatalogID, object_id

. , , , ( ) sysfulltextnotify. MSSearch , , sysfulltextnotify.

, , , , MSSearch, , . , , . , ,

  • ,

, Profiler . Profiler .

, . - , 8 . , MSSearch , SQL Server, - .

  • .

, , SQL FTI

  • , temp pagefiles

, . , RAID-1. temp RAID-1. RAID-1 .

  • Temp DB - 1 / .
+3

  • ?

  • RAID?

  • tempDB?

(BTW, , ...;))

+1

, .

EDIT: , .

0

, reindex live?

; , FTS. ( ). , ?

? 3 ; , , , , . , 40% ( , .) > 40% , , , ( - - ). , , , , reindex .

, FTS ? / - (, 4 5 ). , 2000, .

FTS/Reindexing ; 3x - ; , Pagefile.sys, Pagefile.sys . NT Windows 2000? , 2gb .

0

, , , , , . , , .

0

FT- SQL Server. . :

MAX SERVER MEMORY SQL Server ( ), . , MAX SERVER MEMORY, - , , , 1,5 . .

0

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

More articles:


All Articles