Blog

Let’s step back and take a look at the big picture.  (Today, I’m writing for beginners, so you advanced gurus can go ahead and close the browser now.  I’m going to simplify things and leave a lot out in order to get some main points across.  Don’t well-actually me.)

Microsoft SQL Server databases are stored on disk in two files: a data file and a log file.

What’s Stored in the Data File (MDF)

Let’s start with a simple table.  If you want to follow along with my code, this will work on SQL Server 2005 & newer, but please do it in a brand new database rather than reusing one of your existing ones.  We’ll be looking at the log file later, and you won’t be able to quickly find the relevant entries in a sea of unrelated ones.  Off we go:

CREATE TABLE dbo.Friends (id INT IDENTITY(1,1), FriendName VARCHAR(30));
GO
INSERT dbo.Friends (FriendName) VALUES ('Brent Ozar');
INSERT dbo.Friends (FriendName) VALUES ('Jeremiah Peschka');
INSERT dbo.Friends (FriendName) VALUES ('Jes Schultz Borland');
INSERT dbo.Friends (FriendName) VALUES ('Kendra Little');
GO

We now have a table.  I was going to say you’ve got four friends, but we’re not your friends.  Let’s take this slow, alright?  We just met.  You can start by buying us a drink first.  Let’s see how the table is stored in SQL Server behind the scenes – look under the table, as it were:

DBCC IND('MyDatabaseName', 'Friends', -1);

This command is totally safe to run – it just lists out where SQL Server is storing your data.  Replace ‘MyDatabaseName’ with your database’s name.  The result is a list of pages where SQL Server stored the Friends table:

Data Files Are Broken Up Into 8KB Pages

These pages are the smallest unit of storage both in memory and on disk.  When we write the very first row into a table, SQL Server allocates an 8KB page to store that row – and maybe a few more rows, depending on the size of our data.  In our Friends example, each of our rows is small, so we can cram a bunch of ‘em onto a page.  If we had bigger rows, they might take up multiple pages even just to store one row.  For example, if you added a VARCHAR(MAX) field and stuffed it with data, it would span multiple pages.

Each page is dedicated to just one table.  If we add several different small tables, they’ll each be stored on their own pages, even if they’re really small tables.

If we shut down the SQL Server, started it back up again, and then issued the following query:

SELECT * FROM dbo.Friends WHERE FriendName = 'Brent Ozar'

SQL Server would check to see what page the dbo.Friends table is on, then read our entire 8KB page from disk, and cache that 8KB page in memory.  I say “entire” as if it’s a big deal, but I want to make a point here: pages are stored identically both in memory and on disk, and they’re the smallest unit of caching.  If you use SQL Server’s data compression, the data isn’t uncompressed from the page until it needs to be read again to satisfy another query – you get the benefit of compression in memory as well as on disk.

What happens if we change a data page?  For example, if we issue the following command, what happens:

INSERT dbo.Friends (FriendName) VALUES ('Lady Gaga');

That’s where the log file comes in.

What’s Stored in the Log File (LDF)

The log file is a sequential record of what we did to the data.  SQL Server writes down, start to finish, what we’re trying to do to those helpless, innocent data pages.

Your first reaction is probably, “Wow, I never want to look in there because my users do horrible, unspeakable things to my database server.”  Good news – SQL Server doesn’t need to log the SELECT statements because we’re not affecting the data, and that’s usually where the worst nastiness happens.  Bad news – even if you did want to look in the log file, SQL Server doesn’t give you an easy way to do it.  The log file exists for SQL Server, not for you.

When we insert, update, or delete rows in our table, SQL Server first writes that activity into the log file (LDF).  The log file must get hardened to disk before SQL Server says the transaction is committed.

But not the change to the data page – that part doesn’t have to hit the disk right away.  See, SQL Server knows you’re the kind of person who makes lots of changes to the same data, over and over.  You’re a busy person with things to do and data to trash.  SQL Server can keep the same data page in memory for a while, and then flush it out to disk later – as long as the log file was written.

When Windows crashes hard or somebody pulls the power cables out from under your SQL Server, SQL Server will use the database’s log file on startup.  SQL uses the log file to reconcile the state of the data file, deciding which transactions should be applied to the data file and which ones should be rolled back.

How the Data File and Log File are Accessed

This starts to point to a significant storage difference between these two files.

Log files are written to sequentially, start to finish.  SQL Server doesn’t jump around – it just makes a little to-do list and keeps right on going.  Eventually when it reaches the end of the log file, it’ll either circle back around to the beginning and start again, or it’ll add additional space at the end and keep on writing.  Either way, though, we’re talking about sequential writes.  It’s not that we never read the log file – we do, like when we perform transaction log backups.  However, these are the exception rather than the norm.

Data files, on the other hand, are a jumbled mess of stuff.  You’ve got tables and pages all over the place, and your users are making unpredictable changes all over the place.  SQL Server’s access for data files tends to be random, and it’s a combination of both reads and writes.  The more memory your server has, the less data file reads happen – SQL Server will cache the data pages in memory and just work off that cache rather than reading over and over.  This is why we often suggest stuffing your SQL Server with as much memory as you can afford; it’s cheaper than buying good storage.

