|
Home > Archive > microsoft.public.cert.mcdba > February 2004 > Server Mem Usage, Page Faults
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Server Mem Usage, Page Faults
|
|
|
| My SQL Server 2000 Standard is running on Windows 2000 server with 2
CPUs and 4G RAM. It seems to have performance problem periodically, say
every one or two months.
Following is the statistics. Can you see what is wrong?
-----------------------------------------------
Instance 1 Instance 2
Mem Usage 1,048,036K 1,597,388K
VM Size 1,225,164K 1,719,932K
Page Faults 3,849,535K 667,738K
-----------------------------------------------
Thank you.
| |
| Ray Higdon 2004-02-11, 11:24 pm |
| Do you have your memory set to auto-adjust?
--
Ray Higdon MCSE, MCDBA, CCNA
---
"qluo" <jluost1@yahoo.com> wrote in message
news:402A8F29.9040609@yahoo.com...
> My SQL Server 2000 Standard is running on Windows 2000 server with 2
> CPUs and 4G RAM. It seems to have performance problem periodically, say
> every one or two months.
>
> Following is the statistics. Can you see what is wrong?
> -----------------------------------------------
> Instance 1 Instance 2
>
> Mem Usage 1,048,036K 1,597,388K
> VM Size 1,225,164K 1,719,932K
> Page Faults 3,849,535K 667,738K
> -----------------------------------------------
>
> Thank you.
>
| |
|
| Yes, Memory is set dynamically allocating.
Why the page fault is so large?
Ray Higdon wrote:
> Do you have your memory set to auto-adjust?
>
| |
| Ray Higdon 2004-02-12, 7:24 pm |
| This is from the book "inside SQL Server 2000"
Counter: Pages/sec and Page Faults/sec
These counters watch the amount of paging on the system. As the system
settles into a steady state, you want these values to be 0-that is, no
paging going on in the system. In fact, if you allow SQL Server to
automatically adjust its memory usage, it will reduce its memory resources
when paging occurs. You should find that any paging that does occur is not
due to SQL Server. If your system does experience regular paging, perhaps
due to other applications running on the machine, you should consider adding
more physical memory.
A high number of page faults on a SQL server that is set to autoconfigure
memory might indicate a problem outside of SQL server
--
Ray Higdon MCSE, MCDBA, CCNA
---
"qluo" <jluost1@yahoo.com> wrote in message
news:402B8757.1010302@yahoo.com...
> Yes, Memory is set dynamically allocating.
>
> Why the page fault is so large?
>
> Ray Higdon wrote:
>
| |
|
| The important factor about Page Faults is whether or not
you are looking at Hard or Soft faults.
A Hard fault is one where the SQL has to go to the Hard
Disk to resolve the fault (in other words, the memory is
in the page file and needs to be retrieved). Because a
Hard Fault requires a disk I/O, it's bad for performance.
However, Soft faults are resolved by accessing memory (not
hard disk), because while the required page is NOT in the
working set, it is in memory. Because Soft faults access
memory, they are not bad for performance.
The Page Faults/sec is the TOTAL number of faults, both
Hard and Soft. There is no counter for Soft faults, but
the Pages/sec counter is the number of Hard faults. So
Soft faults is the difference between the two.
I'm guessing the data you are providing has come out of
the "Task Manager". I hope you know that the Page Fault
counter there is the total since the process has been
running, so the value is for the "up time" of your SQL
Server process. Use "PF delta" to get an indcation of how
quickly these faults are occuring. But for the counters I
listed above, you are going to have to get the Performance
Monitor going. All the counters I mentioned above are in
the Memory object.
SQL Server does and will generate a lot of Soft page
faults (if you're giving the DB a good workout). You are
running into trouble if you generating loads of Hard
faults, depending on the type of application and queries
you are running. Let's face it, if you have an OLAP
application and your query requires 2Gb, of data, you're
going to get faults.
Now, if the problem isn't Hard faults, you need to look
elsewhere for the problem. How do you resolve your
occurances of poor preformance? Are you running an OLTP or
OLAP application? It is always at the same time of day?
What is the hard disk configuration ? What languages or
tools are used to query the database?
Access has been a problem for us, as clients would write
there own (very poor) Access reports, which would hold
share table locks for a long time. Start up Profiler and
check for long running queries and/or long waits for
database locks.
Another good counter to look at the disk queue length of
the physical disks. This was a common problem with a lot
of our clients, who kept installing SQL Server on RAID 5
arrays for a OLTP application.
|
|
|
|
|