Skip to main content
To homepage To homepage

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? 

Hi @ekw9 ,

I believe I see this happening from time to time indeed. There are a number of system calls that float to the top of the “most expensive queries” list.

To be more specific queries of this structure:

-- Containing object no longer contains the selected query text.
(@HasData bit)SELECT TOP 1
        @HasData = 1
    FROM sys.dm_db_partition_stats
    WHERE
        object_id = OBJECT_ID(N'_schema].Etable]')
        AND row_count > 0
        AND (index_id = 0 OR index_id = 1)
 

start popping up in the top 50 resource consuming queries because the sys.dm_db_partition_stats DMV is locked or simply performing badly.

That’s exactly correct, Rory! That one runs at the start of every execution package. There are similar queries that check to see if an object exists that run during deployment that can run afoul of this issue as well. 

Hi @ekw9 

I have created a support ticket for this

Much appreciated, Christian!

Reply


Terms and ConditionsCookie settings