SQL Server has a variety of features that let developers and DBAs take advantage of minimally logged inserts and leverage the Bulk Copy API. This is an extremely important area to understand when doing large data loads or moving data between tables, databases, or servers, but I have found that many DBAs don’t understand what’s actually happening under the covers, and/or don’t realize that taking full advantage of these interfaces requires playing by a couple of special rules.

In short:

1. There is no such thing as a “non-logged” transaction in SQL Server (a very common myth)
2. Minimal logging means that only extent allocations will be logged, rather than the actual data copied to the destination. This is a huge plus when moving enough data to fill many new pages… but again, it’s not a lack of logging, just less logging
3. In order to take advantage of minimal logging (as of SQL Server 2005), the bulk copy operation must use a table lock, and the table must have either no rows or no indexes

For more detail on all of this, please see the following two recent posts from the SQL Server Storage Engine blog:

• What are the Bulk Import Optimizations?
• Bulk Import Optimizations (Minimal Logging)

And all of that is great, but it’s not what prompted me to post today.

Background: SQL Server has long had a feature called SELECT INTO that lets you do a minimally logged insert via a SELECT, but it requires that the target table does not exist (it will be created by the SELECT). This feature is a bit limiting; you can’t target a specific filegroup, you need to use somewhat awkward CASTs or CONVERTs to make sure the target columns have the right scale/precision (if you’re working with variable-length types), and you can’t pre-create indexes or constraints.

After spending a long time on a project for a client that does a huge amount of data movement using SELECT INTO, I posted a Connect item asking for an enhancement where a minimally-logged insert would be available from a SELECT doing an INSERT into an existing table. And I waited. And I waited some more. And then one day the item was updated with a comment: “This is targetted to be available in SQL Server 2008.”

I wasn’t sure exactly what this meant until the last few days, when Sunil Agarwal posted a series of three posts (one, two, three) describing the enhancements to minimal logging in SQL Server 2008 — including the ability to do a minimally logged insert via a SELECT, to an existing table!

This is a fantastic enhancement, and one that I am certain some of my customers will be able to make great use of. I have been kind of lukewarm on many of the SQL Server 2008 enhancements, but this one goes immediately into my top 5 and is one of those things I will bring out when certain customers ask me whether it’s worth their time to upgrade. Cheers to the Storage Engine team for making this happen!

We’ve been running into a problem with one client:

SELECT COUNT(*) FROM tbl;

takes 0.25 seconds on one db, and 0.06 seconds on another.

Consistently. That’s a fourfold difference.

There aren’t any significant configuration differences (like query cache, etc.), the software versions are the same, and the table fits into memory. This has been looked at by at least 3 in-house MySQL experts, and the only thing we can determine is that it’s a hardware difference.

The table fits into memory so it’s not a disk issue, and the only other difference among the hardware is that the slower machine has Solaris virtualization in place in the form of “containers” (cpu is the same, etc). Is this something that’s known to cause issues with speed? The “tbl” in question is an InnoDB table, if that means anything. Is there something like the “speed” of RAM?

Note that Sun has already been called in, and scripts have been run and analyzed on their side and they say “must be a db config issue” even though the configs have no significant differences (and neither does the hardware and OS spec).

Today is Hotsos Symposium 2008 Training Day — one full day with Tom Kyte. I haven’t registered for it so I took the chance to sleep until 10 this morning which was excellent idea considering that last night we were quite late going to bed thanks in parts to the joined demo that James Morle and Mike Erwin organized at the last presentation yesterday. I was in the James’ session and he was demonstrating how to hide latency problem with batching. I suspect that Mike, in the next hall, was showing the impact of MTU settings on cluster interconnect. The end result is that beer bottles travelled between the presentation halls and James ended up with about 3 packs of Guinness and Shiner Bock. That what kept us up longer last night.

James’ presentation itself was excellent — he explained that all performance problems can be caused by either skew or latency. You can’t normally fix skew issue so you just need to be aware and account for it. Latency can sometimes be shortened but usually insignificantly or it’s impractical (i. e. very expensive). It’s also very important to distinguish bandwidth and latency. I like his idea that the efficient way to solve latency is hiding it and there are generally two ways to do that — batching and threading. Improving bandwidth often doesn’t cause any performance improvement without taking latency into account. Very insightful talk. Thanks James.
(more…)

The symposia is still ongoing and my head is slowly filling up — relieved from my presentation, finally, I’m able to focus on others’ sessions.

Yesterday, Tanel Poder presented his new tool Sesspack and his integration with Excel. 3 years ago, I created a similar tool and collected session waits and statistics transformed into differences per the interval and organized in the star schema to simplify the analysis. I tried to write a front-end in PHP — it was taking ages and I didn’t have time. Then I tried APEX (HTMLDB 1.6 back then) and it wasn’t flexible enough. I ended up querying the data directly and copy & paste to Excel where I could use pivot charting. What a great feature of Excel — it let me organize the data easily and visualize the problems to management, system and storage administrator and other DBA’s. I moved on and didn’t have time to continue this project, clean it up and put into public domain. I’ve still had it in my mind but there is no need now since Tanel already did far better job. He put the first version of the Sesspack on his web-site about half a year ago. What excited me more this time was the integration with Excel that he did — what a powerful but simple tool in the hands of a smart DBA. I’m looking forward to use it when it becomes available on his web-site.
Update: Tanel put the material on his web-site here.
(more…)

First of all “the before” time is over — I’m done with my presentation. It’s been the first slot of the day — 8:30 and Cary Milsap was presenting in another hall so what chances do I have to get people in? It turned out that some people actually did show up and quite a few considering the circumstances.

I have mixed feeling on the results. The presentation started very well and I managed to wake people up at the very beginning — thanks to the “equipment” I had at hands (thanks Marco and Riyaj!). You can spot one of them on the photo (thanks for the photo Marco):

(more…)