This is one of the scripts that I will be using for my presentation “Tips and Tricks for Using SQL Server 2008 Integrated Full Text Search in a High Volume OLTP Environment” at 10:15-11:30AM in Room 608 at SQLPASS on Tuesday, November 3. There are so many good sessions at PASS this year, that I am a little worried that I will be presenting to an empty room, but hopefully there is some interest in SQL Server 2008 Full Text Search!

We have been using it very heavily at NewsGator for nearly 18 months (we started during the CTP cycle), with very good results. Using iFTS allowed us to stop using a third party search solution that was extremely brittle and labor intensive, and we were able to also repurpose several dedicated servers that we had used for the previous solution.

-- Basic iFTS Monitoring Queries
-- Glenn Berry
-- November 2009
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

USE AdventureWorks;
GO

-- Set Change Tracking on a table to Automatic
ALTER FULLTEXT INDEX ON Person.Contact SET CHANGE_TRACKING AUTO;

-- Get all catalogs that use auto change tracking 
-- with their population status for the current database
SELECT c.name, c.active_fts_index_count, c.is_paused, c.status_description, c.row_count_in_thousands, 
OBJECT_NAME(p.table_id) AS table_name, p.population_type_description, 
p.is_clustered_index_scan, p.status_description, 
p.completion_type_description, p.queued_population_type_description, 
p.start_time, p.range_count
FROM sys.dm_fts_active_catalogs AS c 
CROSS JOIN sys.dm_fts_index_population AS p 
WHERE c.database_id = p.database_id 
AND c.catalog_id = p.catalog_id 
AND c.database_id = (SELECT [dbid] FROM sys.sysdatabases WHERE name = DB_NAME());

-- Set Change Tracking on a table to Manual
ALTER FULLTEXT INDEX ON Person.Contact SET CHANGE_TRACKING MANUAL;


-- Which languages are supported by Fulltext index?
SELECT lcid, [name] 
FROM sys.fulltext_languages 
ORDER BY lcid;

-- Which document types are supported by Fulltext filters currently installed?
SELECT document_type, [path], [version], manufacturer 
FROM sys.fulltext_document_types; 

-- List fulltext catalogs
SELECT fulltext_catalog_id, name, is_default, is_accent_sensitivity_on, 
       principal_id, is_importing 
FROM sys.fulltext_catalogs;

-- List fulltext catalogs and fulltext indexes 
SELECT OBJECT_NAME([object_id]) AS [TableName], index_id, fulltext_catalog_id  
FROM sys.fulltext_index_catalog_usages;

-- List columns and languages that are in each fulltext index
SELECT OBJECT_NAME([object_id]) AS [TableName], column_id, language_id
FROM sys.fulltext_index_columns;

-- List summary information for each fulltext index
SELECT OBJECT_NAME([object_id]) AS [TableName], unique_index_id, fulltext_catalog_id,
      is_enabled, change_tracking_state, change_tracking_state_desc, has_crawl_completed,
      crawl_type, crawl_type_desc, crawl_start_date, crawl_end_date,
      stoplist_id, data_space_id
FROM sys.fulltext_indexes
ORDER BY OBJECT_NAME([object_id]);

    
-- This is deprecated in SQL Server 2008, but still returns some info
EXEC sp_help_fulltext_catalogs 'ftCatalog';


-- Check Full Text Fragments  in FT Catalog
-- (lower number of rows is better, closed fragments are bad)
-- Status Codes
-- 0 = Newly created and not yet used
-- 1 = Being used for insert
-- 4 = Closed. Ready for query 
-- 6 = Being used for merge input and ready for query
-- 8 = Marked for deletion. Will not be used for query and merge source.
SELECT OBJECT_NAME(table_id) AS [TableName], fragment_id, fragment_object_id, 
[timestamp], [status], data_size, row_count
FROM sys.fulltext_index_fragments WITH (NOLOCK);

-- Start a Manual Merge (when fragment count is high)
ALTER FULLTEXT CATALOG ftCatalog REORGANIZE;
    
-- Completely rebuild the FT Index (this may take some time)
ALTER FULLTEXT CATALOG ftCatalog
REBUILD WITH ACCENT_SENSITIVITY=OFF;


-- Querying FULLTEXTCATALOGPROPERTY
    
-- Check Master Merge Status (1 = in Progress)
SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'MergeStatus') AS [Master Merge Status];
    
-- Check Populate Status (1 = in Progress)
-- 0 = Idle 
-- 1 = Full population in progress 
-- 2 = Paused 
-- 3 = Throttled 
-- 4 = Recovering 
-- 5 = Shutdown 
-- 6 = Incremental population in progress 
-- 7 = Building index 
-- 8 = Disk is full. Paused.
-- 9 = Change tracking
SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'PopulateStatus') AS [Populate Status];
    
-- Check Accent sensitivity of the FT Catalog
SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'AccentSensitivity') AS [Accent Sensitivity];
    
-- Number of full-text indexed items currently in the full-text catalog 
SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'ItemCount')AS [Item Count];
    
-- Size of the full-text catalog in megabytes 
SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'IndexSize')AS [Size in MB];


-- Active FTS Catalogs
SELECT database_id,catalog_id,memory_address,name,is_paused,[status],status_description,
       previous_status,previous_status_description,worker_count,active_fts_index_count,
       auto_population_count,manual_population_count,full_incremental_population_count,
       row_count_in_thousands,is_importing
FROM sys.dm_fts_active_catalogs;
    
-- Outstanding FTS batches
SELECT database_id,catalog_id,table_id,batch_id,memory_address,crawl_memory_address,
       memregion_memory_address, hr_batch,is_retry_batch,retry_hints,
       retry_hints_description,doc_failed,batch_timestamp
FROM sys.dm_fts_outstanding_batches;
  
-- FTS Index Population for catalogs with auto change tracking
SELECT database_id,catalog_id,table_id,memory_address,population_type,population_type_description,
     is_clustered_index_scan,range_count,completed_range_count,outstanding_batch_count,[status],
     status_description,completion_type,completion_type_description,worker_count,
     queued_population_type, queued_population_type_description,start_time,incremental_timestamp
FROM sys.dm_fts_index_population;

-- Population types
-- 1 = Full population
-- 2 = Incremental timestamp-based population
-- 3 = Manual update of tracked changes
-- 4 = Background update of tracked changes.



-- Clear Wait Stats
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
    
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN('SLEEP_TASK', 'BROKER_TASK_STOP', 
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  'LAZYWRITER_SLEEP')) -- filter out some irrelevant waits
SELECT W1.wait_type, 
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold for waits
        
    
-- Get clustered index fragmentation
SELECT OBJECT_NAME([object_id]) AS [TableName], avg_fragmentation_in_percent, 
       index_type_desc, alloc_unit_type_desc  
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), 
OBJECT_ID(N'AdventureWorks.Person.Contact'), 1, NULL , 'LIMITED');