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 look at first?”
Brent says: I’d look at the trace flags list.
Trace flags are switches you can use to flip features on & off. Erik’s compiled a list of known trace flags (and whether or not you should freak out when you see them enabled.) It’s a fun read.
But I’m sure it’s not complete, and I’m sure there’s many that Microsoft would rather never see the light of day again. You know how it is – you’ve got a specific problem you have to solve for a specific customer, and there’s just no other choice than to hard-code something into the engine itself.
I don’t want to use those, mind you.
I just wanna see what they are because I’d get a chuckle out of it.
So I’d go looking for any instance in source that checks for the existence of a trace flag, and then go read the comments. I bet there’s some swearing in there.
Erik says: I’d stare my enemy in the face
The first thing I’d do is look at how Scalar Valued Functions work.
Why do they do such awful things?
Did they do something wrong in a past life?
This has been a big issue in SQL Server for many years, and it doesn’t seem to be changing anytime soon.
Unfortunately, there are some things that you can only do with scalar valued functions. You can’t use iTVFs or MSTVFs in computed columns, because they’re not guaranteed to only return one value.
7 Comments. Leave new
I don’t think I want to know how that sausage is made. The only software at Microsoft that would be crazier than SQL Server would be Excel.
Between coding in several different languages and DBs at work and different ones at home for my hobbies, I’ve seen enough source code to have the thousand screen stare. Try dealing with C libraries designed to interface to hundreds of different radios from dozens of different vendors.
computed columns. I never design them in. My first thought when reading this post was that they can’t be consistent with relational theory based on Dr. Codd’s work. So in that case they should probably not be supported at all in any software that calls itself (or should I say where the marketing dept. calls it) an rdbms.
And I did a little googling and folks much stronger on the theory than myself seem to agree that calculated columns are contradictory to relational theory – which is the basis for our world of RDBMS.
I think of computed columns in the same way I think of all the other “non-relational” characteristics of sql server. They cause practical problems on a daily basis that we have to deal with forever because the vendors bastardize Dr. Codd’s model when they implement their products, either for their own convenience or to coddle to short sighted marketing pressure – or the vendors fail to implement fully the relational model (for example user defined domains). We should always be cognizant that a: No current DB fully supports the relational model defined by Dr. Codd and b: the vendors add features that violate the rules, and c: there are practical and non trivial negative consequences from a and b that we have to deal with on a daily basis.
I would try to figure out why it has taken 20+ years for them to be able to add a TRIM() function.
Funny. Trace Flags are the first thing I looked at. BTW…It does look like the Matrix, but mostly because I’m not a developer and don’t understand most of it anyway. Still glad I took the blue pool. The red one didn’t work out very well.
Chris doesn’t want to look at anything. 🙂
Ken’s riffing on a mention of computed columns.
Eric talks about an item on his wish list that wouldn’t be found in source code. Ha!
Ryan, that’s amazing, I’d love to hear what other new employees looked at.
I’m going to try and answer the question. What if I had the source code? Well, I’d dig in to find the root cause of a few recent issues that no internals material seems to cover. Specifically:
1. A problem with Ghost Version cleanup. I wrote a bit about this recently.
2. In memory OLTP. There are some bizarre memory requirements the moment you add the memory-optimized filegroup but before you create the first memory optimized object (blog post still pending). There’s also some bizarre behavior when restoring a 2014 db to a 2016 box if that db uses IM OLTP.
3. I have a SQL Server stack trace of an error where it seems like a read-ahead read is trying to put data into the buffer pool and subsequently flush logs from another database. But a severe error raised can mean result in a getting an error about a problem in another database. I’d want to understand a bit more about that piece.
I feel like in each case, I have had the and energy (and maybe ability) to look into this but I can’t any further without the source code. My hope would be to either uncover an interesting limitation of SQL Server (at least interesting to me) or an actual defect that Microsoft could take action on.
Hi, guys.
We maintained trace flag list on github, now our list have 521 trace flag list: https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Trace%20Flag.md
Be free to add new pull request or open issue.
i will create one system table or view from where i can get the jobs enable and disable time.