November 8, 2010 at 9:39 am
we are using a SQL server 2005 SP3. i have been monitoring the pagelife expectancy and buffer cache hit ratio .the cache hit ratio is
maintaining around 99 but the page life expectancy is very very low around 10 to 12 on an average i guess it should be >300secs.
could this be solved you increasing RAM .Total system RAM is 3,50GB .CPU PF USAGE 2.45GB
We have a lot of locking going on and also query time out..could this be because of LOW PLE.
November 8, 2010 at 10:29 am
You may find this article useful.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 8, 2010 at 10:44 am
Typically if you're seeing low PLE, it means your cache is being cleared out very frequently. Adding more RAM does absolutely help this, but your underlying problem might still be there (if there is one).
If you have insufficient indexes, queries may be reading more data than they need to (doing a clustered index scan caches a lot of data for example, when a nonclustered index seek might have done the job just fine and faster). You also might have excessive query plan recompilations. Try the usual performance tuning first...trace all queries, see which ones are taking the longest/using the most IO, and try to work on those by either reindexing, adding proper covering indexes, etc.
November 8, 2010 at 11:17 am
It's not necessarily a problem. It's just another indicator. I'd see if you have other indications of performance problems, excessive waits or queues, long running queries, that sort of thing. If you have other indicators that support this one, then you dig into the areas where you're hitting issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 8, 2010 at 11:30 am
snoop123 (11/8/2010)
we are using a SQL server 2005 SP3. i have been monitoring the pagelife expectancy and buffer cache hit ratio .the cache hit ratio ismaintaining around 99 but the page life expectancy is very very low around 10 to 12 on an average i guess it should be >300secs.
could this be solved you increasing RAM .Total system RAM is 3,50GB .CPU PF USAGE 2.45GB
We have a lot of locking going on and also query time out..could this be because of LOW PLE.
This sounds like a hard server but just to confirm, is this a physical server, or virtual? 3.5 gigs is really small these days.
Query time outs are possible with a low PLE, yes, but I'd look more to query optimization then the PLE directly. The PLE is more likely a symptom then the cause.
The locking is not directly related. Though, with that much pressure, it might be holding locks longer to be able to get the job done.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 9, 2010 at 7:06 pm
hi thanks for you reply actually there has been a huge amounts of I/o waits ,long running queries and also query timeout ....so i guess was may be an increase in RAM might help the issue ..
November 9, 2010 at 7:51 pm
snoop123 (11/9/2010)
hi thanks for you reply actually there has been a huge amounts of I/o waits ,long running queries and also query timeout ....so i guess was may be an increase in RAM might help the issue ..
Wow. That system is under incredible pressure. This isn't something we'll be able to diagnose over the internet. I would start by double checking the SAN for starters and making sure you didn't lose a drive or two in the RAIDs, especially if they're RAID 5. Why? Because if this is a 'sudden change', I usually double check the hardware *first*.
After that, and yes, I know it's going to add pressure, but run a 1 hour trace looking for your worst offending queries. To determine that, you'll want to multiply # of occurences by the statistics you end up with. A 5 second query run 5000x is more important than a 2 minute query run once at this point.
You're going to have to bleed the pressure off the system, and to do that you'll have to locate the root cause.
This will sound cruel, but this is what you hire top end consultants for. You may be out of your depth on this one. There is a LOT of moving parts to get into when you've got that many things going on simultaneously.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply