From: Elijah on
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??






From: Gert-Jan Strik on
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:
>
> 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??
From: Erland Sommarskog on
Elijah (raz230(a)gmail.com) writes:
> 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")

Having SQL Server on a PDC is not best practice.

> I need to restart the database server instance daily because the
> memory that sqlservr.exe uses climbs up to about 1.7gb.

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.

> 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.

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: Elijah Campbell on
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.