Resource locking in SQL Server Management Studio

views:

answers:

Resource locking in SQL Server Management Studio

I've noticed that when viewing query results in a grid in SQL Server Management Studio, it often causes resource locking on the server, preventing other queries from running. Why does this happen, when I'm only opening the table for viewing and not for editing? Disconnecting the query window from the server releases the resources. I assume it doesn't happen when I return the results as text, but I haven't tested this yet.

What is the best practice for using query windows in SSMS?

What type of "resource locking on the server"? Running a SELECT query will hold read locks (that is normal behaviour).

Read locks do not prevent other simple reads (SELECTs) from occurring.

Do you have other transactions occurring than have a SERIALIZABLE isolation level?

Mitch Wheat 2010-09-28 01:35:29

Yes, it's only a SELECT query, so I imagine we're talking about read locks, which are preventing other SELECT operations. Unless a read operation is part of a transaction, what is the point of locking the records returned? It would make more sense to release the records immediately.

Billious 2010-09-28 01:48:17

OK, I think I just made an idiot of myself. I forgot that what I was testing wasn't a simple SELECT procedure, but that it actually built results in a public temporary table. Somehow, although the query window had returned results, the transaction had actually failed and was locking the temporary table, thus preventing the same procedure from being run elsewhere. Why a public temporary table? Part of the procedure dynamically builds and executes a SQL string which generates the table, so a normal temporary table wouldn't work. Thanks for your help - I'll accept your answer for your trouble!

Billious 2010-09-28 02:23:17

@Billious: no problem. It is easily done! I'm sure I've done it many times! ;)

Mitch Wheat 2010-09-28 03:14:35

ansaurus

tags:

views:

answers:

Resource locking in SQL Server Management Studio

related questions