Index Locking Operation Questiong

Given a table with a clustered index and several other nonclustered indexes
which references the column defined in the clustered index, what type of
locks should I expect to be placed on all of nonclustered index? For example,
create table tblTest
(
col1 int not null,
col2 varchar(10) not null,
col3 varchar(10) not null
)
create unique clustered index (idx1_name) on tblTest (col1)
create nonclustered index (idx2_name) on tblTest(col1, col2)
create nonclustered index (idx3_name) on tblTest(col3)
Assuming the table has been populated with some data, I execute the
following statement within an explicit transaction.
begin tran
update t
set col1 = col1
from tblTest t
where col1 = 1234
end tran
(Yes, I know...as silly as this looks this is what our vendor product does
based on the profile trace we captured.)
Given this situation, I expect that SQL server would exclusive lock TAB,
PAG, RID on the tblTest object. Where PAG implicitly is locking the
(idx1_name) which is the clustered index. Also, I would expect an exclusive
lock on (idx2_name) since it needs to be "updated" however I do not expect
any lock on (idx3_name) since it would not be needed in the transaction. Is
this correct? I would really like to understand the locking mechanism in
this situation.
Thanks,
Mike MHello, Mike
In a table that has a clustered index, any non-clustered index contains
(at the leaf-level) the clustered index key, as a row locator. This is
why you see the exclusive lock for the idx3_name index. For more
informations, see "nonclustered indexes, architecture" in Books Online.
Razvan|||Ok, thank you for the info.
Mike M

Index Locking Operation Questiong

Given a table with a clustered index and several other nonclustered indexes
which references the column defined in the clustered index, what type of
locks should I expect to be placed on all of nonclustered index? For exampl
e,
create table tblTest
(
col1 int not null,
col2 varchar(10) not null,
col3 varchar(10) not null
)
create unique clustered index (idx1_name) on tblTest (col1)
create nonclustered index (idx2_name) on tblTest(col1, col2)
create nonclustered index (idx3_name) on tblTest(col3)
Assuming the table has been populated with some data, I execute the
following statement within an explicit transaction.
begin tran
update t
set col1 = col1
from tblTest t
where col1 = 1234
end tran
(Yes, I know...as silly as this looks this is what our vendor product does
based on the profile trace we captured.)
Given this situation, I expect that SQL server would exclusive lock TAB,
PAG, RID on the tblTest object. Where PAG implicitly is locking the
(idx1_name) which is the clustered index. Also, I would expect an exclusive
lock on (idx2_name) since it needs to be "updated" however I do not expect
any lock on (idx3_name) since it would not be needed in the transaction. Is
this correct? I would really like to understand the locking mechanism in
this situation.
Thanks,
Mike MHello, Mike
In a table that has a clustered index, any non-clustered index contains
(at the leaf-level) the clustered index key, as a row locator. This is
why you see the exclusive lock for the idx3_name index. For more
informations, see "nonclustered indexes, architecture" in Books Online.
Razvan|||Ok, thank you for the info.
Mike M

Index granularity

Hi,

I was looking at the new index locking granularity option available in 2005. I did not understand in what case can this be a performance enhancement. Has anybody looked into this ?

Are you talking about sp_indexoption? It is an advanced tuning option and useful in cases where you understand your query pattern / data completely. You do not need to use it normally.|||

Can you post any examples of possible usage? I was seriously considering setting table granularity locks on my data warehouse type tables, especially during the day, when all access is wholly write during ETL, or wholly read during cube build.

Using a read only database is not really convienient, but this should give some of the same properties, right?

|||

Hi,

I am not talking about the sp_indexoption. I am asking about the new index locking granularity in sql server 2005. You have an option to either lock or unlock the index row or the index page. I did a few tests. I started one transaction(session 1) with isolation level as serializable. and executed a select statement.(for this session i set the index locks to off basically meaning that the index locking for row and page is not allowed) In another session(session 2) i tried to execute an update. It did not happen till i committed my query in session 1. This means that though you have requested not to lock the indexes, still the rules of the isolation level prevail. I did not check if table level locks were used. It might happen that when don't allow the row adn page level locks(setting the option to off) might be the locks are escalated to the table level. I am not able to make out as to for what circumstances this feature can be useful ?

Thanks

Sapna

|||Please post some sample code demonstrating the issues. The index options you talked about can be set via CREATE / ALTER INDEX also in SQL Server 2005. This is the only new change otherwise it was available in SQL Server 2000 too via sp_indexoption.|||

There is no issue as such. I just want to know when this option when set ON, can be of any advantage. And what is the situation when this can be a performance issue.

Thanks

Sapna