In the previous example http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/06/minimal-logging-changes-in-sql-server-2008-part-1.aspx , I described minimal logging while moving data from a source table into a heap. You may recall that it requires...

Please refer to the earlier post for the background information on minimal logging. Starting with SQL Server 2008, the minimal logging has been enhanced. These enhancements are available to regular TSQL Insert as well. One of the key customer scenario...

FILESTREAM is disabled by default in SQL2008. Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine. Enabling/configuring FILESTREAM is a bit different from configuring other SQL features...

This blog shows a very simple example to illustrate data fragmentation, measuring it, seeing behind the scene data and steps to address it. I recommend reading the overall series on index fragementation at http://blogs.msdn.com/sqlserverstorageengine...

In the previous blog I had listed all bulk import optimizations. Now, I will describe each of these optimizations in detail in separate blog entries. Let me first start with minimal logging. By the way minimal logging is also called bulk-logging but the...

Bulk import provides an optimized insert path that minimizes this overhead with following optimizations · Minimal Logging: under this logging mode, individual rows are not logged and only the changes to page allocation structures are logged. This reduces...

Most IT shops using SQL Server need to load/import large amount of data obtained from external sources or from another Database Server into SQL Sever. It is typcially done using the optimized insert path provided by SQL Server through Bulk Insert and...

In the previous blogs, I had described the data compression in SQL Server 2008 and its implications on CPU and IO. One of the question that keeps coming up is ‘Should I compress my database? Or Why does the SQL Server not provide an option to compress...

In the previous blog, I described column-prefix compression that is done as part of PAGE compression. The other component of PAGE compression is the page-dictionary. As the name indicates, this dictionary is specific to a page and the page can be data...

In the previous blog, I had mentioned that the PAGE compression is used to “minimize the data redundancy in columns in one or more rows on a given page”. You may wonder what exactly SQL Server does under the PAGE compression cover? With PAGE compression...

Thanks to all who have contacted me for their interest in the data compression feature. Unfortunately, data compression feature is NOT available in the just released CTP-5 which is now available on the Microsoft Download Center as SQL Server 2008 Community...

Like I had indicated in my previous blog, it is recommended that you estimate compression savings on the object of interest before actually enabling compression as enabling compression is an expensive operation. To show how to estimate data compression...

SQL Server deploys two strategies to compress the data · First, it stores all fixed length data types in variable length format. If you recall, SQL Server 2005/SP2 provided a new variable length storage format decimal and numeric. Please refer to the...

When we think of compressing the database, one of the first question that pops up is why not used the compressed volume? You may know that Windows OS has supported compression of individual files, folders and the entire NTFS volumes since Windows 2000...

I was talking to a customer the other day who had an interesting problem: Successful backups. Specifically, their problem had to do with the success messages that SQL backup puts in the SQL errorlog and the system event log. Seems like a nice, friendly...

So what’s a “fuzzy backup” and how does it relate to STOPAT? There has been some confusion about what RESTORE DATABASE WITH STOPAT does, if it works, and why it behaves as it does. This is not about RESTORE LOG WITH STOPAT, but only RESTORE DATABASE...

Ok, now that we have sort of agreed ( http://blogs.msdn.com/sqlserverstorageengine/archive/2007/09/30/data-compression-why-do-we-need-it.aspx ) that data compression is a good thing, you may wonder how SQL Server compresses the data, what does this compression...

As announced in Tech-Ed 2007, data compression is a new and exciting feature targeted to be available in SQL Server 2008. This is a huge topic to be covered in one BLOG post, I have decided to break it into a series of posts, each building on the previous...

My new blog's not active until September 1st but I wanted to blog about something cool - and totally unrelated to work. Over the weekend Kimberly and I were in Connecticut for the birthday party pig-roast for Carl Franklin (of DotNetRocks fame) along...

Hey folks, This is my last blog post on the SQL Server Storage Engine blog as I've made the decision to leave Microsoft and join Kimberly running her company, SQLskills.com. This lets me indulge my passion for teaching and consulting full-time and...

The session I gave at TechEd this year on 'Secrets of Fast Detection and Recovery from Database Corruptions' was videotaped as part of the Its Showtime! TechEd program. The video is now available to watch at http://www.microsoft.com/emea/itsshowtime/sessionh...

Here’s a topic that’s cropped up several times during Q&As at TechEd this year – what happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Before we get into that discussion, I’ll give...

This year at TechEd there's been even more interest in database mirroring than last year - Kimberly's Always-On Demo-Fest yesterday had a whole bunch of questions during the first demo on mirroring and 50% of the booth questions I've answered have been...

Very exciting!!! On Monday at TechEd we announced the official name of the next version of SQL Server - Microsoft SQL Server 2008 and released the first public CTP (Community Technology Preview) of it for people to start playing with and evaluating. Here...

So I learned something about SQL Server at TechEd today. In Kimberly and Bob Beauchemin's pre-con, Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an aircraft flight-recorder, which I'd never heard of. It's an internal trace that...