As I learn about different systems, I feel like there are two aspects to understanding them. First is getting a feel for how the system works. How does it use resources? Does it split them up into different segments? When it has used all of the memory, how does it respond? How does it prioritize competing jobs? What clever tricks might it use to predict the future?
Once you have a feel for a system, you need to learn the details. And, with many systems, this means learning a good number of arbitrary facts. Some things are configurable, others are set.
So, along those lines, here are a number of “magic” numbers I’ve learned in SQL Server. I’m sure there are many more – I may update this post as I learn about them.
But that doesn’t mean no parallelism; it effectively means “let SQL Server choose.”
In execution plans, you’ll sometime see an estimate of returning a single row. Often, this actually means that the estimator thinks there will be no results, but plans with that assumption would not work if that estimate is incorrect. So, SQL Server hedges it bet and uses an estimate of one. So, the resulting plan might technically work if the estimate is incorrect, but performance might be less than ideal.
SQL Server does a lot of the work you would typically expect the operating system do, including scheduling jobs. In most cases, jobs will yield after 4ms on the CPU (yes, SQLOS is non-preemptive).
By default, SQL Server stores data in 8KB pages.
SQL Server groups pages into contiguous chunks of 8 pages, called an extent.
When SQL Server detects there are more than eight cores per NUMA node, SQL Server will use software NUMA to subdivide the NUMA nodes even further.
SQL Server needs to use a port for communication; TCP 1433 is the default.
When loading data into a columnstore index, “most” of the time, the data is loaded into a delta store, which is later compressed into a rowgroup. However, if you load more than 102400 rows, SQL Server will skip the delta store and directly compress the data into a rowgroup.
Columnstore indexes break up tables “horizontally” into rowgroups.