require additional memory?

Question

require additional memory?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
January 7, 2011 at 3:20 am

#232988

Last 24 Hrs I have monitor for SQL Server performance thru windows performance Tool.
Buffer cache hit ratio - 99 % always - it looks like sql server not extra memory.
Page reads/sec - 0 always - it looks like sql server not extra memory.
But.
Page life expectancy - 160 to 250 always -it looks like sql server not suffecient memory..
I doubt why both counters are good values, and Page life expectancy low values? Please tell me its really required additional memory?
Here attached text report.

Viewing 12 posts - 1 through 11 (of 11 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would first run a script and check for missing indexes. (I'm sure there are SS2K scripts out there...). Most probably a missing index issue as my first wild guess.

As a side note: are you running SQL 2000? (since you posted on an 2K forum). Just want to make sure...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thanks LutzM for reply...

Yes, this is SQL server 2000. I will find the script for missing index in sql 2000.

Could you please share me this missing index script on sql 2000.

Thanks for your help.

ananda

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

ananda.murugesan (1/7/2011)
Thanks LutzM for reply...
Yes, this is SQL server 2000. I will find the script for missing index in sql 2000.
Could you please share me this missing index script on sql 2000.
Thanks for your help.
ananda

Unless there's some undocumented feature in 2000 I don't know about, I don't believe there is a "missing index script" for SQL Server 2000 and I don't believe you could build one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 4

AFAIK there are no missing index info gathered in sql2000.

You'll need to trace for a reference load and have that analysed by database tuning advisor (included in SQL2000).

Then evaluate those results.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

LutzM SSC Guru Points: 107049 More actions · Answer 5

Yep, Tuning Advisor to provide some "hints" is about all that's available with SS2K.

Isn't it amazing, how little time is needed until we forget how hard it was to tune a system?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Just to clarify in SQL2000 it is called 'Index tuning wizard' rather than database tuning advisor

---------------------------------------------------------------------

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 7

Thank you for correcting that George.

Index tuning wizard is the correct name.:blush:

It can be found in SQLserver Profiler toolbar option "tools".

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

ALZDBA (1/9/2011)
Thank you for correcting that George.
Index tuning wizard is the correct name.:blush:
It can be found in SQLserver Profiler toolbar option "tools".

Heh... call it what you want, I actually liked the interface for the old one better.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I would actually start with this.

It will most likely give you the best bang for your time invested :

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

I think what is happening is that you are needing data regularly but the IO system (largely via read-aheads I bet) is getting the data into the buffer pool in time to prevent cache hit ratio from dropping. I agree that either poor queries and/or missing indexes are a culprit here.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

Also (just and FYI), during INSERTs, it could also be too many indexes or an index with extremely low cardinality. For example, any index where the first column has a bit datatype or only a couple of unique values regardless of datatype, will almost guarantee timeouts as the index will have to do multiple extent splits to keep the data in logical order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)