SQL 2000 Performance / Locking / Tweaks?

[C0PP3R]

Hello All,
I am new to SQL server but not database servers. I worked with Pervasive
SQL for years and I have a couple of questions as our current company is
having some problems.

We seem to be having server speed issues and I cant figure out why. Here is
the setup:
Dual Intel 3 gig Xeons (Shows up as 4 processors because of Hyper threads), 4
Gig RAM
RAID 0 for Transaction Logs, RAID 5 for Database files, IDE HD for Windows
and tempdb
Page File is on the RAID 0
Windows 2003 Server Standard Edition with latest updates
Sql Server 2000 Enterprise with latest updates

We have 60 Users accessing VB front end programs that all access a sql
database on this server.
The current size of the database is 3 gigs

Is there anything I should look at for getting this thing to speed up? It
is really slow on updates and data transactions.

For some reason no matter what I do when I configure SQL to take more memory
it never does. I cannot get it to take past 1.7 gigs.


Also we are experiencing problem with locks in the system. Some processes
take a very long time to complete (hence the speed question ) and when
they are running they lock the tables. When they lock the tables of course
the vb interfaces and stored procedures will not run.

We have found that putting no lock helps out a bit but to go and change all
of the code will be impossible because our vendor will not provide their
source code.

I know in pervasive sql I could take off opportunistic locking which would
stop these issues. I do not see any settings in SQL for this. Row locking
would be ideal because other people can access they same table correct?

Sorry for the book but I want to provide the most information possible.

Thanks!
Rob.

 

[MDXer]

as far as the memory usage have you applied the /3gb or AWE flags to the server?

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

You'll need the AWE flag in the OS, and to make sure that AWE is enabled in sp_configure.

Run a trace on the SQL Server using Profiles and biggest queries. Check the execution plan for these queries. The vendor tables could probably use some index tuning or rebuilding.

By default SQL Server runs all transactions in the "READ COMMITTED" isolation level. No lock is the same as setting the isolation level to "READ UNCOMMITTED". Unfornitually I don't know of a way to change the default isolation level. You'd have to modify this within your code, or via the settings in the ADO connection within the app.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)