A couple more questions from the last couple of classes.

Q1) Why doesn't performing an index rebuild alter the fragmentation?

A1) Here are the possibilities - all of which I've seen happen:

• There isn't an index - either DBCC DBREINDEX or ALTER INDEX ... REBUILD are being run on a table that only has a heap, and so the (extent) fragmentation of the heap isn't changing because there's no way to rebuild a heap (except by the heavily NOT recommended method of creating and then dropping a clustered index).
• The index is too small. An index with only a handful of pages may not show any changes in fragmentation because all the pages are single, mixed pages (see my previous post on extent types for more info) and so rebuilding the index does nothing.
• The workload and schema are such that by the time the rebuild has finished and the fragmentation calculation has been done again, the index is already getting fragmented again.
• The Extent Scan Fragmentation result from DBCC SHOWCONTIG is being used to gauge fragmentation for an index stored in a filegroup with multiple files. The Extent Scan Fragmentation in DBCC SHOWCONTIG does not cope with multiple files (as is documented in Books Online) and so the value may even go UP in some cases!

Q2) What operations take advantage of minimal-logging when the recovery mode is BULK_LOGGED?

A2) The list is very small - 4 four classes of operations:

• Index builds, rebuilds, or drop of a clustered index (NOT index defrags with DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE - this is a common misconception).
• Bulk load operations (i.e. BCP, INSERT ... SELECT * FROM OPENROWSET (BULK...), and BULK INSERT).
• Insert or appends of LOB data (either using WRITETEXT/UPDATETEXT for TEXT/NTEXT/IMAGE data types, or UPDATE with a .WRITE clause).
• SELECT INTO operations on permanent tables.

For these operations, only the allocations are logged in the transaction log. Any extents that are allocated and changed through a minimally-logged operation are marked in the ML bitmaps (one for every 4GB of each file) and then the next transaction log backup will also read all those extents and include them in the backup.

This is a question that came up yesterday in out Disaster Recovery class so I'm typing it up in between attending sessions at Microsoft Day here at SQL Connections. It's an interesting experience watching all the MS speakers walking around in the distinctive blue shirts and no longer having to wear one myself.

The question is the following - why does DBCC CHECKDB terminate with an out-of-space error in SS2005? A customer had a 500GB database spread over 17 LUNs on a SAN, with each LUN having only 5GB free. There's a heavy concurrent workload running while CHECKDB is running and very often it doesn't compelte but instead stops with an error. What's going on?

Sitting here in our Disaster Recovery class at SQL Connections and Kimberly's on till lunch so I'm banging out a quick blog post covering the database mirroring (DBM) specific questions.

Q1) Can I use IP addresses instead of server names when using the DBM Monitor?

A1) Unfortunately not.

Q2) Is there any in-built throttling mechanism in DBM to allow the mirror to catch-up when synchronizing a synchronous mirroring session?

A2) Yes, if there's more than 1MB of transaction log on the principal that hasn't been sent to the mirror, the mirroring session state will be switched from SYNCHRONIZED to SYNCHRONIZING and the principal itself will start adding a few milliseconds delay to transaction commits until the amount of unsent log drops below 1MB

Q3) Are there any tips when setting up a mirroring session using backups?

A3) Yes, make sure that all the backups are restored WITH NORECOVERY on the mirror. The database has to be unrecovered otherwise the mirroring session cannot start and you're back to square one with restoring the mirror database.

Q4) What are the performance considerations with DBM - both in terms of the impact on the application workload and on DBM itself?

A4) Here are some links to resources that discuss this:

• Whitepaper: Database Mirroring: Best Practices and Performance Considerations
• Whitepaper: Database Mirroring: Alerting on Database Mirroring Events
• MSDN webcast - look at Sessions 8 and 9: TechNet Webcast Series for the ITPro - Series Links
• TechEd session based on the 1st whitepaper above

Q5) Any other resources?

A5) Blog posts...

