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

PowerShell script for en-GB, UK locale and GMT timezone

Here’s a quick PowerShell script (more of a personal reminder) to set everything for the current user to English-GB, UK locale and GMT timezone. Unfortunately, this doesn’t override the settings for the welcome screen or new users but that’s just one dialog after the restart.

Change Current User

#Set home location to United Kingdom
Set-WinHomeLocation 0xf2

#override language list with just English GB
$1 = New-WinUserLanguageList en-GB
$1[0].Handwriting = 1
Set-WinUserLanguageList $1 -force

#Set system local
Set-WinSystemLocale en-GB

#Set the timezone
Set-TimeZone "GMT Standard Time"

#Restart the OS

Change for All Users

You’ll need to restart the server for the system locale change to come into effect and if you want to change the welcome screen / new users; you’ll need to manually set the options at the bottom or mess about with the registry.

Welcome