Latest Posts

Becoming a better … – lookback

I thought I’d take a quick look back over the last two months, see how I faired on what I set out to do.

  • Finish 2 database books by the end of the year – I didn’t manage to finish either, though I started both the ones I said I’d read, and a third one. Part of the problem is that I was studying for an exam until mid december
  • Write at least 1 blog post a week here – Looking back I see that I wrote 8 posts in 8 weeks, however one was decidedly not SQL related and a second was just a comment on an exam. Not too bad.

Now for the next 6 months.

  • Read and finish one database book every 2 months. I don’t think I’m going to be able to do more than this, especially since I have a thesis to write.
  • Read at least one C# book
  • One blog post a week
  • One SQL or development related podcast a week.

We’ll see in 6 months how well this goes.

Execution plan operations – joins

It’s about time I picked this series up again.

I’m not going to go into too much detail on joins. There are some very good articles elsewhere on joins. The important thing to notice about joins, in the context of an execution plan, is that there are six logical join operators and three physical join operators. The logical operators are what you ask for in the context of the query, the physical operators are what the optimiser picks to do the join.

The six logical operators are:

  • Inner Join
  • Outer Join
  • Cross Join
  • Cross Apply (new in SQL 2005)
  • Semi-Join
  • Anti Semi-Join

Craig Freedman wrote a long article on the logical join operators – Introduction to Joins

The semi-joins are the exception, in that they cannot be specified in a query. Nonetheless, they are present in disguise. They’re the logical operators for EXISTS, IN, NOT EXISTS and NOT IN. They’re used when matching is required, but not a complete join.

(more…)

Temp tables and table variables

I’ve encountered a fair bit of confusion on various forums as to the differences between temporary tables and table variables. As a quick article (I’m knee-deep in some AI stuff at the moment) I thought I’d quickly go over some points on temp tables and table variables.

Temporary Tables

  • Created using the Create table syntax, preceding the table name with a’#’ for a local temp table and ‘##’ for a global temp table
  • Allocated storage space within the TempDB database and entered into the TempDB system tables 1
  • The table’s actual name is the name is was created with, a large number of underscores and a hash value, to prevent object name collisions if two connections create a temp table with the same name
  • Can have a primary key, defaults, constraints and indexes (however the names of these are not hashed, possibly leading to duplicate object errors for constraints and defaults)
  • May not have triggers.
  • Foreign keys are permitted, but are not enforced
  • Have column statistics kept on them. The algorithm for determining when to update is different to permanent tables
  • Exist until they are dropped, or the connection closes.
  • Are visible in any child procedures called from the one where the table was created. Are not visible to parent procedures
  • Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache

(more…)

Once more unto the breach…

I wrote 70-447 on friday. It’s the last of the SQL 2005 exams that I intend to write.

I was pleasantly surprised. It wasn’t nearly as hard as I was expecting and I scored well above what I thought I would – 891. Very surprising, seeing as I don’t do much in the way of administration.

Next on the exam plans, either C# or Sharepoint. I haven’t decided yet.

Now, when are the SQL 2008 exams going to be available…..

Birthday musings

Another year gone, another year older. It’s been an interesting year.

Professionally, I’m very happy with the past year. I’ve achieved several goals, got to know some interesting people in the SQL community and, of course, started this blog. I’ve also made some fairly important decisions about the future.

Academically, I’m not so happy. My masters thesis is behind where it should be. (a fact I’m sure my supervisor would agree with.) Most of the initial reading is done and I know what I need to do. I need to sit down and get busy. This is going to have to take a higher priority in the next few months.

On more personal matters, a good friend moved up to JHB. It’s very good to see her again. The roleplaying campaign is going well and from the feedback I’ve been having, the players are all enjoying themselves.

Overall, it’s been a good year. Here’s to the next one.

Parameter sniffing

This seems to come up again and again on the forums.

At its heart, parameter sniffing is the ability of the SQL Server optimiser to know the values of parameters passed to a stored proc at the point that it compiles the procedure. The idea is that if the parameter values are known, then the appropriate column statistics can be used and the optimiser can estimate the number of rows that the various query operators will have to process for various different possible execution plans.

Since the approximate number of rows is known, the cost of each possible plan can be more accurately calculated and a more accurate execution plan can be selected.

So, why is parameter sniffing so often a problem? Well, mainly, because parameter values do change.

(more…)

SQL Server 2008 Nov CTP

The latest SQL Server 2008 CTP is available on Connect.

Notable features new in this CTP:

  • Intellisense in Management studio. (About bloody time). This was demo’d at PASS back in Sept.
  • Change tracking. What we’ve done all these years with triggers, just without the triggers.
  • The Performance warehouse. This I really want to play with, even if it’s just to see what pieces I can use on 2005 or 2000 servers.
  • Plan freezing. To be able to freeze the plan cache at a point in time, and to move query plans from one server to another. I see this as a fairly advanced feature, probably only really useful in specific cases. (and probably a feature that will be badly abused)
  • Resource Governor. This must be my number one anticipated feature for 2008. The governor allows the creation of limitations and priorities based on properties of the connection, eg login name, application name, host name. It’ll be a very nice way of limiting the damage that ad-hoc queries do to a server’s performance, without stopping them outright.
  • Geospacial data types and functions.
  • The filestream data type

Now I just need to install virtual PC on my laptop and it’s playtime…..

Execution plan operations – scans and seeks

Another post in my ongoing series on reading execution plans. I know I’m jumping around a bit. I hope it makes some kind of sense.

I thought I’d quickly go over the seek and scan operations that can be seen in execution plans. There are 6 main ones. There’s a fair bit that I’m glossing over in this. I’ll get into some details at a later date.

Scans

  • Table scan. This operation only appears for a heap (table without a clustered index). The first page in the heap is located based on info in the system tables, and then the pages are read one by one, using the next and, if necessary, previous pointers in the page headers. This is generally an expensive operation and should be avoided where ever possible
  • (more…)

DateTime Manipulation

The date time data type and the date time functions within SQL are things that I see coming up time and time again in news groups and forums. Questions on how to get rid of the time, how to get the first day of the week, the last day of the month and so on. With the new Date and Time data types coming in SQL 2008, things will get easier, nut until then we have to do things the hard way.

In systems I’ve worked on I’ve seen several implementations of functions to find the first and last day of a week, a month or a quarter. Some have worked well, some have worked and others, well, haven’t

(more…)

Become a better ….

So…

I issued some of my colleagues a challenge this morning.In the vein of the ‘Become a better developer in 6 months’ idea that was going around the net a few months back, I asked them what they were going to do in what’s left of this year to become better DBAs.

Following the idea of ‘make something public and you’ll go through with it’ idea, here are my intentions for the remainder of the year:

  • Finish 2 database books by the end of the year. The two I’m looking at are ‘Guru’s guide to SQL architecture and internals’ and ‘Practical troubleshooting’, The first is written by Ken Henderson, the second is edited by him
  • Write at least 1 blog post a week here. This is the one I’m most likely to have trouble with.
  • Listen to at least 1 SQL related podcast a week. I’m open to suggestions for good SQL-related podcasts.