May 9, 2018 at 11:25 pm
Comments posted to this topic are about the item Automated Indexes
May 9, 2018 at 11:26 pm
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”
May 10, 2018 at 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2018 at 4:52 am
Jeff Moden - Thursday, May 10, 2018 12:28 AMAnd 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”
May 10, 2018 at 5:10 am
Stewart "Arturius" Campbell - Thursday, May 10, 2018 4:52 AMJeff Moden - Thursday, May 10, 2018 12:28 AMAnd 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
Change is inevitable... Change for the better is not.
May 10, 2018 at 5:44 am
Jeff Moden - Thursday, May 10, 2018 5:10 AMSeems 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”
May 10, 2018 at 7:08 am
Thanks Steve for an interesting question. This is an illustrative example of utilizing AI in practice.
May 10, 2018 at 7:28 am
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
May 10, 2018 at 8:12 am
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 .....
May 10, 2018 at 9:30 am
Stewart "Arturius" Campbell - Thursday, May 10, 2018 5:44 AMJeff Moden - Thursday, May 10, 2018 5:10 AMSeems 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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply