I recently discovered that my server is doing a lot of paging (as
measured by the 'Memory: Pages/sec counter') and I'm not sure why. Each
day around midnight paging goes from near zero in to the several
hundreds. It stays at that level until about 9 or 10 AM and then drops
down to near zero. This cycle is repeated every day.

I verified that this wasn't caused by our backups by moving them from
midnight to 6:00 PM. We still see the paging spike at midnight. There
are no other SQL Agent jobs that run around this time. This is a
dedicated SQL Server box so there are no other applications running
(except for things like the SAN management software and things like
that)

I set up a perfmon counter to capture the 'Process: Page Faults/sec'
hoping that I would see a particular process' page faults spike around
midnight but I don't see any such increase.

This is SQL Server 2000 Enterprise Edition sp4 on Windows 2000 Advanced
Server with sp4. The server has 6GB of RAM with 5GB allocated to SQL
Server via AWE.

Ant help would be appreciated.

Thanks!

Hi

Backups will be a very disc intensive operation, both reading and writing.
Make sure that the backup is to a different disc (preferably multiple
spindles) than the data and log files (which should also be on different sets
of spindles). Also check the LUN mapping to make sure that you don't have
overlapping LUNs and that the SAN is configured correctly to handle this high
throughput. Also check that you have correctly alligned allocation units.
Make sure that if you have SAN replication that this is not throttled to an
extent that could impact performance, you may want to turn (temporarily)
replication of an see what effect it is having.

Backing up to multiple files may also help especially if they are also on
different discs.

I assume that these are full backups? You could also consider using
differential backups if the additional recovery time is acceptable, although
if alot of things have changed since the last full backup then you are less
likely to gain as much benefit.

You may also want to look at increasing the memory in the machine.

Other things to check would be things like virus checkers are not impeding
the performance.

John


Show quoteHide quote

John,

Thanks for your reply but as I mentioned in my post I have verified
that the backups are not the cause of the paging because I moved them
to a different time of day but the paging still occurs at midnight each
day. Any other ideas?

Thanks

Hi

Sorry, I miss read that.

Are any other disc intensive jobs occurring such as reindexing at this time?
Do you have any other systems that extract large amounts of data?

You may want to create a server side trace to cover that period and see what
is running. With SQL 2005 you could import the permon stats into SQL Profiler
and see what is being run when the couters are high.

If the SAN is shared someone else's activity may be causing you problems if
the LUNS conflict. Also check out other couters such as CPU.

John

Show quoteHide quote

except keyword
Drop table , procedure Problems
limited permissions on test server for developers
Spid checker query help
Low Disk Space
Delete records in a table with 15 dependencies
how to remove the sql server registry mess?
Dynamic SQL or Table Variables??
CHECKING the TRANSACTION LOG
Installed SQL2000 SP4 successfully but still shows RTM as installe