This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
In the dynamic world of database management, we encounter unique challenges that impact the efficiency and performance of our applications. A common issue observed is the inability to expand the database table list during certain operations. This article addresses a specific situation where clients launch resource-intensive queries that take time and create locks, preventing other users from expanding the table list.
For instance, we have a case where a customer, under a READ Committed transaction, is attempting to delete an index while needing to query the database for its tables and associated indexes.
What happens is a lock is generated because the second session is also in READ Committed and must wait for the first session to complete. In such scenarios, we can switch our isolation level to READ Uncommitted or use hints like ReadPast or NoLock to allow access.
However, there's a catch: the index might still appear in the list even if it no longer exists. This highlights the importance of data partitioning in large-volume databases. Moreover, remember that when using read-only replicas, executing this query will also replicate it to the replicas.
Finally, remember the usage ONLINE=ON in these type of operations: DROP INDEX (Transact-SQL) - SQL Server | Microsoft Learn
Session #1:
Session #2:
Additional Information
Lesson Learned #319: Lock request time out period exceeded using SSMS - Microsoft Community Hub