Hi,
Lets say I have a process running on the server (e.g a stored proc) -
Process A
Then another scheduled process starts to run but is locked up by process A.
Will process A run any slower as it's holding up the second process, or,
does it not affect the performance at all?
Basically, I'm quite happy for the second process to have to wait - but - I
don't want the performance to be radically slowed.
ThanksLondon
http://www.sql-server-performance.com/reducing_locks.asp
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>|||Process A should not be slowed by processes which are waiting on A.
Offcourse if there are more processes which are running or claiming memory,
process A can get slowed. A waiting process should consume very little (or
no) cpu. It is offcourse in a list of processes so the OS uses a very very
VERY small amount of CPU to check or pass over this process when
rescheduling, SQL-server has to set a wait on a lock this consumes very VERY
VERY small amount of cpu.
Offcourse if the process which is blocked by A holds locks and process A
needs those resources you wil have a deadlock. This holds both processes
till a deadlock is detected, one process is 'aborted' the other can
continue.
ben brugman
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>sql
Showing posts with label slow. Show all posts
Showing posts with label slow. Show all posts
Tuesday, March 27, 2012
do indexed views slow down inserts
sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?
A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
sql
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?
A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
sql
do indexed views slow down inserts
sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
do indexed views slow down inserts
sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
Thursday, March 22, 2012
Do CLR DLLs use more memory / slow SQL server?
We has some response issue with minor schema changes.
Except we're using some C# DLLs now.
Kyle JedrusiakBasically, it is not how you do it, it is what you that counts. If you write procedural code instead
of set-based code, it will in vast majority of cases be much slower. CLR has some overhead, so for
cases where you can do the same with TSQL (set based queries etc), CLR will perform worse. But not
as much as you would think (if you know the architecture behind the CLR integration). My guess is
either change of query plans or procedural logic.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
> We has some response issue with minor schema changes.
> Except we're using some C# DLLs now.
> Kyle Jedrusiak
>|||We basically replaced a lot of string manipulation stuff with c# code.
Not doing any record manipulation.
Kyle!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTByEiLvGHA.1956@.TK2MSFTNGP02.phx.gbl...
> Basically, it is not how you do it, it is what you that counts. If you
> write procedural code instead of set-based code, it will in vast majority
> of cases be much slower. CLR has some overhead, so for cases where you can
> do the same with TSQL (set based queries etc), CLR will perform worse. But
> not as much as you would think (if you know the architecture behind the
> CLR integration). My guess is either change of query plans or procedural
> logic.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
>> We has some response issue with minor schema changes.
>> Except we're using some C# DLLs now.
>> Kyle Jedrusiak
>|||String manipulation can be a good candidate for SQLCLR, but we can't say without any knowledge of
what you do and how you do it. Again, if you degraded set based code to procedural code, you will
most probably see worse performance. If you used CLR for the right reasons, the problem is
elsewhere, like SQL Server selecting different execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uXbLckLvGHA.5056@.TK2MSFTNGP06.phx.gbl...
> We basically replaced a lot of string manipulation stuff with c# code.
> Not doing any record manipulation.
> Kyle!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OTByEiLvGHA.1956@.TK2MSFTNGP02.phx.gbl...
>> Basically, it is not how you do it, it is what you that counts. If you write procedural code
>> instead of set-based code, it will in vast majority of cases be much slower. CLR has some
>> overhead, so for cases where you can do the same with TSQL (set based queries etc), CLR will
>> perform worse. But not as much as you would think (if you know the architecture behind the CLR
>> integration). My guess is either change of query plans or procedural logic.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
>> news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
>> We has some response issue with minor schema changes.
>> Except we're using some C# DLLs now.
>> Kyle Jedrusiak
>>
>
Except we're using some C# DLLs now.
Kyle JedrusiakBasically, it is not how you do it, it is what you that counts. If you write procedural code instead
of set-based code, it will in vast majority of cases be much slower. CLR has some overhead, so for
cases where you can do the same with TSQL (set based queries etc), CLR will perform worse. But not
as much as you would think (if you know the architecture behind the CLR integration). My guess is
either change of query plans or procedural logic.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
> We has some response issue with minor schema changes.
> Except we're using some C# DLLs now.
> Kyle Jedrusiak
>|||We basically replaced a lot of string manipulation stuff with c# code.
Not doing any record manipulation.
Kyle!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTByEiLvGHA.1956@.TK2MSFTNGP02.phx.gbl...
> Basically, it is not how you do it, it is what you that counts. If you
> write procedural code instead of set-based code, it will in vast majority
> of cases be much slower. CLR has some overhead, so for cases where you can
> do the same with TSQL (set based queries etc), CLR will perform worse. But
> not as much as you would think (if you know the architecture behind the
> CLR integration). My guess is either change of query plans or procedural
> logic.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
>> We has some response issue with minor schema changes.
>> Except we're using some C# DLLs now.
>> Kyle Jedrusiak
>|||String manipulation can be a good candidate for SQLCLR, but we can't say without any knowledge of
what you do and how you do it. Again, if you degraded set based code to procedural code, you will
most probably see worse performance. If you used CLR for the right reasons, the problem is
elsewhere, like SQL Server selecting different execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uXbLckLvGHA.5056@.TK2MSFTNGP06.phx.gbl...
> We basically replaced a lot of string manipulation stuff with c# code.
> Not doing any record manipulation.
> Kyle!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OTByEiLvGHA.1956@.TK2MSFTNGP02.phx.gbl...
>> Basically, it is not how you do it, it is what you that counts. If you write procedural code
>> instead of set-based code, it will in vast majority of cases be much slower. CLR has some
>> overhead, so for cases where you can do the same with TSQL (set based queries etc), CLR will
>> perform worse. But not as much as you would think (if you know the architecture behind the CLR
>> integration). My guess is either change of query plans or procedural logic.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
>> news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
>> We has some response issue with minor schema changes.
>> Except we're using some C# DLLs now.
>> Kyle Jedrusiak
>>
>
Do CLR DLLs use more memory / slow SQL server?
We has some response issue with minor schema changes.
Except we're using some C# DLLs now.
Kyle JedrusiakBasically, it is not how you do it, it is what you that counts. If you write
procedural code instead
of set-based code, it will in vast majority of cases be much slower. CLR has
some overhead, so for
cases where you can do the same with TSQL (set based queries etc), CLR will
perform worse. But not
as much as you would think (if you know the architecture behind the CLR inte
gration). My guess is
either change of query plans or procedural logic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
> We has some response issue with minor schema changes.
> Except we're using some C# DLLs now.
> Kyle Jedrusiak
>|||We basically replaced a lot of string manipulation stuff with c# code.
Not doing any record manipulation.
Kyle!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTByEiLvGHA.1956@.TK2MSFTNGP02.phx.gbl...
> Basically, it is not how you do it, it is what you that counts. If you
> write procedural code instead of set-based code, it will in vast majority
> of cases be much slower. CLR has some overhead, so for cases where you can
> do the same with TSQL (set based queries etc), CLR will perform worse. But
> not as much as you would think (if you know the architecture behind the
> CLR integration). My guess is either change of query plans or procedural
> logic.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
>|||String manipulation can be a good candidate for SQLCLR, but we can't say wit
hout any knowledge of
what you do and how you do it. Again, if you degraded set based code to proc
edural code, you will
most probably see worse performance. If you used CLR for the right reasons,
the problem is
elsewhere, like SQL Server selecting different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uXbLckLvGHA.5056@.TK2MSFTNGP06.phx.gbl...
> We basically replaced a lot of string manipulation stuff with c# code.
> Not doing any record manipulation.
> Kyle!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OTByEiLvGHA.1956@.TK2MSFTNGP02.phx.gbl...
>
Except we're using some C# DLLs now.
Kyle JedrusiakBasically, it is not how you do it, it is what you that counts. If you write
procedural code instead
of set-based code, it will in vast majority of cases be much slower. CLR has
some overhead, so for
cases where you can do the same with TSQL (set based queries etc), CLR will
perform worse. But not
as much as you would think (if you know the architecture behind the CLR inte
gration). My guess is
either change of query plans or procedural logic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
> We has some response issue with minor schema changes.
> Except we're using some C# DLLs now.
> Kyle Jedrusiak
>|||We basically replaced a lot of string manipulation stuff with c# code.
Not doing any record manipulation.
Kyle!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTByEiLvGHA.1956@.TK2MSFTNGP02.phx.gbl...
> Basically, it is not how you do it, it is what you that counts. If you
> write procedural code instead of set-based code, it will in vast majority
> of cases be much slower. CLR has some overhead, so for cases where you can
> do the same with TSQL (set based queries etc), CLR will perform worse. But
> not as much as you would think (if you know the architecture behind the
> CLR integration). My guess is either change of query plans or procedural
> logic.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23tHqUaLvGHA.4460@.TK2MSFTNGP05.phx.gbl...
>|||String manipulation can be a good candidate for SQLCLR, but we can't say wit
hout any knowledge of
what you do and how you do it. Again, if you degraded set based code to proc
edural code, you will
most probably see worse performance. If you used CLR for the right reasons,
the problem is
elsewhere, like SQL Server selecting different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uXbLckLvGHA.5056@.TK2MSFTNGP06.phx.gbl...
> We basically replaced a lot of string manipulation stuff with c# code.
> Not doing any record manipulation.
> Kyle!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OTByEiLvGHA.1956@.TK2MSFTNGP02.phx.gbl...
>
Wednesday, March 7, 2012
Distribution task marvelously slow
Greetings!
We are replicating a table (many of them actually, but one in particular)
which is about 10 GB...660K rows including some text columns. We needed to
change the properties of one column and did not want to pay the penalty of
reinitializing the subscription, so I added a temp column "A_temp", populated
that with the data from the original column "A" then dropped column "A" and
readded it with new properties including NOT NULL. We are then loading "A"
from "A_temp" and then dropping the "A_temp" column. In theory this is fine
and it is working...however the updates are remarkably slow...three records a
second with the sp_MSUpd_... stored procedure. At that rate, the whole
shebang will take days instead of hour or so it would have with the reinit
and snapshot. This is generally the way MS would recommend doing the schema
change from what I have studied. The publisher and distributor are same
server and the subscriber is a remote, but on the LAN, machine.
Any ideas? Thanks so much!
You could check the query plan of the update stored proc - perhaps it would
benefit from different indexes at the subscriber or perhaps the indexes are
fragmented - also worth checking. Finally, check to see if there is any
blocking going on that accounts for the slowdown.
HTH,
Paul Ibison
|||Thank you for your reply. I did notice that the replication update was
blocking other things, but we have rectified that - for now. The actual
update doesn't appear to be blocked at all. We do have the column with a
clustered index on it. Actually thought of dropping indexes to see if it
sped up, but the column which is getting updated is not indexes, so I wasn't
expected a lot out of that. I haven't checked out the query plan...I think
you are right that it is worth a look. I'll see if I can get one of my
admins to toss me an actual sp call instead of having me guess at the
parameters...
Thanks again!
"Paul Ibison" wrote:
> You could check the query plan of the update stored proc - perhaps it would
> benefit from different indexes at the subscriber or perhaps the indexes are
> fragmented - also worth checking. Finally, check to see if there is any
> blocking going on that accounts for the slowdown.
> HTH,
> Paul Ibison
>
We are replicating a table (many of them actually, but one in particular)
which is about 10 GB...660K rows including some text columns. We needed to
change the properties of one column and did not want to pay the penalty of
reinitializing the subscription, so I added a temp column "A_temp", populated
that with the data from the original column "A" then dropped column "A" and
readded it with new properties including NOT NULL. We are then loading "A"
from "A_temp" and then dropping the "A_temp" column. In theory this is fine
and it is working...however the updates are remarkably slow...three records a
second with the sp_MSUpd_... stored procedure. At that rate, the whole
shebang will take days instead of hour or so it would have with the reinit
and snapshot. This is generally the way MS would recommend doing the schema
change from what I have studied. The publisher and distributor are same
server and the subscriber is a remote, but on the LAN, machine.
Any ideas? Thanks so much!
You could check the query plan of the update stored proc - perhaps it would
benefit from different indexes at the subscriber or perhaps the indexes are
fragmented - also worth checking. Finally, check to see if there is any
blocking going on that accounts for the slowdown.
HTH,
Paul Ibison
|||Thank you for your reply. I did notice that the replication update was
blocking other things, but we have rectified that - for now. The actual
update doesn't appear to be blocked at all. We do have the column with a
clustered index on it. Actually thought of dropping indexes to see if it
sped up, but the column which is getting updated is not indexes, so I wasn't
expected a lot out of that. I haven't checked out the query plan...I think
you are right that it is worth a look. I'll see if I can get one of my
admins to toss me an actual sp call instead of having me guess at the
parameters...
Thanks again!
"Paul Ibison" wrote:
> You could check the query plan of the update stored proc - perhaps it would
> benefit from different indexes at the subscriber or perhaps the indexes are
> fragmented - also worth checking. Finally, check to see if there is any
> blocking going on that accounts for the slowdown.
> HTH,
> Paul Ibison
>
Subscribe to:
Posts (Atom)