Enterprise Manager Locks Table

We have just moved a SQL Server database to a new server and since doing
this I have found that a lock is placed on a table whenever it is being
viewed in Enterprise Manager until the table is closed. This has the effect
of stopping our main program from updating the table whislt it is open in
Enterprise Manager. If the table is viewed by including WITH(NOLOCK) in the
select statment then our main program works okay. If the table is viewed
using SQL Anaylser then the table lock does not remain even with the result
set still open. I am not sure if this happens on all tables but the table in
question contains 125,000 rows.
I was not aware of this behavour before (when the database was on another
server) is this normal and is there a way of forcing Enterprise Manager to
include WITH(NOLOCK) by default or is something strange happening?
Thanks
Phil...
Make sure you have proper indexes on the table. Using EM to view data has
never been a recommended way. I have seen the behavior plenty of times when
clients use EM to view the data. Using QA and running regular select
statements is the best way to do that.
Andrew J. Kelly SQL MVP
"Phil Jenson" <phil@.jenson.co.uk.nospam> wrote in message
news:OlyPbPtXFHA.796@.TK2MSFTNGP09.phx.gbl...
> We have just moved a SQL Server database to a new server and since doing
> this I have found that a lock is placed on a table whenever it is being
> viewed in Enterprise Manager until the table is closed. This has the
> effect of stopping our main program from updating the table whislt it is
> open in Enterprise Manager. If the table is viewed by including
> WITH(NOLOCK) in the select statment then our main program works okay. If
> the table is viewed using SQL Anaylser then the table lock does not remain
> even with the result set still open. I am not sure if this happens on all
> tables but the table in question contains 125,000 rows.
> I was not aware of this behavour before (when the database was on another
> server) is this normal and is there a way of forcing Enterprise Manager to
> include WITH(NOLOCK) by default or is something strange happening?
>
> Thanks
> Phil...
>
|||Thanks Andrew.
At least you have confirmed what I susspected. Yes we will be sticking to QA
from know on.
Phil...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23a2iiCuXFHA.612@.TK2MSFTNGP12.phx.gbl...
> Make sure you have proper indexes on the table. Using EM to view data has
> never been a recommended way. I have seen the behavior plenty of times
> when clients use EM to view the data. Using QA and running regular select
> statements is the best way to do that.
> --
> Andrew J. Kelly SQL MVP
>
> "Phil Jenson" <phil@.jenson.co.uk.nospam> wrote in message
> news:OlyPbPtXFHA.796@.TK2MSFTNGP09.phx.gbl...
>
|||this is exactly why many I.T. organizations do NOT install EM on client
workstations.
I've ran into this problem at various work places.
Another Nemesis is MS Access. You'll have the "Power End Users" of your
organization create Linked Tables from Access to your source SQL data and
get all kinds of contention issues.
Cheers,
Greg Jackson
PDX, Oregon
|||Phil Jenson wrote:
> Thanks Andrew.
> At least you have confirmed what I susspected. Yes we will be
> sticking to QA from know on.
> Phil...
>
SQL EM does not fetch all results at once. It fetches on a as-needed
basis, leaving locks on the server. SQL EM is not recommended for this
type of activity, especially in production. You can verify this behavior
from Profiler.
David Gugick
Imceda Software
www.imceda.com