patternsqlModerate
DateTime2 and Page Life Expectancy (PLE)
Viewed 0 times
datetime2plelifepageandexpectancy
Problem
As I understand it, when you define a column on a table you define its precision. This precision takes 1 byte and is stored at the column level. If you use a precision of 5 or more, then a DateTime2 column will take 8 bytes per row. (The precision is not stored at the row level.)
But when you convert that same DateTime2 as a VarBinary, it will take 9 bytes. That is because it needs the precision byte that is stored at the column level.
I am curious how this relates to when a DateTime2 is stored in memory. Say I have 1,000,000 DateTime2s in memory (each with a precision of 5 or more). Will that take up 8,000,000 bytes of memory, or 9,000,000 bytes of memory?
Basically, I would like to know if a default precision DateTime2 will cause more pressure on Page Life Expectancy than a normal DateTime?
But when you convert that same DateTime2 as a VarBinary, it will take 9 bytes. That is because it needs the precision byte that is stored at the column level.
I am curious how this relates to when a DateTime2 is stored in memory. Say I have 1,000,000 DateTime2s in memory (each with a precision of 5 or more). Will that take up 8,000,000 bytes of memory, or 9,000,000 bytes of memory?
Basically, I would like to know if a default precision DateTime2 will cause more pressure on Page Life Expectancy than a normal DateTime?
Solution
A default precision DATETIME2 will not cause more pressure on PLE compared to DATETIME. The buffer pool consists of 8-KB pages. The page count is what matters as opposed to the internal storage workings of each page. It isn't really correct to say that 1 million column values will take 8 million or 9 million bytes. Quoting from the documentation:
Buffer
In SQL Server, A buffer is an 8-KB page in memory, the same
size as a data or index page. Thus, the buffer cache is divided into
8-KB pages. A page remains in the buffer cache until the buffer
manager needs the buffer area to read in more data. Data is written
back to disk only if it is modified. These in-memory modified pages
are known as dirty pages. A page is clean when it is equivalent to its
database image on disk. Data in the buffer cache can be modified
multiple times before being written back to disk.
Buffer pool
Also called buffer cache. The buffer pool is a global
resource shared by all databases for their cached data pages. The
maximum and minimum size of the buffer pool cache is determined during
startup or when the instance of SQL server is dynamically reconfigured
by using sp_configure. This size determines the maximum number of
pages that can be cached in the buffer pool at any time in the running
instance.
Buffer
In SQL Server, A buffer is an 8-KB page in memory, the same
size as a data or index page. Thus, the buffer cache is divided into
8-KB pages. A page remains in the buffer cache until the buffer
manager needs the buffer area to read in more data. Data is written
back to disk only if it is modified. These in-memory modified pages
are known as dirty pages. A page is clean when it is equivalent to its
database image on disk. Data in the buffer cache can be modified
multiple times before being written back to disk.
Buffer pool
Also called buffer cache. The buffer pool is a global
resource shared by all databases for their cached data pages. The
maximum and minimum size of the buffer pool cache is determined during
startup or when the instance of SQL server is dynamically reconfigured
by using sp_configure. This size determines the maximum number of
pages that can be cached in the buffer pool at any time in the running
instance.
Context
StackExchange Database Administrators Q#311366, answer score: 11
Revisions (0)
No revisions yet.