Is it “index create memory” or “index create memory (KB)”?

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.

Tweet.png

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.

3

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.

4.png

sys.configurations

No surprises here but for completeness, selecting from sys.configurations requires you to use the full name.1.png

2.png

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).

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s