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!

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!

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!

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.

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!

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.

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!