Kevin Krenz

Home // Projects // Blog // Contact

Magic Numbers in SQL Server

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.

0: Default Degree of Parallelism

But that doesn’t mean no parallelism; it effectively means “let SQL Server choose.”

1: “Zero rows, but I want to hedge”

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.

4: Time for a single quantum (milliseconds)

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

8: Size of a page (KB)

By default, SQL Server stores data in 8KB pages.

8: Number of pages in an extent

SQL Server groups pages into contiguous chunks of 8 pages, called an extent.

8+: Automatic Soft-NUMA

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.

1433: Default port

SQL Server needs to use a port for communication; TCP 1433 is the default.

102400: Minimum numbers of rows for bypassing a delta store

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.

1048576: Maximum number of rows in a rowgroup

Columnstore indexes break up tables “horizontally” into rowgroups.