Why can't I open SQL Management Studio after my users edit their data.

Question

My program allows users to view and edit their data online, and admins to download from/upload to my database from their desktop application. Problem is that while an admin is downloading/updating, and for a while after (could be a minute, could be 30 minutes), I can't open SQL Management Studio on the server. Sometimes it won't let me open a table, other times I can't connect and it closes on me, and other times the application won't open at all.

Data can still be viewed/edited online and multiple admins can download/upload simultaneously, I just can't see the data on the server.

Today I just saw that my 2 new webpages that use Membership.GetAllUsers() and Roles.GetUsersInRole(roleName) don't work after the admin does stuff either. They get timeout errors.

Any ideas?

Answer 1

could be a couple of reasons

A) you don't have enough CPU/RAM on the server to open up SSMS while this is being done, remember SQL Server will use all RAM it can grab for its cache

B) maybe a TABLOCK has been issued during the upload/update and then you won't be able to see data until completion of the import/update unless you use NOLOCK

Answer 2

There are a number of things that you could check for.

1. Lingering connections to SQL due to improperly closed connections from the client.
2. Long running query processes, such as "Membership.GetAllUsers()" if you have a lot of users.
3. Network IO limitations
4. Locking on the tables from operations being completed by the administrators
5. Low server resources, preventing you from getting CPU or RAM to run SSMS