DateTime2 and Page Life Expectancy (PLE)



  • 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?



  • 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 https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/buffer-pool-extension?view=sql-server-ver15#concepts :

    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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2