Sluggish performance
in [Using MS SQL Server]
|
From: Elijah on 19 Jul 2010 09:02 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 19 Jul 2010 16:18 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 19 Jul 2010 17:36 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 20 Jul 2010 12:03 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.
|
Pages: 1 Prev: publishing spatial data Next: Tracking CAL on Server (MSSQL2005) |