Latest Posts

Precon – Query plans

The second day pre-conference that I attended was by Kalen Delany, all about query plans.

The first part of the session was an overview of the various methods of getting a query plan, from the showplan options for estimated plans, to the profile options for actual execution plans, the graphical options and the usage of SQL Profiler to get both actual and estimated plans.

She then briefly covered sub-optimal plans, without going into detail on query tuning. Stuff like cardinality estimation errors and potentially slow operators (scans, sorts, hashes)

After lunch we delved into details on the plan cache, including what constitutes a plan, how to view them and what conditions there are around plan reuse. This covered adhoc plans, prepared plans and object plans (stored procedures), as well as recompiles and the downsides of plan reuse.

Finally there was a section on query hints and plan guides, for use when the optimiser just won’t do what you want it to do.

The evening was a great deal of fun, with the opening reception and the SQLServerCentral party. I had the opportunity to take part in the quiz bowl. Got eliminated in the first round (damn movie questions) but was still good fun. Won a couple books. More reading material is always a good thing.

Preconference – Performance Toolset workshop

Spent the first day of the conference at the PSS Bootcamp. The PSS guys always put on a good show as they take people through what they do to solve customer’s problems.

The first part of the day was devoted to a performance tuning methodology. What do you do when the users are complaining that the server’s slow. The presenter went through the methodology that the PSS engineers use when presented with a performance problem.

Most of the process is aimed at finding the problem query or identifying a resource bottleneck on the server.

If the problem is currently occurring, one of the main tools is the performance dashboard, a new report introduced into Management studio with SQL 2005 SP2

If the problem is not currently occurring, then it’s necessary to use SQLDiag, profiler, perfmon or a combination of them. A very interesting new tool that they introduced is a data aggregation and reporting tool for performance data – SQL Nexus. The updated version is supposed to be available by end November.

The session finished with a brief look at some of the new features of SQL 2008 that would help out with performance issues. One of the big ones, at least for me, is the performance warehouse. SQL can be configured to collect performance related data continuously in the background and save that into a data warehouse. There are a collection of reports built into management studio that report off this data. Used properly, that should make finding performance problems much easier than currently.

The other feature in 2008 that looks fantastic – a dependency checker that actually works. Sounds great 

Off to PASS

Well I’m off to PASS tonight. total of 18 hours of flying and 7 or so hours sitting around in London Heathrow airport. What fun.

Looking forward to the conference. Hopefully I’ll get a chance to chat with some people I met last year.

I’ll probably be reporting on some of the sessions while I’m there. If there’s anyone who reads this blog that’s going to be at pass, look me up and say hi. Just look for someone wearing a nametagĀ  with the name ‘Gail’ and country ‘South Africa’

On a haunted house

The second session of the haunted house adventure went down far better than I could have ever hoped. In fact, the players asked to stay late so that they could finish it, they were having so much fun.

They survived the haunted house and uncovered the reason behind all the strange occurrences. they couldn’t prevent a thug from making off with the knife that had been the focus of all the strange events, but that’s fine. It adds possibilities for the future.

Everyone was enthusiastic, interested and most importantly, involved in the story. I’m still on a bit of a buzz from the game and I’m very psyched for the campaign.

Next up, depending on the players, either investigating the happenings at the cathedral, visiting a museum exhibit, or attending the cultural festival.

Shrinking databases

Or “Order the pages, shuffle the pages.

Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again and, especially if the data files are on independent drives, there’s little difference between space free on the drive or space free in the data file. There is also a more insidious reason for not shrinking a database.

Let’s take a very simple database (The creation code is at the end of the post). I have two tables, both with a tens of thousands of rows. Both tables have a clustered index on a uniqueidentifier and are heavily fragmented (>99%).

DBCC SHOWCONTIG(LargeTable1) -- 99.30%
DBCC SHOWCONTIG(LargeTable2) -- 99.21%

To fix the fragmentation, rebuild both indexes. That fixes the fragmentation, but now the data file is using almost twice the space necessary.

DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)

So, shrink the database to release the wasted space back to the OS

DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free

That’s fixed the space issue. But now, have another look at those two indexes that were just rebuilt.

(more…)

Execution plans, estimated vs actual

This is the second post on execution plans. I’m going to briefly discuss estimated execution plans and actual execution plans, the differences between them and when you would want to use which.

First however, a bit on query execution, just so that I know everyone’s on the same page.

When a query is submitted to SQL Server (and for simplicity I’m going to assume it’s a straight select statement not a procedure) the query is parsed, then bound to the underlying objects(tables, views, functions, etc). Once the binding is complete, the query passes to the query optimiser. The optimiser produces one or more suitable execution plans for the query (more on that in a later post). The query is then passed into the query execution engine, which does the memory grants, picks a parallelism option, if necessary and executes the various query operations.

Estimated execution plans

When an estimated execution plan is requested for a query, the query goes through the parsing, binding and optimisation phases, but does not get executed.

(more…)

Back in the GM chair

This last sunday saw me taking back the GM chair for the group that I play with. For the past year we’ve been playing my friend Phillip’s Per-rune game. (details and an in-character journal on my web site)

My campaign is a modern day supernatural game, a bit like Buffy, but darker. It’s set in the historical city of Oxford, in England, in the year 2002. More details are available on the campaign web site. The pages aren’t finished, there’s a lot of links that go no where.

All in all, the game went off without a hitch. Lots of admin-type stuff to start, reminders of clues, shopping, etc, etc but less than I expected.

Now let’s see if the characters can unravel the mysteries of a haunted house, and if they can survive to tell the tale.

I’ll probably comment here occationally on on significant bits of the campaign as they happen.

Structure of an execution plan

So, this is the first part of the series on how to read an execution plan. In this post, I’m going to give a high-level overview of how the execution plan looks and how, in general, to read it.

For the purposes of this and other posts in this series, all screenshots will be from SQL Management Studio (the 2005 GUI) and all comments on features will refer to that tool. Query Analyser, from SQL 2000, is very similar. For other querying tools, your mileage may vary.

Here’s a very simple execution plan. The query that produced this plan is just a join of three tables in the AdventureWorks database on SQL 2005.

Execution plan

(more…)

PASS 2007

Well, looks like I’m going to Denver for the Pass 2007 summit after all. I wasn’t sure I’d be going this year.

As usual there are more fantastic looking sessions that I could possibly manage to go to. In one slot on the Wednesday, I’d need to be in four places at the same time to attend everything I want to see.

(more…)