Investigating the proportional fill algorithm

This is something that came up recently on the Microsoft Certified Master DL, and is something I discuss in our IEPTO1 class because of the performance implications of it, so I thought it would make an interesting post. Allocation Algorithms The SQL Server Storage Engine (SE) uses two algorithms when allocating extents from files in […]

When heap data pages become linked…

The pages at each level of an index are linked together in a doubly-linked list (using the m_nextPage and m_prevPage fields in their page headers) to allow ascending-order and descending-order scans, based on the index key(s). Data pages in a heap are NOT linked together, as there’s no ordering in a heap. However, there is […]

Data recovery: investigating weird SELECT failures around corruption

An interesting corruption problem cropped up on the MCM distribution list yesterday and after I figured it out, I thought it would make a good blog post in case anyone hits a similar problem. In a nutshell, the problem was corruption such that a simple SELECT * query failed, but a SELECT * query with […]

Incomplete checkpoints and recovery

Back in 2009 I blogged about how checkpoints work (see How do checkpoints work and what gets logged) and I received a question in email on Monday that I thought would make a good little blog post. The question is (paraphrased): What happens if a checkpoint starts but doesn’t finish before a crash occurs? Will […]

Are mixed pages removed by an index rebuild?

This is a question that came up this morning during our IE1 class that I thought would make an interesting blog post as there are some twists to the answer. The first 8 pages that are allocated to an allocation unit are mixed pages from mixed extents, unless trace flag 1118 is enabled. See the following blog […]

Inside the Storage Engine: How are allocation unit IDs calculated?

It’s been a long time since I’ve written a post about pure internals, but every so often I get asked how an allocation unit ID is calculated from the m_objId and m_indexId fields that are stored in the header of every page. When DBCC PAGE dumps a page header’s contents, it does the necessary calculations […]

New script: When were the sp_configure options last changed?

In my previous post I explained how the sp_configure settings are stored in a special page in the master database called the CONFIG block. Sometimes you might want to know when these were last changed if error logs are not available for some reason (as sp_configure changes are noted in the error log) or someone […]

Where are sp_configure settings stored? Another reason to backup master…

Earlier today I blogged on our SQL Server Pro magazine blog about false-alarm corruptions you will *definitely* see if you restore a backup of the master database as a user database and run DBCC CHECKDB on the restored copy. You might be doing this as part of offloading consistency checks to another server or validating […]

How are per-column modification counts tracked?

Earlier today there was a question on the MVP mailing list asking how SQL Server keeps track of per-column modification counts. From 2008 onwards, the hidden system table sys.sysrscols tracks modifications to table columns using the rcmodified column. The hidden system tables (introduced in 2005 when we rewrote the entire metadata management system) are only […]

Initial VLF sequence numbers and default log file size

We're teaching on-site with a financial client in New York for two weeks and in a session this morning on log file architecture I was asked why the VLF (virtual log file) sequence numbers in a new database don't start at one. Here's an example: CREATE DATABASE foo; GO DBCC LOGINFO ('foo'); GO FileId  FileSize  […]

Performance issues from wasted buffer pool memory

Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools – how much memory is being used for data file pages and how much of that memory is storing empty space. I got back data from 1394 servers around the world – thanks! […]

Survey: how much server memory is being wasted? (code to run)

In this survey I'd like you to run some code and then send me the results (and I'm sure someone will put together a PowerShell script to make it easy to run on multiple instances). I want to know how much of your precious server memory is being wasted storing empty space on data file […]

Do changes to index keys really do in-place updates?

There was an interesting discussion on Twitter this morning (started by my good friend Erin Stellato (blog|twitter)) about in-place updates for index records where the key value changes and the record remains on the same page. Various sources including the SQL Server 2008 Internals book (pg 361 – I didn't write or review that bit […]

A SQL Server DBA myth a day: (25/30) fill factor

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) I'm all mythed-out from yesterday, so today's is a quick […]

Sample corrupt databases to play with

The very worst piece of advice I ever saw on the Internet was in response to someone asking on a SQL newsgroup ‘how can I create a corrupt database?’. The first response was: When I want to corrupt a database to play with, I go into the data center, find a hard-drive and flick the […]

Lock logging and fast recovery

One of the things I love teaching is how the transaction log and logging/recovery work. I presented a session on this at both PASS and SQL Connections in the last two weeks, and in both sessions I promised to write some blog posts about the deep internals of logging operations. This is the first one […]

How do checkpoints work and what gets logged

This is a blog post I’ve been meaning to do for a while, and I’ve recently noticed some info on the web about checkpoints which is a little misleading, so I want to do a quick post to explain how checkpoints work as far as log records are concerned. When a checkpoint operation occurs, no […]

CHECKDB From Every Angle: How to tell if data purity checks will be run?

Here’s a question that came up recently: if I’ve upgraded a database from SQL 2000 or before, how can I tell if the data purity checks will be run or not? As you may know, DBCC CHECKDB in 2005 onwards includes ‘data purity’ checks. These look for column values where the value is outside the […]

Forwarding and forwarded records, and the back-pointer size

This is a question that comes up every so often, most recently this morning while teaching a private class (and Kimberly's teaching now): how large is the forwarded record back-pointer? (And I haven't posted anything geeky for a while…) In a heap it is possible to get forwarding and forwarded records. They occur when a […]

How expensive are page splits in terms of transaction log?

Page splits are always thought of as expensive, but just how bad are they? In this post I want to create an example to show how much more transaction log is created when a page in an index has to split. I'm going to use the sys.dm_tran_database_transactions DMV to show how much more transaction log […]

Misconceptions around adding columns to a table

This is a quick follow-on from my Misconceptions around null bitmap size post. The null bitmap is *always* present in a data record (i.e. records in a heap or the leaf-level of a clustered index) except when all columns are defined as SPARSE in SQL Server 2008 onwards, but is optional in index records if all […]

Misconceptions around null bitmap size

I'm starting a new series called 'Misconceptions' – a series of short posts that debunk some of the many myths and misconceptions that exist about the way SQL Server behaves. I actually already did the first post a couple of weeks back (Misconceptions around TF 1118) but just went back to re-tag it. In this […]

Misconceptions around TF 1118

There’s been a recent flurry of confusion and misconceptions about trace flag 1118 in SQL Server 2008. This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load […]

Inside the Storage Engine: More on the circular nature of the log

  Every so often (well, ok, very frequently) my internal list of ‘I must blog about that’ gets too big and I have an almost uncontrollable urge. Luckily, these urges end up with me blogging several times, otherwise who knows what kind of trouble I’d get into? :-) First up today is some pretty deep internals […]

Msg 602, Level 21, State 50, Line 1

(Yes, Kimberly's lecturing again…) Here's something that I've seen crop up a lot recently on corruption forums: Server: Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. This isn't corruption – it comes from trying to […]

IAM page corruption examples

A thread cropped up on SQLServerCentral involving IAM chain corruption (see Inside the Storage Engine: IAM pages, IAM chains, and allocation units for details of IAM chains). The error from DBCC CHECKDB was: Server: Msg 2576, Level 16, State 1, Line 1 IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) […]

Ghost cleanup redux

Way back at the start of me blogging here I wrote a comprehensive description of ghost records and the ghost cleanup process – see Inside the Storage Engine: Ghost cleanup in depth. A question came up in the class I’m teaching this week that’s worth answering in a blog post – do ghost records occur […]

FILESTREAM directory structure

After writing the FILESTREAM whitepaper for Microsoft, I’ve had lots of questions about the structure of the FILESTREAM data container. The FILESTREAM data container is the technical term for the NTFS directory structure where all the FILESTREAM data is stored. When you want to use FILESTREAM data, you first add a filegroup (during or after […]

Search Engine Q&A #27: How does the storage engine find variable-length columns?

This question came up in a class I was teaching a few weeks ago: given that the variable-length column offset array in a record only stores offsets, how does the storage engine find a particular variable-length column? The question comes because there's nothing in the record to say which variable-length column is which – so […]

Corruption errors: Msg 5242, Level 22

In SQL Server 2000 and before, the symptoms of database corruption would occasionally manifest themselves as asserts, such as: SQL Server Assertion: File: <recbase.cpp>, line=1378 Failed Assertion = 'm_offBeginVar < m_sizeRec'. To reduce the number of assertions being fired by the SQL Engine, my team changed these asserts into real error messages for SQL Server […]

TechNet Magazine: feature article on understanding logging and recovery

Wow – today is all about new content. As if I haven't already blogged about enough stuff to keep you reading through next week, the February issue of TechNet Magazine is now available and contains a feature article I wrote about understanding how logging and recovery work inside SQL Server. The article covers: What is […]

Corruption bug that people are hitting: Msg 8914 – PFS free space

(Posted with permission of the dev team) Here's an interesting bug that people are hitting. I found out about this while here in Barcelona at TechEd when I got roped into a discussion with a couple of Microsoft colleagues. Their customer was seeing errors like the following: Msg 8914, Level 16, State 1, Line 1 […]

How the on-disk data structures fit together

In the last few classes I've taught, we've discussed the on-disk structures where everything is stored in a database, and I've also been asked privately where all the info is on my blog. I know that a lot of people reading my blog now are new in the last six months or so and will […]

Search Engine Q&A #26: Myths around causing corruption

Every so often I’ll see posts on the various data corruption forums discussing causes of corruption. In this post I want to debunk some of the myths around what can cause corruption. There are really two types of corruption to deal with, physical corruption and logical corruption. Physical corruption This is where something has altered […]

Search Engine Q&A #24: Why can’t the transaction log use instant initialization?

This is a quickie in response to a blog comment from my previous post on instant initialization: How to tell if you have instant initialization enabled? The comment was: I must say, I love instant initialization. It's awesome. But I always wondered why it's not available for the log file. I assume there's a technical reason… […]

Search Engine Q&A #21: File header pages, and file header corruption

Following on from my previous post on boot pages and boot page corruption, I’ve been asked about file header pages – and I was already planning this post as the next in the series. So what’s a file header page? Every data file in a database has the very first 8kb page (i.e. page 0 […]

Search Engine Q&A #20: Boot pages, and boot page corruption

  Now that I’ve done all the business-related blog posts, back to the good stuff to stop people complaining! Something that’s cropped up a few times over the summer so far is people trying to repair boot page corruptions. First off, what’s a boot page? Every database has a single page that stores critical information […]

CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete

(I’m actually on-stage here at TechEd doing the  DAT track pre-con with Kimberly – she’s on now until lunch so I’m catching up on forum problems…) Here’s a question that came up on of the corruption forums I monitor that I think is worth blogging about. To paraphrase: I have a bunch of corruptions in […]

New script: How much of the database has changed since the last full backup?

Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]

Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

This is a really interesting question that came up in the Microsoft Certified Architect class I’m teaching at present – if a database has torn-page protection enabled, and page checksums are enabled, is all the existing torn-page detection lost? This is an important question, because enabling page checksums doesn’t suddenly make all allocated pages be […]

SQL Server 2008: New (undocumented) physical row locator function

One of the drawbacks of not being in the SQL team at Microsoft any longer is that I don’t know about all the undocumented features in the next release – I have to hunt around for them like everyone else :-( So I was poking about in SSMS in 2008 CTP-6 and noticed a function called sys.fn_PhysLocCracker […]

Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps

Well this one is well overdue and I'm in the middle of writing a class where I want to reference this blog post – so I suppose I'd better write it!! This is an updated post from my old Storage Engine blog that now covers DIFF and ML map pages. In some previous posts in […]

Inside The Storage Engine: sp_AllocationMetadata

While I was at Microsoft, I wrote some code in the Storage Engine to very easily return all the IAM chains/allocation units (see this post for more details of the internals of those), what type they are, and the relevant page IDs (first, root, first-IAM) so I could go spelunking with DBCC PAGE. Since I […]

Inside the Storage Engine: When do versioning tags get added?

Ok – so we did more partying than we thought so blog posts have been a little sparse this month, but here’s one to end off the year. There’s a popular impression that turning on snapshot isolation and then rebuilding indexes will cause all rows in the table to get the extra 14-byte versioning tags. […]

Search Engine Q&A #10: When are pages from a truncated table reused?

This is a question I was sent a week or so ago – if a table is truncated inside a transaction, what protects the integrity of the table’s pages in case the transaction rolls back? Let’s find out. First off I’ll create a simple table to experiment with: We can see what pages and extents […]

How can data-type choice affect performance?

In one of the sessions Kimberly and I taught this week at SQL Connections, we discussed how to choose efficient data-types – I’d like to share the discussion here with an example. The crux of the matter is in the amount of space required to store the data-types in the schema, and ensuring that the […]

Tool for estimating the size of a database

[Edit 8/25/2013: The tool referenced in this post is no longer available.] During SQL Server 2005 development I did a comprehensive rewrite of the Books Online entries concerned with estimating index, table, and database size (see I was recently having a discussion with Øystein Sundsbø about a bug in one of the formulas I wrote and […]

Inside the Storage Engine: Ghost cleanup in depth

Over the years I was in the Storage Engine team I saw a lot of concern on the various forums about the ghost cleanup task. There have been a few bugs with it in previous versions  (see these KB articles – 932115 and 815594) and there's very little info available on it. For some reason […]

Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed?

I’d like to kick off the Indexes From Every Angle series this evening by re-posting some articles from my old blog. Both of these topics continue to crop up on newsgroups and forums. What happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Well – it […]

Inside the Storage Engine: Proof that records are not always physically stored in index key order

I mentioned this in my Anatomy of a page post – its a common misconception that records in an index are ALWAYS stored in the same physical order as the logical order defined by the index key. Here's proof for you that this is incorrect (as well as introducing you to the other dump styles […]

Inside the Storage Engine: IAM pages, IAM chains, and allocation units

This is a combo from some previously posted material, with some more DBCC PAGE output thrown in. IAM pages An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called ‘GAM intervals’. An IAM page tracks which extents within that […]

Inside the Storage Engine: Anatomy of an extent

This one's a quickie. In the previous post I explained about database pages – their structure and some page types. Now I'd like to explain how pages are grouped into units called extents. An extent is a group of eight physically consecutive pages in a data file. Extents are always aligned on 64KB boundaries (i.e. […]

Inside the Storage Engine: Anatomy of a page

Next up in the Inside the Storage Engine series is a discussion of page structure. Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Here’s a picture of the […]

Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back

Time for the first post in the Inside the Storage Engine series. I’m going to focus on SQL Server 2005 in this series and I’ll point out major differences between 2005 and previous versions. Please drop me a line if there’s something you’d like to see explained and demo’d. Before jumping into how things work, […]

Inside the Storage Engine: Anatomy of a record

This week I’m going to post a bunch of info on the basic structures used to store data and track allocations in SQL Server. A bunch of this was posted back when I started blogging at TechEd 2006 but I want to consolidate/clarify info and add more about using DBCC PAGE to examine the various […]