front end, SQL server 2000 back end, and about 15 workstations. The blocking
statement is a stored procedure that is called roughly every 15 seconds to
update a waiting list ( this is a medical database). Eventually the blocks
spread to most of the connections and we get connection time out problems
with all the work stations. I have temporarily resolved the spread issue by
using sql guard 2000 which identifies the blocking process and kills it, so
at least the problem then only applies to one workstation. When a block
occurs, in Enterprise Manager I note that it does say that there is 1 open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - however
the SP is only a select statement( select on only one table), and returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run the
index wizard on a profile trace that includes a lock up and no re indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that since
this is a server side problem that the front end recordset type that is
returned may well be irrelevant.
Thank you in advance
Roddy ReidThis is a multi-part message in MIME format.
--=_NextPart_000_05EE_01C3761E.D3AC0B70
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
As a quick fix, you can change the SELECT in the proc to use the NOLOCK =hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in message =news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 seconds =to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread issue =by
using sql guard 2000 which identifies the blocking process and kills it, =so
at least the problem then only applies to one workstation. When a block
occurs, in Enterprise Manager I note that it does say that there is 1 =open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_05EE_01C3761E.D3AC0B70
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:
SELECT
=*
FROM
MyTable WITH =(NOLOCK)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roderick Reid"
--=_NextPart_000_05EE_01C3761E.D3AC0B70--|||This is a multi-part message in MIME format.
--=_NextPart_000_0010_01C37634.04010AE0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
fyi...
the Index Tuning Wizard is almost worthless. Did I say that outloud? In =fact it's more than worthless since it often tricks non-experts into =thinking that additional indexes really wouldn't help. The wizard misses =a lot...
hard to diagnose remotely... but I did want to make sure you knew that =the wizarad doesn't guarantee that all the indexes are really there...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:edFyQBkdDHA.2816@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the SELECT in the proc to use the =NOLOCK hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in =message news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 =adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 =seconds to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread =issue by
using sql guard 2000 which identifies the blocking process and kills =it, so
at least the problem then only applies to one workstation. When a =block
occurs, in Enterprise Manager I note that it does say that there is 1 =open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run =the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that =is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_0010_01C37634.04010AE0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
fyi...
the Index Tuning Wizard is almost worthless. Did I =say that outloud? In fact it's more than worthless since it often tricks =non-experts into thinking that additional indexes really wouldn't help. The wizard misses =a lot...
hard to diagnose remotely... but I did want to make =sure you knew that the wizarad doesn't guarantee that all the indexes are really there...
-- Brian MoranPrincipal MentorSolid Quality LearningSQL Server MVPhttp://www.solidqualitylearning.com">http://www.solidqualitylearn=ing.com
"Tom Moreau"
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:
SELECT
=*
FROM
MyTable =WITH (NOLOCK)
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roderick Reid"
--=_NextPart_000_0010_01C37634.04010AE0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0064_01C3764D.E30C7670
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I have to agree with Brian on that assessment. I stopped using the ITW =a long time ago.
--
Andrew J. Kelly
SQL Server MVP
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message =news:%23S7E6WldDHA.1712@.tk2msftngp13.phx.gbl...
fyi...
the Index Tuning Wizard is almost worthless. Did I say that outloud? =In fact it's more than worthless since it often tricks non-experts into =thinking that additional indexes really wouldn't help. The wizard misses =a lot...
hard to diagnose remotely... but I did want to make sure you knew that =the wizarad doesn't guarantee that all the indexes are really there...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:edFyQBkdDHA.2816@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the SELECT in the proc to use the =NOLOCK hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in =message news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 =adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 =seconds to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread =issue by
using sql guard 2000 which identifies the blocking process and kills =it, so
at least the problem then only applies to one workstation. When a =block
occurs, in Enterprise Manager I note that it does say that there is =1 open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, =and
frankly I cannot see why it should be locking this table. I have run =the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that =is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_0064_01C3764D.E30C7670
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I have to agree with Brian on that assessment. I stopped using the ITW a long time ago.
-- Andrew J. KellySQL Server MVP
"Brian Moran"
fyi...
the Index Tuning Wizard is almost worthless. Did I =say that outloud? In fact it's more than worthless since it often tricks =non-experts into thinking that additional indexes really wouldn't help. The wizard =misses a lot...
hard to diagnose remotely... but I did want to =make sure you knew that the wizarad doesn't guarantee that all the indexes are =really there...
-- Brian MoranPrincipal MentorSolid Quality LearningSQL Server MVPhttp://www.solidqualitylearning.com">http://www.solidqualitylearn=ing.com
"Tom Moreau"
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:
SELECT
=*
FROM
MyTable =WITH (NOLOCK)
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roderick Reid"
--=_NextPart_000_0064_01C3764D.E30C7670--|||This is a multi-part message in MIME format.
--=_NextPart_000_0AB5_01C376B1.528BABC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom,
Doesn't NOLOCK give out dirty reads ... This under the situation =discussed would mean that the data viewed may not be correct ... I guess =if the user would not like to have consistency of data then the solution =would be fine. -- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:edFyQBkdDHA.2816@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the SELECT in the proc to use the =NOLOCK hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in =message news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 =adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 =seconds to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread =issue by
using sql guard 2000 which identifies the blocking process and kills =it, so
at least the problem then only applies to one workstation. When a =block
occurs, in Enterprise Manager I note that it does say that there is 1 =open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run =the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that =is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_0AB5_01C376B1.528BABC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Tom,
Doesn't NOLOCK give out dirty =reads ... This under the situation discussed would mean that the data viewed may not be =correct ... I guess if the user would not like to have consistency of data then =the solution would be fine.
-- HTH,Vinod KumarMCSE, DBA, MCADhttp://www.extremeexperts.com<=/DIV>
"Tom Moreau"
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:
SELECT
=*
FROM
MyTable =WITH (NOLOCK)
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roderick Reid"
--=_NextPart_000_0AB5_01C376B1.528BABC0--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C3769F.F2448400
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Of course. I was suggesting this as a "quick fix". As always, it =really depends on the app. If all they are doing is browsing and =inconsistent data is tolerable, then you're fine. Alternately, you can =use the READPAST hint. This way, you skip the ones that are currently =locked.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message =news:uE2XBModDHA.1128@.tk2msftngp13.phx.gbl...
Tom,
Doesn't NOLOCK give out dirty reads ... This under the situation =discussed would mean that the data viewed may not be correct ... I guess =if the user would not like to have consistency of data then the solution =would be fine. -- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:edFyQBkdDHA.2816@.TK2MSFTNGP10.phx.gbl...
As a quick fix, you can change the SELECT in the proc to use the =NOLOCK hint:
SELECT
*
FROM
MyTable WITH (NOLOCK)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roderick Reid" <roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote in =message news:bjikqp$7pd$1$830fa79d@.news.demon.co.uk...
I would be grateful for any help on a blocking problem. Access 2000 =adp
front end, SQL server 2000 back end, and about 15 workstations. The =blocking
statement is a stored procedure that is called roughly every 15 =seconds to
update a waiting list ( this is a medical database). Eventually the =blocks
spread to most of the connections and we get connection time out =problems
with all the work stations. I have temporarily resolved the spread =issue by
using sql guard 2000 which identifies the blocking process and kills =it, so
at least the problem then only applies to one workstation. When a =block
occurs, in Enterprise Manager I note that it does say that there is 1 =open
transaction present on the blocking SPID ( see later)
I appreciate that this may not supply enough information - =however
the SP is only a select statement( select on only one table), and =returns a
read only, static ADO recordset. It is not part of a transaction, and
frankly I cannot see why it should be locking this table. I have run =the
index wizard on a profile trace that includes a lock up and no re =indexing
suggestions are made
If anyone could point me in the right direction. I am guessing that =since
this is a server side problem that the front end recordset type that =is
returned may well be irrelevant.
Thank you in advance
Roddy Reid
--=_NextPart_000_000D_01C3769F.F2448400
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Of course. I was suggesting this =as a "quick fix". As always, it really depends on the app. If all they =are doing is browsing and inconsistent data is tolerable, then you're fine. Alternately, you can use the READPAST hint. This way, you skip the =ones that are currently locked.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Vinodk"
Tom,
Doesn't NOLOCK give out dirty =reads ... This under the situation discussed would mean that the data viewed may not be =correct ... I guess if the user would not like to have consistency of data then =the solution would be fine.
-- HTH,Vinod KumarMCSE, DBA, MCADhttp://www.extremeexperts.com<=/DIV>
"Tom Moreau"
As a quick fix, you can change the =SELECT in the proc to use the NOLOCK hint:
SELECT
=*
FROM
MyTable =WITH (NOLOCK)
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roderick Reid"
--=_NextPart_000_000D_01C3769F.F2448400--|||On Mon, 8 Sep 2003 20:19:27 +0100, "Roderick Reid"
<roddy@.roderickreid.UNSP!AMdemon.co.uk> wrote:
> I appreciate that this may not supply enough information -
Right, it's not.
> however
>the SP is only a select statement( select on only one table), and returns a
>read only, static ADO recordset. It is not part of a transaction, and
>frankly I cannot see why it should be locking this table.
Well, me neither. So, it probably isn't.
>I have run the
>index wizard on a profile trace that includes a lock up and no re indexing
>suggestions are made
>If anyone could point me in the right direction. I am guessing that since
>this is a server side problem that the front end recordset type that is
>returned may well be irrelevant.
Look through the profiler trace to see whatever else the blocking SPID
is doing.
I haven't used Access with SQLServer for several years, but I recall
that Access was always very "clever" at doing obscure things with
connections and recordsets that caused these kinds of problems.
Joshua Stern
No comments:
Post a Comment