I was updating an Aireforge Advise fix script and as it’s important that we’re using the correct syntax, I double check everything. Including commands that I’ve been using for 18 years as they can change between versions.
I checked a few articles including TechNet, Microsoft Docs and some popular forums. All of them referenced index create memory and not index create memory (KB); which was confusing.
I know I’ve seen and used both, but I can’t remember when it changed; also all of the Aireforge test servers 2008-2017 returned index create memory (KB). So, I asked for help on Twitter using #sqlhelp.
After Aaron replied, I remembered hearing about this during a Kevin Kline talk at SQLBits 14. Basically, with sp_configure, you only need type enough text for it to find a unique match; which could be dangerous and potentially break your application if the SQL Server team add a new setting that contains the same phrase.
Example
These examples are just selects but updating via sp_configure works in the same way. Therefore to update index create memory (KB) you only need to run sp_configure ‘ind’, ‘1024’; which scares the hell out of me.
If you don’t specify enough text to return a unique entry, you’ll get an error but also a result set. So ‘in’ returned an error and 17 rows.
sys.configurations
No surprises here but for completeness, selecting from sys.configurations requires you to use the full name.
Conclusion
I don’t see the point of it. I’m sure it was a time saver for someone but I’ll continue to use the exact wording, otherwise you could run into issues if the SQL Server team add a new setting that contains the same phrase; like Index Create Memory (Max).
Data Boffin 