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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

Date: Mon, 3 Jan 2005 09:44:30 -0600

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?
    ... probably not closing a connection in your application somewhere. ... Pro SQL Server 2000 Database Design - ... > massively attack the system with all the apps feature (purchasing, ... >> locks are fine, as long as you don't have lots of users contending for ...
    (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: PLEASE HELP: Problem with security
    ... How do I make sure ASPNET has access to SQL Server? ... ASPNET full access rights to the directory I am running out of. ... So the only way I can get the apps to work is move them ... >> the inetpub wwwroot directory. ...
    (microsoft.public.dotnet.framework.aspnet.security)