• SQL Server 2008: Automatic Page Repair with Database Mirroring
• Search Engine Q&A #2: Moving a database while Database Mirroring is running
• SQL Server 2008: New Performance Counters for Database Mirroring
• Search Engine Q&A #3: Database mirroring failover types and partner timeouts
• SQL Server 2008: Performance boost for Database Mirroring
• More on Database Mirroring performance and index maintenance
• Search Engine Q&A #8: How can defragging an index break synchronous mirroring? And what happens?

Enjoy!

Quick post this morning before this gets knocked out of my head by the Disaster Recovery session we're doing today at SQL Connections. This came up yesterday for a few people both on 2000 and 2005 - the database is running in SIMPLE recovery mode but the log isn't getting cleared as it usually does with checkpoints. There are two possibilities here - one for 2000 and one for both 2000 and 2005.

Very large VLFs

If the transaction log is comprised of very large (multi-GB) virtual log files, then a checkpoint operation may not clear out all the active log from the VLF - and so the log may not clear as frequently as when its comprised of more, smaller VLFs. Kimberly's blog post yesterday touched on this plus a solution.

SQL 2000 checkpoint bug

This is one I hadn't heard of. There's a bug in 2000 SP4 that stops checkpoint working properly on some configurations and so the log file can grow until it runs out of space. A few people in yesterdays Database Maintenance class were hitting this. Searching through the Knowledge Base articles turned up KB 909369 which explains the issue and has a pointer to the hotfix.

Hope this helps some of you out.

Q1) I have a filegroup with two files. I add a third file, use some of the space, and then do a rebuild of the index that takes up most of the space in the original two files. Why doesn't the newly rebuilt index get spread evenly across the three files? I.e. why doesn't SQL Server rebalance the data across the files?

A1) There are two things to consider here. The first is the way that SQL Server allocates space from multiple files in a filegroup. It uses a mechanism called proportional-fill that will allocates space from files in round-robin fashion, but weights the allocations towards files that are larger and have more free space. In the example above, the space in the newly-added third file will be used before the first and second files are grown to add more space. The second thing to consider is that the process of rebuilding an index requires building a new copy of the index before dropping the old on - so in the example above, the existing allocated space can't be reused until after the index rebuild operation completes.

The concept of adding a file and having SQL Server rebalance the data across the files doesn't exist. It was something I proposed during SQL Server 2005 development but we (seriously) didn't have time to do it. The solution I recommend is to create a new filegroup with as many files as you need, rebuild the index into the new filegroup using the CREATE INDEX WITH DROP_EXISTING command, and then drop the old filegroup.

Q2) Multiple questions about whether a non-clustered index gets rebuilt on SQL Server 2005 under different circumstances

A2) See the blog post I wrote here which goes into all the different combinations. A lot of the confusion comes from the fact that on SQL Server 2000, for non-unique clustered indexes where SQL Server has to generate a uniquifying value (called a uniquifier), when it gets rebuilt all the non-clustered indexes have to be rebuilt too as the uniquifier values are regenerated. On SQL Server 2005 this is not the case - a BIG improvement.

Q3) A lot of the features we discussed (e.g. partitioning and online operations) are in Enterprise Edition only. What's the complete list of features that are in Enterprise vs Standard Editions for SQL Server 2005?

A3) The best list we know of is in MSDN - http://msdn2.microsoft.com/en-us/library/ms143761.aspx

Q4) In a maintenance plan, is it a good idea to do an index rebuild followed by an update statistics?

A4) No! An index rebuild will do the equivalent of an update stats with a full scan. A manual update stats will use whichever sampling rate was set for that particular set of statistics. So - not only does doing an update stats after an index rebuild waste resources, you may actually end of with a worse set of stats if the manualy update stats only does a sampled scan.

Q5) How can you tell whether DBCC CHECKDB is doing a deep-dive that's going to take much longer than usual?

A5) There are several algorithms in DBCC CHECKDB that are designed to quickly tell whether a corruption exists or not, but at the expense of not being able to tell exactly where the corruption is. The justification for this is that corruptions are not very common (considering the millions of times per day that DBCC CHECKDB is run) and so it makes sense to engineer for the success case and take the hit of a longer run-time in the error case. When a corruption is discovered there hasn't been any to tell that DBCC CHECKDB is going to run logner than usual until SQL Server 2005 SP2. In SP2 a new error, 5268 was added that will be output to the errorlog when one of the deep-dive algorithms is triggered. I advise you to add an alert on this error.

