Latest Posts

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.

Memory and SQL 2005 SP2

Or “Why are all my processes waiting on memory. There’s tonnes of memory

It’s probably not new news that there was a fairly nasty memory-related bug in SQL 2005 RTM and SP1 that was related to the relaxing of limits on cache size. Specifically the TokenAndPermUserStore cache.

On systems with large amounts of memory (20GB+) and frequent ad-hoc queries or significant usage of dynamic SQL, the cache can grow quite large, and by quite large I’m talking upwards of 2GB. I think I saw the cache at close on 8GB at one time on one of my servers.

The problem with this is that is takes quite a bit of time to search through several GB of cache to find the required tokens. Making matters worse, access to that cache is synchronised, so only a single thread may have access at a time.

The main symptom of that problem is lots of CMEMTHREAD waits without an apparent wait resource and a higher than normal CPU usage.

But that problem was fixed in SP2 with a change to the caching behaviour. Right?

(more…)

Indexes for aggregates

It’s well known that indexes on columns used in where clause and for joins is a good thing in SQL, but what about other places. How about on aggregates?

Consider a simple table with an amount and a customerID. It’s a common requirement to calculate the total amount that each customer has paid. No conditions are enforced, so this would seem like a place where an index won’t help. Well, let’s see. (sample code at end)

The clustered index (and hence the physical order of the rows) is on the identity column.Take the following query.
SELECT CustomerID, SUM(Amount) FROM Payments group by customerID

(more…)

Execution plans from Profiler

There is another way to get hold of query execution plans than through query analyser/management studio. In SQL 2005, Profiler has a large number of plan-related events. It had some in SQL 2000, but they were quite hard to work with.

The interesting ones in SQL 2005 are as follows, all found under the performance collection in profiler.

  • Showplan XML
  • Showplan XML for query compile
  • Showplan XML Statistics Profile

As soon as one of these is selected, a third tab appears on the trace setup screen, asking for the file to save the xml plans. You can choose to save the plans within the profiler trace file, however the file tends to get very, very large when that is done.

TheĀ  Showplan XML for query compile event fires every time a query is compiled, and it produces an estimated execution plan.

The showplan XML event fires every time a query runs, and it produces an estimated execution plan. When I tested it, it looked as though it was the actual plan, however all the run-time information (actual rows affected, etc) were 0

The Showplan XML statistics profile event fires every time a query runs and produces an actual query plan, with the run-time information in it.

Be careful when running profiler with these events on a busy system as they are quite large, and you can end up with very large output files very quickly. Also, on a busy server they can be very frequent events and capturing them with the profiler front end could result in performance degradation.

Final day

By the last day I was feeling like my head was ready to explode. So much information in so little time.

Kevin Kline’s session kicked off the day with a discussion of benchmarking, baselining and monitoring. Some decent ideas, not really any new information for me. That’s probably more because I’d been at just about every monitoring and performance presentation for the entire conference, than any lack on his part.

After lunch the number of attendees seemed to drop substantially. The best of the afternoon sessions that I attended was Kevin Kline’s interview with Ken Henderson. It wasn’t a technical session, more to do with been an author, a respected sql expert.

The flight home is this evening. I’m not really looking forward to that, I only get back to JHB tuesday morning. Not fun at all.

Day 4 – Monitoring, availability and tough problems

I skipped the keynote on Thursday to spend some time in the SQL Lounge. One of the people there did a demo of a set of scripts, jobs and reports called DMVStat. It’s up on the net somewhere. I don’t have the link right now, but I’ll see if I can dig it up in a day or so.

The first session was on analysing the plan cache. It wasn’t a particularly deep session, just covering how to get execution plans in SQL 2005 (the plan cache DMVs).

The SQL CAT team did a presentation on high availability in the afternoon. Not as good as the session on MySpace, but that would be hard to top.

Bob Ward ran the only level 500 session of the conference, covering debugging difficult problems. The kind of problems that he sees as a senior escalation engineer at PSS. He discusses latch waits, slow IOs, corrupt databases, access violations, memory problems and unexpected shutdowns. It felt something like standing under a waterfall, but it was a brilliant session.

The afternoon wrapped up with a discussion on practical performance monitoring by Andrew Kelly. He went over perfmon, profiler, wait stats, disk stats and showed some techniques for managing the load of data.

All in all, that was a very successful day. One more day to go… 

Day 3

Wednesday at PASS is the first day of the real conference. The day started off with the usual keynote. Ted Kummert of Microsoft went through the data vision that microsoft has, complete with a whole lot of demos.

The part that most caught my eye was the demo of some new SQL 2008 features, including the resource governor with its ability to restrict resource usage depending on properties of the connection (eg application name, host name, login name, etc). The policy-based management should make policy enforcement much easier now, especially since policies can be applied across multiple servers in one operation.

The new spatial data types look cool. I can’t see immediate uses for them myself, but I do like them.

Finally, something that had the entire audience cheering, intellisense in management studio. About time. Something that I also saw but wasn’t mentioned was what appeared to be syntax checking as you type, much like visual studio has. Not sure how far that goes (to objects or just to key words) but it does look interesting.

 

(more…)

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