John Morehouse shows that SQL Server data pages are the same in Windows as they are in Linux:
One of the new phrases coming out of Microsoft is that “SQL is just SQL” regardless of what operating system it resides on. This was echoed during the keynote at SQL Bits 2019 by the Microsoft team, which you can watch here. Later that weekend, I gave a session about database internals. My presentation is about how data is structured within a row and why that matters. Understanding the internals of table structures, even in today’s age of technology, include SQL Server 2019 (which will be released in Q3/Q4 of 2019) is important. During my session, a question came up about how a data page is structured if SQL Server is sitting on top of a Linux server, such as Ubuntu. Does the data page have the same size and shape in Linux as it does in Windows?
They do. Click through to see John prove it.
Jess Pomfret shows us what compressed data looks like in SQL Server:
We first need to switch on trace flag
3604: this will write the output of ourDBCC PAGEcommand to the messages tab instead of the event log.There are 4 parameters for
DBCC PAGE: we will need to pass in the database name (or id), the file number, the page id and the print option. Using a print option of 0 will give us just the page header. In these examples I’m going to use option 3 which gives us more details on the rows stored on the page. For more information on usingDBCC PAGEI’d recommend Paul Randal’s post “How to use DBCC PAGE“.
This kind of investigation lets you see how compression really works.
So we have two entries for this stored procedure. I included the statement sql handle to show that each statement handle has its own text. Let’s parse that text to see each statement. I copied the parsing SQL from this Plan Cache article.
This is a good thing to keep in mind if you’re trying to figure out how often a procedure gets called: SUM on the execution counts grouped only by text might not give you the results you expect.
Randolph West starts a new series on the buffer pool in SQL Server:
Now that we’ve reminded ourselves of those fundamentals, let’s take a closer look at the buffer pool.
The buffer pool in SQL Server resides in the computer’s main memory (RAM). When the database engine requests a data page for reading or writing, it is assumed to be in the buffer pool. The buffer pool itself controls access between RAM and storage. If the data page that the database engine requests is not in RAM, a request is sent to the storage engine to retrieve that page. This may be storage directly attached to the system, or via a network interface card.
This first post is a high-level overview, but it looks like there’s a lot more in store from Randolph.
Hugo Kornelis describes a new operator:
The Bitmap operator is used to build a bitmap that, based on a hash, represents which values may be present in a data flow. Due to the chance of hash collisions in the hash function used, the Bitmap process can produce false positives but not false negatives – so a match based on a bitmap is not guaranteed to be a match to the actual data, but a non-match based on a bitmap is guaranteed to not be a match in the actual data.
The generated bitmap is typically used in other operators to remove rows for which there is no match in the bitmap, and hence guaranteed no match in the original set of data processed by the Bitmap operator. The use of Bitmap operators is most common in execution plans for star join queries in large data warehouses. An example can be seen here.
Click through for details on how it works and plenty of good information on it.
Bert Wagner continues his series on physical join operators:
The base algorithm works as follows: SQL Server compares the first rows from both sorted inputs. It then continues comparing the next rows from the second input as long as the values match the first input’s value.
Once the values no longer match, SQL Server increments the row of whichever input has the smaller value – it then continues performing comparisons and outputting any joined records. (For more detailed information, be sure to check out Craig Freedman’s post on merge joins.)
Beware: Bert has animations which might cause you to space out for a few hours.
If memory consumption is below the Low limit everything is fine and it is free to stay in memory. Once the consumption passes the Low limit a cleaner thread wakes up and tries to clean up memory. At this point price of memory is no longer zero. It starts from 2 at the Low limit and goes as high as 1000 when memory consumption reaches the Total limit. The higher the memory pressure the more aggressive the cleaner gets. Once memory consumption reaches the Hard limit all connections/sessions are closed and queries are cancelled with an out of memory error.
This is a thorough explanation with some good demos and terrible queries. Give it a read.
Hugo Kornelis has a long article which dives into the way SQL Server handles index spooling:
A Table Spool operator stores its data in a worktable that is structured as a clustered index. The index is not built on any of the columns in the data, nor on any artificially added columns. It is structured on zero columns. As is normal for a clustered index on a set of columns that is not unique for the set, a 4-byte uniqueifier is then added to the data to give each row a unique internal address.
The worktable for an Index Spool operator is also structured as a clustered index. However, this operator does actually index actual columns from its data instead of just relying on a uniqueifier. The indexed columns are chosen to effectively satisfy the Seek Predicate property. The statement in the Microsoft’s documentation that a nonclustered index is used for Index Seek is not correct.
A stack spool is represented in execution plans as a combination of an Index Spool and a Table Spool, both with the With Stack property present and set to True. This is misleading because it is actually a different type of spool. The worktable it uses is built as a clustered index on a single column, representing the nesting level. Because this is not unique, a uniqueifier is added where needed.
This is a deep look at some operators which people tend to gloss over but can have huge performance impacts.
The estimated CPU cost for the Hash Aggregate in the plan for Query 8 is 0.166344, and in Query 9 is 0.16903.
It could be an interesting exercise to try and figure out exactly in what way the cardinality of the grouping set, the data types, and aggregate function used affect the cost; I just didn’t pursue this aspect of the costing. So, after making a choice of the grouping set and aggregate function for your query, you can reverse engineer the costing formula. For example, let’s reverse engineer the CPU costing formula for the Hash Aggregate operator when grouping by a single integer column and returning the MAX(orderdate) aggregate. The formula should be:
Operator CPU cost = <startup cost> + @numrows * <cost per row> + @numgroups * <cost per group>Using the techniques that I demonstrated in the previous articles in the series, I got the following reverse engineered formula:
Operator CPU cost = 0.017749 + @numrows * 0.00000667857 + @numgroups * 0.0000177087
Definitely worth reading in detail.
Kendra Little shows us the impact that row width has on snapshot isolation:
So I went to work to demonstrate row width impact on the version store — when only a tiny bit column is changed in the row.
Here’s how I did the test:
- I created two tables, dbo.Narrow and dbo.Wide. They each each have a bit column named bitsy, along with some other columns.
- I inserted one row in each table, but I put a lot more data into the row in dbo.Wide.
- I allowed snapshot isolation on the database
- I began a transaction in another session under snapshot isolation and left the transaction open (so version store cleanup wouldn’t kick in while I looked around)
- I updated the bit column named bitsy for the single row in each table, thereby generating a row-version in tempdb for each table
The code I ran to test this is here, if you’d like to play around with it.
Read on for the results.
Kevin Feasel
2019-03-25
Internals
No Comment