Q6) What are the various forums where Paul answers questions on corruptions?

A6) There are 4 forums that I (and others with good advice also) frequent:

• The SQL Team Data Corruption Forum
• The MSDN Disaster Recovery and Availability Forum that I founded while I was at Microsoft
• The SQL Server Central 2005 Data Corruption Forum
• The SQL Server Central 2000/7.0 Data Corruption Forum

I'm also happy for you to send me email! Just beware that on forum posts and emails you may not get a reply from anyone for a day or more - so if you have a critical server-down issue then you should contact SQL Product Support.

Ok - that's enough for tonight. More tomorrow from the Disaster Recovery workshop. Thanks to everyone who came to the workshop today!

After all the build-up over the last few weeks (putting finishing touches to decks and demos), we're finally off to the last set of conferences for the year. First up is SQL Connections in Las Vegas, with over 5000 attendees!!. We flew down yesterday from Seattle to hang out for an extra few days before the conference, as unbelievably after 9 years in the US it's my first time ever in Las Vegas.

It's a kind of a weird place - you're flying along over desert and mountains and suddenly there's a city in the middle of nowhere. The first landmark I could see that positively ID'd it as Vegas was the distinctive black pyramid of the Luxor hotel - we're actually looking down on it from our room in THE hotel (totally pretentious name but nice rooms). Looking out at the Strip, it seems like every second or third car is a limo of some description.

Now that we're here, we're making full use of the extra time to take in some of the sights and sounds. We had dinner last night at Mix with our good friends Gert Drapers and his wife Karen, plus Michele Leroux Bustamante and her husband Andres. The food was great - I had calamari risotto plus beef tenderloin with foie gras and truffles - and Andres chose some really nice wine for us all. Unfortunately, Kimberly didn't enjoy her entree at all - but she did digress from her usual shrimps and scallops choices (her appetizer was a stunning scallop dish) so I wasn't totally surprised. Actually I tried some of her dish too - cod with sweet crumbly pastry over capers, eggplant, olives, and lemon - not how it was described on the menu and totally not something I'd recommend - and I *love* seafood. Tonight we're heading out again with Michele and Andres to see Elton John playing at Ceasar's Palace. Tomorrow the work begins...

So given that we're doing a pre-pre con tomorrow (see my previous post here for all the details), we needed to head over to the conference centre in the Mandalay Bay to pick up our speaker-shirts and badges. After walking for what seemed like miles through casinos and corridors - Vegas is *busy* - we arrived. Here's the sight that greeted us - a small army of conference crew stuffing bags and finalizing registration details.

This is a pretty exciting conference for us - it's our first as a married couple! Almost all of our sessions are together and for the few that it's only Kimberly speaking I'll either sit at the back and heckle or maybe try my hand at blackjack or some other cunning way of giving all our money away very quickly. I suspect Kimberly would rather I heckle

Seriously though, we *love* presenting together, which is why I left Microsoft in the first place and other the next few weeks we'll certainly be doing a lot of it - with TechEd IT Forum in Barcelona straight after SQL Connections and then a TechNet Deep-Dive on Database Maintenance in Zurich the week after that. When we're done we can finally crash for the year as the next work trip isn't until mid-January when we head to China to teach some more classes (Kimberly's turn to experience somewhere new).

One thing we're definitely going to do while we're on the road is blog lots - at least one of us will post every day with some tidbit of info or answer to an interesting question that came up during a session or workshop.

Hopefully we'll see you at one of these events - stop by and say hi!

This is a quick answer to a question I was sent today by someone who'd read Kimberly's partitioning whitepaper - Partitioned Tables and Indexes in SQL Server 2005 - and is implementing a "sliding-window" scenario. (This is a mechanism to allow fast insertion and deletion of significant portions of data into/from a partitioned production table. Insertion is done by taking a table and making it a new partition of the production table - called switching-in. Deletion is done by removing a partition from the production table and making it into a stand-alone table - called switching-out.)

