Michael J. Swart

January 25, 2012

Rebuild Your Indexes Online (When You Can)

So I was recently reading SQL Server’s blog, specifically the article Customers using SQL Server 2012 today! and some brave businesses are already using and enjoying some of SQL Server 2012 features that help their databases stay available. I’m excited about the new features too. For example, index rebuilds have been improved. We can now rebuild indexes online that include “blob” columns (like nvarchar(max), image, etc…). This means that (almost) every index can be rebuilt without requiring a table lock for the entire operation. And that’s good news for availability!

This wasn’t the case in earlier versions. In earlier versions, you couldn’t rebuild an index online if it included columns that were large strings. If you tried, you would get this message:

An online operation cannot be performed for index 'ix_t1_1' because the 
index contains column '<column name>' of data type text, ntext, image, varchar(max), 
nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered 
index, the column could be an include column of the index. For a clustered 
index, the column could be any column of the table. If DROP_EXISTING is 
used, the column could be part of a new or old index. The operation must be 
performed offline. 

Logic to determine when ONLINE=ON is supported

So that means there’s one more thing to check when finding out whether you can rebuild an index on-line. The logic now goes like this. You can rebuild an index online if:

  • You’re using Enterprise Edition or higher
  • And you’re using SQL Server 2012 or later
  • Or you’re using SQL Server 2008 or earlier
  • And your index is clustered and the table contains no blob columns
  • Or your index is non-clustered and the index includes no blob columns

Hmm… You can’t really put parentheses in a bullet point list. Here try this flow chart:

Clear as mud

Hmm… that really doesn’t clear things up too well either. And I haven’t even mentioned partitioned indexes.

Just Try It

Maybe you’re like me, you may have to deal with multiple versions, multiple editions, and multiple tables and their indexes. Instead of wading through that logic above, just TRY it and let SQL Server figure it out. Here’s a sproc which takes a table name and index name and tries to rebuild the index online. If it can’t, it builds it offline.

create procedure s_TryRebuildOnlineOtherwiseOffline
	@schema sysname = 'dbo',
	@tablename sysname,
	@indexname sysname
	set @schema = QUOTENAME(@schema);
	set @tablename = QUOTENAME(@tablename);
	set @indexname = QUOTENAME(@indexname);
	declare @sqlRebuild nvarchar(max)
	set @sqlRebuild = N'ALTER INDEX ' + @indexname + ' ON ' + @schema + '.' + @tablename + ' REBUILD';
	declare @sqlRebuildOnline nvarchar(max)
	set @sqlRebuildOnline = @sqlRebuild + ' WITH (ONLINE=ON)';
	begin try
		EXEC sp_executesql @sqlRebuildOnline;
		print @sqlRebuildOnline;
	end try
	begin catch
		EXEC sp_executesql @sqlRebuild;
		print @sqlRebuild;
	end catch

Alternatively, you can adjust the script to REORGANIZE an index when it can’t rebuild it online.

By the way, if you use Ola Hallengren’s maintenance scripts, he’s already taken all this logic into account! If you write your own maintenance scripts, feel free to incorporate what I have here.

January 18, 2012

My favourite search terms

Filed under: Miscelleaneous SQL,Tongue In Cheek — Tags: — Michael J. Swart @ 12:00 pm

So I recently looked through my web statistics and I wanted to share some of my favourite search terms. These are words or phrases that people have searched for and for good or bad, they've wound up here on my site. Google Analytics helps me browse these search terms and looking through them I learned that
  • Michael is apparently hard to spell
  • Some people still include question marks in their search queries. It's quaint. I always assume they're asking "Jeeves"
Any way here are my favourites, in no particular order.
  • dba humour
  • trololo
  • trololo guy
  • monkey throwing darts
  • people running from atomic bomb
  • ?
  • swart guts
  • ???
  • is read uncommitted bad?
  • Yes
  • rid lookup good or bad
  • Bad
  • sql undelete
  • Nope, sorry about that. Although
  • how to forget something
  • Someone googled that. There's a story there.
  • reporting services is fun
  • you betcha
  • 10 pockets utility belt
  • Career in construction or Batman wannabe?
  • vampire hierarchy
  • Sorry, you've come to the wrong place, random googler.
  • cartoon cow tossing dog
  • (strangely enough) You've come to the right place, random googler.
  • how to avoid swart
  • HAHAHA! If you find out, let me know.
  • my software never has bugs. it just develops random features
    pivot tables are like good wine you need to learn how to appreciate them
  • 2 things: (1) How did my site come up for these searches and (2) Can we be best friends?
By the way, the phrase "You've come to the right place" reminds me of Engywook, the toothless scientist from the movie The Neverending Story. He's an expert on the Southern Oracle (it's his speciality). So I include him here in the hopes that I get at least one ironic google hit for "Oracle expert":

the (Southern) Oracle Expert

the (Southern) Oracle Expert

January 17, 2012

Clearing out the closet…

Filed under: Data Cartoons,Tongue In Cheek — Michael J. Swart @ 2:31 pm

Hey fellow SQL bloggers,

Have you ever written an amazing blog post that just didn’t pan out because when you reread what you wrote, you found out it was crap? That happens to me sometimes and it’s frustrating. Here are some illustrations that I’ve created in the past that just never made the cut. So I’ll just leave these here.

Clean Your Data


Just slightly hipper than SQLPeople.net

Love Your Data

Inspired by Karen Lopez’s favourite quote (Maybe this should have gone before the first picture).

Brent Ozar PLF

I admit it, I’m a Brent OPLF fanboy. And once upon a time, Jeremiah Peschka’s and his beard reminded me of Yukon Cornelius:



January 13, 2012

Thank you notes…

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

(It’s been a hectic week. The good news is that I’ve got loads of topics to write about. The bad news is that I’ve got no time to do it! Illustrations will make a return next week too because I’m finally getting that Adobe Illustrator license I’ve had my eye on. Maybe I’ll also include a retroactive Jimmy Fallon illustration).

Aaron Bertrand maintains the definitive list of free SQL Server tools. I want to give a shout out to just a couple of them. Without these tools, this week would have been a lot rougher at work for our team.


Thank you Adam Machanic,
For making a lightweight sproc to help me see at a glance the activity on a SQL Server database. (Also kudos to the parameters @get_plans and @find_block_leaders).

If you’re unfamiliar with this stored procedure, start here.

Plan Explorer

Thank you SQL Sentry,
For making a tool that helps me understand everything about a query plan is doing. (Also kudos to the Expressions tab and the Parameters tab).

Plan Explorer presents SQL Server query plans just a bit nicer than SQL Server Management Studio does. When looking at a problematic query plan, I want to go from looking at a plan to a solution as quick as possible. For me this week, it would be safe to say that Plan Explorer cut that analysis time in half.

If you’re unfamiliar with this tool, start here.

Dynamic Management Views

Thank you Microsoft (SQL-Server-2005-and-later-DMVs),
For tracking statistics about query executions and making it possible to examine the history of the db activity without having to trace server activity (so extra kudos to dm_exec_query_stats, dm_exec_cached_plans and dm_exec_query_plan).

I don’t know whether to call these views free. They’re available with any database at SQL Server 2005 (or later). So if you’re using SQL Server, then you can use these DMVs.

If you’re unfamiliar with these views, start here.

January 4, 2012

Then, Now and Later

Filed under: SQLServerPedia Syndication — Tags: , — Michael J. Swart @ 12:00 pm

So happy New Year! This is the week that a lot of people look back on their year and make plans for their new year. Well, I’m no different. This week I’m taking a break from the technical content to take a look at 2011 and what 2012 might bring.

The Past (Looking back at 2011)

I started blogging a couple years ago. I picked the domain MichaelJSwart.com (with the middle initial) because MichaelSwart.com was taken at the time. I liked the sound of it any way, probably because of Michael J. Fox. My favorite character of his was Marty McFly:

And a year ago, (prompted by Jenn McCown’s t-sql tuesday post about resolutions) I made a new year’s resolution to blog weekly, keep the articles technical and to include illustrations.

And I had an absolute blast doing it! So it was easy to keep it up:

  • The topics came from whatever interested me. Luckily I have an interesting job with new and different challenges almost daily.
  • The time came as by treating the blog as a hobby. I figure the average person has that much free time, it’s just a matter of how to spend it.
  • I like to write when I’m feeling particularly awake.
  • I like to draw when I’m not because it’s more fun and it probably uses a different part of the brain so that it feels like less mental effort.

I particpated in a couple SQL Saturdays. The first one in Cleveland was great. Among other things, I learned that if you visit Cleveland’s art museum at 10 am on  Superbowl Sunday, you have the entire museum to yourself. The second SQL Saturday I attended this year was in my own backyard. I presented for the first time in Toronto and it was exciting.

The Present (this week)

So I was excited to learn this week that I received Microsoft’s Most Valuable Professional (MVP) award for SQL Server (achievement unlocked!). I feel honoured to receive the award and inspired to continue writing. I feel like I’ve learned and benefited from the SQL crowd more than I provided (expertise consumer first, expertise provider second). So many people have helped me but I’m not going to name names. That’s next week – seriously 🙂 .

The Future (2012)

So I’m going to resolve to continue blogging the way I have for the past year. It’s a challenging (if not a surprising) resolution. It’s challenging even though this resolution boils down to resolving to continue a fun hobby. This starts now but I’m going to hold off on the technical stuff for just one more week: I want to write something about people who inspire me (and why they might inspire you too). Stay tuned.

And also this year, I’m going to try something new. I want to try to participate in a local user group. Easier said than done because for me, there is no local user group and I’m going to do something about that. (Ohioans, I love you but the commute is killer 🙂 )

It’s all very exciting. Look out 2012, you won’t know what hit you.

Powered by WordPress