Latest Posts

An example exec plan

Back to the technical posts…

I’m going to take an example of a very simple execution plan that I used in a presentation a few weeks back and go over it in some detail, pointing out information that is available and what can be read from the plan.

The execution plan can be downloaded (in xml format) here –

The query that the plan came from is a very simple two table query. It’s not a very optimal plan, but that’s because I forced an index hint in order to generate a more interesting plan. Without the hint, it’s a simple exec plan with two index seeks and a nested loop join.


The first thing that can be seen from the execution plan is that most of the cost of the query is in two operations, a key lookup (formerly called a bookmark lookup) and a clustered index seek. The high cost of the key lookup is a good sign that the query is using an inappropriate index. (in a future post I’ll discuss using the exec plan to choose indexes)


Views or Functions?

Someone asked this question on SQLServerCentral, I thought I’d post the test here for interest.

The question was on which would execute faster, creating a view, then creating a procedure that accepted a parameter and filtered the view, or creating a table valued function that took a parameter.

From testing, the answer is the procedure, but not by much. A inline table-valued-function comes a very close second and the multi-statement table-valued-function waddles in last. Not really surprising. (more…)

TechEd 2008 – Wednesday

And so it’s all over for another year. Time to pack up, travel home and tomorrow, go back to the office. It’s been a great conference. I know there was a lot of concern about the new venue and there were a few teething problems, but overall, I think it was a very good event.

There were only two sessions this morning. I started off with a deep and very technical look at the data mining algorithms in SQL 2005. I understood some of it, but I need to brush up on my statistics.

For the second session of the day, Peter Willmot and I ran a session on basic SQL security concepts for the architects and developers. It seemed well received and it was very well attended for the last session of TechEd.


SQL Server 2008 is here!

SQL Server 2008 RTMs today, 6 August 2008. The announcement was made at the closing keynote of Tech Ed South Africa, about two hours ago.

Congrats to all in the SQL Server team, all the developers, testers, managers and everyone else involved. You guys did an awesome job.

TechEd 2008 – Tuesday

Day two of TechEd sessions and the information deluge continues.

i ran two chalk and talk sessions today, both turned out better than i expected and I was very impressed with both the turn out and involvement of the people at the second. I’ll see if I can get related materials up later in the week.

Other sessions that I attended included a very impressive look at game development using XNA, WPF and Silverlight, with a bit of apache chucked in for good measure. The session was partially delivered by a presenter sitting in Seattle and partially by the presenter in the room. The slow internet access down in this end of the world showed, but not too much.


TechEd 2008 – Monday

So, today was the first full day of teched, and as usual, it felt like standing underneath an information waterfall. So much to see, learn and try to remember.

I started the day with Peter Willmot’s session on BI, specifically on cubes and how to present them to the users. Couple of cool sharepoint features, including reporting service web parts and the excel services. Since I committed to learning sharepoint, it’s interesting to see just how much integrates into it.


TechEd 2008 begins

So, TechEd South Africa started today. So far it’s been fun. As usual for the local TechEd events, the sunday was the opening keynote and a nice party. Some points that struck me during the keynote:

  • Microsoft is heavily pushing the virtualisation technologies, including Hyper-V
  • The application virtualisation looks fantastic for large corporations
  • Silverlight 2 looks very cool
  • Still no release date for SQL Server 2008

I have nothing particularly technical, just a couple pictures of the opening party.


All's well that ends well

So, today was my last day working for the bank and my last day as a full time employee. From the beginning of next month, I’m a consultant. It’s exciting and scary at the same time.

I’m not going to wax long and lyrical about the years working there, that’s boring. Suffice it to say there were good times and bad, late nights and weekends working, and leave it at that.

The one thing that I will say is that without the support and encouragement of several people there, I would not be where I am today. So, a very, very big thank you to my former colleagues and managers. I won’t mention names, you know who you are.

And I have to show off a couple of photos of my old desk 😉

Recovery model and transaction logs

Or “Remind me again why we’re doing these backups in the first place

If there’s one topic that comes up again and again on the forums, it has to be the problem of full transaction logs, usually followed by the discovery that the DB is in full recovery mode and no log backups are running.

Quite often, someone will suggest to just truncate the log and shrink it. It’s a dangerous suggestion, not so much for what is said, but for what is not said. To understand why, requires a little background information. First, a look at recovery models and how they affect the transaction log.

Recovery modes

I’m going to ignore bulk-logged mode for now, mainly it’s less used than the other two and I’m not completely comfortable with how it works. I’m also going to ignore replication and database mirroring, as they complicate the issue.

Regardless of the recovery model that the database is in, transactions are logged into the transaction log before they are considered complete. The entries in the transaction log are considered active until all the data pages that were modified by the transaction have been written to disk. The two processes that write pages to disk are the lazy writer and the checkpoint process. Once all the pages that the transaction have been written to disk, the log records for that transaction are marked as inactive.