You might try scripting it out to see if you get a different behaviour. This simple example runs in under a minute on my local SQL 2005 instance:
USE master
GO
SET NOCOUNT ON
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
BEGIN
ALTER DATABASE fullTextDemo3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE fullTextDemo3
END
GO
IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
CREATE DATABASE [fullTextDemo3]
GO
ALTER DATABASE fullTextDemo3 SET RECOVERY SIMPLE
GO
USE fullTextDemo3
GO
CREATE TABLE dbo.nearnessTest (
rowId INT IDENTITY,
keywords VARCHAR(MAX) NOT NULL,
notes VARCHAR(100) NOT NULL,
CONSTRAINT PK_nearnessTest PRIMARY KEY ( rowId )
)
GO
INSERT dbo.nearnessTest
--SELECT TOP 270000 m1.[text], ''
SELECT m1.[text], ''
FROM sys.messages m1
-- CROSS JOIN sys.messages m2
WHERE m1.language_id = 1033
--AND m2.language_id = 1033
GO
-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List3' )
CREATE FULLTEXT CATALOG ftc_List3
GO
-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.nearnessTest (keywords) KEY INDEX PK_nearnessTest ON ftc_List3 WITH CHANGE_TRACKING MANUAL -- CHANGE_TRACKING OFF, NO POPULATION
GO
SELECT 'before' ft, * FROM sys.fulltext_indexes
GO
DECLARE @i INT
SET @i = 0
WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN
SELECT outstanding_batch_count, *
FROM sys.dm_fts_index_population
WHERE database_id = DB_ID()
--SELECT *
--FROM sys.dm_fts_outstanding_batches
--WHERE database_id = DB_ID()
WAITFOR DELAY '00:00:05'
SET @i = @i + 1
IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END
END
SELECT 'after' ft, * FROM sys.fulltext_indexes
GO
SELECT *
FROM dbo.nearnessTest
WHERE CONTAINS( keywords, 'error' )
What happens when you issue an explicit start for the index population, eg
ALTER FULLTEXT INDEX ON dbo.yourTable START FULL POPULATION;
Run these summary functions
SELECT FULLTEXTSERVICEPROPERTY ( 'IsFulltextInstalled' ) IsFulltextInstalled
SELECT DATABASEPROPERTY('fullTextDemo3','IsFulltextEnabled') IsFulltextEnabled
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'AccentSensitivity ') AS AccentSensitivity
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'IndexSize ') AS IndexSize
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ItemCount ') AS ItemCount
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'LogSize ') AS LogSize
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'MergeStatus ') AS MergeStatus
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateCompletionAge ') AS PopulateCompletionAge
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateStatus ') AS PopulateStatus
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'UniqueKeyCount ') AS UniqueKeyCount
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ImportStatus ') AS ImportStatus
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.
Best Answer
You can query sys.dm_os_buffer_descriptors
An example would be something like:
It doesn't appear that way from the documentation. There aren't a lot of options presented.
You're at the distinct disadvantage of using two features together that not a lot of people use separately. You may have to do your own experiments here.
One thing to be aware of is that BPE activity is single threaded, and each I/O is limited to a single 8K page. This might make them ineffective with Full Text.
I'd much rather stick an appropriate amount of RAM in the server.