[THIN] Re: OT- Perfmon on SQL
- From: Henry Sieff <hsieff@xxxxxxxxxxxx>
- To: "'thin@xxxxxxxxxxxxx'" <thin@xxxxxxxxxxxxx>
- Date: Mon, 6 Dec 2004 10:07:21 -0600
The important thing to realize is how many things contribute to the
performance of SQL server:
For memory -
Memory: Available Mbytes
Memory: Pages/Sec
SQLServer: Memory Manager - Total Server Memory (KB)
SQLServer: Buffer Manager - Free Pages counter
SQLServer: Buffer Manager - Buffer Cache Hit Ratio (most important single
one)
Process: Working Set
Process: Page Faults/sec
The Free Pages should be above 640 (640 * 8kb = about 5 MB of free pages in
the buffer, less than that means that you either have too little physical
RAM or you have allocated too much to SQL server to begin with.
The Buffer Cache Hit Ratio should be above 90 % for online transaction
processing. Its the percentage of requests that can be sastisfied from the
cache without reading to disk. Consitently below this, and you either need
to allocate more memory to SQL server, or if the Free Pages are below 640
already, add more RAM to the server.
The total server memory tells you how much RAM SQL server itself is
currently using.
High Pages/Sec shows that SQL server is starving other apps on the server.
If you use SQL Agent to launch jobs, they run outside of the SQL server
threads, so keep an eye on this. If this is high, you need to lower
allocation of memory to SQL server or increase amount of RAM.
HTH. Most of this is from past work experience and the MS Press SQL Server
2000 Performance Tuning Book by Whalen, et al, which is a very useful book.
Henry
> -----Original Message-----
> From: thin-bounce@xxxxxxxxxxxxx [mailto:thin-bounce@xxxxxxxxxxxxx]On
> Behalf Of Adam.Baum@xxxxxxxxxxxxxx
> Sent: Monday, December 06, 2004 9:27 AM
> To: thin@xxxxxxxxxxxxx
> Subject: [THIN] OT- Perfmon on SQL
>
>
> Hi All,
>
> I'm having some performance issues with a SQL server. I
> tried to run the
> usual perfmon stats (CPU, MEM, DISK q length), but nothing
> show up out fo
> the ordinary. I've noticed that running memory stats is
> pointless because
> SQL grabs X amount of RAM on startup. What should I look for
> to help me
> isolate the bottleneck?
>
> Adam
>
> ********************************************************
> This Weeks Sponsor Activaeon.com
> Reduce licensing costs with activAeon XA and
> get one month completely free.
> http://www.activaeon.com
> **********************************************************
> Useful Thin Client Computing Links are available at:
> http://thin.net/links.cfm
> ThinWiki community
> http://www.thinwiki.com
> ***********************************************************
> For Archives, to Unsubscribe, Subscribe or
> set Digest or Vacation mode use the below link:
> http://thin.net/citrixlist.cfm
>
********************************************************
This Weeks Sponsor Activaeon.com
Reduce licensing costs with activAeon XA and
get one month completely free.
http://www.activaeon.com
**********************************************************
Useful Thin Client Computing Links are available at:
http://thin.net/links.cfm
ThinWiki community
http://www.thinwiki.com
***********************************************************
For Archives, to Unsubscribe, Subscribe or
set Digest or Vacation mode use the below link:
http://thin.net/citrixlist.cfm
Other related posts:
