Why can't we access SQL Server when a long query is running in Management Studio?
It would be impossible to say for sure without examining your web application and the SSMS query.  My guess, however, is that the long query is locking tables which the web page wants to read, so the page has to wait for the query to finish.  The page's timeout occurs before this happens and the page fails to load.

The concepts you are dealing with are locking and concurrency.

There are several ways to get the number of rows in a table.  Some are more accurate than others; some are faster.  This post on Stack Overflow may give you some ideas to work with.



Response to comment: a select count(*).. query will read every page of the table.  Since the SSMS statements are performing updates they will have exclusive locks on some pages and prevent the count(*) from proceeding.  This is exactly what I guessed was happening.

Some options are:


Read the post from Stack Overflow and decide if any of those options would be appropriate for you.  There are ways of getting the row count without touching the data tables.
Batch the updates so you only update, say, 1000 rows before you commit the transaction.  The commit will release locks and allow the counts to proceed.
Add WITH (NOLOCK) to the counting queries, described here.
Explicitly declare an appropriate isolation level for the web page's  queries.
Change the default isolation level for the SQL Server instance.  NOTE WELL - this will have MAJOR implications for ALL activity on the instance and should only be done after CAREFUL, DETAILED discussion with an EXPERIENCED SQL Server DBA.