The question is - what indexes are required on the staging table to prevent the ALTER TABLE ... SWITCH PARTITION statement from failing with a message like that below:

Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'PartitionTest.dbo.StagingTable' for the index 'NC_Birthday' in target table 'PartitionTest.dbo.ProductionTable'.

The answer is that the staging table has to have the exact same indexes - clustered and non-clustered - as the production table. I asked Kimberly if it has to have the same constraints too - the answer is yes, plus the staging table has to have a trusted constraint on it such that SQL Server can tell (without checking all the data in the staging table) that all the data satisfies the partitioning function for the partition that you're switching-in (i.e. the partition that the staging table will become in the production table). If it doesn't, the switching-in will fail with the following error:

Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionTest.dbo.StagingTable' allow values that are not allowed by range defined by partition 4 on target table 'PartitionTest.dbo.ProductionTable'.

One thing that confuses people is that SQL Server does not create the target table for you when doing a switch-out of a partition. The target table has to exist and have the exact same schema as the production table. Also, it has to be completely empty - otherwise you'll get an error like:

Msg 4905, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The target table 'PartitionTest.dbo.StagingTable' must be empty.

The must-be-empty requirement also holds for switching-in operations - the partition that will be created has to be empty otherwise a similar 4904 error results.

Hope this helps!

There's a well known problem that every time a backup operation succeeds a message is written into the error log and Windows event log. If you're taking very frequent log backups (say every 5 minutes) of multiple databases, that's a significant amount of clutter in the logs. Well - now there's a fix!

Kevin Farlee, the Storage Engine PM responsible for (among many other things) BACKUP and RESTORE has just blogged about a trace flag - 3226 - that's been in the product since SQL Server 2000 that will suppress the success messages. He's planning to document this (and other) trace flags in this area starting in SQL Server 2008. Excellent!

Myth 1: A full database backup only contains the transaction log from the start of the backup to the end of the backup

When you restore a full database backup, you get a transactionally consistent database. Consider the case where there's an active transaction that doesn't commit until after the backup completes. If the backup only contained the log that occured while the database was being backed up, how would it roll back the active transaction. It *has* to include enough transaction log to roll back the active transaction. The start LSN of the log included in a database backup is the minimum of:

• LSN of the last checkpoint
• LSN of the start of the oldest active transaction
• LSN of the last replicated transaction

Let me prove it to you. I'm going to create a database, put it into FULL recovery mode, start a transaction, checkpoint, and then take a backup. The checkpoint ensures the page I've altered is flushed to disk.

CREATE DATABASE stopattest;

GO

ALTER DATABASE stopattest SET RECOVERY FULL;

GO

BACKUP DATABASE stopattest TO DISK = 'c:\sqlskills\stopattest.bck' WITH INIT;

GO

USE stopattest;

GO

CREATE TABLE t1 (c1 INT);

GO

BEGIN TRAN;

INSERT INTO t1 VALUES (1);

GO

Now in another connection I'll take another full database backup.

BACKUP DATABASE stopattest TO DISK = 'c:\sqlskills\stopattest.bck' WITH INIT;

GO

The msdb.dbo.backupmedia table will tell us the relevant LSNs in the backup (I added some spaces to delineate the prts of the LSN for clarity):

SELECT last_lsn, checkpoint_lsn, database_backup_lsn FROM msdb.dbo.backupset

WHERE database_name = 'stopattest';

GO

last_lsn              checkpoint_lsn        database_backup_lsn
--------------------- --------------------- ---------------------
21 0000000190 00001   21 0000000174 00037    21 0000000058 00037

[Edit: After swapping some email with Kalen Delaney, I realized that when I originally put this together I had more log records in the post and when I removed them I messed up the description of the (21:174:37) LSN - its now corrected below)

So you can see the checkpoint that begins the backup was at (21:174:37). The LSN of the first log record that the backup contains is (21:58:37), which is before the start of the backup. And the backup contains all the log from then until (21:190:1). Now let's look at the actual transaction log to see what these LSNs correspond to.

SELECT [Current LSN], Operation, [Transaction Name] FROM fn_dblog (null,null);

