unknown unique key count variance

I am pretty new to FTS but found I am stuck with a problem that
doesn't seem common. In all my searching, I have only come across one
post that had the same issue and that was from 2002.

http://groups.google.com.au/group/microsoft.public.sqlserver.fulltext/...wse_thr

The only suggestion from that post (answered by Hilary Cotter) was the
problem could be related to using change tracking with background
indexing or very high cpu activity on my server. Neither are the
case.

To relate my problem:

I am trying to use FTS to perform content searching on only the pdf
files stored in my database.

While trying to discover why I was getting inaccuracies, I found that
if I rebuild the catalog then do a full population, the unique key
count is different each time.

I tested it 12 times in a row, & the unique key counts results:

Min 10,530
Max 42,731
Average 24,888
Median 21,455

I am running SQL Server 2000 and Adobe iFilter 6 on XP SP2. This is a
development machine, so there is no external interference to alter my
testing. I was also able to duplicate the issue on a Windows 2000
server running SQL Server 2000 and iFilter 6.

I am indexing only one table with a single catalog. I used the wizard
to create this & checked on Hilary's site (http://
www.indexserverfaq.com/SQLFTIWizard.htm) to ensure I was doing it
correctly.

The table holding the files contains a total of 1261 files of which
747 are pdfs. The rest are a variety
of .wpd, .doc, .zip, .tif, .exe, .jpeg, & .dwg files.

The table has the following layout.

3 gVfsId uniqueidentifier 16 0
0 sExt varchar 10 1
0 imgTarget image 16 1
0 sCrc varchar 100 1
0 iFilesize int 4 1
0 bEncrypted bit 1 1
0 bRebuild bit 1 1
0 dteCatIncrement timestamp 8 1


If someone could at least point me in a direction to rectify this
issue, I will be eternally grateful.


Cheers,
Rick.

There was a problem in one of the earlier RTM and service packs of SQL
Server 2000 which had a behavior like this. It was solved in one of
the later SP's - SP 4 IIRC.

Are you doing a full population each time?


On Feb 7, 2:20 am, PhantomRick wrote:
> I am pretty new to FTS but found I am stuck with a problem that
> doesn't seem common.  In all my searching, I have only come across one
> post that had the same issue and that was from 2002.
>
> http://groups.google.com.au/group/microsoft.public.sqlserver.fulltext...
>
> The only suggestion from that post (answered by Hilary Cotter) was the
> problem could be related to using change tracking with background
> indexing or very high cpu activity on my server.  Neither are the
> case.
>
> To relate my problem:
>
> I am trying to use FTS to perform content searching on only the pdf
> files stored in my database.
>
> While trying to discover why I was getting inaccuracies, I found that
> if I rebuild the catalog then do a full population, the unique key
> count is different each time.
>
> I tested it 12 times in a row, & the unique key counts results:
>
> Min     10,530
> Max     42,731
> Average 24,888
> Median  21,455
>
> I am running SQL Server 2000 and Adobe iFilter 6 on XP SP2.  This is a
> development machine, so there is no external interference to alter my
> testing.  I was also able to duplicate the issue on a Windows 2000
> server running SQL Server 2000 and iFilter 6.
>
> I am indexing only one table with a single catalog.  I used the wizard
> to create this & checked on Hilary's site (http://www.indexserverfaq.com/SQLFTIWizard.htm) to ensure I was doing it
> correctly.
>
> The table holding the files contains a total of 1261 files of which
> 747 are pdfs.  The rest are a variety
> of .wpd, .doc, .zip, .tif, .exe, .jpeg, & .dwg files.
>
> The table has the following layout.
>
> 3       gVfsId  uniqueidentifier        16      0
> 0       sExt    varchar 10      1
> 0       imgTarget       image   16      1
> 0       sCrc    varchar 100     1
> 0       iFilesize       int     4       1
> 0       bEncrypted      bit     1       1
> 0       bRebuild        bit     1       1
> 0       dteCatIncrement timestamp       8       1
>
> If someone could at least point me in a direction to rectify this
> issue, I will be eternally grateful.
>
> Cheers,
> Rick.