More Resources for SQL Server Data Storage

Want to learn more? We’ve got video training explaining it! In our 90 minute video series How to Think Like the SQL Server Engine, you’ll learn:

  • The differences between clustered and nonclustered indexes
  • How (and when) to make a covering index
  • The basics of execution plans
  • What determines sargability
  • How SQL Server estimates query memory requirements
  • What parameter sniffing means, and why it’s not always helpful

For $29, you get 18 months of access to the videos for one person. You can watch them at work, at home, even on your iPad. Learn more about it now.

↑ Back to top
  1. Is that just me or , the video has just the right speaker audible.

  2. “SQL uses the log file to reconcile the state of the data file, deciding which transactions should be applied to the log file and which ones should be rolled back.”

    Shouldn’t it be that it is applied to the data file, not log?

  3. Brent does a great job explaining the basics of data storage in the video. I recommend all developers with any concern about the security of data in SQL Server check out this video. Well done Brent!

  4. If writing to logs is done in a sequential manner is there much benefit to be had in using SSDs for log files?

    • Chris – that’s a fantastic question, and the answer requires thinking a little out of the box – or rather, out of the database. How many databases do you have on the server, and are they all active at the same time? It’s fairly unusual that I see a server with only one active database, so SSDs can end up making a LOT of sense for the log files. However, if you’ve only got one database, like in a data warehouse scenario, you can usually get the throughput you need from 4-6 hard drives in a RAID 10 setup.

  5. The Smiths guitarist was Johnny Marr :)

  6. Pingback: Something for the Weekend - SQL Server Links 22/02/13

  7. Is this blog post (largely) true for SQL Server 2000?

  8. I am grateful to Brent for trying and featuring a preview version of this tool – and happy to announce the first public version is out!

    http://sqlblog.com/blogs/merrill_aldrich/archive/2013/03/01/public-release-sql-server-file-layout-viewer.aspx

  9. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i’m already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, Oracle Portal online training among the Oracle Portal in Hyderabad. Classroom Training in Hyderabad India

    Sql server 2008R2 dba online training USA,CANADA,UK & AUSTRALIA,!

  10. Surely I can’t be the first one to ACTUALLY follow the examples?

    Msg 207, Level 16, State 1, Line 1
    Invalid column name ‘Name’.

    • Louie – HAHAHA, yeah, you could totally be the first person. When I’m reading a blog, I usually just read the examples and assume that they work. You’re probably #1. Nice find! I tweaked that.

  11. Any idea, why they came up with this 8kb thingy?
    Why not 16kb?
    Why not 4kb?
    Or is it because on early 32-bit systems, 1st-level-cache of most CPUs was 8kb?

    Regards

  12. My boss had just asked me yesterday about our SQL backups. I found the data file and the log file. The data file has a timestamp of 07/21/13 @ 22:33 which is the last time I ran the the backup procedure using the SQL Enterprise manager. However, the log file is timestamped at 8:12 this morning (07/25). It is now 12:42 in the afternoon. Why does the log file not have a more current timestamp?? Where are the transactions that have occurred since 8:12 this morning??

    • Hi, Kerry. Unfortunately this kind of fast on-demand troubleshooting doesn’t work well in blog comments. Your best bet will be to call Microsoft if you need that question answered quickly.

  13. Hi,

    I learned lots of things from your blogs, My question is if one table have more then one page file then all page files for that one table stores in sequential manner or not.

    • Hi, Sachin. Let’s think through that. If you’ve got a database that has a lot of tables in it, and you go back to add data for a table that already exists, will there be free pages right there next to it, ready to be used?

      • Don’t know page will available or not, That’s why I ask you Buddy, but if one table has 10 page and the 10th page size is 4kb then my data will start store on 10th page, Am I right ?

        • Your answer is in the first several words of your question. You don’t know if the page will be available or not – therefore, you can’t predict ahead of time where your data will be. Sometimes the question is the answer, buddy. ;-)

  14. If Hard drive is crash or power failure then after restarting the server machine how log file will work to make data file in consistent.

    • Hi, Sachin. That’s a great question, and it’s well beyond the scope of something we can answer quickly in a blog comment. It sounds like you’re on a fun journey to start learning the internals of databases. There’s plenty of SQL Server internals books available to tackle questions like those – just make sure that as you go on that journey, you’re focusing your learning on the parts that will help you get ahead the most. Enjoy!

  15. Hey Brent, you surely did a fine job.

    Thank you very much!

  16. I’m still confused about the term “committed” in regards to a transaction. When you say a transaction is committed, does that mean it is written to the transaction log file on disk or the data file on the disk?

    Thanks

    • When SQL Server gets the data into the transaction log, it tells the application that the transaction has been committed. The data page doesn’t have to make it to disk. If SQL Server crashes after the transaction was committed but before the data page makes it to disk, that’s cleaned up during the startup process of SQL Server. SQL reads the transaction log, figures out which things it needs to redo and which things it needs to undo.

  17. Are functions and stored procedures are stored in the page structure right next to the data?

    Just curious! Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php