Automated Indexes

  • Comments posted to this topic are about the item Automated Indexes

  • Really cool tool, this.
    has helped ease the load on our dba

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • And yet they can't do a shrinkfile like Peter Norton did 30 years ago and nor can they rebuild an index without temporarily doubling its size and blowing out the MDF file if it's a very large index.  I also hope that the new indexes that are auto-magically created aren't based on the silly index tuning advisor and missing index functionality and I damn sure hope it doesn't drop an NCI that seems to be a duplicate of the CI.

    This is both really exciting but spooky stuff.  I'd love it if it actually works as advertised and also selected the correct FILL FACTOR as a trade off between performance, memory usage, and when it actually needs to be rebuilt.  Got some reading to do.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Thursday, May 10, 2018 12:28 AM

    And yet they can't do a shrinkfile like Peter Norton did 30 years ago and nor can they rebuild an index without temporarily doubling its size and blowing out the MDF file if it's a very large index.  I also hope that the new indexes that are auto-magically created aren't based on the silly index tuning advisor and missing index functionality and I damn sure hope it doesn't drop an NCI that seems to be a duplicate of the CI.

    This is both really exciting but spooky stuff.  I'd love it if it actually works as advertised and also selected the correct FILL FACTOR as a trade off between performance, memory usage, and when it actually needs to be rebuilt.  Got some reading to do.

    That is our dba's one big gripe - the data files are huge in comparison to the amount of data contained therein.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Stewart "Arturius" Campbell - Thursday, May 10, 2018 4:52 AM

    Jeff Moden - Thursday, May 10, 2018 12:28 AM

    And yet they can't do a shrinkfile like Peter Norton did 30 years ago and nor can they rebuild an index without temporarily doubling its size and blowing out the MDF file if it's a very large index.  I also hope that the new indexes that are auto-magically created aren't based on the silly index tuning advisor and missing index functionality and I damn sure hope it doesn't drop an NCI that seems to be a duplicate of the CI.

    This is both really exciting but spooky stuff.  I'd love it if it actually works as advertised and also selected the correct FILL FACTOR as a trade off between performance, memory usage, and when it actually needs to be rebuilt.  Got some reading to do.

    That is our dba's one big gripe - the data files are huge in comparison to the amount of data contained therein.

    Based on that comment, it seems a sure bet that Auto Tuning is similar to the nonsense coming from the DTA.  Wide keys and a bazillion INCLUDEs to build covering indexes for virtually everything with unnecessary stats on a similar number of columns.  Heh... as the Nabisco Company advertised for one of their leading cracker products way back in the '70s, "You can change the wrapper but it's still the same old Ritz". 😀

    Seems like a clever way for MS to increase storage costs in Azure for those that may not know better.  And I'll bet your DBA also goes nuts when it comes to stats and index maintenance.  I wonder which Fill Factor they assign to the indexes that Auto-Tuning creates.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Thursday, May 10, 2018 5:10 AM

    Seems like a clever way for MS to increase storage costs in Azure for those that may not know better.  And I'll bet your DBA also goes nuts when it comes to stats and index maintenance.  I wonder which Fill Factor they assign to the indexes that Auto-Tuning creates.

    I'll ask him when he gets in in the morning tomorrow (he's already left for today)

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Thanks Steve for an interesting question. This is an illustrative example of utilizing AI in practice.

  • Azure Cosmos DB does automatic indexing. By default, it essentially indexes all data access paths, which is possible because it's a collection / document based database where all the relationships are materialized and data access patterns are more predictable. 
    https://docs.microsoft.com/en-us/azure/cosmos-db/indexing-policies
    https://azure.microsoft.com/en-us/resources/videos/azure-cosmosdb-indexing/

    Even with a relational database like SQL Server, the engine can make valid assumptions upfront about how the tables can be indexed by looking at SELECT statements contained in views and stored procedures.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • OOpppss.  Read this >>https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-2017
    and failed to recognize the last sentence .....

  • Stewart "Arturius" Campbell - Thursday, May 10, 2018 5:44 AM

    Jeff Moden - Thursday, May 10, 2018 5:10 AM

    Seems like a clever way for MS to increase storage costs in Azure for those that may not know better.  And I'll bet your DBA also goes nuts when it comes to stats and index maintenance.  I wonder which Fill Factor they assign to the indexes that Auto-Tuning creates.

    I'll ask him when he gets in in the morning tomorrow (he's already left for today)

    Cool.  Thanks, Stewart.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply