I have moved my SQL 7.0 to a brand new machine with SQL
2000 using the copy database Wizard. however it seems to
be too slow now. Does anyone know what I can do to speed
this up? Should I do anything particulary after upgrading.
Thanks for any help -RobOne thing is sp_updatestats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rob" <rob@.nul.ca> wrote in message
news:f1ba01c40c37$363f7700$a101280a@.phx.gbl...
> I have moved my SQL 7.0 to a brand new machine with SQL
> 2000 using the copy database Wizard. however it seems to
> be too slow now. Does anyone know what I can do to speed
> this up? Should I do anything particulary after upgrading.
> Thanks for any help -Rob|||Hi Rob,
I agree with Tibor. Also, I'm including some supplemental information below:
319942 HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
298475 HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/?id=224587
Please try the suggestions in the above articles, which will help you
troubleshoot the performance issue of SQL Server.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Showing posts with label upgrading. Show all posts
Showing posts with label upgrading. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Post SP3 Full text performance degradation
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
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:
Posts (Atom)