long time lock issue

[aelmi]

When a table is locked, reading from sys.dm_db_index_physical_stats cannot be run. So, the page lock of this step cannot be released. The solution is to apply SET LOCK_TIMEOUT before querying sys.dm_db_index_physical_stats Al Elmi

[pmasl]

This will cause an index defrag to be skipped. And any other operation waiting on the table will also be blocked. Are you suggesting that such index whose info we can't read immediately from dm_db_index_physical_stats should be optionally skipped?

[aelmi]

The SP tries to read from sys.dm_db_index_physical_stats first and the SET LOCK_TIMEOUT is after that read. The issue happens here is when sys.dm_db_index_physical_stats first locked it waits for ages! However, SET LOCK_TIMEOUT parameter is supposed to ignore this wait