I have a database that runs on SQL 2000. The machine and database
have all the server packs. The machine is a quad-core intel machine
with 3GB ram. The machine is also a domain controller (windows 2003
"pdc")

The database is the back end to an access application 2000 application
connecting via linked tables in Access over ODBC.

The database has about 40 users.

I need to restart the database server instance daily because the
memory that sqlservr.exe uses climbs up to about 1.7gb. When that
happens, locks develop on some tables and people experience timeouts
and lockups.

We've not made any changes to DB schema or the application that uses
the database. This began about 3 weeks ago and I'm at a loss to
explain why this happens. All the tables that get locked up have
indexes and or primary keys. I rebuilt them all to no avail.

I moved the database to a different server (windows 2003, bdc, 2gb
ram, dual core) and the DB is puring like a kitten. No issues and
memory holding steady at about 600-800mb. This is normal for this
application.

Silly question: is 4 cores too much LOL?? I can't think if anything
that would cause a problem like this and since I moved the application
and found that it works fine on an older, "lesser" machine I'm
thinking that the performance is in the server config somewhere??

Elijah,

By default, SQL Server will use all available memory. But if the server
is not dedicated for SQL Server (as in your situation), then this can
cause memory to be paged out to disk, causes serious performance
problems.

So you should check the memory settings (SQL Server Enterprise Manager,
request Properties of the server, go to tab Memory), and set the maximum
memory to considerably less than 1.7 GB. For starters, set the max to 1
GB. After a few days, check what the available memory is (memory still
available to Windows). If it is less than 30 MB, then lower the max. If
it is more than 100 MB, then increase the max.

Good luck,
Gert-Jan


Elijah wrote:

Elijah (raz230 (AT) gmail (DOT) com) writes:
Having SQL Server on a PDC is not best practice.

That is perfectly normal. SQL Server likes memory and caches as
much as it can, since reading from disk is much slower than reading
from memory.

It's unlikely that the memory has anything to do with it, but you could
do DBCC DROPCLEANBUFFERS to flush the cache entirely. If this has any
positive effect, there is some connction. More likely it only make
things worse.

Most likely you have have some queries that could be better written, or be
served from some better indexes. Since SQL Server uses a cost-based
optimizer query plans can change, even if you make no changes to the
database as data volumes grow and statistics change.

To find slow queries you can use Profiler with some good filter for
duration. To resolve blocking, there is a tool on my web site,
aba_lockinfo that you can use to analyse the blocking situations:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thank you all:

@Erland.
Yeah, I'm aware that the server shouldn't be on a PDC. It's not
really my choice so I'm trying to make do.

@All
The funny thing is that the DB is working fine on the lower-end
system. Certainly there are some bad queries and I'm working on
identifying those with the profiler.