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