Participating in a really worthwhile two days SQL Performance training by Jörg Stryk this week, I heard him pointing out some other “useless” database properties. Beyond Torn Page Detection. Consequently I couldn’t do else, but grab Jörg and welcome him to our growing Clean Team! [8-|]
Therefor a big welcome to another fellow MVP.
More Options
You might recall when fellow MVP Alain Krikilion joined the team suggesting to clean out the Torn Page Detection toggle on Options tab of the database properties of NAV. Didn’t ask him, but he might have found it clean enough. Jörg clearly doesn’t, read below.
Single User
Well, this will set the database into a single user mode. This is only possible, if no other connection is established, except for this single NAV Development client. As long as the NST service etc. is running you cannot enable this setting; you’ll be blocked until you receive a timeout.
I’d rather set this in Management Studio (Database Properties) as here you could also tell to “close all existing connections”, hence SQL Server will disconnect anything else automatically.
ANSI NULL default
This setting tells, if NULL values are allowed in tables/fields. This is pure nonsense, as NAV is not capable to deal with any NULL values (it will break down with an error) and automatically makes sure, that all fields have NOT NULL values. Hence, this is a setting which NAV does not need and cannot use.
SQL Developers who have to create tables in that database, but “outside” the NAV application, know how to deal with that if really needed.
Recursive Triggers
Well, the last time NAV used SQL triggers (this setting does NOT refer to NAV triggers!) was in version 5.0 (prior to SP1). And even in those old days, the triggers could not be – and have not been – recursive (means: SQL trigger executes another SQL trigger). Nowadays there are no more SQL triggers in NAV, thus this setting has zero relevance.
SQL Developers who have to create SQL triggers in that database, but “outside” the NAV application, know how to deal with that if really needed.
Auto Shrink
Let’s be frank: a NAV database is a data-cemetery. All goes in, nothing gets out. A NAV database is actually doomed to grow forever, as there are just few options (if at all) to reduce the amount of data. Hence, a NAV database needs to be configured to have a decent size, receiving all the data. While doing this a correctly set Auto Growth value is feasible. So the NAV db will grow and grow and grow … what’s the point of automatically shrinking it? That would mean: the database is idle and Auto Shrink will shrink the file. Then users start working and Auto Growth will expand it. Then shrink again. Then expand. And so on.
This is maybe a weird disk-benchmark, but actually degrading performance by causing super-fragmentation of the file. And due to the way how Auto Shrink internally works, it will also cause super-fragmentation on indexes.
IF – for whatever reason – the whole database needs to be shrunk, THEN this is an administrative task to be done once in Management Studio.
Sometimes we need to reduce the size of the Transaction Log file (e.g. after migrations etc.) but then it is the DBCC SHRINKFILE command (again: Management Studio), not shrinking the whole database.
Recovery Model
Hence, the only setting on that Options tab which makes slightly sense is the Recovery Model. But this also could be handled in Management Studio.
Hence …
So to keep it simple, maybe it’s the best way to completely get rid of the Options tab.
Let’s Stryke these options out, Vedbeak!