September 15, 2011 at 12:43 pm
I have come across a problem I am trying to understand. I have done weeks of searching and reading but I fear I am missing something obvious.
I have two queries one with a like and one with a cotnains cluase. The two queries return almost the same results with the exception of the contains does not include anything that was added since the last reindex.
I have tested this by adding a record and running the two queries. I then ran our maintenance plan and boom the missing row is now found in the query with the contains cluase. Why would this have an impact on the result?
For the record the query with the contains is from a filter option in a third part app so I can not just change it. So I am trying to understand why the difference is there at all.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 16, 2011 at 7:28 am
So I have a bit more information. It seems if I have peaced the information together correctly that and index rebuild during reqular maintenace can cause the text catalog to not update corectly. as a result I had to rebuild the text catalog and the queries started working correctly. I did have to reboot before I could do a rebuild which I found odd but it did fix the problem. It was suggested on one article I read that you rebuild the catalog as part of your maitenance plan. The question is how? Any thoughts on that?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 16, 2011 at 7:41 am
Contains is dependent on the full-text index. If the index is up-to-date, it will be faster than Like, often much, much faster. (Of course, full text indexing can also be used for things Like can't be used for efficiently, like thesaurus-based searches and proximity searching.)
Take a look at this site: http://msdn.microsoft.com/en-us/library/ms142503.aspx
It's a bunch of how-to for full text indexes and catalogs. You'll get what you need from there with regards to updating/rebuilding/creating and so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 16, 2011 at 7:45 am
I have actually read all those already. What I did not see and have not found an answer for was why was a reboot of SQL server needed before REBUILD was not greyed out. also how do you automate a rebuild as part of the maitenance plan? or is that even a good idea?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 16, 2011 at 8:00 am
If you want to automatically rebuild, you'd use Alter Fulltext Catalog (details here: http://msdn.microsoft.com/en-us/library/ms176095.aspx).
Run that as a script in your maintenance job.
I'm not sure why it would require a reboot. Possibly something got hung up?
Do you have automatic updates turned on in it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 16, 2011 at 11:54 am
Yeah automatic updates are on that was one of the first things I checked. I was a little surprised when I got the different results which was why I was set on the path to do a rebuild to start with. I am still not entirely conviced that the regular maitenance would cause this problem but since I can not recreate this problem in another environment and I can not find a deffenitive answer I do not have much else to go on.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply