poor performance of publication

sql2005 standard edition
In replication monitor, under Publication tab, the Current Average
Performance and Current Worst Performance show "Poor" for one publication,
and this has been like this for a few days.
The publisher server is not under heavy load at all. In Perfmon I checked
Delivery Latency and Delivered Trans.sec for the related replication jobs and
all show 0.
Could anybody tell me what else I need to look into and where to start with
troubleshooting? I am new to replication. Thanks a lot.
Claudia
Look on the subscriber to see if there are user triggers on this or
different indexes. Also is the subscriber the same hardware and
configuration as the other subscribers? Are there any other processes
running on this machine?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:1292940D-2268-4C27-ADED-5206FC3CF225@.microsoft.com...
> sql2005 standard edition
> In replication monitor, under Publication tab, the Current Average
> Performance and Current Worst Performance show "Poor" for one publication,
> and this has been like this for a few days.
> The publisher server is not under heavy load at all. In Perfmon I checked
> Delivery Latency and Delivered Trans.sec for the related replication jobs
> and
> all show 0.
> Could anybody tell me what else I need to look into and where to start
> with
> troubleshooting? I am new to replication. Thanks a lot.
> Claudia
>
|||The subscriber hardware is the same. No triggers on subscriber. The tables
involved in replication are all small tables (18 tables, the biggest one has
600 records, and the rest of tables have < 100 records). There're a few
connections to the subscriber DB but they are in sleeping mode. The only
connections with Open Transaction showing 1 are from the replication, which
seems normal.
The articles
"Hilary Cotter" wrote:

> Look on the subscriber to see if there are user triggers on this or
> different indexes. Also is the subscriber the same hardware and
> configuration as the other subscribers? Are there any other processes
> running on this machine?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Claudia" <Claudia@.discussions.microsoft.com> wrote in message
> news:1292940D-2268-4C27-ADED-5206FC3CF225@.microsoft.com...
>
>
|||Have you updated the indexes?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:7D92F872-3FFE-4AF6-B367-D58CD60C85E2@.microsoft.com...[vbcol=seagreen]
> The subscriber hardware is the same. No triggers on subscriber. The tables
> involved in replication are all small tables (18 tables, the biggest one
> has
> 600 records, and the rest of tables have < 100 records). There're a few
> connections to the subscriber DB but they are in sleeping mode. The only
> connections with Open Transaction showing 1 are from the replication,
> which
> seems normal.
>
> The articles
> "Hilary Cotter" wrote:

Poor performance

Hello Gurus,

SQL Server 2000
Windows 2003 Server, Standard Edition.

Firstly I'm not a SQL server DBA but have a little experience with
Oracle 9i and Oracle Rdb.

An application that I've inherited has started performing very very
slowly over the last few days, as far as I know there have been no
modifications or changes in the volume of data the db is holding. The
database is in simple logging mode and it's updated twice per day from
and Oracle Rdb database.

The SQL database is all in the primary filegroup and is sat on four
disks (RAID mirrored) which form one logical disk of 128gb. It
consists of approx 120 tables, each with a primary key and a number of
unique indicies per table.

Apart from a twice a day update in the small hours the only access to
this database is read only. Users are connecting via the web and
using predefined Crystal reports to retrieve data.

Questions then:

Should this db have more than one filegroup and should I put the
indicies in a different group? Is this relevant when the underlying
storage is RAID?

Should it be using mirrored RAID or should it be striped or should I
steer clear of RAID for a database?

The indicies have not been rebuilt for some time (probably one year).
Is this something I should be doing once per day/week as I do have the
downtime window?

Should I be doing a database/table reorg at fairly regular intervals?

I'm going to generate a workload file from SQL profiler and see what,
in conjunction with the Index tuning wizard it suggests.

Appreciate that poor performance & tuning is a huge subject and not an
exact science but any tips or comments would be gratefully received.

Regards
Dave.David Gray (police@.spamcop.net) writes:
> An application that I've inherited has started performing very very
> slowly over the last few days, as far as I know there have been no
> modifications or changes in the volume of data the db is holding. The
> database is in simple logging mode and it's updated twice per day from
> and Oracle Rdb database.

But as data is added to he database, statistics changes, and the
optimizer may come to different conclusions about query plans. Sometimes
to the worst.

> Should this db have more than one filegroup and should I put the
> indicies in a different group? Is this relevant when the underlying
> storage is RAID?
> Should it be using mirrored RAID or should it be striped or should I
> steer clear of RAID for a database?

Since it appears that the database performance has degraded so all at
once, I doubt that hardware configuration is the right medicine.

> The indicies have not been rebuilt for some time (probably one year).
> Is this something I should be doing once per day/week as I do have the
> downtime window?

Absolutely!

> Should I be doing a database/table reorg at fairly regular intervals?

That is actually the same thing as rebuilding indexes, at least as long
as you rebuild the clustered indexes.

Note that if you have tables without clustered indexes, they cannot
be rebuild by other means than creating a clustered index and then
dropping it. (Thus it's a good idea to have a clustered index on every
table.)

> I'm going to generate a workload file from SQL profiler and see what,
> in conjunction with the Index tuning wizard it suggests.

That may be a good idea anyway, because about every database has its
own set of poorly indexed queries. But I would expect a DBCC DBREINDEX
on all tables to have considerable effect.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Your indexes probably got a lot of fragmentations due to the twice daily
updates over time. You can use DBCC SHOWCONTIG to find out the degree of
fragmentation. I'd strongly suggest you to rebuild all the indexes if they
haven't been rebuilt for one year. Afterwards you should consider to
schedule periodical index reorg/defrag tasks to keep the fragmentation at a
low level.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gray" <police@.spamcop.net> wrote in message
news:1r6j319sk4nnfahn3dsbgs7la3d9q5hkcr@.4ax.com...
> Hello Gurus,
> SQL Server 2000
> Windows 2003 Server, Standard Edition.
> Firstly I'm not a SQL server DBA but have a little experience with
> Oracle 9i and Oracle Rdb.
> An application that I've inherited has started performing very very
> slowly over the last few days, as far as I know there have been no
> modifications or changes in the volume of data the db is holding. The
> database is in simple logging mode and it's updated twice per day from
> and Oracle Rdb database.
> The SQL database is all in the primary filegroup and is sat on four
> disks (RAID mirrored) which form one logical disk of 128gb. It
> consists of approx 120 tables, each with a primary key and a number of
> unique indicies per table.
> Apart from a twice a day update in the small hours the only access to
> this database is read only. Users are connecting via the web and
> using predefined Crystal reports to retrieve data.
> Questions then:
> Should this db have more than one filegroup and should I put the
> indicies in a different group? Is this relevant when the underlying
> storage is RAID?
> Should it be using mirrored RAID or should it be striped or should I
> steer clear of RAID for a database?
> The indicies have not been rebuilt for some time (probably one year).
> Is this something I should be doing once per day/week as I do have the
> downtime window?
> Should I be doing a database/table reorg at fairly regular intervals?
> I'm going to generate a workload file from SQL profiler and see what,
> in conjunction with the Index tuning wizard it suggests.
> Appreciate that poor performance & tuning is a huge subject and not an
> exact science but any tips or comments would be gratefully received.
> Regards
> Dave.
>

Point in Time RESTORE

I have SQL Server Standard edition (that was installed
after un-installing the Enterprise evaluation copy using
add/remove programs). On Retore screen, the option "Point
in Time' is grayed out totally. How can I make this option
usable?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0375_01C3B80E.61607470
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Your database must be using the Full recovery model - and you must backup
the database as well as the log. Use the STOPAT option when restoring logs.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Neeru" <anonymous@.discussions.microsoft.com> wrote in message
news:019f01c3b837$a98a48b0$a301280a@.phx.gbl...
I have SQL Server Standard edition (that was installed
after un-installing the Enterprise evaluation copy using
add/remove programs). On Retore screen, the option "Point
in Time' is grayed out totally. How can I make this option
usable?
Thanks.
--=_NextPart_000_0375_01C3B80E.61607470
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Your database must be using the Full =recovery model - and you must backup the database as well as the log. Use =the STOPAT option when restoring logs.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Neeru" wrote in message news:019f01c3b837$a9=8a48b0$a301280a@.phx.gbl...I have SQL Server Standard edition (that was installed after =un-installing the Enterprise evaluation copy using add/remove programs). On Retore =screen, the option "Point in Time' is grayed out totally. How can I make this =option usable? Thanks.

---

