1

We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started populating the catalog. It almost grew to 835GB and Population status stuck to "Processing Notification". I think is status code 9 which corresponds with the "Change tracking" status in the SQL. It gets stuck in this status almost for ever.

Is there a way to deal with this huge catalog size, may be shrink it or move it to a different location.

Is switching off Change Tracking going to improve the situation.

Any help regarding this will be highly appreciated.

UPDATE:

Some more info about the volume of data we are dealing with:

Row Count: 129,953,562 Total: 1327 GB Data 867 GB Index 452 GB Column size 361GB

Although we went to FT instead of LIKE search, but is there a strategy to optimize LIKE search to perform better (may not be as efficient as FT, but somewhat near to that) enter image description here

1

Please provide Full-text index definition, rowcount and total size of that column. Having VARCHAR(max) gives 32 megabytes per row of storage. If someone inserts huge text in that column many times, your db and index will grow very fast.

You can create index on a different FILEGROUP, which in turn can map to db file on another drive. ALTER DATABASE to add file(s) to new file group. FG is created and file(s) added to it in single statement:

USE master
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2008R2 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

For further reading (and source of this code): ALTER DATABASE File and Filegroup Options

Sorry, cannot add a comment for some reason. wil update /delete this answer later.

UPDATE

Although possible via a mapped drive, storing an index on the network has big performance penalty. If you are using all records in the table frequently and FT index is new functionality to explore, then this is ok: table on local storage and index on the network.

Alternatively I would suggest considering table usage and partition table into current-frequently used - and archive - almost unused - data. You can have several archive tables on different shares. Table and indexes will be stored on the same partition.

RE: Compression. It is a trade off between storage and CPU. If you have plenty of CPU resource, do consider this. If index stored on the network and compression rate is good, then it would also help.

UPDATE on Compression: Compression would requires row size to be less than 8060 bytes. So if you are storing text, then compression would not be possible for the table (= clustered index or heap). But if FTI row size is less than that, then you can compress FTI index.

  • Can we have this filegroup in a UNC path or it needs to be in the same database server. Also, can we compress the file if required. It would be really helpful if you can elaborate on this. Thanks. – Saikat Oct 7 '14 at 18:17
  • Some more info:Row Count: 129,953,562 Total: 1327 GB Data 867 GB Index 452 GB Column size 361GB – Saikat Oct 8 '14 at 2:43
  • @Saikat: You should edit the extra information into your question otherwise other people might see it but not respond because the don't see enough detail and they don't have time to ask for it and come back later. Extra detail that is present only in comments can get lost on the page to a passer-by. – David Spillett Oct 8 '14 at 8:15
  • Compression built in to SQL Server ( ie PAGE and ROW ) does not tend to have much of an effect if any on VARCHAR(MAX) columns. – wBob Oct 8 '14 at 10:58
0

Does seem a bit bloated. What kind of documents do you store in there? What kind of lengths do they have?

Have you done something like turn off the system stop list? This would bloat the Full-text Index (FTI) as characters such as 'a' previously dismissed as noise would now be in the FTI.

Inspect the content of your index using DMVs, eg

SELECT * 
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documents') ) X
WHERE document_id = 1
  • We are trying to store raw/plain text in the column holding the documents. The system stop list is also not turned off also, rather we have added couple of additional words in the stop list. The table although is pretty huge and as I mentioned, the column is varchar(max). – Saikat Oct 7 '14 at 18:16
  • Some more info: – Saikat Oct 8 '14 at 2:41
  • 1
    One thing that caught my eye was the ratio of unique items to items, 523,043,671 unique to 258,911,934 items, ie a 2-1 ratio. I loaded up the entire works of Shakespeare and the ratio is 0.25 - 1, ie lot's of expected duplication. What is so 'unique' about your documents? Are you including GUIDs in the FTI? Please post the table and FTI definitions. – wBob Oct 8 '14 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.