Locks and Blocking

I am working on solving performance problems for a client experiencing
frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
application is a self-developed VB6 application that is very resource
intensive. With about 150 simultaneous users CPU utilization frequently
exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiring
all users to exit and the server to be rebooted.
Their programming model uses ADO recordsets. Once a recordset is fetched to
the application and displayed on the form, the rsX variable is set to nothing
and the user can update the form. When the OK button is pressed on the form,
the application code does the following:
strSQL = "Select * From Contact Where 1=2"
Set rsX = Openrecordset(strSQL)
Rsx.Field1 = Form!Field1
...
...
Rsx.Fieldn = Form!Fieldn
Rsx.Update
Question: Does the grabbing of a recordset in this manner lock the whole
table since there is no individual record or page to lock? An if 20 users did
this simultaneously, would that lead to a blocking issue?
Any insight would be appreciated!
Larry Menzin
American Techsystems Corp.
These links may help. The first link is a VB link about locking -
looks like you definitely might have some locking issues.
http://msdn.microsoft.com/library/de...oidlocking.asp
http://www.sql-server-performance.co...cing_locks.asp
http://msdn.microsoft.com/library/de...on_7a_1hf7.asp
|||Do each of the tables have a valid PK Constraint defined on them? Is the
Update issued by ADO using the PK to do the update? If not it is more
likely the Update is causing the problems. That is a pretty lame way to
update rows anyway. They should create a stored procedure to do the update
and call that from the front end instead.
Andrew J. Kelly SQL MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...
>I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization,
> requiring
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched
> to
> the application and displayed on the form, the rsX variable is set to
> nothing
> and the user can update the form. When the OK button is pressed on the
> form,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users
> did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.
|||Hi,
When u execute a select statement it will fetech all the columns and
rows now imagine if the table has 40 or 45 coulmns then how much
resource it will use .
u should field name and ur where clause.
Secondly when the recodset work is done close the recordset.
hope this help
from
killer
Andrew J. Kelly wrote:[vbcol=seagreen]
> Do each of the tables have a valid PK Constraint defined on them? Is the
> Update issued by ADO using the PK to do the update? If not it is more
> likely the Update is causing the problems. That is a pretty lame way to
> update rows anyway. They should create a stored procedure to do the update
> and call that from the front end instead.
> --
> Andrew J. Kelly SQL MVP
>
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...
|||Hi
Run profiler and see what/where this is happening. You may want to look for
items in the locking event class and use the read/write/duration values to
find queries that take a long time and do a large number of I/O. You will
then be able to analyse query plans and indexes, or maybe even want to pass
the output of a trace into the index tuning wizard and see what it comes up
with.
John
"Larry Menzin" wrote:

> I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiring
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched to
> the application and displayed on the form, the rsX variable is set to nothing
> and the user can update the form. When the OK button is pressed on the form,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.