SQL Server 2000 + Reporting Services (on Windows 2003) Performance

Question

Post reply

SQL Server 2000 + Reporting Services (on Windows 2003) Performance

Stephen Fletche

SSC Enthusiast

Points: 103
More actions
November 19, 2007 at 4:15 am

#119569

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Peter Gadsby SSCrazy Points: 2718 More actions · Answer 1

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

Stephen Fletche SSC Enthusiast Points: 103 More actions · Answer 2

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.

Jo Pattyn SSC-Dedicated Points: 31672 More actions · Answer 3

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.

Stephen Fletche SSC Enthusiast Points: 103 More actions · Answer 4

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.