For this week’s What If series, we’re exploring what would happen if you had access to SQL Server’s source code – like if you got a job at Microsoft, signed a partner NDA, if the code leaked, or if it went open source.
Today’s question is, “What would you fix or add?”
Erik says: Skipping Logging
Okay, look, minimal logging is okay, but the song and dance you have to go through to get it is ranges from absurd to impossible.
And it’s only for inserts.
Not everyone can be in Simple or Bulk Logged. I mean, really. Mirroring? AGs? Nope! Log Shipping? At least Bulk Logged!
Plus, the list of things that can be Bulk Logged is depressingly limited.
But that doesn’t mean you care equally about every table or process.
- Do you really want to fully log every change to an ETL table?
- Does that index rebuild to align to a partition need to take up 500 GB of log space?
- Does changing a column data type really need to be fully logged? (Before and after seems reasonable)
This isn’t crazy talk either, other database platforms have it implemented. Brent has a Connect Item about it, if you feel like voting.
Plus, the way I see you folks taking backups, you might as well not be logging anything anyway.
Brent says: Unsupported Trace Flag for Unlimited Memory in Standard Edition
Look, I know everybody needs to pay their bills and put food on the table. I understand Microsoft would certainly go broke if they let Standard Edition users leverage all the memory you can cram in a 2U pizza box these days. I totally understand Nadella needs his Nutella.
So here’s a compromise: let’s sneak in an unsupported trace flag that lets gambling users run unlimited memory in Standard Edition.
It’ll leak out on the Internet, and we’ll start using it, and get addicted to In-Memory OLTP and building big ginormous columnstore indexes. Eventually, we’ll need to make a support call, and we’ll drop back down into limp-home mode, at which point our bosses will realize how awesomely fast memory is these days.
What about you? What would you want to sneak in?
21 Comments. Leave new
“Nadella needs his Nutella” :))))))) Love it, BrentO!!!
I’d change the aggravating “formatting” of system generated scripts.
I’d create a global variable type that persists in a session across multiple statements. Nothing worse that finding out you need a “GO” to get something to work only to find out the variable you declared and set at the top gets wiped out and is unusable below the “GO”.
I’d setup an exchange rate for converting “query bucks” to some crypto-currency so I can provide a quasi monetary value to how much my tuning is saving the company money. :-$
To the topic, maybe it is time to scratch that “Auto Close” from Database Properties? Just wondering what might be a valid justifiable reason for using it…
Throw a bunch of customer dbs on a box (like 10k) and then use auto close. It can make sense there if you know workload patterns of your peoples.
If you’re going to add the trace flag for unlimited memory, be sure and note T-1254 (Go ahead and search for it :D) if they are going to utilize MOT.
I don’t often use trace flags so I’m unfamiliar with T-1254. Could you link some documentation for Trace flag 1254? I couldn’t find any documentation on that
You won’t see it with any luck. If you have a busy server and you’re using Memory Optimized Tables (MOTs) there is an optimization bug they haven’t been able to figure out that will cause an MOT thread to try and service a non-MOT related operation and will result in the exception
“Error: 3628, Severity: 16, State: 1.
The Database Engine received a floating point exception from the operating system while processing a user request. Try the transaction again. If the problem persists, contact your system administrator.”.
This trace flag disables the optimization. The problem manifests itself in completely unrelated areas and that is what is most frustrating about it. You could receive this on an insert, delete, index operation, backup, etc. . So if you ever see this error and you are using MOTs anywhere in any of the databases on the server, throw this flag into operation and your troubles will go away. I will note they are working to resolve the error but to date have no solution.
Thanks! That sounds like a good thing to know if anyone is considering using a Memory Optimized Table…add that to the list of caveats
And BAM I just experienced this but only on a dev box that is tight for RAM. The culprit is actually on a select statement that is referencing the in memory table and it goes away if I remove a redundant unique constraint on the memory optimized table
We’ve had this on a simple select using a memory optimised table as a variable. Only on some databases – other databases of identical structure don’t exhibit the problem. Been raised with MS for 2 months with no progress.
Then today got the issue again on a simple UPDATE statement with no MOTs. However it follows a MOT command on the same connection.
Luckily found this comment and the trace flag fixes it. I’ve added all the details to our support ticket and am asking for either a fix or appropriate public documentation of the trace flag so that I can advise our customers to deploy it. I don’t think i can reasonably ask customers to deploy a trace flag with no information where it’s only existence is highlighted by a comment in a blog post!
Fingers crossed for action from MS! If this was found in 2017 and hard to figure out then i’m not hopeful that they’ll do any better in 2019.
I should also say we replicated it in a simple database (shared with MS) in SQL 2014 Enterprise, SQL 2016 (Standard) and 2017 (Standard). All on the latest SP and CU.
We also found that re-creating the DB with script stopped the issue being reproduced. Only a DB backup allowed us to replicate it; so clearly some internal structures must be involved.
Re-indexing the involved tables (even the whole DB) has had no impact.
We’ve also tested on Physical (on different Intel CPU generations) and Virtual machines – given the floating point and OS involvement in the error text we didn’t discount a CPU or OS bug which might have explained why MS couldn’t reproduce it using our backup but we could. We have the issue occurring on Windows 2012R2 and 2016.
A year and a half later and there is still no documentation on this trace flag. That was part of the reason why I mentioned it here. This site gets indexed enough that it will show up in searches. I could tell you that I had to go pretty high up the chain at Microsoft to even get this flag and once we got into the discussion it was clear this was one of the “dirty little secrets” of MOT. Thread operations can be a real bear to track down when you can reproduce them so if we ever officially hear anything on this for a fix, I’ll be very surprised. I am actually surprised they haven’t abandoned the optimization stack that is causing the issue since it’s a minor performance drop. Anyway, the best advice I can give is to tell you that if you see the floating point error in an MOT DB, try the flag. Glad to hear it solved your problem. Spread the word.
Bobby, you’re my hero. Thank you so much!
[what would you like to sneak in]
Support for something that Dr. Codd defined in his original spec for RDBMS: user defined domains (user defined range of allowable values AND the operations supported on the data type). Read up on it. I believe you will learn that such support would remove about 50% of the practical hassles we now go through dealing with nullable columns, type conversions both implicit and explicit, having to write tons of code to enforce constraints that we have to use instead (or dealing with the dirty data that inevitably results from lack of those constraints).
in addition to SELECT TOP 1000 or whatever…I would add SELECT BOTTOM. I usually want to see the last records inserted not the first.
SELECT TOP requires an ORDER BY or the resulting order can be anything and can change over time. For SELECT BOTTOM just reverse your sort order (ORDER BY Table_Id DESC)
I would like to see Index rebuild skip logging, just track the rebuild statement.
Just MATCH. I don’t care which flavor of regex, but dang man, we’ve been waiting 15+ years since everybody else added it to their SQL flavors. POSIX, PERL, PYTHON, JAVA, ECMASCRIPT, or even the broken nightmare that is .NET regex. Pick one and add it!
I’d like the list of unsupported features with indexed views get tremendously shrinked. It’s a real nightmare.
The minimum would be to support outer joins, that’s my personal show stopper no. 1
The behind-the-scenes maintenance needed to keep up with an outer join would be disappointing, I think.
For SSMS, frequently my team asks for a particular search on databases, I would like to have a “session mode”, a mode where I can search or query some tables, open procedures, and rollback to my initial windows once I made the asked search.
For SQL Server in general, my main problem on these weeks is to estimate the time a query can take on VLDBs. A great improvement for me would be a way to kill process without damages (for exemple a select into, an index creation on large tables, actions whom I don’t care about target objects).
Sorry for my english 🙂