Brent Ozar joining SQLskills.com

Since the start of the year we've been inundated with consulting and auditing requests, which is a good sign for the recovery from the economic effect on the IT industry over the last few years. Unfortunately Kimberly and I just don't scale, so while we've worked with many people this year on their SQL Server problems, a few times we've had to pass on the opportunity to work with others because we've constantly been running at capacity.

Over the last few months we've been looking around for another top-notch SQL consultant to come work with us so we can help out more companies – a hard task as many of the best people are already happily entrenched in their jobs. Then, as luck would have it, we happened to be talking to Brent a few weeks ago on unrelated matters and discovered that we actually had the same goals and we fit together perfectly!

We got to know Brent well during the MCM class we taught back in March, and we were both very impressed with his technical skills – he's also one of the few people to pass the MCM course on the first attempt. And of course, he's a huge community guy like we are, and blogs and tweets, and is very approachable, and loves helping people… so we jumped at the chance to bring Brent on board as Principal Consulting Partner.

We are *extremely* pleased and excited that Brent accepted and will be starting with us from July 12th.

Brent brings a wealth of DBA and consulting experience to SQLskills.com (specializing on storage, virtualization, performance tuning, and design) and is very well-known and highly-respected in the global SQL community. He will be full-time working with clients, training, blogging and all the other things you've come to expect from Kimberly and I – with the same high level of quality and dedication. We'll be collaborating on customer work and doing training events together – look for more SQLskills.com Immersion events starting in 2011 – and he'll be putting in a guest appearance at our upcoming Immersion Event in WA in August (see here for details).

Brent will be reachable at brent@SQLskills.com and he will be blogging his technical content on SQLskills.com as well as continuing with his personal blog on www.BrentOzar.com.

You can read Brent's blog post about joining by clicking here.

Of course, this means we have increased capacity for working with customers – so drop us a line and let us know how we can help you. Full details of our services can be found at www.SQLskills.com.

Finally, a huge welcome Brent – there are good times ahead!

Last call for Ireland SQL classes next week

One of the places I really look forward to going every year is Dublin – not just for the countryside, friendly people, and our very good friends Carmel, Sandra, and Bob of Prodata, but also for the fabulous Irish breakfasts that the hotel serves – nowhere beats it, not even Scotland!

And now that time is almost upon us again! This will be the third year we’ve taught classes in Ireland and this year we’re doing a lot more than before:

  • 4-Day Immersion Event, June 28-July 1 2010
  • 2-Day Performance Tuning Masterclass, July 5-6 2010
  • 2-Day Disaster Recovery Masterclass, July 7-8 2010

Last Thursday we taught a 1-day SQL MasterClass in London to 170 attendees and it was a huge success. If you’re on the fence about whether you’ll get a good ROI from coming to Ireland, or if you’ve never been in one of our classes, check out some of the reviews of the 1-day event last week:

    • “I would jump at the chance to attend future talks by Paul and Kimberly – if you get the chance to, I cannot recommend it highly enough. Awe-inspiring knowledge, that is shared in such an effortless and enjoyable way, bouncing off each other.”
    • “By far, the best SQL Server event I have attended. Total value for money,…  …and of course 2 outstanding speakers in Paul and Kimberly.”
  • http://blog.gre-sqlserver-solutions.com/2010/06/sql-server-masterclass.html (defunct)
    • “I have attended several taught MCP SQL Server training courses over the years and several conferences including SQL Bits and this MasterClass taken Paul Randal and Kimberly L Tripp has to be the best training/knowledge transfer day I have been on. Kimberly and Paul have an amazing knowledge of SQL Server and such a great presentation style that makes this day a must for any SQL Server professional.”

And if that’s not enough to sway you, remember that the Guinness also seems to taste better in Dublin than anywhere else in the world, or maybe it’s just me… :-)

Hope to see you in Dublin!

Cheers

 

Benchmarking: do multiple data files make a difference?

Many times I'm asked whether having multiple data files can lead to an improvement in performance. The answer, as with all things SQL (except concerning auto-shrink) is a big, fat "it depends." It depends on what you're using the database for, and the layout of the files on the IO subsystem, and the IO subsystem capabilities. I've heard examples of "yes" and I've heard examples of "no."

Just for kicks, I put some of my test hardware to use to do some experimentation. (You can get to all my other Benchmarking posts using this link.)

My setup for this series of tests is:

  • Log file pre-sized to 8GB (to avoid log growth) on 8 x 1TB 7.2k SATA RAID-10 array, one iSCSI NIC, 128 KB stripe size
  • 160GB database, variously setup as (all in the PRIMARY filegroup): 
    • 1 x 160GB file
    • 2 x 80GB files
    • 4 x 40GB files
    • 8 x 20GB files
    • 16 x 10GB files
  •  16 connections inserting 100/16GB each, no other activity, all code executing on the server, no data transfer from clients

Each test was run 5 times and then the time-for-test calculated as the average of the 5 test runs, so the two tests together represent 50 test runs. Luckily I wrote a test harness that will tear down and setup the database automatically each time in the different configurations, so just double click a cmd file and then a day or so later I get an email saying the test has finished. Great when we're traveling!

Here are the test results:

As you can see, it's pretty clear that with both test setups, having more data files definitely does produce a performance improvement, but only up to a point.

Test 1: Data files on 8 x 300GB 15k SCSI RAID-10 array, two iSCSI NICs, 128KB stripe size

Test 2: Data files round-robin between two 4 x 300GB 15k SCSI RAID-10 array, one iSCSI NIC each, 128KB stripe size

In both cases, the performance increases up to eight data files, and then begins to decrease again with sixteen data files. The single data file case was bound to be slower on the SCSI array with fewer drives, and we see that in the results (left-most result in red).

In the best case, the eight-file case on two arrays was just over 6% faster than the single-file case on the single array. Hardly earth-shattering, but still a non-trivial gain.

Where's the gain coming from? I ran wait stats analysis for a few test variations – for example, between the eight data files test and the single data file test using two arrays, the cumulative wait stats were almost identical – 38/39% PAGELATCH_EX, 19/21% PAGELATCH_SH, 12/13% WRITELOG. The gain is mostly coming from the IO subsystem, but the SCSI arrays are still overloaded, as I showed in plenty of the previous benchmarking tests.

Now, this is a very contrived test, with a single operation in my workload – it's definitely NOT representative of a mixed-operation OLTP workload. However, I did see a gain from having multiple data files – and I believe I would have seen more gain had the SCSI array(s) not been pretty much maxed out already.

I've heard plenty of anecdotal evidence that adding a few more data files for user databases can lead to performance improvements, but your mileage is definitely going to vary. I'd be very interested to hear your observations in production as comments to this post (but please keep the comments constructive – don't give me a laundry-list of tests/settings you want me to try, or rant about real-life vs. lab tests).

Enjoy!

PS The next post on SSDs is forthcoming – just finishing up the (extensive) tests – and also the post on how you all have your log files configured, from the survey I did a while ago. Thanks for being patient!