Solved

SQL SERVER 2000: low page life expectancy, high cache hit ration

Posted on 2010-09-02
5
794 Views
Last Modified: 2012-05-10
Hi, I have a 32 BIT sql 2000 server with a database of +-300 GB. (AWE not enabled) on a IBM blade server HS21
We often have performance drops. ( large query can take from 10 till 300 sec).

When i look at the statistics is see a low pagelife expectancy.

What are the counters i should look for to exactly find my bottleneck?

Will enabling the AWE option and using all the physical ram (8GB) be a solution?

in attachment an example of values.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                                                                              

example.xls
0
Comment
Question by:capsugel
5 Comments
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 33589484
Hi,
I don't really see a problem with 10 to 300 seconds with complex queries against a +300 GB DB, but if you're saying there's a drop in performance, that means you're been looking at  stats earlier.

anyway you're a large DB which less than 3% of it will fit in the cache, so I would say YES AWE will help, Especially you're using Enterprise edition so take full advantage of it.

about the counters, I would suggest to watch that video about Perfmon and counters, it's a good one

http://www.brentozar.com/sql-server-training-videos/perfmon-and-profiler-for-sql-server/


so I would recommend:

Step 1: Enable PAE support on Windows Server to allow large segment of physical memory to be used.

Step 2: Assign to enable Lock Pages in Memory permission to SQL Server account.

Step 3: Enable AWE Option:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

Restart SQL Server with the following commands:

net stop mssqlserver
net start mssqlserver

Then, configure memory:

sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 8192
RECONFIGURE
GO

hope that helps :)
 
0
 
LVL 7

Expert Comment

by:lundnak
ID: 33591070
Look at your CPU consumption when the performance is terrible.  (Open Task manager or use Process Explorer from SysInternals).
Look at the SQLServer.exe process.  If it is high, then I bet you have some bad queries.
Open Enterprise manager (or SSMS) and run sp_who2.  Look for any queries that with high I/O counts.

Also, following HuSlayer's steps to upgrade the memory would help also.

Normally, major drops in performance are often caused by high-CPU utlization.  High CPU is often caused by High I/O queries or lots of blocking.
0
 

Author Comment

by:capsugel
ID: 33593882
HI, thanks a lot for the tips.

When the performance is dropping, i don't see any significant values in my monitoring tool (which is IDERA diagnostics manager), this is why it's so difficult for me to tackle this.

The CPU stays well below 20%, i only see spikes in my disk queue length up to 240 on my E drive (which are 2 mirrored disk, and my tempdb is on this.)

At busy times i have +- 11000 compilations on avg is this a lot?

The performance drop is mainly seen by a very complex query which does +-300000 reads in different local databases. but when this occures all other queries are slower too.
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 125 total points
ID: 33595654
<<Will enabling the AWE option and using all the physical ram (8GB) be a solution?>>
Hard to say but you should assign only 6 GB to SQL and not all of it...

You may want to put a perfmon trace on the following:
> Physical Disk:Avg sec per read/write --> A value above 20 may be a cause
> Table Scans /sec --> to see if you have operations taht are slowing down all others

<<At busy times i have +- 11000 compilations on avg is this a lot?>>
Yes that is a lot...I t means you server does not cache much...I suggest you to identify the processes causing the bulk of the compilations and wrap them up into stored procedure to reduce their memory footprint...

Hope this helps...
0
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 125 total points
ID: 33595688
At busy times i have +- 11000 compilations on avg is this a lot? YES

Read the 2nd post here about cache and how it's calculated  here


Also something else very important. read about SQL cache plans Here

Hope that helps
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBScript Write Column Headers 3 38
Passing Parameter to Stored Procedure 4 24
SQL Syntax: How to force case sensitive query? 2 31
SQL Count issue 24 16
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question