October 4, 2013 at 1:53 pm
I mean the normal dictionary definition of the word.
An OLTP system does not automatically have low PLE from being a OLTP system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2013 at 1:58 pm
Hmmm, would it not depend on frequency? If you inserted 5000 rows into 100 pages per second then they wouldn't be discarded right away on account of the LRU algorithm?
Or does it take into account a frequency of calls to a page & automatically pin pages for longer?
October 4, 2013 at 2:49 pm
Dird (10/4/2013)
If you inserted 5000 rows into 100 pages per second then they wouldn't be discarded right away on account of the LRU algorithm?
Why would a page that's just been used be discarded by a least-recently-used algorithm (which isn't exactly what SQL uses)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2013 at 2:56 pm
Exactly, so wouldn't it be more likely to push out blocks from a query that returned 3 seconds earlier? Or is frequency a page is touched counted towards what becomes discarded?
October 4, 2013 at 3:11 pm
If you're under so much memory pressure that pages read in 3 seconds ago are getting discarded, that server's going to be dead in the water no matter what kind of workload is running on it (OLTP or OLAP or a mix) unless it's got SSDs for storage or some awesomely maxed out throughput drives.
Disks are slow (SSD being the exception, but even they are still slower than memory), if SQL's cache is virtually useless due to the churn on the pages in buffer then there are going to be serious performance implications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2013 at 3:20 pm
GilaMonster (10/4/2013)
I mean the normal dictionary definition of the word.An OLTP system does not automatically have low PLE from being a OLTP system.
Absolutely not. We had OLTP systems with insanely high PLE. Others that would have been insanely high except weekend data processing would flush the cache completely. In fact, depending on the OLTP system, I kind of expect them to have higher PLE in general.
"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
October 4, 2013 at 3:25 pm
Grant Fritchey (10/4/2013)
Others that would have been insanely high except weekend data processing would flush the cache completely.
That matches what I've seen in a lot of places. High and steady (or steadily increasing) during the day, sawtooth pattern overnight or over the weekends as batch processing or maintenance churns the cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply