October 2, 2014 at 5:39 pm
hi,
in microsoft doc there is written on the topic of BP Extensions with SSD's in SQL Server 2014: only clean pages are written to disk... does this mean data pages that have not been modified yet? or also those data pages that have already been modified, and where log has finished writing and the transaction has been marked as commited??
My second question is, why are there clean data pages being written to L2 cache to make space for other not modified pages? I mean, shoudnt they be modified first, before letting other unmodified data pages into the Cache? I mean they have still to be modified..that makes no sense to me to page them out and page them in again just for other data pages...
October 9, 2014 at 2:30 am
Let me try answering it as this looks like a mix of question from BP fundamentals and new features in SQL Server 2014.
freddyism00 (10/2/2014)
hi,in microsoft doc there is written on the topic of BP Extensions with SSD's in SQL Server 2014:
From BOL "Introduced in SQL Server 2014, the buffer pool extension provides the seamless integration of a nonvolatile random access memory (that is, solid-state drive) extension to the Database Engine buffer pool to significantly improve I/O throughput. "
You can read more from the link.
only clean pages are written to disk... does this mean data pages that have not been modified yet? or also those data pages that have already been modified, and where log has finished writing and the transaction has been marked as commited??
Clean pages here are the pages which were modified while they stay in buffer Pool due to user transaction. SQL Server periodically hardens those pages to disk. pages which are not modified (or used only for read) will continue to stay in buffer pool.
My second question is, why are there clean data pages being written to L2 cache to make space for other not modified pages? I mean, shoudnt they be modified first, before letting other unmodified data pages into the Cache? I mean they have still to be modified..that makes no sense to me to page them out and page them in again just for other data pages...
This is how SQL Server memory manager optimizes the Buffer Pool. Since it needs to accommodate many other pages in BP for:
> Faster access to serve user's requests.
> Maintaining integrity of the data stored in these pages
> concurrency
If pages keeps on staying in buffer pool , there won't be free buffer space to handle new page requests. Hence it's a buffer manager calls to maintain the free space in BP. while it will harden the pages only when they are validated in terms of data integrity.
Hope this clarifies. Feel free to post for any doubts you still have.
October 9, 2014 at 6:27 am
yeah thx, much better now, I was just wondering about my 2nd question, because I was confused by "clean pages" , those that are in fact, the dirty (modified) pages as it seems, now it makes sense of course why those are being buffered out (or paged out, however one says...) onto SSD storage...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply