SSMS Gets Blocked and Locks Up During Index Creation - by Adam Machanic

Status : 

 


101
0
Sign in
to vote
ID 478568 Comments
Status Active Workarounds
Type Bug Repros 18
Opened 7/30/2009 7:48:38 AM
Access Restriction Public

Description

Re-opening this issue. Although it has been "fixed" in a sense, the fix does not adequately address the core issue. The "fix" is that instead of SSMS locking up, we now get a lock timeout exception. That's a good start, but we still can't see the information we're looking for. 

Example: If I'm creating an index on some table, I still want to be able to get a list of all of the tables in the database. I still want to be able to look at properties of some of those tables. I still want to be able to do all of the stuff I'd usually be able to do. 

As mentioned below, this can be accomplished by using the READ UNCOMMITTED isolation level for the core queries. This will enable users of SSMS to get a much better experience, especially when working with large databases.


---


Any time someone is doing a large DML operation--particularly, index creation--the components of SSMS that use metadata will lock up and become unresponsive. This is bad enough when it happens with the Object Browser, but today I saw it happen with Intellisense.

User A was creating a large indexed view in a database.
User B came in, typed the database name, hit ".", and his query editor window locked up, for several minutes, until User A killed his CREATE INDEX statement.

This kind of behavior is unacceptable for a what is supposed to be a "management" package. Lock timeouts should be set, and READ UNCOMMITTED or READPAST should be used if timeouts do occur, as a backup. Users should not be forced to wait for information--potentially for hours--just because a large index operation happens to be occurring.
Sign in to post a comment.
Posted by Steve St.Laurent on 10/19/2016 at 10:21 AM
Are there any updates on this? This is a major issue for us where we are doing large ETL's and making a lot of DML changes to backup tables and locking out the use of SSMS for large periods of time (30+ minutes).
Posted by David [MSFT] on 6/2/2016 at 6:51 AM
I'm looking at some ways to make this experience better. In July we will enable some "experimental" code to improve the experience in Object Explorer and "Generate Scripts" . Once it's live I'll share the details on how to try it out. We'll gather feedback from the community on how well the experiment works and if it proves effective we'll turn it into a real feature with UI for enabling the new behavior.

Next on my agenda is looking at intellisense.
Posted by RLMiller on 11/12/2012 at 1:23 PM
Hi Sam,

The DBA's "Go To" tool must work in all situations or it is useless. When there is an issue with a database instance, we need to be able to dig-in and find the root cause or causes. Encountering situations where parts of a database are not available or having to kill and restart SSMS due to it locking up are not acceptable.

As someone who also lives in SSMS, I agree with Aadam regarding this and and related SSMS accessibility issues.

Regards,

Robert Miller
Posted by mrdenny on 11/12/2012 at 10:14 AM
Sam,
While it's fantastic that the development tool has been fixed, can we get a little attention paid to the administration tool. You know, the one that only works when actually connected to instances?

Denny
Posted by Adam Machanic on 11/12/2012 at 10:13 AM
Hi Sam,

I think you misunderstand what a major usability problem this is. DBAs need to be able to take a look around at database schemas when there is an issue, and this bug prevents them from being able to do exactly that. It makes the tool absolutely useless at exactly the point where it's needed the most.

As someone who works in the product all day, every day, I really think that this, and related SSMS inaccessibility issues, need to be fixed.


Thank you,
Adam Machanic
Posted by Microsoft on 11/12/2012 at 10:07 AM
Hi Adam
Thanks for reporting this issue. We have been able to reproduce this issue and it's actually fixed in SSDT, however I regret to inform you it will not be fixed in this version SSMS.

Very Sorry for the mix up,
-Sam
Posted by Adam Machanic on 7/18/2012 at 11:24 AM
Repro:


First do this:

---------
CREATE DATABASE someDb
GO

USE someDb
GO

CREATE TABLE ABC
(
    i INT
)
GO

CREATE TABLE DEF
(
    i INT
)
GO
---------


Now open Object Explorer, connect to the database server, connect to the database, and open the Tables node. See how you can see the tables? Interact with one of them. Right-click. Click Properties. Have fun :-)


Now go back to your query window and do this:

---------
BEGIN TRAN
GO

SELECT
    a.*
INTO GHI
FROM
    master..spt_values AS a,
    master..spt_values AS b,
    master..spt_values AS C
GO
---------

... and now back to object explorer. Try to interact with one of the tables that already exists. ABC, for example. Right-click, and click Properties. Lock timeout error. Try to refresh the Tables view. Lock timeout error. Try to do anything with SSMS that an administrator might want to do, and ... you get the idea.

Sometimes in big databases we can't avoid operations that are going to take long-term schema locks. We shouldn't have to suffer a lack of tooling as a result.

Thanks!
Posted by Adam Machanic on 7/18/2012 at 7:58 AM
And, lo and behold, SSMS just locked up, completely, when I hit Refresh on the Tables node. So even that part of the bug isn't, apparently, quite fixed yet.
Posted by Adam Machanic on 7/18/2012 at 7:56 AM
I've reopened this due to sitting here today waiting for an index to complete building. The CREATE INDEX has been running for 16 hours now, during which we've been unable to get any information about the database from SSMS (2012). The lock timeout exception is not especially comforting, as all it tells me is "instead of using this nice graphical tool, please go ahead and write your own query."
Posted by Microsoft on 8/29/2011 at 4:47 PM
Hi Adam
Unfortunately we can't publicly comment on specific release vehicles or implementation details. Thanks for your understanding and the bug report.

Cheers,
-Sam Hughes
Posted by Adam Machanic on 8/26/2011 at 12:01 PM
This is great news! By "next release" I assume you mean Denali. Is the fix already available in CTP3? And can you share how the fix was implemented? (Just curious.)
Posted by Microsoft on 8/26/2011 at 11:54 AM

Thanks for your feedback. This issue should be fixed in the next release of SSMS.
Posted by Mike C_1 on 7/20/2010 at 2:02 PM
Here are some related issues:

https://connect.microsoft.com/SQLServer/feedback/details/301073/msft-edw-tools-management-studio-use-read-committed-isolation-level-to-retreive-metadata-hence-frequently-blocked-on-data-warehouses [Active]

https://connect.microsoft.com/SQLServer/feedback/details/576780/openquery-blocks-catalog-view-access-lock-request-timeout# [Active]

https://connect.microsoft.com/SQLServer/feedback/details/324835/msft-bicoe-csat-object-explorer-throws-lock-request-timeout-error-while-accessing-objects-in-a-database-if-any-of-the-table-is-being-loaded-from-linked-server [Closed as not Reproducible]
Posted by Mike C_1 on 7/20/2010 at 1:50 PM
Ran into this issue with large SELECT INTO statements.
Posted by Jason Kresowaty on 10/3/2009 at 2:30 PM
Better yet, if the database supports snapshot isolation, SSMS should make use of it.