none
how can temporals be optimized for performance RRS feed

  • Question

  • Hi we run 2016 enterprise. We gravitated to temporals (with row version to identify candidates for incremental update) early in our warehouse design but now wonder how the historic portions can be optimized for performance.

    Its my understanding that PKs cant be built on historic parts of versioned tables.  I wonder if that's true.  I forget if the indexes (begin and end) I've seen automatically generated in some environs are PK's or not.

    I've also heard that some folks favor non clustered column stores on the historic portions of versioned tables.

    I suspect we will think strongly about the surrogate PK from the base portion being a non clustered index in the historic portion.

    And of course the biz keys themselves will be candidates for some sort of index on our historic parts of versioned tables.

    Any discussion would be appreciated.  I'm just thinking out loud looking for a little feedback.

       

    Wednesday, February 27, 2019 10:24 PM

Answers

All replies

  • >>>how the historic portions can be optimized for performance.

    Take a loom into partitioning 

    https://www.sqlshack.com/archiving-sql-server-data-using-partitions/

    If you are talking in terms of DW, yes you can be benefit from having clustered column story indexes 

    I am still confused what  you are trying to achieve, can you explain a little bit more?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 28, 2019 5:19 AM
    Moderator
  • Hi db042190,

    >>Its my understanding that PKs cant be built on historic parts of versioned tables. I forget if the indexes (begin and end) I've seen automatically generated in some environs are PK's or not.

    Yes, history table cannot have constraints like: primary key, foreign key, table or column constraints. But the default history table has a clustered rowstore index created for you based on the period columns (end, start).

    For your question, you will need at least a non-clustered row store index. An optimal indexing strategy recommended by Microsoft will include a clustered columns store index and / or a B-tree row store index on the current table and a clustered column store index on the history table for optimal storage size and performance. Please refer to OLTP with Auto-Generated Data History.


    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, February 28, 2019 5:49 AM
    Moderator
  • thx Uri and Puzzle.  I'm trying to get subjects from the community for discussion here.  Sure temporals are cool but how are they going to perform without some central strategy, perhaps similar to what all/most shops conclude?

    wouldn't non clustered cs's be better than clustered cs so we don't have to include all cols, especially those with times in the date cols?  My understanding is that times in cs dates that are included in many queries drag performance down because of their density.

      


    • Edited by db042190 Thursday, February 28, 2019 7:03 PM respond to puzzle too
    Thursday, February 28, 2019 6:57 PM
  • >>>Its my understanding that PKs cant be built on historic parts of versioned tables.  I wonder if that's true.  I forget if the indexes (begin and end) I've seen automatically generated in some environs are PK's or not.

    Again , there is little info about your scenario... I would not choose a versioned tables for DW design.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 3, 2019 5:55 AM
    Moderator