Full-text Index Hang! - SQL Server Forums

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

Express Edition and Compact Edition (2005)

Full-text Index Hang!

Author

Topic

dvdavid2001
Starting Member

1 Post

Posted - 2009-11-21 : 03:07:13

Hi guys,

I am beginner in supporting SQL server so please guide me along. I have one strange problem in my SQL server. My SQL Server edition is Express Edition with Advanced Services SP3 - 9.00.4035.00.

I am querying a table with full-text index on it. The table contains approximately 3 million records with the size of 1.5 GB.

The problem is that SQL server hangs (CPU 100%) if the WHERE criteria contains both the date range as well as full-text index. I can only fix this either by restarting SQL server services or by killing the msftesql.exe manually via Task Manager.

Please refer to the SQL below to the problem.

Any comments on this? This is driving me nuts.

Thanks
David


-- this works fine and return result 
select count(1)
from product_pass
where (CONTAINS(CUSTOMER, '"*test*"') OR CONTAINS(SAM_DESC, '"*test*"'))
 
-- this hangs!!!
select count(1)
from product_pass
where result_approved_dt >= CONVERT(DATETIME, '11/01/09', 1) 
AND   result_approved_dt <= CONVERT(DATETIME, '11/01/09', 1) 
AND (CONTAINS(CUSTOMER, '"*test*"') OR CONTAINS(SAM_DESC, '"*test*"'))
 
-- change the year to 2008, this is working fine
select count(1)
from product_pass
where result_approved_dt >= CONVERT(DATETIME, '11/01/08', 1) 
AND   result_approved_dt <= CONVERT(DATETIME, '11/01/08', 1) 
AND (CONTAINS(CUSTOMER, '"*test*"') OR CONTAINS(SAM_DESC, '"*test*"'))
 
-- this also works fine
select count(1)
from product_pass
where result_approved_dt >= CONVERT(DATETIME, '11/01/09', 1) 
AND   result_approved_dt <= CONVERT(DATETIME, '11/01/09', 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources