It's official! We're putting on a 5-day BI Immersion Event this year in the US!

We've just confirmed with our well-known BI expert, Stacia Misner, who will be teaching the class. Stacia teaches all the Microsoft internal BI classes and we're looking forward to sitting in on the class to learn from her too. Check out her bio here.

Even with little to no knowledge of BI, this course will provide you with immediately-usable, production-level knowledge of BI. The course has been designed to take you from installation to configuration to application and integration – for all facets of the BI stack (SSIS, SSAS, SSRS, PowerPivot, and SharePoint) - it is meant for anyone who wants to get involved in BI. Our first thought was to create a class for DBAs who want to better understand their environment, but the course content is just as valuable whether you’re new to BI or ready to expand your skills. This class might be the perfect way to advance your career and dig deeper into topics that don’t typically get covered in most BI classes.

Maybe you accomplish quite a bit with Excel - but how do you get the data there? Quite a few folks use cut-and-paste, or import from a CSV - but of course there's a much better, and much more scalable and robust way using SSIS! Relational data warehousing – with lots of aggregate tables – helps you keep your data organized for Excel or any other type of reporting or analysis tool that you might want to use, but some types of business questions are answered more easily when you move your data from the data warehouse into SSAS cubes. Whether you store data in tables or cubes, you have a variety of options in the BI stack to access and analyze the data, but which is best? Learn how to choose the right tool for the task and how to manage a secure and scalable environment for reporting and analysis.

The course starts out in the 100-200 range on Day 1 but moves into the 200-300 range for the remainder of the week. For each technology in the BI stack, you’ll learn basic usage and design principles, followed by more coverage of configuration, performance tuning, and other management tasks than you’ll find in most BI end-to-end classes. The course will not only bridge the gap in your BI knowledge, but will continue on to take you a lot further. If you’ve always wanted to immerse yourself into BI, figure out the end-to-end story, and go behind-the-scenes to learn how to install, configure, and deploy BI solutions, this is the place to be!

The class will follow our regular 5-day format with Stacia's best content covering:

  • What is Business Intelligence?
  • Building a Business Intelligence Infrastructure
  • Relational Data Warehousing
  • Integration Services
  • SSIS Package Reliability and Performance
  • Managing SSIS in Production
  • Analysis Services
  • Designing Aggregations
  • Using MDX to Enhance a Cube
  • Managing SSAS in Production
  • Reporting Services
  • Developing Reports
  • Managing SSRS in Production
  • Excel and PowerPivot for Excel
  • SharePoint 2010
  • PerformancePoint Services
  • PowerPivot forSharePoint

The event will be held in the Marriott Courtyard Bellevue Downtown and will be fully catered - there's a special room rate of $159/night.

The full cost is US$3200 with an early-bird special of US$2600 for registrations before midnight PST September 17th.

Check out the Immersion Event page for full details, registration, and feedback from attendees of our other events (or jump straight to registration).

We hope to see you there!

Categories:
Classes

We've got two more classes on the books now for 2010 - a 5-day BI class in Bellevue, WA the week of October 25th, and a 5-day SQL Immersion class in San Diego, CA the week of December 6th. Watch the blogs for announcements for when registration goes live.

In the meantime, here a some comments from those who attended our 5-day Immersion Event  last week.

"Best class I have been to!" - Julie Finnell

"Best training I ever attended in the last 9 years. The course is very well planned and executed. Excellent Job!! Will come back next year."

"This was the best course I've EVER attended. I could list the features that distinguish this course from the others, but I won't. The least of which, however, is not the mentors. You both were just fabulous." - Nick Loghides.

"I really loved the way you both indulged our questions. Great demos!" - Nick Loghides.

"When my boss first asked me why this class of the other, I told him that I wanted to go to the source. As for the class, immersion is definitely the best way to describe the overall content and presentation. There is so much content, but it is presented in a way that you won't drown. Every question is answered considerately and concisely; whether it's a complex question that needs to be answered at breaks or something that can be answered quickly. Overall, I can't say enough about the class and would recommend it to anyone who wants to know how SQL Server works." - Kevin Eckart

"A lot of great material! As a newbie, there is a lot of stuff I can use immediately, and also a lot for fuure consideration. Really gave me a good understanding of some of the internals and a new way to look at our systems." - Tim Magney

"Overall, excellent course!! Thank you! I've learned new tips and techniques that will save me time and my company money." - Ed Quick

"Overall, I think anyone who works professionally with SQL Server should come to this training, even if they have to pay for it themselves." - Eric Maibach

"This was the *BEST* training event I've ever attended! Thank you for a fantastic experience!" - Rowland Gosling

"The level of knowledge you have and impart was invaluable to making this class totally worth my time! Thanks." - Chris Swartley

"This was an incredible week and I can't wait to get back to work and start implementing what I've learned." - George

"Top tactics one-and-all. I'm excited. And love my job more!! Thank you!" - Kyle

Thanks everyone - it was a pleasure teaching you!

Categories:
Classes

Hey folks,

Just a quick note to let you know that Kimberly and I will be offline from this weekend for four weeks. We're going on vacation - taking the kids up to Alaska to explore for two weeks and then Kimberly and I are taking an ex-Russian ice-breaker with Heritage Expeditions over to the Kamchatka Pensinsula in Siberia and up into the Arctic Circle to Wrangel Island in the Arctic Ocean.

The island is a polar bear breeding ground (actually with the densest population in the world) and fabulous bird life (see the Fauna and Flora tab here). We'll be tweeting and updating our Facebook pages from our sat-phone while we're up there, and we should have some fabulous photos to share here when we get back.

In the meantime, Brent will be holding down the SQLskills fort - any queries sent to either me or Kimberly will go unanswered until we return. Give him hell! :-)

Hope you enjoy the rest of your summer!

Cheers

Last time I posted about SSDs I presented the findings from sequential inserts with a variety of configurations and basically concluded that SSDs do not provide a substantial gain over SCSI storage (that is not overloaded) - see this blog post for more details.

You can see my benchmarking hardware setup here, with the addition of the Fusion-io ioDrive Duo 640GB drives that Fusion-io were nice enough to lend me. (For the next set of benchmarks I've just upgraded to 16GB of memory and added the second 640GB Fusion-io Duo, for a total of 1.2TB... watch this space!).

In this set of tests I wanted to see how the SSDs behaved for random reads and writes. To do this my test harness does the following:

  • Formats the SSDs in one of three ways:
    • Fusion-io basic format (each 320GB drive has 300GB capacity)
    • Fusion-io improved write performance format (each 320GB drive has only 210GB capacity, 70% of normal)
    • Fusion-io maximum write performance format (each 320GB drive has only 151GB capacity, 50% of normal)
  • The SSD format is performed using Fusion-io's ioManager tool, with their latest publicly-released driver (1.2.7.1).
  • Creates 1, 2, 4, 8, or 16 data files, with the file sizes calculated to fill the SSDs
  • My table structure is:

CREATE TABLE MyBigTable (
    c1 UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID (),
    c2 DATETIME DEFAULT GETDATE (),
    c3 CHAR (111) DEFAULT 'a',
    c4 INT DEFAULT 1,
    c5 INT DEFAULT 2,
    c6 BIGINT DEFAULT 42); 
GO

CREATE CLUSTERED INDEX MyBigTable_cl ON MyBigTable (c1);
GO

  • I have 16 connections each inserting 2 million records into the table (with the loop code running server-side)

Now before anyone complains, yes, this is a clustered index on a random GUID. It's the easiest way to generate random reads and writes, and is a very common design pattern out in the field (even though it performs poorly) - for my purposes it's perfect.

I tested each of the five data file layouts on the following configurations (all using 1MB partition offsets, 64k NTFS allocation unit size, 128k RAID stripe size - where applicable):

  • Data round-robin between two RAID-10 SCSI (each with 4 x 300GB 15k and one server NIC), log on RAID-10 SATA (8 x 1TB 7.2k)
  • Data on two 320GB SSDs in RAID-0 (each of the 3 ways of formatting), log on RAID-10 SATA (8 x 1TB 7.2k)
  • Log and data on two 320GB SSDs in RAID-0 (each of the 3 ways of formatting)
  • Log and data on two 320GB SSDs in RAID-1 (each of the 3 ways of formatting)  
  • Log and data on single 320GB SSD (each of the 3 ways of formatting)
  • Log and data on separate 320GB SSDs (each of the 3 ways of formatting)
  • Log and data round-robin between two 320GB SSDs (each of the 3 ways of formatting)

That's a total of 19 configurations, with 5 data file layouts in each configuration - making 95 separate configurations. I ran each test 5 times and then took an average of the results - so altogether I ran 475 tests, for a cumulative test time of just less than 250 thousand seconds (2.9 days) at the end of July.

The test harness takes care of all of this except reformatting the drives, and also captures the wait stats for each test, making note of the most prevalent waits that make up the top 95% of all waits during the test. The wait stats will be presented in the following format:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
PAGEIOLATCH_EX                       26833.45       26822.85          10.60      867558      75.88
WRITELOG                              7097.77        6647.26         450.51     3221475       20.0

The columns are:

  • WaitType - kind of obvious
  • Wait_S - cumulative wait time in seconds, from a thread being RUNNING, going through SUSPENDED, back to RUNNABLE and then RUNNING again
  • Resource_S - cumulative wait time in seconds while a thread was SUSPENDED (called the resource wait time)
  • Signal_S - cumulative wait time in seconds while a thread was RUNNABLE (i.e. after being signalled that the resource wait has ended and waiting on the runnable queue to get the CPU again - called the signal wait time)
  • WaitCount - number of waits of this type during the test
  • Percentage - percentage of all waits during the test that had this type

On to the results...

Data on SCSI RAID-10, log on SATA RAID-10

 

Once again this shows what I've shown a few times before - on SCSI having multiple data files on the two RAID arrays gives a performance boost. The two-file case is going from a single RAID array to two RAID arrays - bound to get a performance gain - and it gets a 35% performance boost - 6 times the boost I got from messing around with multiple files for the sequential inserts case last time (see here and here for details).

The best performance I could get from having data on the SCSI arrays was 1595 seconds.

Representative wait stats for a run of this test - one file:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
PAGEIOLATCH_EX                       28993.08       28984.66           8.42      647973      75.53
WRITELOG                              7333.36        6883.82         449.54     3223809      19.10
SLEEP_BPOOL_FLUSH                     1786.18        1781.94           4.24     1147596       4.65

Representative wait stats for a run of this test - two files:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
PAGEIOLATCH_EX                       15306.22       15296.67           9.55      679281      63.87
WRITELOG                              7762.25        7270.79         491.47     3215377      32.39

Representative wait stats for a run of this test - four files:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
PAGEIOLATCH_EX                       26833.45       26822.85          10.60      867558      75.88
WRITELOG                              7097.77        6647.26         450.51     3221475      20.07

Representative wait stats for a run of this test - eight files:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
PAGEIOLATCH_EX                       27556.79       27547.83           8.96      674319      75.09
WRITELOG                              7545.40        7118.93         426.47     3221841      20.56

Representative wait stats for a run of this test - sixteen files:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
PAGEIOLATCH_EX                       37716.72       37705.87          10.85      792189      80.13
WRITELOG                              7150.01        6699.36         450.64     3228609      15.19

These numbers are showing the majority of waits are for data pages to be read into the buffer pool - random reads, and the next most prevalent wait is for log block flushes to complete. The more PAGEIOLATCH_EX waits there are, the worse the performance is.

Data on 640GB RAID-0 SSDs, log on SATA RAID-10

 

Don't let this graph fool you - the top and bottom of the scale are only 30 seconds apart. Basically moving the data files from the SCSI arrays to the RAID-0 SSD got around a 3-5x performance gain, no matter how the SSDs are formatted.

Representative wait stats for a run of this test - one file:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
WRITELOG                              8459.65        7789.91         669.73     3207448      94.48
PAGEIOLATCH_EX                         440.27         392.51          47.77      828420       4.92

Representative wait stats for a run of this test - two, four, eight, or sixteen files:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
WRITELOG                              7957.35        7356.01         601.34     3206855      95.75

The log is the obvious bottleneck in this configuration. 

Data and log on 640GB RAID-0 SSDs

 

And again - high and low values are only 25 seconds apart. Moving log off to the same SSD gave a further 45%-ish improvement across the board, with little difference according to how the SSDs were formatted.

Representative wait stats for a run of this test - any number of files:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
WRITELOG                              2955.69        2184.99         770.69     3203957      89.24
PAGEIOLATCH_EX                         330.11         288.89          41.23      653147       9.97

The percentages fluctuate up and down a few percent depending on write format and number of files, with the maximum write performance format tending to have a slightly higher percentage of WRITELOG waits than the other two formats.

Note that moving the log to the SSD as well as the data files drastically cuts down the number of WRITELOG waits - what we'd expect.

Data and log on single 320GB SSD

 

The performance numbers for having everything on a single 320GB SSD are only a tiny amount slower than those for two 320GB SSDs - which is what I'd expect.

Representative wait stats for a run of this test - one file with basic format or improved write performance format:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
WRITELOG                              2911.22        2121.05         790.17     3204459      81.44
PAGEIOLATCH_EX                         602.11         546.56          55.55      758271      16.84

And for one file with maximum write performance format:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
WRITELOG                              3363.11        2523.63         839.48     3204110      87.54
PAGEIOLATCH_EX                         428.68         406.77          21.92      412081      11.16


You can see that the higher amount of PAGEIOLATCH_EX waits leads to lower overall performance. This makes sense to me.

Data and log on two 320GB RAID-1 SSDs

 

Now, I have an issue with people using SSDs in RAID-0 because it's a single point of failure. In an environment that's going all out on high-availability, if I was using SSDs for performance, depending on the criticality of the data I'd want to at least double-up to RAID-1. For all the various configurations, moving from a single 320GB SSD to two of them in RAID-1 resulted in no more than a 10-15% drop in performance and it's still 3-5x faster than the SCSI setup.

Here's a representative set of wait stats for the entire set of tests:

WaitType                       Wait_S         Resource_S     Signal_S       WaitCount   Percentage
------------------------------ -------------- -------------- -------------- ----------- ----------
WRITELOG                              3949.44        3031.14         918.30     3204694      85.68
PAGEIOLATCH_EX                         608.62         555.98          52.65      692934      13.20

In general the RAID-1 configuration had more waits of both types than the single drive configuration.

Data and log on separate 320GB SSDs

 

Splitting the data and log make for a 5-20% improvement over having everything on a single 320GB SSD.

The wait stats for these configurations show the same trends that we've seen so far - slightly slower performance = slightly more PAGEIOLATCH_EX waits.

Data and log round-robin between separate 320GB SSDs

 

This confused me - the single file case is exactly the same configuration as the test case above, but the results (for each test being run 5 time and then averaged) were almost 10% faster for the first two formats. No significant differences for the other configurations.

The wait stats for these configurations show the same trends that we've seen so far - slightly slower performance = slightly more PAGEIOLATCH_EX waits.

Best-case performance for each number of data files

 

 

 

Well big surprise - the SSDs outperform the SCSI storage for all these tests. The improvement factor varied by the number of data files:

  • 1: SSD was 7.25x faster than SCSI
  • 2: SSD was 4.74x faster than SCSI
  • 4: SSD was 6.81x faster than SCSI
  • 8: SSD was 7.64x faster than SCSI
  • 16: SSD was 9.03x faster than SCSI

The configuration of 4 data files on one SSD and the log on the other SSD, with basic format for both, was the best overall performer, beating the best SCSI configuration (2 data files) by a factor of 4.96.

Summary

Reminder: this test was 32 million inserts with no reads or updates (i.e. no random IO). It is very important to consider the limited scenario being tested and to draw appropriate conclusions.

My conclusions are as follows:

  1. For a random read+write workload, the method of formatting the Fusion-io drives doesn't make much difference. I'd go for the basic format to get the higher capacity, but I'd always to a representative load test to make sure.
  2. For a random read+write workload, the SSDs give at least a 5x performance gain over iSCSI storage
  3. Once again, having multiple data files outperforms having a single data file in most configurations
  4. I can easily correlate IO-subsystem related wait stats to the varying performance of the various configurations

Compared to the sequential insert workload that I benchmarked in the previous set of tests, the random read+write workload makes it worth investigating the investment of moving to SSDs.

Just like last time, these results confirm what I'd heard anecdotally - random operations are the sweet-spot for SSDs.

Brent's playing with the server over the next 4 weeks so I won't be doing any more benchmarking until mid-September at least.

Hope these results are interesting to you!

The July edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column (and I forgot to blog about it a couple of weeks back).

This month's topics are:

  • Why shrink runs slower on some databases than others
  • Is it true that autogrow should always be turned off?
  • Why database snapshots are not a good substitute for transaction log backups
  • Why I/O subsystem-caused corruption does not propagate to a database mirror

Check it out at http://technet.microsoft.com/en-us/magazine/ff808322.aspx.

Back at the start of July I kicked off a survey around your plans for SSDs (see here) and now I present the results to you. There's not much to editorialize here, but the numbers are interesting to see.

 

The "other" answers were (verbatim):

  • 3 x 'have bought and am trying them out'
  • 3 x 'not sure if we need them or not'
  • 2 x 'all production servers are hosted'
  • 1 x 'bought them, tried them..not good enough yet for tempdb'
  • 1 x 'Have some, want more, could you really every have enough?'
  • 1 x 'We get every penny from or spinning media, and have no need for SSD'

The results reflect what I've been hearing when teaching classes and talking to customers/conference attendees over the last six months. People are becoming more interested in SSDs but there's still a lot of wariness about them and of course the whole money issue of being able to buy them. I'm also not surprised (given the general readership demographics of this blog) by the number of people who've analyzed their IOPS requirements and concluded that they don't need SSDs to accomplish that.

 

The "other" answers were (verbatim):

  • 3 x 'not in the budget'
  • 1 x 'I plan to buy expensive drives and throw them at you, paul! love, conor'
  • 1 x 'I'm going to do the same thing Conor will do. Denny'
  • 1 x 'OLAP Scale Out'
  • 1 x 'Use them as cache'
  • 1 x 'Using in an EMC V-MAX SAN to dynamically move high workloads to SSD temporarily'

Ahem - thanks Conor and Denny :-)

Another unsurprising set of results that reflects what I've been hearing. One number I'd be interested in drilling deeper into is answer #3 - are people putting/planning to put tempdb on SSDs because that's what they've heard is the best thing to do, or because tempdb truly is the largest I/O bottleneck that can benefit the most from SSDs? That's a set of experiments I'd like to try out with my Fusion-io drives.

The final "other" answer is also interesting - I was talking to a couple of folks from EMC in Ireland about the V-MAX when we were there earlier this month. Very cool idea to migrate data up and down a set of devices with varying latencies (at the block level, not the file level) - I'd like to see more on how the technology copes with one-off operations like consistency checks or backups - do those IOs affect which layer a block resides in?

Anyway, hope you find these results interesting.

Thanks to all those who responded!

Last week we sat down with our good friends Richard Campbell and Greg Hughes of RunAs Radio and recorded an interview about how to learn effectively, plus some of the usual nonsense and insults.

Check it out at http://www.runasradio.com/default.aspx?showNum=169.

Enjoy!

Categories:
Interviews

Every month there's a flurry of blog posts around the same topic - it's called T-SQL Tuesday and is a neat concept. This month it's being driven by Robert Davis (blog|twitter), who I had the pleasure of teaching in the March rotation of the Microsoft Certified Master - SQL class (he passed first time) and previously in internal Microsoft classes. The topic is about how to teach and learn, so I want to twist it around a little and talk about things from an instructor's perspective.

Kimberly and I teach a *huge* amount - classes, workshops, conferences, private clients - and we've seen the whole gamut of student types and classroom antics. In this post I'd like to lay out what I consider to be the things most likely to annoy your fellow students, annoy the instructor, and/or prevent you from getting the most from your class. Think of it as a not-too-subtle rant at a few bad apples out there. Either read along and feel guilty that you've done some of these, or read along and tut-tut that you've seen someone do this and it sucked.

These are in loose order, with #1 being the worst mistake to make. Some of these are controversial, but I'm an honest kind of guy, and people like how I run my classroom, so I want to get them out there. Here you go:

10 Take a phone call during class

If you take a phone call during class, I'll ask you to leave the room. At the start of the class I always ask for phones to be on vibrate and to step out if you have a call. I don't mind people walking in and out a few times to take calls - it's very hard to put me off when I'm teaching. But talking on a phone in class (apart from saying 'hold on a sec while I go outside') is just antisocial and inconsiderate. Don't do it.

If a phone rings during class, I'll start to dance. Everyone laughs. I'm letting you know that we all realize you totally ignored the instructions about phones that everyone else adhered to.

And if you *make* a call during class, expect no mercy. I've had this happen once. After he came back in from making the call, and at the next break, I went over and explained how incredibly rude that was and he could choose to stay in the class without his phone or leave. He stayed.

9 Sit at the back and do email/surf and then ask questions

There's one person in every class like this - who surfaces every so often and asks questions about stuff we just covered. My response is usually something like "we just covered that ten minutes ago, read the slides and let me know if you have questions". If you can't get it together to pay attention, at least check where we are in class before asking questions that tell everyone else you've been doing something else and are now wasting their time.

8 Persist with a tangential rat-hole

While laying out the ground-rules of the class at the start, I talk about how questions are excellent, the whole point is that you're here to learn, but that long discussions about your particular situation will have to go to the break, lunch, or after class. And I mean it. Classes are carefully planned to have a certain percentage of question and discussion time (some more than others) and so if you're going on and on about something that's not relevant for the rest of the class, you'll need to wait to monopolize the instructor's time when it's not everyone else's time too. I've actually had to say "ok - stop talking about that now, we have to move on with the class". Most often these people are really trying to do #1 below.

7 Bring your smelly lunch into the classroom

Everyone will hate you.

6 Come to a class where you don't understand the language it's being taught in

I struggled over whether to include this one, but it has to be said. Don't come to a class where you can't understand the language it's being taught in. I speak English, reasonably well :-), and I make a point of speaking clearly and explain things in a concise, unambiguous way. If I'm teaching a class in the US, the UK, or any other English-is-the-first-language country, I expect that students in a deep technical class about an engineering topic, with lots of arcane terms and the need for precision in explanations, are able to understand the language. I know there are a lot of ESL (English-as-a-Second-Language) folks in these countries, but if you come to a class with a bunch of other people and ask me at lunch on the first day to speak a lot slower and with smaller words because you don't understand English very well, the answer has to be no. I'm not being inconsiderate, you are. On the other hand, if I'm teaching in China, for instance, I'll seriously go out of my way to speak slowly and avoid language complexities and colloquialisms as that's the totally different audience.

The MCM has a prerequisite that you have to understand English really well before being accepted on the course, as it's fast-paced and deeply technical. A couple of ESL folks have fudged that requirement, come on the course, and failed because they couldn't keep up. It's really not fair to everyone else to have to slow right down for one person in a face-to-face class.

That's the most controversial of the mistakes I wanted to list, but I stand by what I've said. I'm not against ESL students in any way - many of the people I teach inside Microsoft are ESL - but you have to have a certain level of proficiency in the language the class is being taught in to be able to keep up. I've had people in classes that knew so little English they couldn't even ask a question I could understand - and I'm very patient and usually able to understand most people.

5 Come to a class without the required experience and knowledge

Most classes list the detailed agenda and the prerequisite knowledge, if applicable. This is so that you can gauge whether you're qualified to take the class. Don't come to an advanced class on disaster recovery and ask how to take backups using SSMS, or come to a workshop on performance tuning using wait stats and ask what an index is. You wouldn't send someone who can't swim to a class on cave diving, or send a freshman medical student to a symposium on endovascular aneurysm repair techniques, would you? So don't take a SQL class that you're not qualified to understand. You will end up a) not being able to follow the class and getting frustrated b) asking really basic questions that annoy the rest of the class and the instructor.

Oh, and by the way, reading a book about SQL Server doesn't remotely equal having experience as a DBA - so if you simply read a book to pass a qualification, you're doing yourself and whoever employs you a disservice.

4 Don't take notes

If you really want to learn, take notes about what gets drawn on the whiteboard and salient points of what gets discussed. That's why we give you a printout of the slides - so you can take notes on them. This may be more necessary with some instructors than with others - our slides are pretty dense so you can follow the story when reading them later (but that's a whole other discussion...) If you don't take notes, you'll forget things. And if you ask the same thing several times because you didn't note down the answer the first time, you'll really piss off the instructor. I had a class earlier this year where someone asked me the same thing 4 times over the course of 3 days. I was not happy, and I made sure it showed the last time by starting with "you've already asked me that three times..." as it was beyond ridiculous.

3 Ask questions to try to make it look like you know more than the instructor

You don't look cool. You look like a fool. Everyone is rolling their eyes at you, but you just can't see it. Yes, really.

Every so often I'll have someone in a class who wants to prove to everyone that they're very clever and know more than everyone else, and really doesn't need to be in the class because they're so smart. 100% of the time it's a man. There's nothing to be gained from trying to one-up the instructor. If you succeed, you may sit back all smug, but everyone else is thinking 'jerk' (or worse). These kinds of questions are usually about really narrow scenarios, or deep internals, that are beyond the scope of the class and most often the tactic fails, which makes the questioner more frustrated and ask more questions...

Invariably this leads to #2...

2 Argue that the instructor is wrong

Cardinal sin. If you think the instructor is wrong there are two correct ways to express that opinion: 1) say something along the lines of seeing different behavior in some circumstances, which leads to a nice discussion where everyone can agree and the instructor can explain he can't remember everything with a smile 2) come up to the instructor at the break to discuss it. Never accuse the instructor of being downright wrong in front of everyone. If you do, you'd better be 100%-absolutely-sure-beyond-a-shadow-of-a-doubt because one of two things is going to happen: 1) you'll be proved right and everyone will think 'jerk' (or worse). Or, and this is much, much, much more likely, 2) you'll be proved wrong, become embarrassed, frustrated, and angry and everyone will think 'jerk' (or worse).

Arguing obnoxiously is not the way to win friends and influence people, or to endear you to the class and the instructor. Most often the instructor is there because he or she knows way more than anyone in the class about the topic at hand - which is the whole point, so it's unlikely that they're wrong. It does happen, people are not infallible, but point it out nicely. And be really, really sure you know who you're arguing with before you start - pay attention to the two minute bio at the start of the class, because that's the explanation of why the instructor is qualified to teach the class, and what their expertise is. Every few classes I find myself arguing with someone about how DBCC works, or what allows the log to clear, or this or that and very occasionally I have to resort to one of the trump cards, which I hate doing, by saying "I'm sorry, you are wrong - I wrote that code", or "I'm sorry, you are wrong, I designed that feature". That sucks because I feel like I'm being arrogant. Sigh.

1 Come to class looking for "the answer"

There's one of these people in every class, who simply wants to know "how to index for *this* query" or "the *best* backup strategy". I like to joke that the answer to every question about SQL Server is "it depends!", with one exception: "should auto-shrink be enabled?". That's because there are no hard and fast answers - the answer really does depend on the circumstances. A good instructor does not teach answers, but instead teaches methodologies, theory, and background information, along with real-life examples of applying all of those so that you can find the answer for yourself, and even pass along the knowledge to your team/company. There's no point just teaching the answer, because what happens next week when you have another question? If you don't understand how the first answer was derived, you'll be stuck again and no better off for attending the class.

I see this over and over and it's depressing.

Summary

Ah - that's better. If you avoid doing all these things then you'll have a great learning experience and the atmosphere in the classroom will be conducive to being a sponge to the fire-hose of information. If not, then now you know why the instructor is looking at you disdainfully...

This turned out to be a lot longer than I expected. Now, don't take this the wrong way - I *really* love teaching, which is why I do it so much, so I'm not being a jerk saying all of this - I expect that when you come into a class, you come to learn. I don't expect you to disrupt things for the other students, and disrespect me as the instructor. I guarantee you that everyone reading this who's ever been an instructor has agreed with everything I've written above.

Don't be that person.

Categories:
General

Over the last month we've been teaching in Europe and I haven't had much time to focus on benchmarking, but I've finally finished the first set of tests and analyzed the results.

You can see my benchmarking hardware setup here, with the addition of the Fusion-io ioDrive Duo 640GB drives that Fusion-io were nice enough to lend me.

In this set of tests I wanted to check three things for a sequential write-only workload (i.e. no reads or updates)

  • Best file layout with the hardware I have available
  • Best way to format the SSDs
  • Whether SSDs give a significant performance gain over SCSI storage

The Fusion-io SSDs can be formatted four ways:

  • Regular Windows format
  • Fusion-io's format
  • Fusion-io's improved write performance format
  • Fusion-io's maximum write performance format

I'm using one of the 640GB SSDs in my server, which presents itself as two 320GB drives that I can use individually or tie together in a RAID array. The actual capacity varies depending on how the drives are formatted:

  • With the Windows and normal Fusion-io format, each of the 320GB drives has 300GB capacity
  • With the improved write performance format, each of the 320GB drives has only 210GB capacity, 70% of normal
  • With the maximum write performance format, each of the 320GB drives has only 151GB capacity, 50% of normal

In my tests, I want to determine whether the loss in capacity is worth it in terms of a performance gain. The SSD format is performed using Fusion-io's ioManager tool, with their latest publicly-released driver (1.2.7.1).

My tests involve 16 connections to the server, running server-side code to insert 6.25GB each into a table with a clustered index, one row per page. The database is 160GB with a variety of file layouts:

  • 1 x 160GB file
  • 2 x 80GB files
  • 4 x 40GB files
  • 8 x 20GB files
  • 16 x 10GB files

These drop down to 128/64/32/etc when using a single 320GB drive with the maximum write capacity format. The log file is pre-created at 8GB and does not need to grow during the test.

I tested each of the five data file layouts on the following configurations (all using 1MB partition offsets, 64k NTFS allocation unit size, 128k RAID stripe size - where applicable):

  • Data on RAID-10 SCSI (8 x 300GB 15k), log on RAID-10 SATA (8 x 1TB 7.2k)
  • Data round-robin between two RAID-10 SCSI (each with 4 x 300GB 15k and one server NIC), log on RAID-10 SATA (8 x 1TB 7.2k)
  • Data on two 320GB SSDs in RAID-0 (each of the 4 ways of formatting), log on RAID-10 SATA (8 x 1TB 7.2k)
  • Log and data on two 320GB SSDs in RAID-0 (each of the 4 ways of formatting)
  • Log and data on single 320GB SSD (each of the 4 ways of formatting)
  • Log and data on separate 320GB SSDs (each of the 4 ways of formatting)
  • Log and data round-robin between two 320GB SSDs (each of the 4 ways of formatting)

That's a total of 22 configurations, with 5 data file layouts in each configuration - making 110 separate configurations. I ran each test 5 times and then took an average of the results - so altogether I ran 550 tests, for a cumulative test time of just less than 110 million seconds (12.7 days) over the last 4 weeks.

And yes, I do have a test harness that automates a lot of this so I only had to reconfigure things 22 times manually. And no, for these tests I didn't have wait stats being captured. I've upgraded the test harness and now it captures wait stats for each test - that'll come in my next post.

On to the results... bear in mind that these results are testing a 100GB sequential insert-only workload and are not using the full size of the disks involved!!!

Data on SCSI RAID-10, log on SATA RAID-10

 

I already blogged about these tests here last week. They prove that for this particular workload, multiple data files on the same RAID array does give a performance boost - albeit only 6%.

The best performance I could get from the SCSI/SATA configurations was completing the test in 1755 seconds.

Data and log on 640GB RAID-0 SSDs (Data on 640GB RAID-0 SSDs, log on SATA RAID-10)

 

The performance whether the log file was on SATA or on the SSD was almost identical, so I'm only including one graph, in the interests in making this post a little shorter.

These results clearly show that the SSDs have to be formatted correctly to get any performance out of them. The SSDs performed the same for all data file configurations until performance almost doubles when the number of data files hits 16. I tested 32 and 64 files and didn't get any further increase. My guess here is that I had enough files that when checkpoints or lazywrites occured, the behavior was as if I was doing a random-write workload rather than sequential-write workload.

The best performance I could get here was with 16 files and the maximum-write format when the test completed in 934 seconds, 1.88x faster than the best SCSI time. This is only 13 seconds slower than the normal format which gives 100% more capacity.

Data and log on single 320GB SSD

 

Here the performance truly sucked when the SSD wasn't formatted correctly. Once it was, the performance was roughly the same for 1, 2, or 4 files but degraded by almost 50% with normal formatting for 8 or 16 files. With improved-wait and maximum-write formatting, the performance was the same as for the 640GB RAID-0 SSD array, but the sharp performance increase with 16 files only happened with the maximum-write formatting.

Data and log on separate 320GB SSDs

 

No major difference here - same characteristics as before when formatted correctly, and the best performance coming from maximum-write formatting and 16 data files.

This configuration gave the best overall performance - 909 seconds - 1.93x the bext performance from the SCSI storage.

Data and log round-robin between separate 320GB SSDs

 

No major differences from the previous configuration.

Best-case performance for each number of data files

  

  

 

Clearly the SSDs outperform the SCSI storage for these tests, but not by very much. The improvement factor varied by the number of data files:

  • 1: SSD was 1.11x faster than SCSI
  • 2: SSD was 1.09x faster than SCSI
  • 4: SSD was 1.06x faster than SCSI
  • 8: SSD was 1.04x faster than SCSI
  • 16: SSD was 2.03x faster than SCSI

The configuration of 16 data files on one SSD and the log on the other SSD, with maximum-write format for both, was the best overall performer, beating the best SCSI configuration (8 data files) by a factor of 1.93.

Summary

Reminder: this test was 100GB of sequential inserts with no reads or updates (i.e. no random IO). It is very important to consider the limited scenario being tested and to draw appropriate conclusions

Several things are clear from these tests:

  1. The Fusion-io SSDs do not perform well unless they are formatted with Fusion-io's tool, which takes seconds and is very easy. I don't see this as a downside at all, and it makes sense to me.
  2. For sequential write-only IO workloads, the improved-write and maximum-write SSD formats do not produce a performance gain and so the loss in storage capacity (30% and 50% respectively) is not worth it.
  3. For sequential write-only IO workloads, the SSDs do not provide a substantial gain over SCSI storage (which is not overloaded).

All three of these results were things I'd heard anecdotally and experienced in ad-hoc tests, but now I have the empirical evidence to be able to state them publicly (and now so do you!).

These tests back-up the assertion I've heard over and over that sequential write-only IO workloads are not the best use-case for SSDs.

One very interesting other result came from these tests - moving to 16 data files changed the characteristics of the test to a more random write-only IO workload, and so the maximum-write format produced a massive performance boost - almost twice the performance of the SCSI storage!

The next set of tests is running right now - 64GB of inserts into a clustered index with a GUID key - random reads and writes in a big way. Early results show the SSDs are *hammering* the performance of the SCSI storage - more in a week or so!

Hope you find these results useful and thanks for reading!

Categories:
Benchmarking | Performance | SSDs

This November Brent, Kimberly and I will be at the PASS Summit in Seattle as usual doing workshops and Spotlight Sessions (90 minute sessions to dive deeply into a subject).

The PASS Summit is an excellent learning, networking and socializing event - we always have a great fun!

Here's what SQLskills will be doing and we really hope to see you there!

Pre-Con Workshop

Database Best Practices for the Involuntary DBA (Paul and Kimberly)

Are you the "SQL person" on your team? Have you become a DBA (maybe involuntarily) and you find yourself managing SQL Server databases more and more? The one thing you NEED now, to manage the system correctly is knowledge! Paul and Kimberly will run through their top-ten database maintenance recommendations with lots of tips and tricks along the way. These are distilled from almost 30 years combined experience working with SQL Server customers and are geared towards making your databases more performant, more available, and more easily managed (and to save you time!). Everything in this session will be practical and applicable to a wide variety of databases you create, implement and manage yourself, as well as third party databases you manage. Topics covered include: data and log file configuration, tempdb, backups, consistency checking, database settings, statistics, and much more! Focus will be on 2008 but we'll explain where there are key differences for 2005 as well.

Post-Con Workshop

Indexing Strategies that work: Covering Concepts, Concerns, Costs (Kimberly)

In my highly rated spotlight session at PASS 2009, I spent only 90 minutes on this incredibly important topic and I could have done many more demos. This year, we’re going to spend the entire day focusing on the concepts, concerns and costs associated with the single most important tuning feature in indexing strategies: covering. Do you really know when and how to use this appropriately? Do you know what features provide covering options? Indexing is by far the most important aspect to database performance and health. But, do you have the right indexes? And, how are these indexes being used? This will be a demo packed session with numerous examples so that your indexing strategies come together for the best balance. In addition to looking at which indexes work for what types of queries, we'll look at the DMVs that can help us better understand where to look but we’ll also get insight into the pitfalls of these tools. DBAs, Devs and DB Architects can all benefit from this workshop.

Spotlight Sessions

Tales from the Trenches: GUIDs – Use, Abuse and How to Move Forward (Kimberly)

Since the addition of the GUID (Microsoft’s implementation of the UUID), my life as a consultant and "tuner" has been busy. I’ve seen databases designed with GUID keys run fairly well with small workloads but completely fall over and fail because they just cannot scale. And, I know why GUIDs are chosen – it simplifies the handling of parent/child rows in your batches so you can reduce round-trips or avoid dealing with identity values. And, yes, sometimes it's even for distributed databases and/or security that GUIDs are chosen. I'm not entirely against ever using a GUID but overusing and abusing GUIDs just has to be stopped! Please, please, please let me give you better solutions and explanations on how to deal with your parent/child rows, round-trips and clustering keys! Come to this session and you’ll really understand the *true* cost of GUIDs? We’ll look at programming, storage, maintenance, and above all – performance; I think you’ll be surprised at how much these really cost!

DBA Mythbusters (Paul)

It's amazing how many myths and misconceptions have sprung up and persisted over the years about SQL Server - after more than 10 years helping people out on forums, newsgroups, and customer engagements, Paul's heard it all. Are there really non-logged operations? Can interrupting shrinks or rebuilds cause corruption? Can you override the server's MAXDOP setting? Will the server always do a table-scan to get a row count? These are just a few of many, many myths that Paul will debunk in this fast-paced session on how SQL Server operates and should be managed and maintained.

You're Not Attractive - But Your Presentations Can Be (Brent, and our good friend Buck Woody from Microsoft)

Come hear Buck Woody (Microsoft's Real World DBA) and Brent Ozar (SQL Server Certified Master) explain how they make high scores at presentations. It's not luck, charm or (surprisingly) good looks - there are tips and tricks you can use to make your own presentations rock. With Buck and Brent in the same room it's much like Forest's Box of Chocolates, but you're sure to learn more about presentation techniques that you can extract into your own style.

Virtualization and SAN Basics for DBAs (Brent)

These two technologies can make a very big – and very bad – difference in how your SQL Server performs. Wouldn’t it be great if you could get the real, honest lowdown from a virtualization administrator, a SAN administrator, and a DBA? Wouldn’t it be even better if one person had done all three, and could give you the pros and cons of each point of view? That person is Brent Ozar, a Microsoft Certified Master who’s been there and done that.

Categories:
Conferences

With exactly a month to go until our 5-day Immersion event in Bellevue, WA we've got 25 people registered for the class (wow!) and space for a few more!

It's going to be a *really* good class - with people flying in from all over the country to spend the week with us. We're also going to bring Brent Ozar out from Chicago to do an evening session on virtualization for the class.

With many company's financial year just ending and FY11 just beginning, now's the time to get an early jump on the training budget and come to the best SQL training around.

We decided to put on a class near Seattle this time as Washington is *fabulous* in August!

The class will follow our regular 5-day format with our best content covering:

  • On-disk structures: how the data is stored
  • Index internals: how the data is organized
  • Logging and recovery: how the data is protected
  • Choosing the RIGHT Data Type
  • Table & Index Partitioning Strategies
  • Data Access
  • Indexing Strategies
  • Data and log file provisioning and management
  • Tempdb
  • Index and statistics maintenance
  • Using backup and restore (plus internals)
  • Consistency checking and disaster recovery

The event will be held in the Marriott Courtyard Bellevue Downtown and will be fully catered - there's a special room rate of $159/night but that's filling up fast with so many out-of-towners coming in.

Check out the Immersion Event page for full details, registration, and feedback from attendees of our event in Boston earlier this year (or jump straight to registration).

We hope to see you there!

Categories:
Classes

It's been a while since I conducted blog surveys so I'm going to do a few through July and August.

First up - I'd like to know what your plans are for purchasing SSDs - do you already have them or are you planning to buy some in the next year?

Secondly - I'd like to know what you are (or will be) doing with them.

I'll report on the results in a couple of weeks. The surveys are completely anonymous. Spread the word so we get a good volume of responses! 

As always, a big Thanks! for contributing to the blog/community by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

Cheers!

(PS Comments are deliberately disabled.)

Categories:
SSDs | Surveys

(Happy Independence Day to everyone back home in the USA! We're in Dublin for another week...) 

Back at the end of April I conducted a survey of transaction log characteristics (see here) and now I present the results to you.

I had a great response to the survey from almost 100 people, and the total data set for these observations is 16841 databases on 847 SQL Server instances (SQL Server Edition is irrelevant).

This isn't the most exciting blog post, but I at least found the numbers interesting - hope you do too! I've got some pointers in the summary to log management blog posts.

Number of Databases Per Instance 

Each plot on the graph below is a count of the number of instances having that many databases, including the four system databases.

 

Just under 75% of instances have 20 or fewer databases (including system databases). The highest number per instance is 571.

Number of Log Files Per Database

This wasn't worth plotting. The results are:

Log Files   Number of Databases
1           16657
2           158
3           9
4           8
5           1
6           4
7           1
24          1

I'm impressed by this - I expected to see more databases with multiple log files. The one with 24 is tempdb - someone setting the number of log files to equal the number of processor cores I'd guess.

System Databases: Log File Size

Each plot on the graph below shows the number of log files of that size across all the instances, color-coded for master, msdb and tempdb.

 

89% of master database log files are less than 10MB (a number I arbitrarily chose as a comparison point).

Only 40% of msdb log files are less than 10MB. This would suggest that there are a high proportion of msdb databases not using the SIMPLE recovery model. However, only 5% of them aren't using SIMPLE.

Only 33% of tempdb log files are less than 10MB. I would expect this number to be low.

Looking at the distribution on the graph, master log files (blue) are generally smaller, tempdb log files (green) are generally larger, with msdb (red) in the middle. Again, this is what I'd expect to see.

System Databases: Number of VLFs

Each plot on the graph below shows the number of log files with that many VLFs across all the instances, color-coded for master, msdb and tempdb. (If you're unfamiliar with the term 'VLFs', see the TechNet Magazine article I link to at the bottom).

 

98.6% of all master log files have less than 100 VLFs (our arbitrary 'you may have too many VLFs' tipping point), with the maximum being 331.

Only 65% of all msdb log files have less than 100 VLFs. From the other 35%, only 5% of them aren't using the SIMPLE recovery model. The maximum number of VLFs is 7646.

Surprisingly, 75% of tempdb log files have less than 100 VLFs. I expected this number to be higher in general, because of tempdb autogrowth. The maximum number of VLFs is 59708.

Looking at the graph, master (blue) is as I expected, but msdb (red) and tempdb (green) are the opposite of what I expected - with more msdb log files having more VLFs.

User Databases: Log File Size

Out of all 13451 user databases in the sample:

  • 6797 use the SIMPLE recovery model (50.5%)
  • 177 use the BULK_LOGGED recovery model (1.3%)
  • 6477 use the FULL recovery model (48.2%)

This in itself is surprising - I expected to see a higher proportion of FULL recovery model databases. 

Given that the log file behavior is the same for FULL and BULK_LOGGED recovery models, and as so few databases use the BULK_LOGGED recovery model, I lumped the FULL and BULK_LOGGED databases together for analysis.

Each plot on the graphs below shows the number of log files of a certain size across all the instances.

  

 

The graphs above have different sizes because more databases in the SIMPLE recovery model are larger sized. The overall distribution looks very similar though.

90.7% of user databases using the SIMPLE recovery model have a log file size less than 1GB, compared with 83.0% for non-SIMPLE user databases.

98.2% of SIMPLE user databases have a log file size less than 10GB, compared with 96.8% for non-SIMPLE user databases.

The maximum size of a log file for a SIMPLE user database is 1020GB, dropping to 326GB for a non-SIMPLE user database.

The non-SIMPLE user databases seem to be slightly better managed. My guess would be this represents databases that have been in the FULL or BULK_LOGGED recovery models, have grown out of control and been switched to SIMPLE, but not cleaned up.

User Databases: Number of VLFs

Each plot on the graphs below shows the number of log files with a certain number of VLFs across all the instances.

 

 

As with the log file sizes, the distributions of these two graphs look very similar. 

76.9% of user databases using the SIMPLE recovery model have less than 100 VLFs, dropping to 67.8% for those not using SIMPLE.

98.4% of user databases using the SIMPLE recovery model have less than 500 VLFs (definitely a point at which maintenance is required), dropping to 97.0% for those not using SIMPLE.

The maximum number of VLFs for the SIMPLE user databases is 34057, and 27093 for those not using SIMPLE.

Given that more user databases using SIMPLE are larger, but have slightly fewer VLFs, I'd guess this indicates that autogrowth of thse databases was slightly higher.

User Databases: Log File Size vs. Number of VLFs

At first glance, the graph below looks confusing. It shows the number of VLFs each log file has compared to its size in MB. If you look at the first few log file sizes, you'll see that there are multiple points plotted on the Y-axis. This is because there are databases that have the same size log files, but the log files have different numbers of VLFs.

 

There's definitely a loose trend-line in the graph, with a knee at around 50MB log file size.

Summary

I'm impressed. 75% of all databases in the sample have less than 100 VLFs, and 97.4% have less than 500 VLFs. I expected the results to be worse, but they seem show that the majority of you are managing your logs well. Now, these results are a bit skewed though because they're coming from people who read my blog, where I bang on and on about good maintenance and management.

I was very surprised by the 50/50 split of SIMPLE/non-SIMPLE recovery model for user databases. My guess is that more of these *should* be in FULL to comply with business SLAs around RPO/data-loss, but for some reason aren't, or aren't any longer because of out-of-control growth issues.

Thanks to everyone who sent me results!

Here are some links for further reading:

Enjoy!

Categories:
Surveys | Transaction Log

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!

Categories:
Consulting | General

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: See this site for full details, registration links and options.
  • 2-Day Performance Tuning Masterclass, July 5-6 2010: See the this site for full details, registration links and options.
  • 2-Day Disaster Recovery Masterclass, July 7-8 2010: See the this site for full details, registration links and options.
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:

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

Categories:
Classes

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!

As a follow-up to my previous post Disaster recovery 101: backing up the tail of the log, I want to describe how to re-attach a damaged database if someone's detached it accidentally while performing disaster recovery. A couple of years ago I blogged a detailed post on Creating, detaching, re-attaching, and fixing a suspect database but now I'd like to do a short, concise post for reference.

There are many reasons why you may have a damaged database that you're trying to attach to SQL Server:

  • It may be a SUSPECT database that someone is erroneously trying to fix by detaching+attaching
  • It may be a damaged database from a server that is inaccessible that needs to be attached for disaster recovery
  • It may be a database you've been sent/given that was not cleanly shut down (i.e. there were active transactions) and you were not given the transaction log file

In any of these scenarios,  a damaged database cannot be attached to SQL Server using the normal methods: sp_attach_db, CREATE DATABASE ... FOR ATTACH, or CREATE DATABASE ... FOR ATTACH_REBUILD_LOG (the latter two being in SQL Server 2005 onwards). Either the data file damage will prevent the attach, or the log file damage will prevent crash recovery from being started or completed.

To attach the database you need to fool SQL Server into thinking it's already attached. The set of steps to go through are as follows:

  1. Create a dummy database, with the same name, the same number of data and log files, with the same file names and rough sizes (make sure you have instant file initialization enabled so the file creations don't take ages - see this blog post)
  2. Set the dummy database offline (ALTER DATABASE SET RealDatabaseName OFFLINE) or shut the server down
  3. Delete all the data and log files from the dummy database
  4. Drop in as many of the data and log files from the damaged database as possible
  5. Set the dummy database online (ALTER DATABASE SET RealDatabaseName ONLINE) or start the server if you shut it down for step 2

At this point SQL Server will be fooled into thinking the database is attached (as it thinks it's just restarting the dummy one you created) and you'll be able to take further measures to recover the database.

Further measures may include:

  • Taking a tail-of-the-log backup to use as part of a restore sequence (see this blog post)
  • Use EMERGENCY mode to extract data into a new database
  • Using EMERGENCY mode repair to repair a damaged transaction log (see this blog post)

Note: it's entirely possible that your database may be so damaged that not even EMERGENCY mode repair can fix it. If you cannot even get the database into EMERGENCY mode (e.g. if the database boot page, or the primary file's file header page is damaged) then there is no way for SQL Server to access the database. At this point the only option is to restore from backups. If you don't have backups, game over.

Hope this helps!

One of the first things you should always check when a database has been damaged and you're going to perform a restore operation is whether you need to back up the tail of the log.

The tail of the log contains the transaction log that's been generated since the most recent log backup was taken. If you want to be able to recover right up to the point of the disaster, you need to be able to get those log records so they can be part of the restore sequence.

There are two cases to consider here 1) the server is still available 2) the server is not available. For case 1, you can just perform a regular tail-of-the-log backup. For case 2, you'll need to hack-attach the log into another server to be able to back it up.

Case 1: tail-of-the-log backup when server is available

If the database is damaged but the server is still available, taking a tail-of-the-log backup is simple. The only exception is when there has been a minimally-logged operation performed in the BULK_LOGGED recovery model since the previous log backup - in that case a tail-of-the-log backup is not possible at all, and you'll have lost all transaction log generated since the last log backup. See A SQL Server DBA myth a day: (28/30) BULK_LOGGED recovery model for more details.

When the data files are damaged and you want to take a log backup, you'll get an error if you try to back up the log normally.

As an example, I'll create a database and put some transactions in it:

CREATE DATABASE DBMaint2008;
GO
USE DBMaint2008;
GO

CREATE TABLE TestTable (C1 INT IDENTITY, C2 CHAR (100));
GO

-- Take a full backup
BACKUP DATABASE DBMaint2008 TO DISK = 'D:\SQLskills\DemoBackups\DBMaint_Full.bck' WITH INIT;
GO

-- Insert some rows
INSERT INTO TestTable VALUES ('Transaction 1');
INSERT INTO TestTable VALUES ('Transaction 2');
GO

-- Take a log backup
BACKUP LOG DBMaint2008 TO DISK = 'D:\SQLskills\DemoBackups\DBMaint_Log1.bck' WITH INIT;
GO

-- Insert some more rows
INSERT INTO TestTable VALUES ('Transaction 3');
INSERT INTO TestTable VALUES ('Transaction 4');
GO

Now disaster will strike - I'll do the following to simulate a disaster:

  1. ALTER DATABASE DBMaint2008 SET OFFLINE
  2. Delete the data file

If I try to then access the database, I'll get:

ALTER DATABASE DBMaint2008 SET ONLINE;
GO

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBMaint2008.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 945, Level 14, State 2, Line 1
Database 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

So I'll try a normal log backup:

BACKUP LOG DBMaint2008 TO DISK = 'D:\SQLskills\DemoBackups\DBMaint_Log_Tail.bck' WITH INIT;
GO

Msg 945, Level 14, State 2, Line 1
Database 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

It doesn't work - as the data files aren't all accessible.

The trick is to use the NO_TRUNCATE option, which allows the log backup even if the database files aren't there:

BACKUP LOG DBMaint2008 TO DISK = 'D:\SQLskills\DemoBackups\DBMaint_Log_Tail.bck' WITH INIT, NO_TRUNCATE;
GO

Processed 2 pages for database 'DBMaint2008', file 'DBMaint2008_log' on file 1.
BACKUP LOG successfully processed 2 pages in 0.010 seconds (0.972 MB/sec).

I can then use the tail-of-the-log backup as the final backup in the restore sequence, saving transactions 3 and 4 from above. Try it for yourself.

Case 2: tail-of-the-log backup when server is no longer available

This is the case where the server crashed and cannot be brought online. If you have access to all the data and log files for the database, you can attach it to another server and crash recovery will run automatically. If you only have access to the log file, you'll need to perform some extra steps to allow a tail-of-the-log backup to be performed - basically performing what I call a hack-attach.

Assuming I've run the script above to setup the database, this time I'll do the following to simulate a server-crash disaster:

  1. ALTER DATABASE DBMaint2008 SET OFFLINE
  2. Delete the data file
  3. Copy the log file somewhere else
  4. DROP DATABASE DBMaint2008

Now all I have is some backups and a log file. I'll need to attach the log file to SQL Server somehow so that I can perform the vital tail-of-the-log backup. The way to do it is:

  1. Create a dummy database with the same name as the one that we're interested in (make sure you have instant file initialization enabled so the file creations don't take ages - see this blog post)
  2. Set the database offline (or shutdown the server)
  3. Delete all the files from the dummy database
  4. Drop in the log file from our real database

Like so for steps 1 and 2:

CREATE DATABASE DBMaint2008;
GO
ALTER DATABASE DBMaint2008 SET OFFLINE;
GO

Now I'll perform steps 3 and 4.

I need to attempt to bring the database online again:

ALTER DATABASE DBMaint2008 SET ONLINE;
GO

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBMaint2008.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 945, Level 14, State 2, Line 1
Database 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

And then I can perform the tail-of-the-log backup as before, and use it to recover everything up to the point of the disaster.

Note: This procedure does not work if I try to perform the hack-attach to a more recent version of SQL Server. I tried hacking a 2005 log into a 2008 server and taking the tail-of-the-log backup - which worked fine, but the tail-of-the-log backup could not be used in conjunction with the first set of backups from the 2005 server. The reason for this is that the database version number in the tail-of-the-log backup is 655 (SQL Server 2008) and those for the 2005 backups are 611 (SQL Server 2005). The database doesn't get upgraded when restoring on the 2008 server until the end of the restore sequence - so as far as the 2008 server is concerned, my 2008 tail-of-the-log backup can't be used in the restore seqeunce of a still-2005-really database. Hope that makes sense!

Summary

Checking whether a tail-of-the-log backup is required is essential during a disaster recovery, and it's not hard to do. As with any disaster recovery procedures, make sure you've practiced doing it in advance!

Yesterday morning I was tagged in a blog post by my very good friend Buck Woody (twitter|blog), wondering how I travel (and by extension, how *we* travel, as I'm almost 100% of the time traveling with Kimberly). I think it might make for interesting reading for some of you who travel a lot too.

Buck's way of traveling is very different from ours, I'm guessing for a number of reasons:

  • There's usually two of us as we travel together, and just one of Buck
  • Buck's trips are usually 1-2 days, and hardly ever international, whereas our trips are usually 1-2+ weeks and commonly international (we're about to go on a 4-week trip to Europe)
  • Buck has to live within the Microsoft travel rules, whereas we own our own company so have a lot more flexibility

Before 2006 I didn't travel much at all, and most of the air-travel rules and tips I've learned from Kimberly. She's fabulous.

Most of our travel is for business, but occasionally we go on vacation, mostly diving. When we travel for diving, we get stared at a lot as we travel with 6 very large checked bags (about 400lb total) and our 4 carry-on bags - and yet we *never* pay baggage fees.

The amount of analysis of planes, routes etc is going to sound like Up In The Air, which incidentally I haven't seen (I don't watch in-flight TV), and Kimberly's seen 3 times, all while flying :-) This may all sound insane, but when you're away more than 50% of the year, and fly as much as we do, you have to get routines sorted out.

Read on...

I'll follow Buck's headings for more in-depth descriptions.

Preparing for travel

Kimberly usually books everything. She loves organizing things. I'm very lucky. I hate organizing travel. Really. My input will be on flight times, route costs, hotel rooms, and what we're going to tack on to the trip (we usually add in a few days here and there wherever we're going internationally for some R&R). If I'm traveling on my own (rarely) I'll book everything myself.

We try to fly United as much as humanly possible, even eschewing direct flights sometimes to rack up United miles. We're both 1K (United's top non-invite-only elite travelers), which means we both fly way more than 100000 miles/year on United or Star Alliance airlines (including US Airways, Continental, Lufthansa, Singapore Airlines, Thai Airlines). By being 1K we get enormous amounts of free upgrades, and *superb* treatment by airline staff (e.g. automatic rebooking, even on non-partner airlines, if there's a delay or cancellation). But we have to make sure we hit the magic 100000 miles/year - so we're locked in. It's a win-win for United and for us, especially as there are two of us at 1K. 1K also means we get 3 checked bags each for free, up to 70lbs each bag when flying internationally, and on Star Alliance partners. Plus we always get to go through the elite flyer lines at security.

Given that we have so many miles and free upgrades, we nearly always fly first class (domestic) and business class (international). We're both over 6-ft tall and we fly too much to put up with cramped, noisy conditions with crappy refreshments and no power. Usually we upgrade, and occasionally we buy, depending on why we're traveling and who's paying the bills.

When we do travel economy, we always get the exit row. If we go A-C or D-F, as we're both 1K they try to leave the middle seat open between us. Sometimes it's just not worth using a regional upgrade - e.g. flying SEA-LAX on a CRJ-700. We almost never travel economy when flying internationally, as you don't get a good rest on the flight in economy. Many of our European clients pay for business class specifically so that we're rested when we arrive. On the last flight Kimberly was on, she chatted to a gentleman whose (large) company always buys international first class, to ensure the best possible rest - nice!

Most of the time we're booking flights 3-4 months in advance, sometimes as much as 6 months early to make sure we get good routes, good seats, and good prices. For seats, we know the domestic configurations that United flies really well, but for international, we tend to use Seat Guru to make sure we have good seats. Here are some examples of what we usually fly on United:

  • Airbus A320: seats 2A-B (full recline, no bulkhead)
  • Boeing 757: seats 5A-B (as near to the door as possible, with full recline)
  • Boeing 747: seats 17J-K (in the upper cabin, facing aft by the galley - best service with no-one walking past except crew)
  • Boeing 767 or 777: seats in the little mini-cabin right behind international first class (own crew, plus usually nice and quiet)

As far as hotels go, we tend to stay with Hyatt, Hilton, or Marriott - as we're in the elite programs of each and get room upgrades for free. In major cities outside the US, we might hit a Grand Hyatt (Beijing, Shanghai, Bangkok, Melbourne are all particularly good) for a central location and great services, but sometimes if we're purely onsite with a US client we'll do a Hilton Garden Inn or Homewood Suites - plenty of space and economical for the client. Occasionally we'll do something different, like a boutique hotel in Zurich, or the Novotel in Hyderabad - just depends what there is and what has good locations and rates. We almost always get a suite of some kind (many times through free upgrades) - there's two of us, with multiple laptops, usually for at least week or two so we want some space and don't like feeling cramped.

If we're in the US or UK we get a car - usually an SUV (to accommodate luggage, and I don't like being low-down in a car). Anywhere else we either rely on cabs or we have a driver (e.g. in India).

Packing

I have a long list of things to consider packing (e.g. local currency, binoculars, local bird book, spare XYZ, all paraphenalia for presenting). I'd always recommend a pack list. I always pack the day before, Kimberly's more of a day-of-travel packer - which works out well so we're not in each other's way.

We always have checked baggage. Always. We also always have two carry-on bags each. One is a laptop bag (we both have Tumi Alpha wheeled laptop bags - at almost $700 each they're expensive, but they're fabulous and can hold 3 laptops) and one a 'stuff' bag. I've usually got 4 books, travel wallet (passports, tickets, airline/club cards), sunglasses, iPhone, Dramamine, tissues, mirror, sewing kit, etc in my stuff bag.

Given that we don't pay for checked bags, neither of us pack particularly light. For domestic travel, I have a big blue giant Samsonite hard-case with everything in. Everything's folded nicely and packed neatly, with a pillow on top to hold everything in place (I need a thin pillow to sleep well so bring my own always). I've had the same case since I bought it in Vienna in 1998 - it's fabulous - and easily spotted on the luggage belt. For international I spread out to two bags, more books, workout gear, and more clothes. I use a Victorinox WT-27 as my second bag, and when it breaks I buy another one. It's also bright blue so easily spotted.

I've always got a bunch of books with me, more than I could read on the trip I'm on. I gave up bringing Bose noise-cancelling headphones as they're uncomfortable. I try not to take a coat on the plane - if we're going somewhere cold, I'll pack the coat and bring it out when we collect our luggage. If we're hiring a car in the US, I'll bring a dash-mount GPS - buying one worked out cheaper than renting one each trip.

Journey logistics

On the day of the trip, we compromise and leave for the airport 120-90 minutes before the first flight.  I always take Dramamine to prevent any possibility of feeling crappy from turbulence. I always strive to make us first (or near first) to board so we don't have problems with carry-on luggage. Even in domestic first class, if everyone brings on a laptop back and a rollaboard, there's no space left for late arrivals. Our 4 carry-on bags fit in the space of two rollaboards, so we're not being bad in any way. It's also fun to watch everyone else getting on the plane - traveling is great for people-watching :-)

I alway carry a spare laptop battery and make sure both are charged to 100% - that gives me about 6 hours without needing power. I work maybe 10% of flights I'm on - it's just too much hassle. I much prefer to have a glass of wine and read a book. Kimberly usually has at least two laptops, plus a spare battery.

We are now both trusted travellers as far as the US is concerned which means that was long as we fly back into the correct airport (happily, all of United and Continental's US hubs) we skip the immigration and customs lines. I strongly recommend it if you qualify and can pass all the (pretty stringent I was told this morning) background checks - it's called the Global Entry program.

Working while away

Always. One reason we need a suite is that we both have to setup a laptop. If we're in the US, we both have USB 3MB/s 3G wireless through AT&T. If we're outside the US, I take a LinkSys WR54G hub in checked luggage so we only pay for one internet connection in the hotel and all our laptops can be online. As per usual, we're online almost all the time when we're awake, unless we're specifically on vacation and not answering email. This may sound extreme, but comes with owning your own business, and who needs sleep anyway? Kimberly jokes that I get up when she's asleep and do email and blog posts :-)

We set OOFs to let people know what's going on, and email all current clients in advance so they know when we're contactable - or not.

Summary

This might all sounds glamorous - flying all over the world and staying in nice hotels - but it's not really. We travel a lot because we work a lot, we have to fly business/first so we're not a frazzled mess when we arrive in Asia or Europe, and we need a big room in a decent hotel so we can feel comfortable and concentrate on the client. We're also away 50% or more of the year, so again, we want to be comfortable.

The best thing about all the travel is that we're almost always traveling together, and we both like traveling - most people we know travel on their own.

So there you have it - the Paul & Kimberly travel guide.

Categories:
Personal

A couple of weeks ago, Kimberly and I spent a week on the *excellent* live-aboard dive boat Cayman Aggressor IV, diving off Grand Cayman, Little Cayman, and Cayman Brac in the Caribbean (diving is our #1 hobby and passion). The diving was absolutely fabulous, and we'd both strongly recommend the boat and location. The crew and fellow divers were great folks, which made the trip all the more enjoyable - and we got to meet and hang out with the editor of Sport Diver magazine, Eric Michael, cool guy with a pony-tail :-)

Although Kimberly's an accomplished underwater photographer, this was the first trip where I dove with my own camera gear and I took a bunch of videos. I've uploaded the best videos to YouTube and thought I'd share them with you too.

  1. 2m30s video of me swimming 150 feet through a sunken Russian destroyer off Cayman Brac. See http://www.youtube.com/watch?v=z6seLk9tO5M
  2. 45s video of us tickling and stroking one of the large friendly groupers (you've got to see this to believe it!). See http://www.youtube.com/watch?v=boxfknLyZ2Q
  3. 52s video of Kimberly with the friendly grouper. See http://www.youtube.com/watch?v=Ue7GZTjoEoo
  4. 22s video swimming through a school of fish. See http://www.youtube.com/watch?v=DeKqMEFAZsk
  5. 23s video of swimming through a school of fish. See http://www.youtube.com/watch?v=pT0-PeWz-vM
  6. 1m45s video of the friendly grouper again. See http://www.youtube.com/watch?v=SCIKDkjgccI
  7. 47s video of a turtle swimming underwater. See http://www.youtube.com/watch?v=pBIyzgmznqo
  8. 30s video of a squid swimming at night. See http://www.youtube.com/watch?v=jtoMEM9qS4M
  9. 47s video of octopus swimming at night. See http://www.youtube.com/watch?v=F0MWUh30oFc
  10. 31s video of juvenile drumfish dancing. See http://www.youtube.com/watch?v=pUH21CCvsuQ
  11. 45s video of swimming through a school of fish. See http://www.youtube.com/watch?v=aQSMUHTZsyI
  12. 43s video of another swimming turtle. See http://www.youtube.com/watch?v=CeQFRluKMyo
  13. 34s video of very large tarpin. See http://www.youtube.com/watch?v=YBjHISvG0kk
  14. 22s video swimming through the wreck of the Doc Polson tugboat. See http://www.youtube.com/watch?v=Jusl6bLdt08

The camera I'm using for now is just a Panasonic Lumix DMC-TZ5, but it takes great shots and video. We've got a pro video camera but the tricked-out housing prices are out of control...

Over the next few weeks we'll both be uploading a bunch of photos too - but it takes time to mess around with them in Adobe Lightroom, and we took over 4000 shots altogether...

Enjoy the videos!

Theme design by Nukeation based on Jelle Druyts