TimeXtender beings almost all tasks by checking to see if the objects involved currently exist. It does this by querying SQL Server system views.
However, this does not take into account a very unusual property of system views: querying them naively can result in SQL Server attempting to get a lock on the underlying system metadata tables. When it does this is wildly inconsistent - sometimes it will try to get a lock on the table when doing only a simple SELECT, other times it will only attempt to get a lock when certain joins are used. This isn’t really documented anywhere I’ve been able to find, but I have observed it in action many times.
The problem is that SQL Server schema changes write to those metadata tables, which also locks them. And as long as the change is occurring, the tables stay locked.
This becomes an issue when rebuilding indexes. This is a very frequent occurrence curing TimeXtender execution as it happens whenever a table is fully reloaded. For large tables, this process can take quite some time. During that window, it’s possible for both deployment and execution to be blocked. This can greatly slow down executions (especially large reloads) and can lock developers out of making changes for long periods of time.
I believe that querying the system views WITH (NOLOCK) will resolve this problem. Is there any reason that this fix cannot be implemented?
Best answer by Christian Hauggaard
View original
