Change lock type on large table

[Former Member]

I have a table which is about 2G size. the lock type on this table is set as AllPage.  I think this cause performance issue when issue select query, many SH lock applied. I try to change the lock type to datarow, which should have no lock when issue select as document indicated.

but when I try it with DBArtisan, it took longtime and never end. Then I stop the app and connect it again. I found the lock type do change it to datarow. but there is a new table name like mytab_3309ac22 created.

then I try to change lock type again on other table and got following info:

You can not run Alter Table Lock in this database because the 'select into/bulkcopy' option is off.

Looks like dboption changed. So want to know if this is safe for data when change lock type? how to ensure it is done properly?

[Former Member · Accepted solution]

Hi Kent,

The problem is that allpages has different physical structure than datarows.

Changing from one format to another requires to rewrite the whole table.

There are a few ways to achieve this goal.

a) running the alter table directly:

alter table x lock datarows

This command causes to make the change internally. This is the fastest way to do that, but it requries to set the 'select into' option on the database. And that might be problematic because it breaks the  the transaction log sequence.

If you don't have the option set on the database you are not able to run the command.

b) doing it manually:

1. create a second table with datarows scheme

2. copy the rows to the table

3. dropping the old and renaming the new table

If you have a very busy system with little time for downtime then that would be the only solution. You can create triggers to manually log all the changes on the old table if that process would take too long.

At the end you would have to add all the foreign keys to the new table. And of course recreate the indexes.

I don't exactly know what DBArtisan is doing, but since you don't have the 'select into' option then probably it is running the second option. This new table with name mytab_3309ac22 might be the effect of the operation broken in the middle. How many rows has this table have? And what is the scheme?

If you have plenty of time then I would recommend to run the alter table command together with the 'select into' option. Anyway you won't have any progress bar during this operation so it is very hard to predict the time of the operation.

HTH,

Adam

[Former Member · Accepted solution]

Thanks, Adam. As changing lock type from allpage to datarow will increase total number of open locks when system is running. A few questions:

1. how to estimate the total lock maybe needed?

2. which config param should be changed?

3. How to check if the config is enough ?

[Former Member · Accepted solution]

Hi Kent,

I think, first of all, when you run alter table .. lock datarows then you just create one exclusive lock on the whole table, so the problem with number of locks does not count.

When you copy the table to a copy with datarows lock scheme then the number of locks would depend on the size of the batch.

Generally i would recommend to monitor the number of locks with:

exec sp_monitorconfig 'number of locks'

The procedure tells you what is the maximum (high water mark) number of locks that has already been used. If you are reaching your maximum, that is configured, then you have to increase the parameter:

exec sp_configure 'number of locks'

HTH,

Adam

[kevin_sherlock]

I don't understand your premise here:

>> I think this cause performance issue when issue select query, many SH lock applied. I try to change the lock type to datarow, which should have no lock when issue select as document indicated.

Shared "page" (assume page locks since you don't mention the locking level) locks would only cause a "performance" issue if there were other incompatible lock requests for the same page.  Changing to datarows locking would require shared "row" locks to be acquired (actually, quite a few more on aggregate since the granularity of the lock is smaller).  Same holds true there that if you have a shared-row lock and another task requests an incompatible lock on the same row, there is blocking there too.  Datarows locking does NOT mean "no lock" is used to read a row during a SELECT statement.

So, do you have lock contention problems that you are trying to resolve?, or are you simply trying to avoid locking all together (which you can't do, regardless of a table's lock setting for allpages, datapages, or datarows locking).

[Former Member]

Hi,

When needed to change lock scheme, please take a look at the new capabilties of SRS using 'direct load' clause in subscriptions.

Hans