GO

Here's some selected output:

Current LSN              Operation        Transaction Name
------------------------ ---------------- ------------------
00000015:0000003a:0025   LOP_BEGIN_CKPT   NULL
                (this is the calculated minimum LSN the backup must contain (21:58:37) - which is (15:3a:25) in hex)
.
.
00000015:00000061:0001   LOP_BEGIN_XACT   user_transaction
                (here's my transaction starting - before the backup started but within the LSN range contained in the backup)
.
.
00000015:000000ab:0004   LOP_BEGIN_XACT   Backup:InvalidateDiffMaps
                (this is the backup clearing the differential bitmaps)
.
.
00000015:000000ae:0025  LOP_BEGIN_CKPT    NULL
                (this is the checkpoint that BACKUP does - matching the checkpoint LSN above)
.
.

So - this clearly shows that the backup contains more than just the log from the time the backup was running.

Myth 2: It's possible to do a STOPAT with only a full database backup

This myth is that its possible to use STOPAT with a full database backup to stop during the time the backup was being taken. The argument FOR this myth is that the backup contains the log for all the changes that happened while the backup was being taken, so it must be possible to stop at any point in time. Technically, that's correct, but in practice it's wrong - you cannot stop at a point while the backup was running, using only the database backup.

This one's more complicated to explain. Doing a STOPAT operation means getting the database to a state where operations later than the time or LSN specified in the STOPAT clause haven't affected the database yet. A database backup reads pages that may or may not have been changed while the backup was running. If they are changed, it could be at any point while the backup is running.

Consider the case where page X is changed at LSN (10:45:12), *just* before the backup completes and is read by the backup at the time equivalent to LSN (10:45:13). The backup will contain the changed page image, plus the log record for the change. What if I want to stop at a point while the backup was running but *before* the change to page X, say at LSN (10:44:00). The backup only contains the image of page X at LSN (10:45:12) - how can it be put back to the image at the time we want to stop at? The argument is that we have the log record for the change - can't SQL Server just undo it?

No. It won't even see it. STOPAT works by recovering the database up to the point that the STOPAT specified. If we ask to stop at LSN (10:44:00), then the log will only be read and recovered up to that point. However, because the database backup didn't read page X until LSN (10:45:13), it only has the image of it from when it was altered at (10:45:12). This clearly won't give a database image as of (10:44:00).

The only way to stop at a particular time/LSN, is to have images of *all* database pages from before that time/LSN (i.e. from the *previous* database backup) and then restore all the transaction logs up to and including the time/LSN to stop at.

Hopefully that makes sense.

(Been a few days since I posted - had some real work to do Today I'll post a few things from the queue that's been building up)

This is part Q&A and part follow-on from my last post about running index maintenance when a database is mirrored.

A customer has a maintenance plan that involves running regular ALTER INDEX ... REORGANIZE on a 100GB clustered index to remove fragmentation. Three weeks ago they added database mirroring, with the database setup for synchronous mirroring. Every so often, they see the state of the mirror change from SYNCHRONIZED to SYNCHRONIZING and then a bit later back to SYNCHRONIZED. What's going on? Once a synchronously-mirrored database is synchronized, it should ever get out of sync, right?

Well not quite - if the communication link between the principal and the mirror is broken, then the mirror becomes unsynchronized. The exact behavior in this situation depends on how mirroring is setup and what's failed:

1. If there's no witness instance, then transactions will continue on the principal database but the transaction log starts to grow, because the transactions can't be cleared from the principal's log (even after a log backup) until they've been sent to the mirror. The database is running 'exposed'. Once the link is reestablished, the mirror while synchronize again.
2. If there's a witness, and the witness can still talk to the principal, then everything continues as in #1
3. If there's a witness, and the communication link between it and principal is also broken, the the principal will stop serving the database - transactions will stop. In this case, if the mirror and the witness can still see each other, then a failover will occur.

There are some great Books Online entries that describe all of this - see http://msdn2.microsoft.com/en-us/library/ms179344.aspx to start with.

The customer had situation #1. Every so often the mirror would change state and it seemed to coincide with the defrag job. Looking in the error log shows messages like:

2007-10-24 11:43:36.21 spid23s     Error: 1474, Severity: 16, State: 1.

2007-10-24 11:43:36.21 spid23s     Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://roadrunnerpr.sqlskills.com:5022'.

So the network link was dying sometimes when the defrag was running - that explains the switch between SYNCHRONIZED and SYNCHRONIZING. Why the network link was dying is still under investigation but it seems like the additional transaction log generated by the defrag job was causing the network to become overloaded and some component of it wasn't behaving correctly under load.

There are a few things to learn from this:

1. Not only do you need to make sure that your IO subsystem can handle the load on it correctly, you also need to make sure your network can handle the load on it. There are a bunch of tools available to stress-test network paths - one simple one is TrafficEmulator.
2. When you're running on your test system before going into production, make sure you test *everything* as if you were running in production - including maintenance jobs because they can add significant load to a production system.
3. When you implement an HA solution such as mirroring, consider all the ways that transaction log will be generated when figuring out the required network bandwidth to support your HA configuration - something like a defrag or rebuild can cause an enormous spike in log generation

Two weeks to go until the conferences start...

I've revamped the main Upcoming Events page on SQLskills.com to list all the DBA/IT-Pro, Developer, and BI sessions we're collectively doing at SQL Connections Fall, Microsoft TechEd Developers, Microsoft TechEd IT Forum, and a Microsoft TechNet Deep-Dive in November. (Now that I've finished my Lego model, what else am I going to do on a wet Sunday afternoon?)

And there's a lot of it - 4 full-day workshops, 17 sessions, 9 chalk-talks, 3 instructor-led labs, and 1 lunchtime demo session - but they're filling up fast and it looks like we'll be doing a few repeats at IT Forum. Anyone coming to any of our full-day workshops will get one of our DVDs packed full of Hands-On labs - incidentally, the Always-On DVD will be the base for our post-con workshop at SQL Connections.

One of the events I haven't mentioned before - the TechNet Deep-Dive. This is being organized by Microsoft in Switzerland and will be a full-day workshop taught by Kimberly and I on database maintenance. Checkout the link above for details on how to register.

We've had some questions about which of the full-day workshops are applicable to customers running SQL Server 2000. Well, a lot of the technology we talk about was introduced in SQL Server 2005, but the concepts and best-practices remain the same. I'd say that the database maintenance workshop probably has the most content that will transfer to SQL Server 2000. However, if you're planning to upgrade to SQL Server 2005 or 2008, these workshops are packed full of info on new features and syntax that you'll need to know.

Look forward to seeing you at one of the events!

Two of the cool features in SQL Server 2005 are CROSS APPLY and DMVs (Dynamic Management Views). Now, far be it for me to get my hands dirty explaining developer stuff like CROSS APPLY but I was having a discussion with Colin Leversuch-Roberts in the UK about the composability limitations of the sys.dm_db_index_physical_stats DMV. (Btw - you should check out Colin's blog post series on Analysing Indexes - lots of useful stuff).

So CROSS APPLY lets you do join-like functionality with table-valued functions that take parameters - which you can't do using JOIN. This works for most of the DMVs, but some of them are written to an older internal implementation that doesn't support CROSS APPLY, and sys.dm_db_index_physical_stats is one of them. If you try it you'll get an error like:

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 formulas.

In case you're interested, the part of the formula I had incorrect was for figuring out how many pages would be needed for the upper levels of an index b-tree. My formula was:

Num_Index_Pages = ∑_Level (Index_Rows_Per_Page)^{Level – 1}
where 1 <= Level <= Levels

and Øystein came up with a better formula:

Num_Index_Pages = ∑_Level (Total_Num_Rows/(Index_Rows_Per_Page)^Level )
where 1 <= Level <= Levels

where in both formulas:

Levels = 1 + log _{Index_Rows_Per_Page} (Total_Num_Rows / Index_Rows_Per_Page)

Check out Øystein's cool tool at http://dbgoby.blogspot.com/2007/10/db-goby-v10.html!

Today I finally managed to hook-up with SQL Server MVP and Regional Director Greg Low so he could interview me for his podcast show - we'd spent the last few months juggling schedules and time-zone differences (he's 17 hours ahead of Redmond) but today the stars aligned and we made it. You can download the interview at www.SQLDownUnder.com - show #24. Thanks Greg!

Ok - this is really geeky, but I love making models, and especially the harder Lego models. Lego is one of the coolest toys ever and I've been a big fan since I was a small child. Lego announced in the Spring their biggest Lego model ever - a large scale Ultimate Collector's Millenium Falcon^TM with 5195 pieces (check it out here on the Lego site). I pre-ordered mine right away!

Kimberly was away teaching for Microsoft in India the first two weeks of October this year. So what does Paul do when Kimberly's away? Well, apart from feverishly blogging on my new blog, I made the Falcon, which arrived the day before she left. I reckon it took me about 25-30 hours total time over the course of two weeks.

Here's a picture of the finished model, with a Diet Pepsi can alongside to give you an idea of the scale (click on it to get to a hi-res image you can scroll around to see the detail). Below is a bit of detail blow-up featuring my nick-name-sake Chewie.

      Pretty cool eh?

There's been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs.

Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down to the amount of transaction log generated and whether this causes a problem.

• In synchronous mirroring, the additional log being generated could overload the network link between the principal and mirror. This will slow down the hardening of regular transactions in the mirror's transaction log, and thus led to a decrease in transaction throughput on the principal.
• In asynchronous mirroring, the additional log being generated could again overload the network link - but this time there's no requirement for transactions to harden in the mirror before they can commit on the principal, so instead the SEND queue on the principal gets really large. Now, in asynchronous mirroring the SEND queue represents the amount of work that will be lost if a failover occurs, as its all the transaction log that hasn't yet been sent to the mirror. So, in asynchronous mode, a large index rebuild operation could lead to increase exposure to data loss in the event of a failover.

In SS2008, the log stream compression I blogged about here should go a long way to alleviating this problem. There are three ways I can think of to reduce the amount of transaction log generated by and index maintenance plan in SS2005:

1. Use a potentially less expensive (in terms of logging) solution for removing index fragmentation. Doing an index rebuild is guaranteed to generate an equivalent amount of transaction log to the size of the index being rebuilt, no matter how much fragmentation there is - because an index rebuild always rebuilds the entire index. The alternative is to do an index reorganize, either using my old DBCC INDEXDEFRAG or the new ALTER INDEX ... REORGANIZE. These will only generate transaction log when index pages are compacted and reorganized - so for less heavily fragmented indexes. There's no hard and fast rule here but I generally say where Logical Scan Fragmentation/Average Fragmentation in Percent from DBCC SHOWCONTIG/sys.dm_db_index_physical_stats, respectively, is less than 30%. You also need to consider page density too - but really this is a topic for a whole other post.
2. Be very selective on which indexes you choose to rebuild/reorganize. A lot of people have a maintenance plan that does this for every index every week, without checking whether the index is even fragmented or whether removing fragmentation for an index improves workload performance. Again, this a whole other topic but there is an old whitepaper for SS2000 I helped with that describes some of this - Microsoft SQL Server 2000 Index Defragmentation Best Practices.
3. Partition the tables/indexes so that the changing portion of the data is the only portion that's affected by index maintenance. If most of your data is read-only, there's no point in having it included in reindex/reorganize operations, right? Kimberly recently wrote a blog post about such an architecture here.

For more info on database mirroring performance considerations, checkout the whitepaper Database Mirroring: Best Practices and Performance Considerations. There's also a slide deck presentation based on this whitepaper that's been presented at various TechEds (I did it in China and Hong Kong last year and Kimberly did it in South Africa last year) - you can download it from the Hong Kong website here.

The bottom line is when mirroring is in the mix, you need to be more intentional with your database maintenance.

Thanks for your patience and to all those who emailed to let me know. All the SQLskills.com blogs have been updated to the latest dasBlog version and everything's working again. I'd appreciate you taking the time to go back and re-enter any comments you tried to over the last few days.

Thanks!