Full Text searching eventually utilizes 100% CPU

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Full Text searching eventually utilizes 100% CPU

Author

Topic

CodeDreamer68
Starting Member

9 Posts

Posted - 2008-03-13 : 13:23:51

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

CodeDreamer68
Starting Member

9 Posts

Posted - 2008-03-13 : 21:19:34

New information:

As I may have mentioned, executing the Stored Procedure (shown in my previous post) in a New Query Window in SQL Management Studio hangs up the server at 100% CPU.

As a test, I took just the SELECT statement out of the stored procedure and executed it in a New Query Window within the SQL Management Studio.

It returned all matching records almost instantly without even a hickup on the server.

I then tested the exact query within a stored procedure, and back to the 100% CPU usage again. (it takes more than 20 seconds to return results)

So, what I am doing now is re-writing my code so it builds these queries as Ad-Hoch queries instead of using a Command object and passing parameters to a stored procedure. (less efficient, but if it works...)

Obviously this is not the ideal way to do this. I'd much rather call a stored procedure.

Can anyone shed any light on this?

maassql
Starting Member

13 Posts

Posted - 2008-03-14 : 12:26:18

Look at the query plan after a copy ( fast ) and in the morning ( slow ). Detect differences. Diagnose from there.

Anything will give up its secrets if you love it enough. -- George Washington Carver

CodeDreamer68
Starting Member

9 Posts

Posted - 2008-03-14 : 16:11:34

quote:
Originally posted by maassql

Look at the query plan after a copy ( fast ) and in the morning ( slow ). Detect differences. Diagnose from there.

Anything will give up its secrets if you love it enough. -- George Washington Carver

Sorry, how do I do that in SQL 2005 Management Studio?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-14 : 22:13:51

Choose include actual execution plan under query in query window of ssms.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources