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 [...]
Paul S. Randal
In Recovery...
New script: When were the sp_configure options last changed?
Posted on: April 26, 2012 11:30 am
Where are sp_configure settings stored? Another reason to backup master…
Posted on: April 26, 2012 10:15 am
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?
Posted on: March 13, 2012 11:30 am
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
Posted on: September 13, 2011 11:52 am
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
Posted on: June 1, 2011 10:13 am
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)
Posted on: April 15, 2011 8:52 am
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?
Posted on: February 25, 2011 9:22 am
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
Posted on: April 25, 2010 9:51 am
(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
Posted on: February 26, 2010 12:02 pm
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
Posted on: November 13, 2009 1:24 pm
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
Posted on: September 27, 2009 9:28 pm
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?
Posted on: August 19, 2009 11:04 am
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
Posted on: August 10, 2009 10:53 am
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?
Posted on: May 12, 2009 5:50 pm
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
Posted on: May 6, 2009 3:38 pm
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
Posted on: May 4, 2009 2:47 pm
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
Posted on: April 23, 2009 9:19 pm
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
Posted on: April 21, 2009 12:42 am
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
Posted on: March 25, 2009 8:13 am
(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
Posted on: March 22, 2009 6:14 am
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
Posted on: March 19, 2009 10:44 am
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
Posted on: March 11, 2009 3:50 pm
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?
Posted on: March 1, 2009 10:42 am
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
Posted on: February 13, 2009 3:33 pm
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
Posted on: January 16, 2009 10:01 am
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 on: November 6, 2008 11:51 pm
(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 [...]
Search Engine Q&A #26: Myths around causing corruption
Posted on: August 27, 2008 5:11 pm
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?
Posted on: August 20, 2008 7:47 pm
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
Posted on: July 25, 2008 2:10 pm
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
Posted on: July 11, 2008 12:01 am
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 about [...]
CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete
Posted on: June 9, 2008 3:54 pm
(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 SQLServerCentral.com 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?
Posted on: April 8, 2008 4:49 pm
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?
Posted on: March 21, 2008 11:23 pm
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
Posted on: March 20, 2008 12:25 am
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
Posted on: March 14, 2008 3:31 am
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 – putting undocumented system catalog views to work
Posted on: March 12, 2008 10:38 pm
It's been a long few days of building slide decks and other content and I just had to stop for a bit and take care of my internals-hacking withdrawal symptoms! 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 [...]
Inside the Storage Engine: When do versioning tags get added?
Posted on: December 31, 2007 8:53 pm
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?
Posted on: December 5, 2007 7:37 pm
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?
Posted on: November 9, 2007 10:31 pm
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
Posted on: October 19, 2007 7:26 pm
During SQL Server 2005 development I did a comprehensive rewrite of the Books Online entries concerned with estimating index, table, and database size (see http://msdn2.microsoft.com/en-us/library/ms187445.aspx). I was recently having a discussion with Øystein Sundsbø about a bug in one of the formulas I wrote and it turns out he’s written a neat tool that codifies all the [...]
Inside the Storage Engine: Ghost cleanup in depth
Posted on: October 8, 2007 4:24 pm
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?
Posted on: October 5, 2007 12:52 am
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
Posted on: October 4, 2007 3:29 pm
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
Posted on: October 4, 2007 12:42 am
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
Posted on: October 3, 2007 10:13 pm
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
Posted on: October 3, 2007 6:49 pm
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
Posted on: October 1, 2007 1:53 am
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
Posted on: September 30, 2007 4:57 pm
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 [...]
