Post reply

Full Text indexes Not Populating - SP_Blitz Warning

  • October 25, 2017 at 1:12 pm

    #329277

    I ran sp_Blitz on a server and it indicated that full text indexes were not populating. I got a list of the indexes that are not populating and looked at the definitions of the indexes. The syntax of those indexes does not look like BOL examples for full text index creation, but instead they look like traditional row indexes. And they are on BigInt data types not text.

    Here is how I got a list of the indexes that hadn't been populated recently.  
    SELECT    FTCatalogName    = c.name
      ,TableName    = t.name
      ,IndexName    = i.name
      ,LastCrawlEnd    = fi.crawl_end_date
    FROM    sys.fulltext_indexes fi
      JOIN sys.tables t            ON t.[object_id] = fi.[object_id]
      JOIN sys.fulltext_catalogs c    ON fi.fulltext_catalog_id = c.fulltext_catalog_id
      JOIN sys.indexes i            ON fi.unique_index_id = i.index_id AND fi.[object_id] = i.[object_id]
    WHERE    fi.is_enabled        = 1
     AND    fi.crawl_end_date < DATEADD(DAY, -7, GETDATE())
    ORDER    BY fi.crawl_end_date, t.name, i.name;

    Here is an example of DDL for one of the indexes returned by the above query.

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblIntAnalysedItems_106]') AND name = N'PK_tblIntAnalysedItems_106')
    ALTER TABLE [dbo].[tblIntAnalysedItems_106] ADD CONSTRAINT [PK_tblIntAnalysedItems_106] PRIMARY KEY CLUSTERED
    (
        [RowId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70)
    ON [SomeObject]
    GO

    I'm not particularly familiar with full text indexing but it seems sp_Blitz is highlighting full text index issues that aren't really full text indexes. Just confused by what I'm seeing and could use some help  deciphering whether I really have an issue or not.

  • October 25, 2017 at 1:41 pm

    #1965227

    Have you tried going to the horse's mouth and asking Brent?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 25, 2017 at 2:30 pm

    #1965236

    Phil, part of why I'm here asking the question is that the first query in my post identifies the indexes, but those indexes don't seem to be full text indexes.  As a result, I was wondering if there was something wrong with that initial query, my understanding of full text indexes or both.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply