Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Exclamation Unanswered: Full Text searching eventually utilizes 100% CPU

    For one day, this SPROC executes very quickly to return results on a Full Text catalog.


    ALTER Procedure dbo.sp_RSSHarvestedHeadlines_FullTextSearch
    (
    @ORKeywords varchar(4000) = 'xxxx',
    @ANDKeywords varchar(4000) = 'xxxx',
    @NOTKeywords varchar(4000) = 'xxxx',
    @SourceID int = -1,
    @IsHidden bit = null
    )
    As
    set nocount on

    SELECT HHL.HeadlineID,
    HHL.Title,
    HHL.Link,
    HHL.[Description],
    HHL.PubDate,
    HHL.GMTDateAdded,
    RSSSources.SourceTitle,
    RSSSources.SourceLink
    FROM RSSHarvestedHeadlines HHL
    INNER JOIN RSSSources ON HHL.SourceID = RSSSources.SourceID
    WHERE HHL.PublishedFlag = 0
    AND (@IsHidden is null OR HHL.HideFlag = @IsHidden)
    AND (@SourceID = -1 OR HHL.SourceID = @SourceID)
    AND (@ORKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ORKeywords) OR CONTAINS(HHL.Description, @ORKeywords)))
    AND (@ANDKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ANDKeywords) OR CONTAINS(HHL.Description, @ANDKeywords)))
    AND (
    @NOTKeywords = 'xxxx'
    OR (
    (NOT CONTAINS(HHL.Title, @NOTKeywords)
    AND
    NOT CONTAINS(HHL.Description, @NOTKeywords))
    )
    )

    ORDER BY HHL.GMTDateAdded DESC, HHL.PubDate DESC




    But somethign happens overnight and in the morning the sproc times out. While running (even from a new query window on the SQL 2005 server itself) it utilizes 100% CPU until it times out.


    When I pass default parameters to the sproc (not using any part of the query that uses Full Text) the sproc returns every record in the database very quickly. No hang ups.

    But the moment I add any text in say the @ORKeywords parameter, for example, the sproc utilizes 100% CPU for maybe 15 seconds and then times out.

    By accident I discovered that I can fix this temporarily by copying the database. I don't use the new copy or anything. Just the act of copying the database fixes it. The sproc then executes normally, and quickly. But the next morning it's back to slow again.

    Note, over night I am adding about 1000 records to the table.

    Would automatic updates to the FT Catalog choke on 1000 records?

    Also note that one of the fields being cataloged is a "Text" field (blob). Would that cause this?

    Would what text is being added to the table matter? What if an invalid character was added (like some european character or a control character)? Would FT indexing hang up on that?

    I am at a loss.


    The following code will recreate the table:


    CREATE TABLE [dbo].[RSSHarvestedHeadlines](
    [HeadlineID] [int] IDENTITY(1,1) NOT NULL,
    [SourceID] [int] NOT NULL,
    [Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Link] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PubDate] [datetime] NULL,
    [GMTDateAdded] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTDateAdded] DEFAULT (getutcdate()),
    [GMTLastHarvested] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTLastHarvested] DEFAULT (getutcdate()),
    [HideFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_HideFlag] DEFAULT ((0)),
    [PublishedFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_PublishedFlag] DEFAULT ((0)),
    [EditStamp] [timestamp] NOT NULL,
    CONSTRAINT [PK_RSSHarvestedHeadlines] PRIMARY KEY CLUSTERED
    (
    [HeadlineID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Hi Dreamer

    What a concise Post - well explained - how refreshing! - Welcome to the Board.

    I smell from the syntax that this challenge may not be an easy one.

    Unfortunately I've no experience with Full Text searching but will throw in a couple of ideas .

    But somethign happens overnight
    check the Scheduled Jobs.

    100% CPU for maybe 15 seconds and then times out
    look for what setting has such a low timeout?

    Just the act of copying the database fixes
    this would obviously release any Locking Issues!!

    fields being cataloged is a "Text" field (blob).
    Text or Binary - specify datatype?

    What if an invalid character was added
    specify source type & SQL Character set in use for SQL Box

    I'm not sure what needs configuring/setting up to enable the SQL2005 MSFTESQL.exe engine service but a quick Google throws numerous incidents of hiccups/self blocking etc for this new product - Check SPacks & MSoft for fixes.

    Good Luck
    Last edited by GWilliy; 03-14-08 at 18:27.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Just another quick thought

    a) can you reproduce error writing another SProc that just does the nasty bit (simplify through elimination)

    b) don't forget to consider using SQLProfiler
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Mar 2008
    Posts
    2
    As it turns out the Query Plan / Execution Plan that SQL created for the Stored Procedure was probably the issue.

    I ran a test shortly after posting, in that I took the SELECT statement out of my Stored Procedure and placed it into a New Query Window.

    When I ran it in the New Query Window the SELECT statement returned results almost instantly.

    But running that SELECT statement in the Stored Procedure eats up 100% CPU and hangs the box.

    To resolve the issue (because I don't have time to learn how to deal with Execution Plans for compiled Stored Procedures right now) I simply re-wrote my code to build an Ad-Hoch query instead.

    Problem solved.

    Yes, I'm sure there's a better way, but I don't have the time.

    For anyone else who happens accross this post with a similar problem, I can suggest some other possibilities that I looked into:

    Blocking due to Locks:
    http://support.microsoft.com/kb/263889

    Monitoring Blocking:
    http://support.microsoft.com/kb/271509

    Tuning Stored Procedures
    http://www.codeproject.com/KB/databa...utorial-1.aspx

    Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
    http://www.microsoft.com/technet/pro...05/recomp.mspx


    Also note: SQL 2005 Server SP2 (latest build) contains many fixes for "Full Text" stuff. So I made sure I had that installed too.

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    nice 1 - thx 4 the update
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •