long running transactions w/ other users needing to read data

[Erland Sommarskog]

Re: long running transactions w/ other users needing to read data

pb648174 (google@webpaul .net) writes:[color=blue]
> Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
> some work on the indexes in order to fix it? Would changing to a
> clustered index make it less likely to block?[/color]

I sort of assumed that all new rows were inserted at the end of the
clustered index. But if the rows you insert are not aligned with the
clustered index, then the problem becomes a lot worse. Any process
that finds itself in need ot a table scan would be blocked.
[color=blue]
> The rows aren't being inserted one row at a time, but they are being
> done one "project" at a time(maybe a few thousand records), with pretty
> standard Insert Into..Select statements. I have my application pausing
> for a few seconds between projects to try and reduce stress on the
> server and give other processes a chance for freedom, but from what you
> are saying, perhaps I need to remove the wait times in order to get the
> transaction over as quickly as possible.[/color]

Had you committed after each batch, the pause could make some sense. But
if you don't commit until the end, then you should get away with those
pauses.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[pb648174]

Re: long running transactions w/ other users needing to read data

The only thing about taking out the pauses is that it hammers on the
database so hard, it pegs the CPU.. With the pauses other processes
have a chance to go forward, but with this locking it doesn't seem to
matter.

[pb648174]

Re: long running transactions w/ other users needing to read data

The only thing about taking out the pauses is that it hammers on the
database so hard, it pegs the CPU.. With the pauses other processes
have a chance to go forward, but with this locking it doesn't seem to
matter.

[pb648174]

Re: long running transactions w/ other users needing to read data

Isn't this "batch" approach sort of ridiculous, given that databases
are supposed to be able to run transactions?? We have hundreds of
tables and the above approach would be extremely onerous. I don't think
it would work anyway since there are identity columns in most of the
tables which need to match up between parent and child relationships.
If somebody inserted a single record while the transaction was running,
that would hose the whole process.

[pb648174]

Re: long running transactions w/ other users needing to read data

Isn't this "batch" approach sort of ridiculous, given that databases
are supposed to be able to run transactions?? We have hundreds of
tables and the above approach would be extremely onerous. I don't think
it would work anyway since there are identity columns in most of the
tables which need to match up between parent and child relationships.
If somebody inserted a single record while the transaction was running,
that would hose the whole process.

[pb648174]

Re: long running transactions w/ other users needing to read data

This is interesting - adding a clustered index on the identity column
fixed the previous problem, so I picked one module (which has about 5
or 6 tables) to see if I could free up that module while the copy
process was running. I went and put clustered indexes on all the
identity columns of those tables, but now another table has a locking
problem, although this time, not on the index. The output from that
line from sp_lock is below:

54 8 2101582525 1 PAG 1:26568 S WAIT

So now it is a page lock, which from what I've read is a group of
records. That seems resonable given that I am copying large amounts of
data, but why is it stopping my other process?

[pb648174]

Re: long running transactions w/ other users needing to read data

This is interesting - adding a clustered index on the identity column
fixed the previous problem, so I picked one module (which has about 5
or 6 tables) to see if I could free up that module while the copy
process was running. I went and put clustered indexes on all the
identity columns of those tables, but now another table has a locking
problem, although this time, not on the index. The output from that
line from sp_lock is below:

54 8 2101582525 1 PAG 1:26568 S WAIT

So now it is a page lock, which from what I've read is a group of
records. That seems resonable given that I am copying large amounts of
data, but why is it stopping my other process?

[Erland Sommarskog]

Re: long running transactions w/ other users needing to read data

pb648174 (google@webpaul .net) writes:[color=blue]
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU..[/color]

CPU:s are humans. Just keep them working!

