I’ve always worked to make ClearTrace perform well.  That’s probably because I spend so much time watching it work.  I’m often going through two or three gigabytes of trace files but I rarely get the chance to run it on a really large set of files.

One of my clients wanted to run a full trace for a week and then analyze the results.  At the end of that week we had 847 200MB trace files for a total of nearly 170GB.

I regularly use 200MB trace files when I monitor production systems.  I usually get around 300,000 statements in a file that size if it’s mostly stored procedures.  So those 847 trace files contained roughly 250 million statements.  (That’s 730 bytes per statement if you’re keeping track.  Newer trace files have some compression in them but I’m not exactly sure what they’re doing.)  On a system running 1,000 statements per second I get a new file every five minutes or so.

It took 27 hours to process these files on an older development box.  That works out to 1.77MB/second.  That means ClearTrace processed about 2,654 statements per second. You can query the data while you’re loading it but I’ve found it works better to use a second instance of ClearTrace to do this.  I’m not sure why yet but I think there’s still some dependency between the two processes.

ClearTrace is almost always CPU bound.  It’s really just a huge, ugly collection of regular expressions.  It only writes a summary to its database at the end of each trace file so that usually isn’t a bottleneck.  At the end of this process, the executable was using roughly 435MB of RAM.  Certainly more than when it started but I think that’s acceptable.

The database where all this is stored started out at 100MB.  After processing 170GB of trace files the database had grown to 203MB.  The space savings are due to the “datawarehouse-ish” design and only storing a summary of each trace file.

You can download ClearTrace for SQL Server 2008 or test out the beta version for SQL Server 2012.  Happy Tuning!

I have a beta version of ClearTrace that supports 2012.  You can download it at http://www.scalesql.com/cleartrace/ClearTrace.2012.40.beta.zip.  Please let me know if you find any issues.

1. Denial: There’s nothing wrong with the code SQL Server has a bug in it. There is a network problem.
2. Anger: You’re doing what in your code?! Why on earth are you doing that? That’s crazy.
3. Bargaining: Fine you can keep your cursor but let’s speed things up a bit.
4. Depression: Ugh, this is so horrible I’m never going to be able to fix all of it.
5. Acceptance: Ok, we’re screwed and we know we’re screwed. This is going to hurt…

Up until SQL 2012, I recommended installing Books On-Line (BOL) anywhere you installed SQL Server.  It made looking up reference information simpler, especially when you were on a server that didn’t have direct Internet access.  That all changed today.  I started the new Help Viewer with a local copy of BOL.  I actually found what I was looking for and closed the app.  Or so I thought.  Then I noticed something.  A little parasite had attached itself to my system. 

Yep, the “Help” system left an “agent” behind.  Now I shouldn’t have to tell you that running application helper agents on server platforms is a bad idea.  And it gets worse.  There is no way to configure the app so that it does NOT start the parasite agent each time you restart help.  So the solution becomes do not install help on production server platforms.  Which is pretty unhelpful.

A new version of this fantastic tool, used by Microsoft BI Stack developers, is out. Now it’s compatible with SQL Server 2012.

In this post, Teo Lachev gives us more details.

This is the project’s Home.

Enjoy!

Last spring I released a utility to script SQL Server configuration information on CodePlex.  I’ve been making small changes in this application as my needs have changed.  The application is a .NET 2.0 console application. 

This utility serves two needs for me.  First it helps with disaster recovery.  All server level objects (logins, jobs, linked servers, audits) are scripted to a single file per object type.  This enables the scripts to be easily run against a DR server.  If these are checked into source control you can view the history of the script and find out what changed and when.

The second goal is to capture what changed inside a database.  Objects inside a database (tables, stored procedures, views, etc.) are each scripted to their own file.  This makes it easier to track the changes to an object over time.  This does include permissions and role membership so you can capture security changes.  My assumption is that a database backup is the primary method of disaster recovery for databases so this utility is designed to capture changes to objects. 

You can find the full list of changes from the original on the Downloads page on CodePlex.

In my current position as Software Engineering Manager I have been through a lot of ups and downs with staffing, ranging from laying-off everyone who was on my team as we went through the great economic downturn in 2007-2008, to numerous rounds of interviewing and hiring contractors, full-time employees, and converting some contractors to employee status.  I have not yet blogged much about my experiences, but I plan to do that more in the next few months.  But before I do that, let me point you to a great article that somebody else wrote on The Unspoken Truth About Managing Geeks that really hits the target.  If you are a non-technical person who manages technical employees, you definitely have to read that article.  And if you are a technical person who has been promoted into management, this article can really help you do your job and communicate up the line of command about your team.  When you move into management with all the new and different demands put on you, it is easy to forget how things work in the tech subculture, and to lose touch with your team.  This article will help you remember what’s going on behind the scenes and perhaps explain why people who used to get along great no longer are, or why things seem to have changed since your promotion.

I have to give credit to Andy Leonard (blog | twitter) for helping me find that article.  I have been reading his series of ramble-rants on managing tech teams, and the above article is linked in the first rant in the series, entitled Goodwill, Negative and Positive.  I have read a handful of his entries in this series and so far I pretty much agree with everything he has said, so of course I would encourage you to read through that series, too.

For those who are not familiar with this add-in, the OLAP PivotTable Extensions add features of interest to Excel 2007 or 2010 PivotTables pointing to an OLAP cube in Analysis Services. One of these features I like very much, is to know the MDX query code associated with the pivot used at that time in Excel:

You can find all the details here:

http://olappivottableextend.codeplex.com/

It was recently released a new version of the add-in (version 0.7.4), which does not introduce any new features, but fixes a significant bug:

Release 0.7.4 now properly handles languages but introduces no new features. International users who run a different Windows language than their Excel UI language may be receiving an error message when they double click a cell and perform drillthrough which reads: "XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value". This error was caused by OLAP PivotTable Extensions in some situations, but release 0.7.4 fixes this problem.

Enjoy!

Have you ever wanted to add more than one Integration Services existing package (e.g. 20 packages) in a SSIS project? Well, you may suppose that an Open Dialog supports multiple files selection to import more than one file at a time ...

BIDS Open Dialog doesn’t allow this, you can just select a single file! Hence the loss of valuable time spent to import the packages one at a time.

Few days ago I learned a trick that solves the problem, thanks to this post by Matt Masson.

Just copy all the packages to import from Windows Explorer (Ctrl + C):

Then just right click on the SSIS Packages folder of the Integration Services project and make a simple Past (CTRL + V):

So “auto-magically” you’ll have all those packages imported in your Integration Services project!!

What can I say... this feature was well hidden!

This marks my last post as a SQLPASS Board member.  I learned a lot during my year of service and I thank everyone involved for this opportunity.  I would especially like to thank the Chapter leaders and Regional Mentors for Virtual Chapters who (mostly) patiently taught me about Virtual Chapters.   I hope the changes I put in place will help strengthen and grow VCs and PASS going forward.  I would also like to thank every one who encouraged me to reach beyond my comfort zone and accept a leadership position within the PASS organization. 

My overall principle was to be a good steward of the PASS community.  Could I have done more?  Always. Did I do enough?  I hope so.  But PASS is a volunteer organization and my time, like yours, is limited.  I have other obligations in life that supersede PASS.  Now I have more time for some of those.  I won’t be going away or leaving the SQL Community.  I will still contribute to the community and support PASS, just in a different role.  Time to let somebody else enjoy the hot seat for a while.

Finally, everyone who voted (not just for me) deserves a thanks.  More voters and more engaged voters, strong candidates, and a vigorous debate were all I wanted out of declaring as a candidate last year. This year the SQL community got exactly that.

Thank you..

“SELECT *” isn’t just hazardous to performance, it can actually return blatantly wrong information.

There are a number of blog posts and articles out there that actively discourage the use of the SELECT * FROM …syntax.  The two most common explanations that I have seen are:

1. Performance:  The SELECT * syntax will return every column in the table, but frequently you really only need a few of the columns, and so by using SELECT * your are retrieving large volumes of data that you don’t need, but the system has to process, marshal across tiers, and so on.  It would be much more efficient to only select the specific columns that you need.
2. Future-proof:  If you are taking other shortcuts in your code, along with using SELECT *, you are setting yourself up for trouble down the road when enhancements are made to the system.  For example, if you use SELECT * to return results from a table into a DataTable in .NET, and then reference columns positionally (e.g. myDataRow[5]) you could end up with bad data if someone happens to add a column into position 3 and skewing all the remaining columns’ ordinal position.  Or if you use INSERT…SELECT * then you will likely run into errors when a new column is added to the source table in any position.

And if you use SELECT * in the definition of a view, you will run into a variation of the future-proof problem mentioned above.  One of the guys on my team, Mike Byther, ran across this in a project we were doing, but fortunately he caught it while we were still in development.  I asked him to put together a test to prove that this was related to the use of SELECT * and not some other anomaly.  I’ll walk you through the test script so you can see for yourself what happens.

We are going to create a table and two views that are based on that table, one of them uses SELECT * and the other explicitly lists the column names.  The script to create these objects is listed below.

IF OBJECT_ID('testtab') IS NOT NULL
   DROP TABLE testtab
go
IF OBJECT_ID('testtab_vw') IS NOT NULL
   DROP VIEW testtab_vw
go   
IF OBJECT_ID('testtab_vw_named') IS NOT NULL
   DROP VIEW testtab_vw_named
go   
CREATE TABLE testtab (col1 NVARCHAR(5) null, col2 NVARCHAR(5) null)
INSERT INTO testtab(col1, col2)
VALUES ('A','B'), ('A','B')
GO
CREATE VIEW testtab_vw AS SELECT * FROM testtab
GO
CREATE VIEW testtab_vw_named AS SELECT col1, col2 FROM testtab
go

Now, to prove that the two views currently return equivalent results, select from them.

SELECT 'star', col1, col2 FROM testtab_vw
SELECT 'named', col1, col2 FROM testtab_vw_named

OK, so far, so good.  Now, what happens if someone makes a change to the definition of the underlying table, and that change results in a new column being inserted between the two existing columns?  (Side note, I normally prefer to append new columns to the end of the table definition, but some people like to keep their columns alphabetized, and for clarity for later people reviewing the schema, it may make sense to group certain columns together.  Whatever the reason, it sometimes happens, and you need to protect yourself and your code from the repercussions.)

DROP TABLE testtab
go
CREATE TABLE testtab (col1 NVARCHAR(5) null, col3 NVARCHAR(5) NULL, col2 NVARCHAR(5) null)
INSERT INTO testtab(col1, col3, col2)
VALUES ('A','C','B'), ('A','C','B')
go
SELECT 'star', col1, col2 FROM testtab_vw
SELECT 'named', col1, col2 FROM testtab_vw_named

I would have expected that the view using SELECT * in its definition would essentially pass-through the column name and still retrieve the correct data, but that is not what happens.  When you run our two select statements again, you see that the View that is based on SELECT * actually retrieves the data based on the ordinal position of the columns at the time that the view was created.  Sure, one work-around is to recreate the View, but you can’t really count on other developers to know the dependencies you have built-in, and they won’t necessarily recreate the view when they refactor the table.

I am sure that there are reasons and justifications for why Views behave this way, but I find it particularly disturbing that you can have code asking for col2, but actually be receiving data from col3.  By the way, for the record, this entire scenario and accompanying test script apply to SQL Server 2008 R2 with Service Pack 1.

So, let the developer beware…know what assumptions are in effect around your code, and keep on discouraging people from using SELECT * syntax in anything but the simplest of ad-hoc queries.

And of course, let’s clean up after ourselves.  To eliminate the database objects created during this test, run the following commands.

DROP TABLE testtab
DROP VIEW testtab_vw
DROP VIEW testtab_vw_named

Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks.  And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them.  You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted.  Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway.

Some of you have seen or read about SQL Server constraints and have applied them to your table designs…unless you're a vendor ;)…and may even use CHECK constraints to limit numeric values, or length of strings, allowable characters and such.  CHECK constraints can, however, do more than that, and can even provide enhanced security and other restrictions.

One tip or trick that I didn't cover very well in the presentation is using constraints to do unusual things; specifically, limiting or preventing inserts into tables.  The idea was to use a CHECK constraint in a way that didn't depend on the actual data:

-- create a table that cannot accept data
CREATE TABLE dbo.JustTryIt(a BIT NOT NULL PRIMARY KEY,
CONSTRAINT chk_no_insert CHECK (GETDATE()=GETDATE()+1))

INSERT dbo.JustTryIt VALUES(1)

I'll let you run that yourself, but I'm sure you'll see that this is a pretty stupid table to have, since the CHECK condition will always be false, and therefore will prevent any data from ever being inserted.  I can't remember why I used this example but it was for some vague and esoteric purpose that applies to about, maybe, zero people.  I come up with a lot of examples like that.

However, if you realize that these CHECKs are not limited to column references, and if you explore the SQL Server function list, you could come up with a few that might be useful.  I'll let the names describe what they do instead of explaining them all:

CREATE TABLE NoSA(a int not null, 
    CONSTRAINT CHK_No_sa CHECK (SUSER_SNAME()<>'sa'))

CREATE TABLE NoSysAdmin(a int not null, 
    CONSTRAINT CHK_No_sysadmin CHECK (IS_SRVROLEMEMBER('sysadmin')=0))

CREATE TABLE NoAdHoc(a int not null, 
    CONSTRAINT CHK_No_AdHoc CHECK (OBJECT_NAME(@@PROCID) IS NOT NULL))

CREATE TABLE NoAdHoc2(a int not null, 
    CONSTRAINT CHK_No_AdHoc2 CHECK (@@NESTLEVEL>0))

CREATE TABLE NoCursors(a int not null, 
    CONSTRAINT CHK_No_Cursors CHECK (@@CURSOR_ROWS=0))

CREATE TABLE ANSI_PADDING_ON(a int not null, 
    CONSTRAINT CHK_ANSI_PADDING_ON CHECK (@@OPTIONS & 16=16))

CREATE TABLE TimeOfDay(a int not null, 
    CONSTRAINT CHK_TimeOfDay CHECK (DATEPART(hour,GETDATE()) BETWEEN 0 AND 1))
GO

-- log in as sa or a sysadmin server role member, and try this:
INSERT NoSA VALUES(1)
INSERT NoSysAdmin VALUES(1)
-- note the difference when using sa vs. non-sa
-- then try it again with a non-sysadmin login

-- see if this works:
INSERT NoAdHoc VALUES(1)
INSERT NoAdHoc2 VALUES(1)
GO

-- then try this:
CREATE PROCEDURE NotAdHoc @val1 int, @val2 int AS
SET NOCOUNT ON;
INSERT NoAdHoc VALUES(@val1)
INSERT NoAdHoc2 VALUES(@val2)
GO

EXEC NotAdHoc 2,2

-- which values got inserted?
SELECT * FROM NoAdHoc
SELECT * FROM NoAdHoc2

-- and this one just makes me happy :)
INSERT NoCursors VALUES(1)
DECLARE curs CURSOR FOR SELECT 1
OPEN curs
INSERT NoCursors VALUES(2)
CLOSE curs
DEALLOCATE curs
INSERT NoCursors VALUES(3)

SELECT * FROM NoCursors

I'll leave the ANSI_PADDING_ON and TimeOfDay tables for you to test on your own, I think you get the idea.  (Also take a look at the NoCursors example, notice anything interesting?) 

The real eye-opener, for me anyway, is the ability to limit bad coding practices like cursors, ad-hoc SQL, and sa use/abuse by using declarative SQL objects.  I'm sure you can see how and why this would come up when discussing Revenge: The SQL.;) And the best part IMHO is that these work on pretty much any version of SQL Server, without needing Policy Based Management, DDL/login triggers, or similar tools to enforce best practices.

All seriousness aside, I highly recommend that you spend some time letting your mind go wild with the possibilities and see how far you can take things.  There are no rules!

(Hmmmm, what can I do with rules?)

#TSQL2sDay

At Pass Summit 2011 a new project was announced. It’s a Microsoft SQL Azure Lab and its codename is Microsoft “Data Explorer”. According to the official blog (http://blogs.msdn.com/b/dataexplorer/), this new tool provides an innovative way to acquire new knowledge from the data that interest you. In a nutshell, Data Explorer allows you to combine data from multiple sources, to publish and share the result. In addition, you can generate data streams in the RESTful open format (Open Data Protocol), and they can then be used by other applications. Nonetheless we can still use Excel or PowerPivot to analyze the results.

Sources can be varied: Excel spreadsheets, text files, databases, Windows Azure Marketplace, etc.. For those who are not familiar with this resource, I strongly suggest you to keep an eye on the data services available to the Marketplace:

https://datamarket.azure.com/browse/Data

To tell the truth, as I read the above blog post, I was tempted to think of the Data Explorer as a "SSIS on Azure" addressed to the Power User. In fact, reading the response from Tim Mallalieu (Group Program Manager of Data Explorer) to the comment made to his post, I had a positive response to my first impression:

“…we originally thinking of ourselves as Self-Service ETL. As we talked to more folks and started partnering with other teams we realized that would be an area that we can add value but that there were more opportunities emerging.”

The typical operations of the ETL phase ( processing and organization of data in different formats) can be obtained thanks to Data Explorer Mashup. This is an image of the tool:

The flexibility in the manipulation of information is given by Data Explorer Formula Language. This is a formula-based Excel-style specific language:

Anyone wishing to know more can check the project page in addition to aforementioned blog:

http://www.microsoft.com/en-us/sqlazurelabs/labs/dataexplorer.aspx

In light of this new project, there is no doubt about the intention of Microsoft to get closer and closer to the Power User, providing him flexible and very easy to use tools for data analysis. The prime example of this is PowerPivot.

The question that remains is always the same: having in a company more Power User will implicitly mean having different data models representing the same reality. But this would inevitably lead to anarchical data management... What do you think about that?

This version adds support for SQL Server 2012 RC0 and fixes a few bugs with SQL History.

Because of the support for regions in SSMS 2012 the regions and debug sections feature has been removed from SSMS Tools Pack for SQL Server 2012.

The feature is still available for previous SSMS versions.

In other news SSMS Tools Pack has won the SQL Magazine bronze award for best free tool of 2011. You can view all the details at the SQL Server Magazine Award page.

Thanx to all the people who voted for it. I'm glad you all like it and use it with great success.

Also I've added a possibility for you to subscribe to email notifications in case the auto-updater doesn't work for you for some reason like being behind a proxy.

Enjoy it!

I’m presenting in Omaha on Writing Faster SQL at 6PM on December 7th.  You can find meeting details on the Omaha SQL Server User Group page. The meeting location requires an RSVP so building security has a list of attendees.

The presentation is a series of suggestions on improving performance.  It ranges from simple things like comparing indexed columns to scalar values up to tips for reducing query compiles and asynchronous processing patterns.  Nearly all of these come from specific issues I’ve encountered working on poorly performing SQL Servers.

It is always a good start when you can steal a title line from one of the best writers in the English language.  Let’s hope I can make the rest of this post live up to the opening. 

One recurring problem with SQL server is moving databases to new servers.  Client applications use a variety of ways to resolve SQL Server names, some of which are not changed easily <cough SharePoint /cough>.  If you happen to be using default instances on both the source and target SQL Server, then the solution is pretty simple.  You create (or bug the network admin until she creates) two DNS “A” records. One points the old name to the new IP address.  The other creates a new alias for the old server, since the original system name is now redirected.  Note this will redirect ALL traffic from the old server to the new server, including RDP and file share connection attempts. 

Figure 1 – Microsoft DNS MMC Snap-In

Figure 2 – DNS New Host Dialog Box

Both records are necessary so you can still access the old server via an alternate name.

Table 1 – Alias List

If you or somebody set up connections via IP address, you deserve to have to go to each app and fix it by hand.  That is the only way to fix that particular foul-up.

If have to deal with Named Instances either as a source or a target, then it gets more complicated.  The standard fix is to use the SQL Server Configuration Manager (or one of its earlier incarnations) to create a SQL client alias to redirect the connection.  This can be a pain installing and configuring the app on multiple client servers.  The good news is that SQL Server Configuration Manager AND all of its earlier versions simply write a few registry keys.  Extracting the keys into a .reg file makes centralized automated deployment a snap.

If the client is a 32-bit system, you have to extract the native key.  If it is a 64-bit, you have to extract the native key and the WoW (32 bit on 64 bit host) key.

First, pick a development system to create the actual registry key.  If you do this repeatedly, you can simply edit an existing registry file.  Create the entry using the SQL Configuration Manager.  You must use a 64-bit system to create the WoW key.  The following example redirects from a named instance “SQL01\SQLUtiluty” to a default instance on “SQL02”.

Figure 3 – SQL Server Configuration Manager - Native

Figure 3 shows the native key listing.

Figure 4 – SQL Server Configuration Manager – WoW

If you think you don’t need the WoW key because your app is 64 it, think again.  SQL Server Management Server is a 32-bit app, as are most SQL test utilities.  Always create both keys for 64-bit target systems.

Now that the keys exist, we can extract them into a .reg file. Fire up REGEDIT and browse to the following location:  HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo.  You can also search the registry for the string value of one of the server names (old or new).

Right click on the “ConnectTo” label and choose “Export”.  Save with an appropriate name and location.  The resulting file should look something like this:

Figure 5 – SQL01_Alias.reg

Repeat the process with the location: HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

Note that if you have multiple alias entries, ALL of the entries will be exported.  In that case, you can edit the file and remove the extra aliases.

You can edit the files together into a single file.  Just leave a blank line between new keys like this:

Figure 6 – SQL01_Alias_All.reg

Of course if you have an automatic way to deploy, it makes sense to have an automatic way to Un-deploy.  To delete a registry key, simply edit the .reg file and replace the target with a “-“ sign like so.

Figure 7 – SQL01_Alias_UNDO.reg

Now we have the ability to move any database to any server without having to install or change any applications on any client server.  The whole process should be transparent to the applications, which makes planning and coordinating database moves a far simpler task.

This is the final blog for my PASS Summit 2011 series.  Well okay, a mini-series, I guess.

On the last day of the conference, I attended Keith Elmore’ and Boris Baryshnikov’s (both from Microsoft) “Introducing the Microsoft SQL Server Code Named “Denali” Performance Dashboard Reports, Jeremiah Peschka’s (blog|twitter) “Rewrite your T-SQL for Great Good!”, and Kimberly Tripp’s (blog|twitter) “Isolated Disasters in VLDBs”.

Keith and Boris talked about the lifecycle of a session, figuring out the running time and the waiting time.  They pointed out the transient nature of the reports.  You could be drilling into it to uncover a problem, but the session may have ended by the time you’ve drilled all of the way down.  Also, the reports are for troubleshooting live problems and not historical ones.  You can use Management Data Warehouse for historical troubleshooting.  The reports provide similar benefits to the Activity Monitor, however Activity Monitor doesn’t provide context sensitive drill through.

One thing I learned in Keith’s and Boris’ session was that the buffer cache hit ratio should really never be below 87% due to the read-ahead mechanism in SQL Server.  When a page is read, it will read the entire extent.  So for every page read, you get 7 more read.  If you need any of those 7 extra pages, well they are already in cache. 

I had a lot of fun in Jeremiah’s session about refactoring code plus I learned a lot.  His slides were visually presented in a fun way, which just made for a more upbeat presentation.  Jeremiah says that before you start refactoring, you should look at your system.  Investigate missing or too many indexes, out-of-date statistics, and other areas that could be leading to your code running slow.  He talked about code standards.  He suggested using common abbreviations for aliases instead of one-letter aliases.  I’m a big offender of one-letter aliases, but he makes a good point.  He said that join order does not matter to the optimizer, but it does matter to those who have to read your code.  Now let’s get into refactoring!

• Eliminate useless things – useless/unneeded joins and columns.  If you don’t need it, get rid of it!
• Instead of using DISTINCT/JOIN, replace with EXISTS
• Simplify your conditions; use UNION or better yet UNION ALL instead of OR to avoid a scan and use indexes for each union query
• Branching logic – instead of IF this, IF that, and on and on…use dynamic SQL (sp_executesql, please!) or use a parameterized query in the application
• Correlated subqueries – YUCK! Replace with a join
• Eliminate repeated patterns

Last, but certainly not least, was Kimberly’s session.  Kimberly is my favorite speaker.  I attended her two-day pre-conference seminar at PASS Summit 2005 as well as a SQL Immersion Event last December.  Did I mention she’s my favorite speaker?  Okay, enough of that.

Kimberly’s session was packed with demos.  I had seen some of it in the SQL Immersion Event, but it was very nice to get a refresher on these, especially since I’ve got a VLDB with some growing pains.  One key takeaway from her session is the idea to use a log shipping solution with a load delay, such as 6, 8, or 24 hours behind the primary.  In the case of say an accidentally dropped table in a VLDB, we could retrieve it from the secondary database rather than waiting an eternity for a restore to complete.  Kimberly let us know that in SQL Server 2012 (it finally has a name!), online rebuilds are supported even if there are LOB columns in your table.  This will simplify custom code that intelligently figures out if an online rebuild is possible.

There was actually one last time slot for sessions that day, but I had an airplane to catch and my kids to see!