Since upgrading to service pack 3a (from sql server with
no sp) I am experiencing performance issues with my full
text searches. Its hard to quantify the problem - but
since the upgrade I am definitely seeing significatnt
increase in duration for searches - especially when they
run concurrently.
As part of the upgrade the catalogs were rebuilt.
Any ideas would be appreciated
This is abnormal. There were some problems with the search algorithim prior
to SP 3 relating to ranking. If you are doing ordering you may experieince
some difference in performance.
I noticed a performance increase. How many simultaneous queries are you
running? You can get this metric by looking at the performance monitor
counter Microsoft Search:Queries
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:3ff201c4ab8e$5a1c3400$a301280a@.phx.gbl...
> Since upgrading to service pack 3a (from sql server with
> no sp) I am experiencing performance issues with my full
> text searches. Its hard to quantify the problem - but
> since the upgrade I am definitely seeing significatnt
> increase in duration for searches - especially when they
> run concurrently.
> As part of the upgrade the catalogs were rebuilt.
> Any ideas would be appreciated
|||Hilary,
Right now it is running at an average of about 300000, but
we are experiencing light load, and we are having no
problem. I'll keep the metric running and monitor what
happens when we do have problems.
The stored procedure that runs the various
containscontable commands uses dynamic sql, and also joins
on tables in other databases on the same server - do you
know if either of these aspects could have been affected
by SP3. I have made no code changes that would account
for the problems I am seeing.
THanks, Rob
>--Original Message--
>This is abnormal. There were some problems with the
search algorithim prior
>to SP 3 relating to ranking. If you are doing ordering
you may experieince
>some difference in performance.
>I noticed a performance increase. How many simultaneous
queries are you
>running? You can get this metric by looking at the
performance monitor
>counter Microsoft Search:Queries
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3ff201c4ab8e$5a1c3400$a301280a@.phx.gbl...
>
>.
>
|||Hilary,
I just saw the cpu flat-lining again, but the metric did
not really change.
Rob
>--Original Message--
>This is abnormal. There were some problems with the
search algorithim prior
>to SP 3 relating to ranking. If you are doing ordering
you may experieince
>some difference in performance.
>I noticed a performance increase. How many simultaneous
queries are you
>running? You can get this metric by looking at the
performance monitor
>counter Microsoft Search:Queries
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3ff201c4ab8e$5a1c3400$a301280a@.phx.gbl...
>
>.
>
|||are there any error messages in the event log from MSSearch or MSSCi?
When you say you saw the CPU flat lining I take it, that CPU is going to
100%, or it is remaining constant?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:333301c4aba7$988ef3d0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> I just saw the cpu flat-lining again, but the metric did
> not really change.
> Rob
> search algorithim prior
> you may experieince
> queries are you
> performance monitor
> message
|||There are no errors or warnings - and the searches do
return after several seconds, but they run the cpu at 100%
until they return, and are much slower than before SP3 was
installed.
Do you think the problem could be related to cross-
database queries or dynamic sql?
Rob
>--Original Message--
>are there any error messages in the event log from
MSSearch or MSSCi?
>When you say you saw the CPU flat lining I take it, that
CPU is going to[vbcol=seagreen]
>100%, or it is remaining constant?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:333301c4aba7$988ef3d0$a501280a@.phx.gbl...
with[vbcol=seagreen]
full[vbcol=seagreen]
but[vbcol=seagreen]
they
>
>.
>
|||dynamic sql is often inefficient, but not always as in some cases the
execution plan will be cached and reused.
However, in the type of queries you would use with SQL FTS it is not cached.
It could be related to cross database queries, especially if you are not
fully qualifying your database and owner names.
Can you post your query here? Also how many queries are occuring per second
or minute? And how many rows are in your table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:36bb01c4abca$99400670$a501280a@.phx.gbl...[vbcol=seagreen]
> There are no errors or warnings - and the searches do
> return after several seconds, but they run the cpu at 100%
> until they return, and are much slower than before SP3 was
> installed.
> Do you think the problem could be related to cross-
> database queries or dynamic sql?
> Rob
> MSSearch or MSSCi?
> CPU is going to
> with
> full
> but
> they
|||Rob,
There were several MAJOR fixes related to FTS in SQL Server 2000 SP3 that
caused existing FT Catalogs to be automatically rebuilt (if you selected
them during the SP3 installation) or your could of deferred the rebuild if
the FT Catalogs were large and use the procedures in KB article Q240867 at a
later point in time. Do you know which option you selected during the SP3
upgrade?
Below are two KB articles that document these fixes related to FTS in SP3:
327217 (Q327217) INF: Installing SQL Server 2000 Service Pack 3 Rebuilds All
Full-Text Catalogs
http://support.microsoft.com/default...b;en-us;327217
312410 (Q312410) "FIX: Full Text RANK Results May Vary After Catalog
Repopulation"
http://support.microsoft.com/default...b;en-us;312410 (references
Q240867)
Regards,
John
<anonymous@.discussions.microsoft.com> wrote in message
news:36bb01c4abca$99400670$a501280a@.phx.gbl...[vbcol=seagreen]
> There are no errors or warnings - and the searches do
> return after several seconds, but they run the cpu at 100%
> until they return, and are much slower than before SP3 was
> installed.
> Do you think the problem could be related to cross-
> database queries or dynamic sql?
> Rob
> MSSearch or MSSCi?
> CPU is going to
> with
> full
> but
> they
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment