November 19, 2007 at 4:15 am
I've had poor performance for some time now but it seems to be getting worse. I've actually taken to rebooting the box each night as this improves things, but that surely shouldn't be necessary for a server.
The hardware is a Dual 3.1 Pentium 4 with 2GB of memory.
I'm looking for advice (firstly) on appropriate SQL memory settings etc.
The fact that performance deteriorates after a couple of days (or at least to before reboots) makes me wonder about swapfiles etc.
Any advice hugely appreciated.
November 19, 2007 at 10:08 am
Are you autoshrinking any of the databases (including tempdb) it could be that the server is having to re-create the space which eventually fragments the disk.
Also what version of SQL Server are you running? Have you run any performance monitors on the machine to see whether it is I/O memory or CPU being hit hard?
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 20, 2007 at 3:16 am
I shrink the database once a week, and truncate the log.
I haven't done anything with the tempdb. At this stage (10:00 am) the mdf is only 8mb and the ldf 4mb, which doesn't strike me as particularly big. (The main db is nearly 9GB).
I did run a disk defrag a month or so ago and I don't think fragmentation is the problem.
I'm running Sql Server 2000 - 8.00.2039
I started looking at SQL Profiler yesterday - and it's a can of worms!
Incidentally, one of my problems is the first job I run on logging in takes 5+ seconds the first time it runs and <1 subsequently. How can I flush the cache to test (and improve, hopefully) first time performance.
Many thanks.
November 20, 2007 at 4:54 am
You can use
DBCC DROPCLEANBUFFERS
Remarks
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.
and
DBCC FREEPROCCACHE
Remarks
Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.
November 20, 2007 at 8:39 am
Thanks Jo.
That's very interesting! Aside from general performance, I have one specific problem; the first time users log on, it takes upwards of 30 seconds to return an RS report. I had assumed this was the db (there's a new call to the db to find reports the user has access to etc.).
Turns out the new call _always_ comes back in under 1 sec.
If I log on as a user, it can take 30 seconds to return the first report.
If I subsequently log on as any user the first report is returned in 1-2 seconds, which is what I expect. (even though it may be a different report, showing different data).
Something is being cached somewhere! Stuffed if I know where though, as it behaves differently in the dev environment.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply