TechEd Sri Lanka 2010: Two Sessions and a Room Full of Questions

TechEd Sri Lanka took place at Waters Edge in Colombo from February 8 to 10, 2010. I presented two sessions there. It was one of the largest successful technical events in Sri Lanka. I was surprised by how technically sound it was and how excited the attendees were.

The attendees at TechEd Sri Lanka asked many interesting questions in both of my sessions. The index session received a rating of 8.9, and the change data capture session received 7.8. Both scored above average, and I’m grateful to everyone who sent feedback.

TechEd Sri Lanka at Waters Edge

The conference ran for three days in Colombo. Each of my sessions took on an old problem. One was the damage a badly chosen index does. The other was how to keep a history of changes to your data.

The entrance arch

Two other Solid Quality Mentors, Rushabh Mehta and Joy Ratnayake, also presented at the event, each on other topics.

Rushabh Mehta presenting

The Index Session

The first session was The Other Side of SQL Server Index: Advanced Solutions to Ancient Problem. It showed how incorrect indexing reduces performance and what the correct way to build an indexing solution looks like. The problem is old, so I built the solutions around the latest versions of SQL Server. This session earned the 8.9.

Pinal presenting the index session

Indexes Today: Reads Gain, Writes Pay

The lesson still holds. An index speeds up reads, and it charges you on every write. Each insert and delete has to maintain every index on the table. Each update has to maintain every index that holds a changed column. Ten indexes on a busy table mean ten structures to change for each inserted row.

Indexes cost memory and space too. An index that nobody reads is still updated whenever a write touches its columns. That loads its pages into memory and can push out data your queries need. The wrong index hurts writes and memory at the same time.

So build indexes for the queries you run. Check the existing ones before you add another, and drop the ones that nothing reads. Treat a missing index suggestion as a hint to test, not an order. Judge usage over a full business cycle, because a month-end report reads indexes that a quiet Tuesday never touches.

The Change Data Capture Session

The second session was The History of the Log: Change Data Capture (CDC). For years, people tracked changes in a table with their own queries and triggers. CDC replaces that with an asynchronous method. The session promised to show how to configure it in less than 60 seconds. It received a rating of 7.8.

Pinal presenting the change data capture session

Change Data Capture Today: What It Records and What It Costs

CDC still does the same job. It reads the transaction log after a change commits. Then it writes the changed rows into change tables in the same database. For an update, those tables hold both the old and the new values. You get history, not only the latest row. The reading happens in the background, so your own transactions don’t carry the extra work that a trigger adds.

It isn’t free. The capture job runs under SQL Server Agent, so Agent must be running. The change tables take space. A cleanup job removes old rows after a retention period of three days by default. The log can’t clear past a change that the capture job hasn’t read. A stopped job makes the log grow. CDC also doesn’t record who made a change.

Use CDC when you need the history of values, for a warehouse load or a review of what changed. When you only need to know that a row changed, a lighter feature called change tracking is enough.

What the Attendees Said

One attendee wrote that they gained a great deal of knowledge. Another called it a brilliant effort to educate. A third praised the demo and the presentation. Someone else found the presentation interesting and easy to follow.

Keynote

You could argue that a rating measures mood, not learning. That’s fair. A score of 8.9 tells me people enjoyed the hour. It doesn’t tell me whether they checked their own indexes the next Monday, and that’s the result I care about.

Pinal presenting, with the closing slide

Readers also asked whether you must work for Microsoft to speak at TechEd. I wasn’t a Microsoft employee. My passion for SQL Server was what I brought.

Meeting the Community

During my visit I met the MVPs of Sri Lanka.

UG leaders and MVPs of Sri Lanka: Joy, Chandana, Gogula, Dinesh

I also attended a meeting of the Sri Lanka SQL Server User Group.

Sri Lanka SQL Server User Group

Sri Lanka is a wonderful place, and I had a great time visiting the country and attending the event.

Pinal at the event

What to Remember

Test an index against both the reads it helps and the writes it slows. Before you turn on CDC, plan for Agent jobs, storage, log space and retention.

Both sessions taught one habit: know what a feature records and what it costs before you switch it on. That’s the part of TechEd Sri Lanka that still matters to me.

A good session is not a rating, it is a habit your audience takes back to work.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.