(But, OK, if you see a puff of smoke, it's probably time for a pause.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: long running transactions w/ other users needing to read data

pb648174 (google@webpaul .net) writes:[color=blue]
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU..[/color]

CPU:s are humans. Just keep them working!

(But, OK, if you see a puff of smoke, it's probably time for a pause.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: long running transactions w/ other users needing to read data

pb648174 (google@webpaul .net) writes:[color=blue]
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU.. With the pauses other processes
> have a chance to go forward, but with this locking it doesn't seem to
> matter.[/color]

On a little more serious note... if the machine has more than one
CPU, you could consider to reduce the degree of parallelism, to leave
some CPUs to the rest of the pack. You do this by adding

OPTION (MAXDOP n)

at the end of the query. In fact you can even try 1, to abort parallelism
entirely. SQL Server appears to be over-optimistic by the benefits of
parallelism, and non-parallel plans may be better.

If your CPU(s) are hyper-threaded, you should server-configuration
parameter "Max degree of parallelism" to the number of physical CPUs.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: long running transactions w/ other users needing to read data

pb648174 (google@webpaul .net) writes:[color=blue]
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU.. With the pauses other processes
> have a chance to go forward, but with this locking it doesn't seem to
> matter.[/color]

On a little more serious note... if the machine has more than one
CPU, you could consider to reduce the degree of parallelism, to leave
some CPUs to the rest of the pack. You do this by adding

OPTION (MAXDOP n)

at the end of the query. In fact you can even try 1, to abort parallelism
entirely. SQL Server appears to be over-optimistic by the benefits of
parallelism, and non-parallel plans may be better.

If your CPU(s) are hyper-threaded, you should server-configuration
parameter "Max degree of parallelism" to the number of physical CPUs.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: long running transactions w/ other users needing to read data

pb648174 (google@webpaul .net) writes:[color=blue]
> This is interesting - adding a clustered index on the identity column
> fixed the previous problem, so I picked one module (which has about 5
> or 6 tables) to see if I could free up that module while the copy
> process was running. I went and put clustered indexes on all the
> identity columns of those tables, but now another table has a locking
> problem, although this time, not on the index. The output from that
> line from sp_lock is below:
>
> 54 8 2101582525 1 PAG 1:26568 S WAIT
>
> So now it is a page lock, which from what I've read is a group of
> records. That seems resonable given that I am copying large amounts of
> data, but why is it stopping my other process?[/color]

Lacking telepathic abilities, I can't say why.

What you could try is to use aba_lockinfo, which you find on my web
site, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This procedure
give you an overview active process, which object they lock, and also
current statements. This gives a little better idea of what is going
on.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: long running transactions w/ other users needing to read data

pb648174 (google@webpaul .net) writes:[color=blue]
> This is interesting - adding a clustered index on the identity column
> fixed the previous problem, so I picked one module (which has about 5
> or 6 tables) to see if I could free up that module while the copy
> process was running. I went and put clustered indexes on all the
> identity columns of those tables, but now another table has a locking
> problem, although this time, not on the index. The output from that
> line from sp_lock is below:
>
> 54 8 2101582525 1 PAG 1:26568 S WAIT
>
> So now it is a page lock, which from what I've read is a group of
> records. That seems resonable given that I am copying large amounts of
> data, but why is it stopping my other process?[/color]

Lacking telepathic abilities, I can't say why.

What you could try is to use aba_lockinfo, which you find on my web
site, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This procedure
give you an overview active process, which object they lock, and also
current statements. This gives a little better idea of what is going
on.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[rcamarda]

Re: long running transactions w/ other users needing to read data

I have no idea if this will help or not....
A vendor gave me a giant sql select that has 200 fields and 25 joins.
The sample of the join:
LEFT OUTER JOIN view_TPSEnum_10 145 WITH(NOLOCK) ON
view_TPSEnum_10 145.nIndex = [tblObjectType50 01_1].[FldNumeric21251]
I'm told the WITH(NOLOCK) wont lock the source table *shrug*. Read the
docs that it allows dirty reads and is used only for selects.

[rcamarda]

Re: long running transactions w/ other users needing to read data

I have no idea if this will help or not....
A vendor gave me a giant sql select that has 200 fields and 25 joins.
The sample of the join:
LEFT OUTER JOIN view_TPSEnum_10 145 WITH(NOLOCK) ON
view_TPSEnum_10 145.nIndex = [tblObjectType50 01_1].[FldNumeric21251]
I'm told the WITH(NOLOCK) wont lock the source table *shrug*. Read the
docs that it allows dirty reads and is used only for selects.