--=_NextPart_000_0375_01C3B80E.61607470--|||Thanks, Tom. I used the Maintenace Wizard to create backup
jobs. Is there an option not to use Full Recovery model?
Neeru
>--Original Message--
>Your database must be using the Full recovery model - and
you must backup
>the database as well as the log. Use the STOPAT option
when restoring logs.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Neeru" <anonymous@.discussions.microsoft.com> wrote in
message
>news:019f01c3b837$a98a48b0$a301280a@.phx.gbl...
>I have SQL Server Standard edition (that was installed
>after un-installing the Enterprise evaluation copy using
>add/remove programs). On Retore screen, the option "Point
>in Time' is grayed out totally. How can I make this option
>usable?
>Thanks.
>|||Neeru:
If you want "Point in Time" be enabled then the option "Recovery Model" of
your database must be set to "Full Recovery". To set this option using
Enterprise Manager--> (1) Right Click your database and then click
"Properties" (2) Then click "Options" set and choose "Full" as Recovery
Model.
By this......">> >Your database must be using the Full recovery model -
and you must backup the database as well as the log. >>" Tom meant to
say the same thing but I think you undertood that you are not seeing
"Point-In-Time" because your database is set to "Full Recovery"
"Neeru" <anonymous@.discussions.microsoft.com> wrote in message
news:a85a01c3b83a$629742c0$a601280a@.phx.gbl...
> Thanks, Tom. I used the Maintenace Wizard to create backup
> jobs. Is there an option not to use Full Recovery model?
> Neeru
> >--Original Message--
> >Your database must be using the Full recovery model - and
> you must backup
> >the database as well as the log. Use the STOPAT option
> when restoring logs.
> >
> >--
> >Tom
> >
> >----
> --
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional
> >Toronto, ON Canada
> >www.pinnaclepublishing.com/sql
> >
> >
> >"Neeru" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:019f01c3b837$a98a48b0$a301280a@.phx.gbl...
> >I have SQL Server Standard edition (that was installed
> >after un-installing the Enterprise evaluation copy using
> >add/remove programs). On Retore screen, the option "Point
> >in Time' is grayed out totally. How can I make this option
> >usable?
> >Thanks.
> >|||THANKS, DON and Tom.
It worked. Have a nice week.
Neeru
>--Original Message--
>Neeru:
>If you want "Point in Time" be enabled then the
option "Recovery Model" of
>your database must be set to "Full Recovery". To set this
option using
>Enterprise Manager--> (1) Right Click your database and
then click
>"Properties" (2) Then click "Options" set and
choose "Full" as Recovery
>Model.
>By this......">> >Your database must be using the Full
recovery model -
>and you must backup the database as well as the log.
>>" Tom meant to
>say the same thing but I think you undertood that you are
not seeing
>"Point-In-Time" because your database is set to "Full
Recovery"
>
>"Neeru" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a85a01c3b83a$629742c0$a601280a@.phx.gbl...
>> Thanks, Tom. I used the Maintenace Wizard to create
backup
>> jobs. Is there an option not to use Full Recovery model?
>> Neeru
>> >--Original Message--
>> >Your database must be using the Full recovery model -
and
>> you must backup
>> >the database as well as the log. Use the STOPAT option
>> when restoring logs.
>> >
>> >--
>> >Tom
>> >
>> >----
--
>> --
>> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >SQL Server MVP
>> >Columnist, SQL Server Professional
>> >Toronto, ON Canada
>> >www.pinnaclepublishing.com/sql
>> >
>> >
>> >"Neeru" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:019f01c3b837$a98a48b0$a301280a@.phx.gbl...
>> >I have SQL Server Standard edition (that was installed
>> >after un-installing the Enterprise evaluation copy
using
>> >add/remove programs). On Retore screen, the
option "Point
>> >in Time' is grayed out totally. How can I make this
option
>> >usable?
>> >Thanks.
>> >
>
>.
>

Please help: Can not start Report Server Web Service for 2000

I have spent several days trying to figure it out, I am out of options now.
I have a SQL Server 2000 Standard, patched with SP4, installed on Windows
Server 2003 SP1, I installed Report Server 2000 Standard edition (Report
Server and Report Manager components). At the end of installation, I got a
warning saying that I have to manually initialize Report server for the first
time for it to work. But when I tried somethng like
http://localhost/ReportServer, I received "Service Unavailable". I have made
sure ASP .Net v1.1.4322 is registered with IIS (6.0 SP1), use Network Service
account for ASP .Net, etc. From the event log, itsaid W3Wp encounterd an
application exeption, there was no log for ReportServer.
I really don't know what if going on here as it worked perfectly on another
Windows Server 2003, the difference is that I has Visual Studio .Net 2003
installed on that machine, but I think VS is only necessary if you want to
install Report Designer.
I read through a lot of newsgroup, tried all those suggestions, but still
not be able to fix it.
Thanks in advance for any info.
did you try using RSactivate utility to manually activate report
server?
If yes is it successful, if not what is the error message
|||Yes I did try it, it failed. The message was like " the report server web
service has not generated a public key".
I checked ReportServer database keys table, there is an entry for client =
1, from what I read, it is supposed to have an entry for client = 0 once
http://localhost/ReportServer is invoked or manually activated.
I think the problem is that Report Server web service somehow crashed, that
would explain whay there was no log file like ReportServer_timestamp.txt.
Now I am suspecting it may be because of the patches, Win2003 SP1 or SQL
2000 SP4. The another Win2003 worked has no SP1, and SQL 2000 is SP3.
Unfortunatlly right now rolling back is not an option.
"Shadow" wrote:

