Full Text shutdown status

I have a large full text index (30 millions records) that is
populating on a live server (they are just doing without the full text
search stuff for now) and the index goes into shutdown mode. What is
that? Last time it did this i stopped and started the mssearch
service and it started back up again. The only problem is that when
it completed last time the number of records in the index was like 8
million. I did a test and a bunch of the rows did not get into the
population even though it supposedly finished correctly. So I am
hesitant to restart the service since I'm afriad it will not
completely fill the index again.
This is SQL 2000 on a Windows 2003 entprise box. But not in a
cluster. It's a stand alone machine. 4 proc, 16 gigs on the machine.
13 of the gigs used by sql. The box does stay pretty busy but not
completely bogged down.
Ryan
are there any event messages in the application log from MSSearch or MSSCI?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"RyanOly" <ryanoly@.gmail.com> wrote in message
news:661760f9.0407141123.66388c39@.posting.google.c om...
> I have a large full text index (30 millions records) that is
> populating on a live server (they are just doing without the full text
> search stuff for now) and the index goes into shutdown mode. What is
> that? Last time it did this i stopped and started the mssearch
> service and it started back up again. The only problem is that when
> it completed last time the number of records in the index was like 8
> million. I did a test and a bunch of the rows did not get into the
> population even though it supposedly finished correctly. So I am
> hesitant to restart the service since I'm afriad it will not
> completely fill the index again.
> This is SQL 2000 on a Windows 2003 entprise box. But not in a
> cluster. It's a stand alone machine. 4 proc, 16 gigs on the machine.
> 13 of the gigs used by sql. The box does stay pretty busy but not
> completely bogged down.
> Ryan

Full Text Searching....THOUSANDS of records!

Hope I am in the correct section.
I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in acommon word like "damage" I get like 32,000 records. Now, theserver handles those records in about one second but the ASP page thatreturns the results takes about one MINUTE to download. When Isave the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
Thanks!
cbmeeks

Your Top 500 query is good, but you could also do a SELECT COUNT SQL query first getting exactly how may records would be returned. Just replace the fields to be returned by "COUNT(*)".
// Instantiate a Command object...
SqlCommand dbCommand = new SqlCommand();
dbCommand.Connection = yourConnectionObject;
dbCommand.CommandText = "SELECT COUNT(*) " +
"FROM table-name WHERE column-name = 'some-value'";
dbCommand.CommandType = CommandType.Text;

// Execute the Command object...
int returnValue = (int)dbCommand.ExecuteScalar();
if ( returnValue > 500 )
string errorMessage = "Your query brings back " + returnValue.ToString() + " records!";
else
// Execute your regular query...
Or you could also just execute your normal query and test the number of rows in the DataTable:
if ( dataSet.Tables[0].Rows.Count > 500 )
string errorMessage = "Your query brings back " + dataSet.Tables[0].Rows.Count.ToString() + " records!";
else
// Display it...
The last could be your best bet as it only incurs one trip to the database.
NC...

|||Thanks!
What I actually did (after I posted the question) is leave the TOP 200(was 500 but I shortened it) and as I was displaying the results, Iupdated a variable. At the end of the page, I say somethinglike: "Over 200 records found, try narrowing your search".
The disadvantage is that you never really know how many records therewas (201 would be the same as 10,000) and it's at the bottom of thepage. But, I can live with it.
How much overhead would the extra SELECT COUNT method cost? Icould profile it I guess. But everyone is complaining about thespeed now.
I wrote the program 4 years ago when I was a rookie.
At least now even very common words just take a second or two to get the page. :-)
cbmeeks
|||

Run a search for CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE Microsoft proprietry implementation of ANSI SQL in SQL Server BOL (books online). Full Text is an add on to SQL Server so you must populate the Microsoft search catalog to get expected results. Hope this helps.

sql

Full Text Search Performance

Hello,
I have full text search enabled on two large tables, the first one has 3
million records (FT index column length is around 512-1025 char), and the
other one has around 1.6 records (FT index column length is around 50-120
char)
The simplest query that uses the smiplest form of FT on the first table FT
index search needs 23-30 seconds to complete and from 10-15 seconds on the
seconds table as it the indexed column is smaller.
SQL server is deployed on 4 P servers with 8 GB of memory and attached to a
very powerful SAN system. Memory usage on the server is not very high so I
believe there is enough memory for the mssearch service to use.
I just wonder if this is the normal throughput of the Full Text search of
SQL Server. If not, I will appreciate any tips and hints that might be the
reason of the system.
Ali Salem
That depends. SQL FTS performance is most sensitive to the number of rows
you are returning. You should limit your results set as much as possible - I
think you will find that the practical limit for most applications is around
100-200 rows.
Limit it by using the the top_n_by_rank operator in ContainsTable or
FreeTextTable, ie
USE Northwind
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'test',200
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||thanks for your reply.
I cannot limit the search in this way, as there are other parameters in my
query that can affect the returned result set and that are ourside the FTS.
I am asking for any figures about FTS performance? What should I expect from
it? Is this behavior am getting normal for the data size or, I am having
something wrong
"Hilary Cotter" wrote:

> That depends. SQL FTS performance is most sensitive to the number of rows
> you are returning. You should limit your results set as much as possible - I
> think you will find that the practical limit for most applications is around
> 100-200 rows.
> Limit it by using the the top_n_by_rank operator in ContainsTable or
> FreeTextTable, ie
> USE Northwind
> GO
> SELECT FT_TBL.Description,
> FT_TBL.CategoryName,
> KEY_TBL.RANK
> FROM Categories AS FT_TBL INNER JOIN
> CONTAINSTABLE (Categories, Description,
> 'test',200
> ) AS KEY_TBL
> ON FT_TBL.CategoryID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> a
>
>
|||No, this is highly abnormal. Performance should be sub second. However I
need to know what your queries look like, ie how many search arguments, how
many rows are returned, and what language you are querying in.
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> thanks for your reply.
> I cannot limit the search in this way, as there are other parameters in my
> query that can affect the returned result set and that are ourside the
FTS.
> I am asking for any figures about FTS performance? What should I expect
from[vbcol=seagreen]
> it? Is this behavior am getting normal for the data size or, I am having
> something wrong
> "Hilary Cotter" wrote:
rows[vbcol=seagreen]
possible - I[vbcol=seagreen]
around[vbcol=seagreen]
3[vbcol=seagreen]
the[vbcol=seagreen]
50-120[vbcol=seagreen]
table FT[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
so I[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
|||Ali Salem,
Could you provide the full output of the following SQL script as it is very
helpful in troubleshooting SQL FTS issue and understanding your environment
and issues!
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
SELECT FULLTEXTSERVICEPROPERTY('ResourceUsage')
go
Additionally, where is your FT Catalog located as by default it is created
under the \FTDATA directory where you have SQL Server installed as well as
where your FT-enabled database files (*.mdf, *.ndf, * *.ldf) are located
relative to the location of your FT Catalog folder? If your 'ResourceUsage
is set to 3, you should increase it to 5 (dedicated) via sp_fulltext_service
'resource_usage' <value>, where <value> is 5.
Thanks,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||Thanks.
- We are using enlgihs language.
- Returned number of rows should be very large (thousands, in some cases it
can be 100, 000). this from the Full-Text Seach, other SQL filters will be
applied as well, but from mssearch this is what will be returned. Please note
that the table size is around 3million records.
- The smiplest query such as SELECT * FROM TABLE1 WHERE CONTAINS(COL1,
'ABCD') need around 30 seconds complete.
I do appreciate any help.
thank you
"Hilary Cotter" wrote:

> No, this is highly abnormal. Performance should be sub second. However I
> need to know what your queries look like, ie how many search arguments, how
> many rows are returned, and what language you are querying in.
>
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> FTS.
> from
> rows
> possible - I
> around
> 3
> the
> 50-120
> table FT
> the
> to
> so I
> of
> the
>
>
|||Below are the result of the script you requested me to run it:
My FT files are stored on SAN storage. So IO should be performing well.
Regarding the ResourceUsage, it is 3. Will not raising it to 5 harm the sql
server performance! I dont want to make the full text search fast by slowing
down SQL Server itself.
I have splitted the result into two posts so that I can post it here.
Thank you for you help
------
us_english
(1 row(s) affected)
------
------
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
(1 row(s) affected)
name minimum maximum config_value
run_value
-- -- -- --
default full-text language 0 2147483647 1033 1033
ftcatid NAME
PATH
STATUS NUMBER_FULLTEXT_TABLES
------
------
------
-- -- --
5 ICSubject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
6 Subject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
7 Names
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
(3 row(s) affected)
TABLE_OWNER
TABLE_NAME
FULLTEXT_KEY_INDEX_NAME
FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
------
------
------
-- -- --
----
dbo
IC_SUBJECTS
PK_IC_SUBJECTS
1 1 ICSubject
dbo
MAIN
PK_INOUT_MAIN
1 1 Subject
dbo
PERSONS
PK_PERSONS
1 1 Names
(3 row(s) affected)
TABLE_OWNER
TABLE_ID TABLE_NAME
FULLTEXT_COLUMN_NAME
FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME
FULLTEXT_BLOBTP_COLID
FULLTEXT_LANGUAGE
------
-- --
-----
-----
-- --
--- --
dbo
2069582411 IC_SUBJECTS
ITEM_DESCRIPTION_AR
3 NULL
NULL 0
dbo
274100017 MAIN
SUBJECT
42 NULL
NULL 0
dbo
466100701 PERSONS
FIRSTNAME_AR
4 NULL
NULL 0
dbo
466100701 PERSONS
LASTNAME_AR
8 NULL
NULL 0
dbo
466100701 PERSONS
FULL_NAME
9 NULL
NULL 0
dbo
466100701 PERSONS
FATHERNAME_AR
12 NULL
NULL 0
dbo
466100701 PERSONS
GRANDFATHERNAME_AR
14 NULL
NULL 0
(7 row(s) affected)
Name
Owner
Type Created_datetime
------
------
-- --
MAIN
dbo
user table 2004-10-14 18:14:31.457
|||The Rest of the script result, one more post is required
Column_name
Type
Computed Length Prec Scale Nullable
TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- -- --
-- --
-- --
DOCID
int
no 4 10 0 no
(n/a)
(n/a) NULL
DESCRIPTION
varchar
no 255 yes
no
no Arabic_CI_AS
DOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
RDOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
EXT_PARTY_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
EXT_PARTY_REP_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBCAT_ID
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
STATUS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CONFID_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REFERENCE_NO
varchar
no 50 yes
no
no Arabic_CI_AS
FORWARD_TO
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FORWARD_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
TO_REMIND
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
REMIND_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
REMARKS
varchar
no 1024 yes
no
no Arabic_CI_AS
REVISION_NO
varchar
no 50 yes
no
no Arabic_CI_AS
TRAN_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
SYSTEM_ID
varchar
no 50 yes
no
no Arabic_CI_AS
IP
varchar
no 50 yes
no
no Arabic_CI_AS
CHECK_SUM
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
SUBMIT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT
nvarchar
no 8000 yes
(n/a)
(n/a) Arabic_CI_AS
PRIORITY_NO
int
no 4 10 0 yes
(n/a)
(n/a) NULL
PROCESS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REMIND_TIME
varchar
no 20 yes
no
no Arabic_CI_AS
WAITING_FOR_REPLY
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SITE_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
DELIVERY_METHOD_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
GROUPID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
LINK_STATUS
int
no 4 10 0 yes
(n/a)
(n/a) NULL
INITIAL_PROCEDURE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CREATOR_USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
IS_SPLITTED
bit
no 1 yes
(n/a)
(n/a) NULL
CHAR_FIELD2
varchar
no 500 yes
no
no Arabic_CI_AS
CHAR_FIELD5_AR
nvarchar
no 100 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD1
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
CHAR_FIELD66_AR
nvarchar
no 2000 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD6
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD1
int
no 4 10 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD2
int
no 4 10 0 yes
(n/a)
(n/a) NULL
NUM_FIELD2
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD3
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD4
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD5
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
DATE_FIELD1
datetime
no 8 yes
(n/a)
(n/a) NULL
DATE_FIELD2
datetime
no 8 yes
(n/a)
(n/a) NULL
CHAR_FIELD1
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD3
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD4
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD5
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD1_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD2_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD4_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
Identity
Seed Increment
Not For Replication
------
-- ---
--- --
No identity column defined.
NULL NULL
NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
SECONDARY2
|||Last post of the script result.
index_name
index_description
index_keys
------
------
----- --
------
IX_Index1
nonclustered located on SECONDARY2
DOCID, STATUS_ID, CABID, YEAR,
CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
IX_Index2
nonclustered located on SECONDARY4
INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
YEAR, DOCDATE, OUTBOUND_REQUEST
IX_Index3
nonclustered located on SECONDARY2
GROUPID
IX_Index4
nonclustered located on SECONDARY4
YEAR, EXT_DOCNO, RDOCDATE_HJ,
EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
IX_Index5
nonclustered located on SECONDARY2
REQUEST_NO
IX_Index6
nonclustered located on SECONDARY4
STATUS_ID, CABID
PK_MAIN
clustered, unique, primary key located on SECONDARY2
DOCNO
constraint_type
constraint_name
delete_action update_action status_enabled
status_for_replication constraint_keys
------
-- -- -- --
-- --
------
-----
DEFAULT on column OUTBOUND_REQUEST
DF_MAIN_OUTBOUND_REQUEST
(n/a) (n/a) (n/a) (n/a)
(0)
FOREIGN KEY
FK_MAIN_EMPLOYEES
No Action No Action Enabled
Not_For_Replication USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES1
No Action No Action Enabled
Not_For_Replication WRITER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES2
No Action No Action Enabled
Not_For_Replication CREATOR_USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_LO_CONFIDENTIAL
No Action No Action Enabled
Not_For_Replication CONFID_ID
REFERENCES dbo.LO_CONFIDENTIAL (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_DOC_SOURCE_TYPE
No Action No Action Enabled
Not_For_Replication DOC_ORIGN_SOURCE
REFERENCES dbo.LO_DOC_SOURCE_TYPE
(TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS
No Action No Action Enabled
Not_For_Replication DELIVERY_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS1
No Action No Action Enabled
Not_For_Replication LINK_STATUS
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS4
No Action No Action Enabled
Not_For_Replication RECEIVE_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_PRIORITY
No Action No Action Enabled
Not_For_Replication PRIORITY_NO
REFERENCES dbo.LO_PRIORITY (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION
No Action No Action Enabled
Not_For_Replication EXT_PARTY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION1
No Action No Action Enabled
Not_For_Replication EXT_PARTY_REP_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION2
No Action No Action Enabled
Not_For_Replication CITY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SIGNATURE_TYPE
No Action No Action Enabled
Not_For_Replication DIWAN_REP_ID
REFERENCES dbo.LO_SIGNATURE_TYPE (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SITE
No Action No Action Enabled
Not_For_Replication DELIVERY_PLACE
REFERENCES dbo.LO_SITE (SITE_ID)
FOREIGN KEY
FK_MAIN_LO_STATUS
No Action No Action Enabled
Not_For_Replication STATUS_ID
REFERENCES dbo.LO_STATUS (TAB_ID)
FOREIGN KEY
FK_MAIN_MAIN_GROUP_HDR
No Action No Action Enabled
Not_For_Replication GROUPID
REFERENCES dbo.MAIN_GROUP_HDR (
GROUPID)
FOREIGN KEY
FK_MAIN_PROCESSES
No Action No Action Enabled
Not_For_Replication INITIAL_PROCEDURE
REFERENCES dbo.PROCESSES (
PROCESS_ID)
PRIMARY KEY (clustered)
PK_MAIN
(n/a) (n/a) (n/a) (n/a)
DOCNO
Table is referenced by foreign key
------
------
dbo.TASKS: FK_TASKS_MAIN
dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
dbo.COPY: FK_COPY_MAIN
dbo.INQUIRY: FK_INQUIRY_MAIN
dbo.LINKS_F: FK_LINKS_F_MAIN
dbo.CLASS: FK_CLASS_MAIN
dbo.XLASS: FK_CLASS_MAIN
dbo.REQUEST: FK_REQUEST_MAIN
dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
dbo.PERSONS: FK_PERSONS_MAIN
dbo.RESERVED: FK_RESERVED_MAIN
dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
Table is referenced by views
------
3
(1 row(s) affected)
|||Ali,
First of all, thank you for providing this info as it is most helpful! You
are using SQL Server 2000 SP2 on Windows 2000 Server SP3 and your default
language is US_English (1033). However, all of your char, varchar and
nvarchar columns using the Arabic_CI_AS collation with the FT-enabled column
"Language for Word Breaker" set to Neutral. Can I assume that these column
contain Arabic text?
Yes, I can understand why you want the Resource_Usage level to remain at 3,
but you might try setting to 5, as you can always set it lower if it is
affecting your other SQL Server processing. Although, this bump in the
MSSearch resource usage might not be significant. Note, it affect how much
memory MSSearch can use up to a max if 512MB RAM, if available as well as
the number of concurrent connections allowed.
What is the drive letter for your SAN storage? Could you also run the
following SQL query and post it's results?
sp_helpdb <full_text_enabled_database_name>
I'm not sure if the use of Arabic text is causing the poor query performance
(even with the Neutral wordbreaker), but language is a consideration with
FTS query performance issues.
Thanks again,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:D20604B8-21E5-496F-A957-1F1CF8949716@.microsoft.com...
> Last post of the script result.
>
>
> index_name
>
> index_description
>
> index_keys
>
>
>
> ----
----
> --
> ----

> ----
-- --
> ----
----
> ----
--
> IX_Index1
>
> nonclustered located on SECONDARY2
>
> DOCID, STATUS_ID, CABID, YEAR,
> CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
> IX_Index2
>
> nonclustered located on SECONDARY4
>
> INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
> YEAR, DOCDATE, OUTBOUND_REQUEST
> IX_Index3
>
> nonclustered located on SECONDARY2
>
> GROUPID
> IX_Index4
>
> nonclustered located on SECONDARY4
>
> YEAR, EXT_DOCNO, RDOCDATE_HJ,
> EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
> IX_Index5
>
> nonclustered located on SECONDARY2
>
> REQUEST_NO
> IX_Index6
>
> nonclustered located on SECONDARY4
>
> STATUS_ID, CABID
> PK_MAIN
>
> clustered, unique, primary key located on SECONDARY2
>
> DOCNO
>
> constraint_type
>
> constraint_name
>
> delete_action update_action status_enabled
> status_for_replication constraint_keys
>
>
>
> ----
----
> --
> ----
--
> -- -- -- --
> -- --
> ----
----
> ----
--
> DEFAULT on column OUTBOUND_REQUEST
>
> DF_MAIN_OUTBOUND_REQUEST
>
> (n/a) (n/a) (n/a) (n/a)
> (0)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES
>
> No Action No Action Enabled
> Not_For_Replication USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES1
>
> No Action No Action Enabled
> Not_For_Replication WRITER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES2
>
> No Action No Action Enabled
> Not_For_Replication CREATOR_USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_LO_CONFIDENTIAL
>
> No Action No Action Enabled
> Not_For_Replication CONFID_ID
>
>
>
> REFERENCES dbo.LO_CONFIDENTIAL (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_DOC_SOURCE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DOC_ORIGN_SOURCE
>
>
>
> REFERENCES dbo.LO_DOC_SOURCE_TYPE
> (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS1
>
> No Action No Action Enabled
> Not_For_Replication LINK_STATUS
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS4
>
> No Action No Action Enabled
> Not_For_Replication RECEIVE_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_PRIORITY
>
> No Action No Action Enabled
> Not_For_Replication PRIORITY_NO
>
>
>
> REFERENCES dbo.LO_PRIORITY (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION1
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_REP_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION2
>
> No Action No Action Enabled
> Not_For_Replication CITY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SIGNATURE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DIWAN_REP_ID
>
>
>
> REFERENCES dbo.LO_SIGNATURE_TYPE (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SITE
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_PLACE
>
>
>
> REFERENCES dbo.LO_SITE (SITE_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_STATUS
>
> No Action No Action Enabled
> Not_For_Replication STATUS_ID
>
>
>
> REFERENCES dbo.LO_STATUS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_MAIN_GROUP_HDR
>
> No Action No Action Enabled
> Not_For_Replication GROUPID
>
>
>
> REFERENCES dbo.MAIN_GROUP_HDR (
> GROUPID)
> FOREIGN KEY
>
> FK_MAIN_PROCESSES
>
> No Action No Action Enabled
> Not_For_Replication INITIAL_PROCEDURE
>
>
>
> REFERENCES dbo.PROCESSES (
> PROCESS_ID)
> PRIMARY KEY (clustered)
>
> PK_MAIN
>
> (n/a) (n/a) (n/a) (n/a)
> DOCNO
>
> Table is referenced by foreign key
>
>
>
> ----
----
> ----
----
> --
> dbo.TASKS: FK_TASKS_MAIN
> dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
> dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
> dbo.COPY: FK_COPY_MAIN
> dbo.INQUIRY: FK_INQUIRY_MAIN
> dbo.LINKS_F: FK_LINKS_F_MAIN
> dbo.CLASS: FK_CLASS_MAIN
> dbo.XLASS: FK_CLASS_MAIN
> dbo.REQUEST: FK_REQUEST_MAIN
> dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
> dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
> dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
> dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
> dbo.PERSONS: FK_PERSONS_MAIN
> dbo.RESERVED: FK_RESERVED_MAIN
> dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
> Table is referenced by views
>
> ----
----
> --
>
> --
> 3
> (1 row(s) affected)
>
sql

Full Text Search Index build is running too long

I did a DTS insert from a .CSV file and loaded 10 million records into a
table. I am doing a full text index on 3 columns in the table. The full
text index has been populating now for 24 hours continuous. How can I tell
if it's hung, or is it possible that it really takes that long to run? It
took only about 30 minutes to load 10 million records into the database.Brian,
Take a look at Current Activity - Process Info in Enterprise Manager. Might
find some useful insight there.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13440424-19D0-4E7D-9E23-1BFEB9513303@.microsoft.com...
>I did a DTS insert from a .CSV file and loaded 10 million records into a
> table. I am doing a full text index on 3 columns in the table. The full
> text index has been populating now for 24 hours continuous. How can I
> tell
> if it's hung, or is it possible that it really takes that long to run? It
> took only about 30 minutes to load 10 million records into the database.

Full text search help

Hi I have a full text index on my product table.

When I do a search for Record, it returns all values for Record and Records.

Now If I do a search with a spelling mistake say Recod . it doen't return anything.

How can I get the full text to return my query even if there is a spelling mistake ?

Thanks

My query:

SELECT * From Product
WHERE FREETEXT (description, @.SearchString)

Try using Soundex|||

Hi dear,

You have to use LIKE operator

http://www.devguru.com/technologies/t-sql/7116.asp
http://doc.ddart.net/mssql/sql70/la-lz_2.htm

i hope these links will help you in this regard.

Thank You

Regards,

Muhammad Akhtar Shiekh

|||

Thank you both for your replied.

The thing is you cannot use LIKE or SOUNDEX in a FULL TEXT SEARCH.

|||

Hi Sleb ,

According to your question, If you want to get the full text to return your query even if there's a spelling mistake, you may achieve that through some special algorithm.

1. You may extend your keyword into several combinations.

eg:

bus -> bus array[0]
bsu array[1]
ubs array[2]
usb array[3]
sub array[4]
sbu array[5]

2. Make the select statement

string sqltxt="select * from Product where ";

for(int i=0;i<array.length;i++)
{
if( i==0 )
{
sqltxt+=" description like '% " + array[i] + " %'" ;
}
else
{
sqltxt+=" or description like '% " + array[i] + " %'" ;
}
}

In this way, you could make every combination from the original keyword match the content.

3. Make your intelligent spelling correcting more smart.

By now, you could get every combination from the original keyword, but you are not sure which one is the correct spelling. So we advice that you should build up a data table to record the user's searching behavior. eg:

keyword SubmitTimes
======================
bus 231254
bsu 12
sub 265421
sbu 125

If the user submit the keyword as "bsu", we are easy to know the possible keyword which the user wants is "bus" or "sub" but not "sbu" according to the submit times from other users.

If this does not help you, pls feel free to mark the post as Not Answered and reply. Thanks.

|||

Hi

From what I can see there is 2 methods. Method one is to use software that is available on the web to do spell checks, or you can write your own code to do a spell check. There are many text file dictionaries on the web so you do not have to be concerned about that.

I had a similar problem, but have come to an understanding that if a surfer cannot spell when surfing a website or the web then they should not be using the web. You cannot cater for every possible problem that a user can create.

I use the FREETEXT operator. What I have noticed is that it will find a plural of a word if the search is singular. For example search for "Card" will return card and cards.

I am sure by this example I think that MSSQL can be used to do a spell check. However I am not sure.

I hope this information will help you

Regard

Full Text Search

I have a strange issue with full text search. I have a table that has only 700 records in it, when I enable full text search and follow the wizard the index is not getting build. the storage full text say it is populating and it never does. I have restarted the service and rebuild the index few times with no benefits. I can do the same steps on another server with the same database and it works just fine. does anyone have any hints or things I can try? Please help

Check what are privileges associated with the login used to create this fulltext, also check whether MSsearch service is up and running.

Did you get any errors or warnings during thsi process?

|||

MSFTESQL is up and running. I don't get any errors when creating the Index. everything is successful status but when I am looking the property of the full text under storage the status is always "populating Catalog". The only error I can see is in the event viewer-

Performance Counters could not be loaded for msftesqlFD for instance MSSQLSERVER due to the following error: The system cannot find the file specified. 0x80070002.

not sure if this is related to full text search?

Please help.

Full Text Search

Hi,
I am wondering if *Full Text Search* is efficient or not (not too slow ?!)
with a table having more than 200.000 records ?
Thanks !Lionel,
Yes, it is efficient and not too slow with either FT Indexing or FT
Searching of a table with more than 200.000 rows.
In fact, Full-text Indexing (FTI) and Full-text Search (FTS) of a table with
200K rows is well within the "sweet spot" for not only SQL Server 7.0, but
SQL Server 2000 and SQL Server 2000 (Yukon) will rock for FT-enabled tables
of this *small* size!!
What is your primary concern? FTI or FTS performance with larger tables? If
you have a table with a more than 2 million rows, you may need to tune both
the hardware and software configurations, (see SQL Server 2000 BOL title
"Full-text Search Recommendations" for more details), but still you should
be able to FT Index million+ row tables.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Lionel" <nospam@.org.org> wrote in message
news:Of3BxwpDFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am wondering if *Full Text Search* is efficient or not (not too slow
?!)
> with a table having more than 200.000 records ?
> Thanks !
>|||I'm new to this - where can I find this doc Full-text Search recommendations
?
Looking for performance tuning tips. What is a BOL?
Thanks
"John Kane" wrote:

> Lionel,
> Yes, it is efficient and not too slow with either FT Indexing or FT
> Searching of a table with more than 200.000 rows.
> In fact, Full-text Indexing (FTI) and Full-text Search (FTS) of a table wi
th
> 200K rows is well within the "sweet spot" for not only SQL Server 7.0, but
> SQL Server 2000 and SQL Server 2000 (Yukon) will rock for FT-enabled table
s
> of this *small* size!!
> What is your primary concern? FTI or FTS performance with larger tables? I
f
> you have a table with a more than 2 million rows, you may need to tune bot
h
> the hardware and software configurations, (see SQL Server 2000 BOL title
> "Full-text Search Recommendations" for more details), but still you should
> be able to FT Index million+ row tables.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Lionel" <nospam@.org.org> wrote in message
> news:Of3BxwpDFHA.1496@.TK2MSFTNGP14.phx.gbl...
> ?!)
>
>|||You're welcome, Chris,
The BOL is short for "Books Online" and is the online (vs. printed)
documentation that ships with SQL Server 2000. You can also find an Internet
version of "Full-text Search Recommendations" at:
http://msdn.microsoft.com/library/d...m
er.asp
See also my blog entry: "SQL Server 2000 Full-Text Search Resources and
Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.e
ntry
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:93C06810-E298-4148-963F-79584FAAA640@.microsoft.com...
> I'm new to this - where can I find this doc Full-text Search
recommendations?
> Looking for performance tuning tips. What is a BOL?
> Thanks
> "John Kane" wrote:
>
with
but
tables
If
both
should
slow

Full Text Indexing

Hi,
I have a large table with millions and millions of records all related to
web traffic. I am trying to match certain records based on a query string
variable; say varname.
If I use the like operator it is SELECT ... FROM PageView WHERE QueryString
LIKE '%varname=%'
I was hoping to get better performace out of this using full text indexing
but I am not sure it will be able to accomodate my needs. The string string
I am searching in can have other things before and after and rarely any
spaces in it: Like 'abc=123&varname=xxx&ggg=000' and many other variations.
Will full text indexing help in such cases?
Do I need to define certain characters as word seperators for this type of
index (in a query string it will be the '&' sign).
Thanks
Hello,
I understand that you'd like to use full-text search to improve perofrmance
of query with clause such as "LIKE '%varname=%'". If I'm off-base, please
let me know.
Fulltext index is based on key words of the document stored in table. The
word breaker split work of the documents by white space and word separators
for this locale. If the key word you search could be properly splited by
word breaker, you could use full-text search. For exmaple:
select * from tbl where varname= 'data'
"varname=" might be splited correctly by word breaker.
Howerver, the following string might not be splited properly:
select * from tbl where varname='data'
Also, you may use wildcard to search the word with prefix you want. For
example:
Select * from table contains (columnname, '"varname*"')
This query can return varname1, varname=...
However, you could use wildcard * before "varname" and it does not take
efffect. This is a limitation of SQL full-text search.
You could refer to the following articles for more related information:
Full-Text Search Fundamentals
http://msdn2.microsoft.com/en-us/library/ms142581.aspx
200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
Queries
http://support.microsoft.com/?id=200043
Word Breaker and Stemmer Sample
http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_3e91.asp?f
rame=true
Implementing a Word Breaker
http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_54bp.asp?f
rame=true
271818 How to configure Windows 2000 Indexing Service to use the Neutral
word
http://support.microsoft.com/?id=271818
If you have further questions or concerns, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks for the great response.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:8z82E3skHHA.5432@.TK2MSFTNGHUB02.phx.gbl...
> Hello,
> I understand that you'd like to use full-text search to improve
> perofrmance
> of query with clause such as "LIKE '%varname=%'". If I'm off-base, please
> let me know.
> Fulltext index is based on key words of the document stored in table. The
> word breaker split work of the documents by white space and word
> separators
> for this locale. If the key word you search could be properly splited by
> word breaker, you could use full-text search. For exmaple:
>
> select * from tbl where varname= 'data'
> "varname=" might be splited correctly by word breaker.
> Howerver, the following string might not be splited properly:
> select * from tbl where varname='data'
> Also, you may use wildcard to search the word with prefix you want. For
> example:
> Select * from table contains (columnname, '"varname*"')
> This query can return varname1, varname=...
> However, you could use wildcard * before "varname" and it does not take
> efffect. This is a limitation of SQL full-text search.
> You could refer to the following articles for more related information:
> Full-Text Search Fundamentals
> http://msdn2.microsoft.com/en-us/library/ms142581.aspx
> 200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
> Queries
> http://support.microsoft.com/?id=200043
> Word Breaker and Stemmer Sample
> http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_3e91.asp?f
> rame=true
> Implementing a Word Breaker
> http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_54bp.asp?f
> rame=true
> 271818 How to configure Windows 2000 Indexing Service to use the Neutral
> word
> http://support.microsoft.com/?id=271818
> If you have further questions or concerns, please feel free to let's know.
> Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Full Text Indexing

Hi,
I have a large table with millions and millions of records all related to
web traffic. I am trying to match certain records based on a query string
variable; say varname.
If I use the like operator it is SELECT ... FROM PageView WHERE QueryString
LIKE '%varname=%'
I was hoping to get better performace out of this using full text indexing
but I am not sure it will be able to accomodate my needs. The string string
I am searching in can have other things before and after and rarely any
spaces in it: Like 'abc=123&varname=xxx&ggg=000' and many other variations.
Will full text indexing help in such cases?
Do I need to define certain characters as word seperators for this type of
index (in a query string it will be the '&' sign).
ThanksHello,
I understand that you'd like to use full-text search to improve perofrmance
of query with clause such as "LIKE '%varname=%'". If I'm off-base, please
let me know.
Fulltext index is based on key words of the document stored in table. The
word breaker split work of the documents by white space and word separators
for this locale. If the key word you search could be properly splited by
word breaker, you could use full-text search. For exmaple:
select * from tbl where varname= 'data'
"varname=" might be splited correctly by word breaker.
Howerver, the following string might not be splited properly:
select * from tbl where varname='data'
Also, you may use wildcard to search the word with prefix you want. For
example:
Select * from table contains (columnname, '"varname*"')
This query can return varname1, varname=...
However, you could use wildcard * before "varname" and it does not take
efffect. This is a limitation of SQL full-text search.
You could refer to the following articles for more related information:
Full-Text Search Fundamentals
http://msdn2.microsoft.com/en-us/library/ms142581.aspx
200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
Queries
http://support.microsoft.com/?id=200043
Word Breaker and Stemmer Sample
http://msdn.microsoft.com/library/e...ario_3e91.asp?f
rame=true
Implementing a Word Breaker
http://msdn.microsoft.com/library/e...ario_54bp.asp?f
rame=true
271818 How to configure Windows 2000 Indexing Service to use the Neutral
word
http://support.microsoft.com/?id=271818
If you have further questions or concerns, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the great response.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:8z82E3skHHA.5432@.TK2MSFTNGHUB02.phx.gbl...
> Hello,
> I understand that you'd like to use full-text search to improve
> perofrmance
> of query with clause such as "LIKE '%varname=%'". If I'm off-base, please
> let me know.
> Fulltext index is based on key words of the document stored in table. The
> word breaker split work of the documents by white space and word
> separators
> for this locale. If the key word you search could be properly splited by
> word breaker, you could use full-text search. For exmaple:
>
> select * from tbl where varname= 'data'
> "varname=" might be splited correctly by word breaker.
> Howerver, the following string might not be splited properly:
> select * from tbl where varname='data'
> Also, you may use wildcard to search the word with prefix you want. For
> example:
> Select * from table contains (columnname, '"varname*"')
> This query can return varname1, varname=...
> However, you could use wildcard * before "varname" and it does not take
> efffect. This is a limitation of SQL full-text search.
> You could refer to the following articles for more related information:
> Full-Text Search Fundamentals
> http://msdn2.microsoft.com/en-us/library/ms142581.aspx
> 200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
> Queries
> http://support.microsoft.com/?id=200043
> Word Breaker and Stemmer Sample
> Windows 2000 Indexing Service to use the Neutral
> word
> [url]http://support.microsoft.com/?id=271818" target="_blank">http://msdn.microsoft.com/library/e...com/?id=271818
> If you have further questions or concerns, please feel free to let's know.
> Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Full Text Indexing

Hi,
I have a large table with millions and millions of records all related to
web traffic. I am trying to match certain records based on a query string
variable; say varname.
If I use the like operator it is SELECT ... FROM PageView WHERE QueryString
LIKE '%varname=%'
I was hoping to get better performace out of this using full text indexing
but I am not sure it will be able to accomodate my needs. The string string
I am searching in can have other things before and after and rarely any
spaces in it: Like 'abc=123&varname=xxx&ggg=000' and many other variations.
Will full text indexing help in such cases?
Do I need to define certain characters as word seperators for this type of
index (in a query string it will be the '&' sign).
ThanksHello,
I understand that you'd like to use full-text search to improve perofrmance
of query with clause such as "LIKE '%varname=%'". If I'm off-base, please
let me know.
Fulltext index is based on key words of the document stored in table. The
word breaker split work of the documents by white space and word separators
for this locale. If the key word you search could be properly splited by
word breaker, you could use full-text search. For exmaple:
select * from tbl where varname= 'data'
"varname=" might be splited correctly by word breaker.
Howerver, the following string might not be splited properly:
select * from tbl where varname='data'
Also, you may use wildcard to search the word with prefix you want. For
example:
Select * from table contains (columnname, '"varname*"')
This query can return varname1, varname=...
However, you could use wildcard * before "varname" and it does not take
efffect. This is a limitation of SQL full-text search.
You could refer to the following articles for more related information:
Full-Text Search Fundamentals
http://msdn2.microsoft.com/en-us/library/ms142581.aspx
200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
Queries
http://support.microsoft.com/?id=200043
Word Breaker and Stemmer Sample
http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_3e91.asp?f
rame=true
Implementing a Word Breaker
http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_54bp.asp?f
rame=true
271818 How to configure Windows 2000 Indexing Service to use the Neutral
word
http://support.microsoft.com/?id=271818
If you have further questions or concerns, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the great response.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:8z82E3skHHA.5432@.TK2MSFTNGHUB02.phx.gbl...
> Hello,
> I understand that you'd like to use full-text search to improve
> perofrmance
> of query with clause such as "LIKE '%varname=%'". If I'm off-base, please
> let me know.
> Fulltext index is based on key words of the document stored in table. The
> word breaker split work of the documents by white space and word
> separators
> for this locale. If the key word you search could be properly splited by
> word breaker, you could use full-text search. For exmaple:
>
> select * from tbl where varname= 'data'
> "varname=" might be splited correctly by word breaker.
> Howerver, the following string might not be splited properly:
> select * from tbl where varname='data'
> Also, you may use wildcard to search the word with prefix you want. For
> example:
> Select * from table contains (columnname, '"varname*"')
> This query can return varname1, varname=...
> However, you could use wildcard * before "varname" and it does not take
> efffect. This is a limitation of SQL full-text search.
> You could refer to the following articles for more related information:
> Full-Text Search Fundamentals
> http://msdn2.microsoft.com/en-us/library/ms142581.aspx
> 200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
> Queries
> http://support.microsoft.com/?id=200043
> Word Breaker and Stemmer Sample
> http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_3e91.asp?f
> rame=true
> Implementing a Word Breaker
> http://msdn.microsoft.com/library/en-us/indexsrv/html/wbrscenario_54bp.asp?f
> rame=true
> 271818 How to configure Windows 2000 Indexing Service to use the Neutral
> word
> http://support.microsoft.com/?id=271818
> If you have further questions or concerns, please feel free to let's know.
> Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Full Text Index not populating

I have a table with 13,000,000 records. I want to generate a full-text index on one column (a varchar 2000). I am able to define the full-text index, but when I click on "Start Full population", there is virtually no activity (no disk activity, no CPU activity, very little to indicate anything is happening.

When I check the properties of the catalog, it shows 1 MB size and 0 records in the catalog. The status of the catalog is "idle" and the display in EM shows that the last full population occurred at (about) the time that I generated the population request. I have generated the request by using EM (right click on table) and through SQL Agent with the same result (no catalog generated).

I am running SQL 2000 (SP4) on Windows 2000 (SP4) with 4 GB RAM and sufficient disk space available. I have enabled the full-text service and verified that it is running (I have stopped and restarted it as well).

I have worked with Full Text indexes before and never had any kind of issue before. Any thoughts or suggestions would be welcome.

Regards,

hmscott

CREATE TABLE [OMBRE_AUDIT_LOG] (
[LOG_SEQ_NBR] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[APP_NAME] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USER_ID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USER_ORGANIZATION] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTION_START_DATE] [datetime] NOT NULL ,
[ACTION_END_DATE] [datetime] NULL ,
[ACTION_CODE] [int] NOT NULL ,
[VIEW_NAME] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER_DEF_TRACKING_NBR] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMD_XML_STREAM] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REC_CREATE] [datetime] NULL CONSTRAINT [DF_OMBRE_AUDIT_LOG_REC_CREATE] DEFAULT (getdate()),
[REC_UPDATE] [datetime] NULL ,
[ATTENTION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REASON] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_OMBRE_AUDIT_LOG] PRIMARY KEY CLUSTERED
(
[LOG_SEQ_NBR]
)
)
GOPoor scott :D ,

Have u checked any errors in Microsoft Windows 2000 Event Viewer application log which related to Microsft Search?

Another hint is ,

Make sure that the BUILTIN\Administrators login exists in SQL Server.|||Poor scott :D ,

I don't want sympathy, I want answers!!! :D

Have u checked any errors in Microsoft Windows 2000 Event Viewer application log which related to Microsft Search?

Ding! Score 1 point for mallier!


Make sure that the BUILTIN\Administrators login exists in SQL Server.
Ding! Ding! We have a winner!

WTF? Best practice says remove the BUILTIN\Administrator account and yet it's required for this!?! I gotta go read up more on Full text.

Thanks for the help!

Regards,

hmscott|||Hi,
I also got the same problem as you Scott.
I found I have the BUILTIN\Administrator in SQL Server.

So what will be the reason?|||To access existing full-text search catalogs, rebuild and repopulate them. The existing catalogs can also be accessed by switching back to an administrator account.|||Hi Satya,

I have the exact problem as Scott, could you give me some idea how to solve the problem please?

FTS Q - Proximate meaning of phrases

Hi
Is it possible to find records that contain the string "cyber-shot" when the value for search is "cybershot"?? (This is an example and I need a dynamic solution)
Thanks,
Inon.Probably (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_3rqg.asp).

-PatP|||Depends upon exactly what you want. Are you doing a fuzzy search, or do you want to find all the values that contain the same characters? Do the characters have to be in the same order? Do you just want to ignore non-alphanumeric characters?
You will have to give more details on the problem if you want more details on the answer.