3 ms·

Microsoft SQL Server has had this built in since “forever”, but instead of looking at your config, it does its analysis based on workload trace, analyzing how t

Microsoft SQL Server has had this built in since “forever”, but instead of looking at your config, it does its analysis based on workload trace, analyzing how that workload performs and what particular queries are having sub-par performance. Thus the “tuning” matches your actual use. If there can be done guaranteed improvements to your DBs performance based on static analysis of the config-alone, why on earth is that not the standard config? That seems like a no-brainer.

endorphone 8y ago
The SQL Server tuning advisor has historically been terrible for any database of any complexity at all. It imposes an enormous load, and the results are usually contradictory and often very sub-optimal if not detrimental. Works great for a hello world style database, though. There was a post earlier (https://news.ycombinator.com/item?id=19422554 https://news.ycombinator.com/item?id=19422554) that I assume inspired this post and it was someone who benefited by correcting the DBMS' very wrong assumptions about the cost of random IO, and this was always the Achilles heel of SQL Server as well with any sort of fast storage -- it absolutely refused to accept that anything could be faster than about 150 IOPS, and would try to brute force through with terrible approaches. In this case the guy could just change the configuration and it would properly calculate query plan costs and execute accordingly, where SQL Server, to my knowledge, still doesn't let you change this costing, nor does it dynamically determine it.
- ximeng 8y ago
  On large databases, the Sql Server tool simply failed to complete its analysis for me.
atlasduo 8y ago
Standard config and actual use are very different things. The first one implies adequate performance for most use cases, while actual use implies adequate performance considering current use case. This tool seems to be analyzing current use case.
Twirrim 8y ago
So does Oracle DB as well. In the mysql world, mysqltuner has been kicking around for more than a decade now that spits out suggested configuration changes. I haven't had a need to use it in several years though https://github.com/major/MySQLTuner-perl https://github.com/major/MySQLTuner-perl There was an interesting project around applying blackbox optimisation to MySQL, called OtterTune on which a paper was published a couple of years ago, https://blog.acolyer.org/2017/08/11/automatic-database-management-system-tuning-through-large-scale-machine-learning/ https://blog.acolyer.org/2017/08/11/automatic-database-manag.... They got some good results out of it. Haven't attempted to use it myself: https://github.com/cmu-db/ottertune https://github.com/cmu-db/ottertune There are just so many dials and switches that can be changed that can modify the performance of the database, it really has to be moving far beyond anything any one individual could possibly grok.

endorphone 8y ago

The SQL Server tuning advisor has historically been terrible for any database of any complexity at all. It imposes an enormous load, and the results are usually contradictory and often very sub-optimal if not detrimental. Works great for a hello world style database, though. There was a post earlier (https://news.ycombinator.com/item?id=19422554 https://news.ycombinator.com/item?id=19422554) that I assume inspired this post and it was someone who benefited by correcting the DBMS' very wrong assumptions about the cost of random IO, and this was always the Achilles heel of SQL Server as well with any sort of fast storage -- it absolutely refused to accept that anything could be faster than about 150 IOPS, and would try to brute force through with terrible approaches. In this case the guy could just change the configuration and it would properly calculate query plan costs and execute accordingly, where SQL Server, to my knowledge, still doesn't let you change this costing, nor does it dynamically determine it.

ximeng 8y ago
On large databases, the Sql Server tool simply failed to complete its analysis for me.

atlasduo 8y ago

Standard config and actual use are very different things. The first one implies adequate performance for most use cases, while actual use implies adequate performance considering current use case. This tool seems to be analyzing current use case.

Twirrim 8y ago

So does Oracle DB as well. In the mysql world, mysqltuner has been kicking around for more than a decade now that spits out suggested configuration changes. I haven't had a need to use it in several years though https://github.com/major/MySQLTuner-perl https://github.com/major/MySQLTuner-perl There was an interesting project around applying blackbox optimisation to MySQL, called OtterTune on which a paper was published a couple of years ago, https://blog.acolyer.org/2017/08/11/automatic-database-management-system-tuning-through-large-scale-machine-learning/ https://blog.acolyer.org/2017/08/11/automatic-database-manag.... They got some good results out of it. Haven't attempted to use it myself: https://github.com/cmu-db/ottertune https://github.com/cmu-db/ottertune There are just so many dials and switches that can be changed that can modify the performance of the database, it really has to be moving far beyond anything any one individual could possibly grok.