> did you try using RSactivate utility to manually activate report
> server?
> If yes is it successful, if not what is the error message
>

Please help: Can not start Report Server Web Service for 2000

I have spent several days trying to figure it out, I am out of options now.
I have a SQL Server 2000 Standard, patched with SP4, installed on Windows
Server 2003 SP1, I installed Report Server 2000 Standard edition (Report
Server and Report Manager components). At the end of installation, I got a
warning saying that I have to manually initialize Report server for the firs
t
time for it to work. But when I tried somethng like
http://localhost/ReportServer, I received "Service Unavailable". I have made
sure ASP .Net v1.1.4322 is registered with IIS (6.0 SP1), use Network Servic
e
account for ASP .Net, etc. From the event log, itsaid W3Wp encounterd an
application exeption, there was no log for ReportServer.
I really don't know what if going on here as it worked perfectly on another
Windows Server 2003, the difference is that I has Visual Studio .Net 2003
installed on that machine, but I think VS is only necessary if you want to
install Report Designer.
I read through a lot of newsgroup, tried all those suggestions, but still
not be able to fix it.
Thanks in advance for any info.did you try using RSactivate utility to manually activate report
server?
If yes is it successful, if not what is the error message|||Yes I did try it, it failed. The message was like " the report server web
service has not generated a public key".
I checked ReportServer database keys table, there is an entry for client =
1, from what I read, it is supposed to have an entry for client = 0 once
http://localhost/ReportServer is invoked or manually activated.
I think the problem is that Report Server web service somehow crashed, that
would explain whay there was no log file like ReportServer_timestamp.txt.
Now I am suspecting it may be because of the patches, Win2003 SP1 or SQL
2000 SP4. The another Win2003 worked has no SP1, and SQL 2000 is SP3.
Unfortunatlly right now rolling back is not an option.
"Shadow" wrote:

> did you try using RSactivate utility to manually activate report
> server?
> If yes is it successful, if not what is the error message
>

Please help: Can not start Report Server Web Service for 2000

I have spent several days trying to figure it out, I am out of options now.
I have a SQL Server 2000 Standard, patched with SP4, installed on Windows
Server 2003 SP1, I installed Report Server 2000 Standard edition (Report
Server and Report Manager components). At the end of installation, I got a
warning saying that I have to manually initialize Report server for the first
time for it to work. But when I tried somethng like
http://localhost/ReportServer, I received "Service Unavailable". I have made
sure ASP .Net v1.1.4322 is registered with IIS (6.0 SP1), use Network Service
account for ASP .Net, etc. From the event log, itsaid W3Wp encounterd an
application exeption, there was no log for ReportServer.
I really don't know what if going on here as it worked perfectly on another
Windows Server 2003, the difference is that I has Visual Studio .Net 2003
installed on that machine, but I think VS is only necessary if you want to
install Report Designer.
I read through a lot of newsgroup, tried all those suggestions, but still
not be able to fix it.
Thanks in advance for any info.did you try using RSactivate utility to manually activate report
server?
If yes is it successful, if not what is the error message|||Yes I did try it, it failed. The message was like " the report server web
service has not generated a public key".
I checked ReportServer database keys table, there is an entry for client =1, from what I read, it is supposed to have an entry for client = 0 once
http://localhost/ReportServer is invoked or manually activated.
I think the problem is that Report Server web service somehow crashed, that
would explain whay there was no log file like ReportServer_timestamp.txt.
Now I am suspecting it may be because of the patches, Win2003 SP1 or SQL
2000 SP4. The another Win2003 worked has no SP1, and SQL 2000 is SP3.
Unfortunatlly right now rolling back is not an option.
"Shadow" wrote:
> did you try using RSactivate utility to manually activate report
> server?
> If yes is it successful, if not what is the error message
>