Re: A lot of lockings and connections to SQL Server. Is it normal or bad?

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/05/05 

Date: Wed, 5 Jan 2005 12:21:27 -0600

Yeah, I think the telltale sign here is the 80,000 connections. You are
probably not closing a connection in your application somewhere. This can
actually be dangerous if you are using any transactions in your code (not
using transactions is dangerous for other reasons, like data integrity!)

> So, using SQL Server as a backend is not _that_ easy... Oh well, at
> least I know for sure that the problem won't appear in my client for a
> near future.

It isn't that bad though. Just like any code you have to be careful to
clean up after yourself when you connect to and use a resource. Either way,
understanding the need for improvement is step number one, and if the
application is meeting all of your current needs without much troublem,
sounds like you have time to isolate the issues. 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message 
news:e3bYpJv8EHA.2196@TK2MSFTNGP14.phx.gbl...
> Hi Louis:
>
> Thanks for your reply.
>> best that can be said is that you might be fine, or you might have
> written a
>> terrible app (something we all have done at one time or another :)
> Lots of .. [skipped]
> Looks like my case is the second one :(
> As I stated in my post, the application runs fine, but it runs with a
> very minimum load. That's because the company using my apps is a
> machinery trading company. The activity is very low. To give you a clue
> how 'low' is that; they only have to create less than 10 invoices a day.
> Actually, that's the best numbers they got for years (that means less
> than ten sales a day - but, hey, the profit of selling one machine could
> cover three months expenditure!). Anyway, what I did is I backup the
> database, call up some of my fellow, line up eight computers, and
> massively attack the system with all the apps feature (purchasing,
> receive goods, stock preview, stock opname, delivery order, customer
> order, etc...). After an hour we manage to hang the server which by
> then, got about 350,000 locking and 80,000 external connections :(
>
> So, using SQL Server as a backend is not _that_ easy... Oh well, at
> least I know for sure that the problem won't appear in my client for a
> near future.
>
> Regards,
> Willianto
>
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:OCObfsa8EHA.3476@TK2MSFTNGP15.phx.gbl...
>> This is the open question of all times.  Without looking at the code,
> the
>> best that can be said is that you might be fine, or you might have
> written a
>> terrible app (something we all have done at one time or another :)
> Lots of
>> locks are fine, as long as you don't have lots of users contending for
> those
>> locks.  You say you have 8 users, is that all you are planning for?
> If
>> everyone is happy, it might be fine.
>>
>> Bottom line is that you need to get some literature and read up on
> design,
>> internals, optimization, etc before you can decide if your application
> is
>> well written, and experience is a must.  Without being able to
> magically
>> aquire this (and it takes a while and sadly we learn from failure!) I
> would
>> suggest that you take this statement:
>>
>> > Whilst the apps so far running fine, it's never been really tested
> to
>> > the max.
>>
>> Define what max means and test for it.  If it works fine under max
>> conditions, then your application is fine.
>>
>> --
>> ----------------------------------------------------------------------
> ------
>> Louis Davidson - drsql@hotmail.com
>> SQL Server MVP
>>
>> Compass Technology Management - www.compass.net
>> Pro SQL Server 2000 Database Design -
>> http://www.apress.com/book/bookDisplay.html?bID=266
>> Note: Please reply to the newsgroups only unless you are interested in
>> consulting services.  All other replies may be ignored :)
>>
>> "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
>> news:OpJil1Z8EHA.2804@TK2MSFTNGP15.phx.gbl...
>> > Hi all,
>> >
>> > First of all:
>> > *** HAPPY NEW YEAR TO YOU ALL!!! :) ***
>> >
>> > then, here it goes; I've downloaded SQL Spy 6.0 from Hybridx (got it
>> > from http://files.webattack.com/localdl834/sql_spy_setup.zip). It's
> a
>> > freeware tools to monitor SQL Server activity. I use this tool to
> watch
>> > how my apps (created with Microsoft Visual FoxPro 8.0 SP1, connect
> with
>> > SQL Server thru ODBC) interact with SQL Server. I found out that on
> some
>> > parts of my apps (where there are lots
>> > of traverse and calculation), the number of locks on the SQL Server
>> > could go up to 700 and the external connection could go up to 15 or
> 20.
>> > And I tested that on my box (that means; I run my apps on the server
> and
>> > no workstation connect to the server).
>> >
>> > Whilst the apps so far running fine, it's never been really tested
> to
>> > the max. The traffic are quite low and the concurrent user has never
>> > exceed eight users. Yet, looking at the numbers, I'm afraid that
> there
>> > something that I don't do effectively.
>> > My Questions:
>> > 1. Is the number normal?
>> > 2. Anybody here can give me advices on C/S programming, I mean just
> a
>> > quick thumb rules what should and what, like should I make a
> connection,
>> > get the data, and disconnect immediately. Or should I share all SPT
> in
>> > one connection?, or any other rule what should and shouldn't?
>> >
>> > Thanks in advance
>> >
>> > Willianto
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: A lot of lockings and connections to SQL Server. Is it normal or bad?
    ... massively attack the system with all the apps feature (purchasing, ... using SQL Server as a backend is not _that_ easy... ... > locks are fine, as long as you don't have lots of users contending for ... >> one connection?, or any other rule what should and shouldn't? ...
    (microsoft.public.sqlserver.programming)
  • Re: A lot of lockings and connections to SQL Server. Is it normal or bad?
    ... This is the open question of all times. ... locks are fine, as long as you don't have lots of users contending for those ... Pro SQL Server 2000 Database Design - ... > parts of my apps (where there are lots ...
    (microsoft.public.sqlserver.programming)
  • Re: eternal lock?
    ... Restarting the apps doesn't solve the problem, ... To wit, if a query times out, and there is a transaction in progress, ... query batch, or started within the query batch that timed out. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Remote Database Connection with PocketPC/MSAccess Front End
    ... SQL Server which Windows Mobile devices can access directly remotely) is ... including the Access Sync Services that your question isn't about. ... I develop MS Access Apps for my clients. ... I also checked out MS SQL server 2005 Express & Compact Edition. ...
    (microsoft.public.pocketpc.developer)
  • Re: Unlocking Record
    ... Assuming you refer to locking as in exclusive locks, ... Only the connection having a log on a resource can un-lock it. ... > Is it possible to unlock a record from directly inside the SQL Server MMC? ...
    (microsoft.public.sqlserver.server)