Hi,
I heard that simple delete stmt which deletes no of rows everytime in
replication is not using came plan caching why you have any idea or is there
any option that we can turn on to use cache plan..
Thanks in advance
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
No, it will use the plan that either the ad hoq sql statements use or the
stored procedure plan.
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
"pardhiveswar via droptable.com" <u8956@.uwe> wrote in message
news:73eafdf38c451@.uwe...
> Hi,
> I heard that simple delete stmt which deletes no of rows everytime in
> replication is not using came plan caching why you have any idea or is
> there
> any option that we can turn on to use cache plan..
>
> Thanks in advance
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
>
|||Hillary,
ok if Autoparameterization is not using for simple delete statement in 2000
what we have to do..
is there an option to force sql server...
Thanks in advance
Hilary Cotter wrote:[vbcol=seagreen]
>No, it will use the plan that either the ad hoq sql statements use or the
>stored procedure plan.
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
|||Hillary,
Just ignore the above stsmt.
Actually my questions is during replication from sql to sql it will create
stored prodecudres
but from sql to oracle iinstead of stored procedure it will only create sql
stmt so during this time it is not using autoparameterisation for simple
delete stmt(adhoc stmt),is there a way that we can use autoparam...
please let me know if you didn't understand
sorry for the confusion before..
Thanks, in advance
pardhiveswar wrote:[vbcol=seagreen]
>Hillary,
>ok if Autoparameterization is not using for simple delete statement in 2000
>what we have to do..
>is there an option to force sql server...
>Thanks in advance
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
Showing posts with label everytime. Show all posts
Showing posts with label everytime. Show all posts
Friday, March 23, 2012
Tuesday, March 20, 2012
Replication and Reports
Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.
In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.
In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Replication and Reports
Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Replication and Reports
Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Subscribe to:
Posts (Atom)