At the end of December I showed you how to discover if power saving is enabled on your server, which can lead to variable and often degraded performance. I also included a survey to let me know what you found after running the free CPU-Z tool on your servers. See here for the original post.

I want to do a quick post to show you the results of the survey.

The five 'other' results were:

• "As part of my server build scripts I disable power management"
• "I thought I had power savings on and it was. However, I had never confirmed. (Personal Computer)"
• "I thought we had power saving OFF and it was OFF. Good deal."
• "Not sure if power saving is on -- "Core Speed" is 75% of rated speed"
• "Told IT that they need to change the bios settings and reboot. have they ? have they hell!"

As you can see, almost 40% of the people who tried the tool AND took the time to fill in the survey reported that they discovered power saving was erroneously enabled.

Have you tested your system yet?

Thanks!

Last Tuesday I hosted T-SQL Tuesday #012 (see here for the call for participation) and posed the question "why are DBA skills necessary?" This month broke the participation record with 46 people contributing posts - fabulous! Lots of people contributed for the first time too. And what I think is also really cool is that 45 out of these 46 are on Twitter too.

The downside of so many people participating is that it's taken me quite a while to read through all the posts to compile this summary - maybe I should have picked something obscure and boring instead! One of the most interesting things about doing this wrap-up was experiencing a huge variety of blog themes and fonts - a few of which actually made my eyes hurt!

A HUGE thank you to everyone that participated, T-SQL Tuesday doesn't work without you. I really enjoyed reading through everyone's posts and seeing the passion out there for the DBA role. As I've summarized the posts I've added my own comments too.

Here are the posts from all the participants in the order in which their links appeared in my blog post comments (or I dug out additional posts using Google).

Matt Whitfield (blog | @atlantis_uk): T-SQL Tuesday #12 - Why are DBA skills necessary?

Matt tells the story of himself as a database designer making a plethora of hard-to-remedy mistakes 9 years ago before he had DBA skills that would have helped him not make those mistakes. His take is that it's essential to have someone with DBA skills involved in system design as newbie DBAs or developers don't know the impact of design choices on SQL Server. I love this line from his post: "There is a whole world of epic-database-fail out there, and that world needs us."

Noel McKinney (blog | @NoelMcKinney): T-SQL Tuesday #012 – DBA Skill or Nil?

Noel takes a look at different RDBMS platforms and how the need for DBA skills could change - from Oracle to MySQL to the cloud to NoSQL. Even with automated solutions, he still thinks you wouldn't want to just apply all the default policies and walk away. I'd have to agree. 

Pinal Dave (blog | @PinalDave): SQL SERVER – Are you a Database Administrator or a Database Developer?

Pinal's short post focuses on how the line is often blurred between being a database administrator and a database developer. He has a survey in the post which is currently showing 60% of respondents believe they're more a dev than a DBA.

Audrey Hammonds (blog | @Datachix2): T-SQL Tuesday: Why are DBA Skills Necessary? – A Datachix Perspective

Audrey's post is about how some of the knowledge a DBA has is essential for a database developer to have too - but does that make you a DBA? No. I love her medical analogy: "See, asking me, a non-DBA, to attempt to be a DBA is like asking a psychiatrist to perform heart surgery". DBAs are essential to look after SQL Server.

Erik Bitemo (blog): T-SQL Tuesday #12 – Why are DBA skills necessary?

Erik's post explains some of the knowledge DBAs should have as a way of illustrating the problems that can occur if they don't. He also reminds us that one of the major causes of SQL Server being regarded as not requiring a DBA was the marketing around the 7.0/2000 time-frame that proclaimed how easy SQL Server was to use. IMHO that really hurt the image of SQL Server and SQL Server DBAs.

Rob Farley (blog | @rob_farley): Why bother with database professionals?

Rob's argument is that you don't necessarily need DBA skills - you need people who can apply the right paradigm to their work. For database work that paradigm is set based.

Aaron Bertrand (blog | @AaronBertrand): T-SQL Tuesday : Are hotshot DBA skills necessary?

Aaron (one of my favorite MVP friends) makes a great point that many small business *don't* need a full-time rockstar DBA and that many of the defaults will allow them to continue along happily. But at some point as the business grows and the data/transaction volume grows with it, you cross a line and then a DBA really *is* necessary. I totally agree. When I'm teaching about index fragmentation I always say that at the low end, rebuilding all your indexes every night is probably fine, but as things get larger and downtime becomes important, DBA skills are needed to take a more analytical approach.

Robert Hartskeerl (blog | @rhartskeerl): T-SQL Tuesday #12–Why are DBA skills necessary?

Robert explains how even though there are tools to assist with managing SQL Server, someone has to interpret what the tool is saying and no how to fix the problem. And the tools can be wrong - like the RESTORE bug in SSMS in SQL 20008. Robert also used my favorite phrase... "it depends!"

Erin Stellato (blog | @erinstellato): TSQL Tuesday #012: Why are DBA Skills Necessary?

Erin (my favorite stalker :) recounts some of the problems she's helped with on customer systems that affected business continuity. Her take is that if the data is really important, you need a DBA to make sure it doeesn't get irretrievably damaged or destroyed.

Grant Fritchey (blog | @GFritchey): TSQL Tuesday: Why Are DBA Skills Necessary

Grant (who scares me sometimes) tells some good stories and preaches the same mantra: at some point you need a DBA who knows how to protect and recover data, perform tuning etc. Even with NoSQL - you're still storing data and you still need to be able to restore it, get at it quickly, etc. Hear, hear.

Steve Jones (blog | @way0utwest): T-SQL Tuesday #12 – Why Do You Need a DBA?

Steve (who scares me most of the time with his pink cowboy hat) joins the chorus that DBAs aren't always required, but when they are they can save a company a lot of time and money and hence can be a really worthwhile investment.

Jes Borland (blog | @grrl_geek): T-SQL Tuesday #012: Why Are DBA Skills Necessary?

Jes explains why DBAs should market their skills as a problem-solvers and money-savers, and that a DBA isn't an IT function, it's a business need. Oh yes, and she says that we should all wear super-hero capes!

Bob Pusateri (blog | @SQLBob): T-SQL Tuesday #12: DBA Skills

Bob tells some stories from his DBA past but says that one of the most important DBA skills is to know when to ask others for help. He goes on to say that "presence of DBA skills can make a big difference between just getting something done, and doing something exceptionally well."

Mike Reigler (blog | @RMikeReigler): TSQL Tuesday #12 – I’m not a DBA, But I Got DBA Skills

Mike lists a bunch of SQL knowledge that most developers wouldn't know or care about, but makes the point that *someone* has to know and care about it, or people are going to get annoyed when performance tanks. He also explains how knowing some DBA skills sets him apart from other developers.

Mark Blakey (blog | @Blakmk): T-SQL Tuesday #12 – Why are DBA skills necessary?

Mark's the first poster to bring up the fact that developers and DBAs often have different priorities - with devs churning out code quickly while DBAs are trying to preserve resources.

Oscar Zamora (blog | @ZamoraO): Who needs DBA Skills? [#TSQL2sDay]

Oscar (who I just met at SQL PASS on Monday) lists 14 reasons why DBA skills are necessary and throws down the gauntlet by stating he thinks "most developers have no business in the DB world".

Tamera Clark (blog | @tameraclark): TSQL Tuesday – Why are DBA skills necessary?

Tamera explains how some DBA skills can help write better T-SQL as you'll know more about how SQL Server is handling the T-SQL you casually throw at it.

Jeremy Carter (blog): TSQL Tuesday – Why are dba skills necessary?

Jeremy explains that even with small databases, sometimes a DBA needs to be involved from the get-go, before the hardware is even provisioned.

Kerry Tyler (blog | @AirborneGeek): T-SQL Tuesday #12: Why are DBA Skills Necessary?

Kerry explains that databases are hard, and that it takes many different roles to look after a corporations data properly - the DBA being just one of them.

Andy Lohn (blog | @SQLQuill): T-SQL Tuesday #012 – Smart, Technologically Up-to-date, Well-Meaning Application Developers

Andy explains how he picked up DBA skills when he became a database developer and how useful they've been for his company.

Ricardo Leka (blog | @BigLeka): Por que habilidades de DBA são necessárias?

Portuguese? Google Translate to the rescue! Hmm - Google Translate didn't do such a good job, but I think the gist of Ricardo's post is that too many companies that think that SQL Server doesn't need a DBA.

Andrew Vogel (blog | @sqlreader): T-SQL Tuesday: Why Are DBA Skills Necessary?

Andrew makes a very interesting point that consultants know what's best about a product, but only you know what's best for your environment. Totally true - so many clients expect us (consultants) to provide the best answer for them straight away.

Wendy Pastrick (blog | @wendy_dance): Who Needs DBA Skillz?

Wendy (who I just met at SQL PASS on Monday) says anyone who works with a database needs some DBA skills. And if you don't have anyone with DBA skills, check out the #sqlhelp tag on Twitter. Hear hear!

Pat Wright (blog | @SqlAsylum): T-SQL Tuesday, Why are DBA skills necessary?

Pat (who I just met at SQL PASS on Monday) introduces me to an expansion of the DBA TLA I've never heard before: Default Blame Acceptor. While I've never heard it, I totally get it. DBAs are the unsung heroes of countless nasty situations caused by others.

Cade Roux (blog | @caderoux): T-SQL Tuesday: Why are DBA skills necessary? I pick the most important one...

Cade focuses on the skills a DBA has and explains why he thinks troubleshooting is the number-one skill he looks for in a DBA.

Tim Ford (blog | @sqlagentman): T-SQL Tuesday – Sometimes DBAs Are Required – Even for the Amish

Tim (who looks great in a Utilikilt) manages to unite Amish people and brothels in the same post. That takes a different kind of skill.

Geoff Hiten (blog | @SQLCraftsman): Why DBA skills are important (T-SQL Tuesday)

Geoff's short post tells the tale of someone attempting to use replication as a DR solution without understanding what replication does and does not provide. And that person was a 'DBA', but without DBA skills.

Jason Strate (blog | @StrateSQL): Why are DBA skills necessary? #TSQL2sDay

Jason gives some examples of why "what you don't know can hurt you" and then explains three critical things you should know if you're a DBA.

Jason Brimhall (blog | @sqlrnnr): T-SQL Tuesday #012 – Skills

Jason departs from the norm and discusses the NON-technical skills that a successful DBA needs to have. I particularly like the emphasis on a sense of community. I'm a huge community guy and I really like to see those that benefit from the SQL community eventually starting to give back in the form of things like blog posts, tweets, and user group presentations. People really ARE interested in what you've experienced as that will help them when they have the same problem.

Gill Rowley (blog | @SQLGill): T-SQL Tuesday #012: Why Are DBA Skills Necessary?

Gill tells a story of trying to get an IT department out of the dark ages and under control using his DBA skills.

Brent Ozar (blog | @BrentO): T-SQL Tuesday: Why Do You Need DBA Skills?

Brent (our very own Mr Wonderful) talks about when saying 'whoops' isn't code for "oh no, I dropped the database" and how real DBAs need to know enough to fix the junior DBA's "fixes"...

Matt Velic (blog | @mvelic): November’s T-SQL Tuesday: Importance of DBA Skills

Matt went as far as recording his T-SQL Tuesday message, describing how valuable DBA skills are.

Gethyn Ellis (blog | @SQLGRE): TSQL2SDAY #12 - Why are DBA Skills Necessary?

Gethyn explains some of the problems that can cause business continuity to suffer, all of which could be solved with backups, being taked by someone with DBA skills.

Jack Corbett (blog | @unclebiguns): T-SQL Tuesday–Why are DBA Skills Necessary?

Jack (whose Twitter alias I've been afraid to ask about...) tells a story if disaster and data loss that would have been prevented had someone with DBA skills been involved.

Chris Shaw (blog | @SQLShaw): T-SQL Tuesday

Chris gives some examples of why companies need databases today to stay competitive, and hence need DBAs to keep those databases healthy.

Sal Young (blog | @EmpiricalDataMa): We don’t need no stinking DBAs! (T-SQL Tuesday #12)

Sal describes some of the different DBA specialties there are, and how you need to be prepared to be dealt a hard knock by clueless executives bent on saving company money by laying off employees.

Robert L Davis (blog | @SQLSoldier): T-SQL Tuesday #012 – the DBA as a Modern-day Specialist

Robert (DBM book writer extraordinaire) tells a *really* interesting story of a high-school injury that nearly left him paralyzed because it was mis-diagnosed by GPs rather than a specialist - and draws the analogy to under-qualified people trying to diagnose problems affecting an important database.

Kimberly L Tripp (blog | @KimberlyLTripp): TSQL Tuesday - Why DBA skills are important

Kimberly (lovely wife) highlights a couple of areas where you can get bitten without DBA skills, and explains why *someone* in the organization has to have DBA skills because SQL Server is a *general purpose* RDBMS, and so it needs to be tweaked to work best for your environment.

Kendra Little (blog | @Kendra_Little): TSQL Tuesday #12: Why Are DBA Skills Necessary? Fido, Please Turn Your Head And Cough.

Kendra draws a very clever analogy between DBAs and veterinarians, saying that both look after birth, death, growth, prevention of illness, and so on.

Jeremiah Peschka (blog | @PeschkaJ): T-SQL Tuesday – Why Are DBA Skills Necessary?

Jeremiah (who has quite amazing tattoos) says that DBA skills aren't necessary, but instead the ability to learn is what sets the successful apart from the unsuccessful.

Josh Feierman (blog | @awanderingmind): Who needs a DBA? – TSqlTuesday #12

Josh recalls how he started out as an involuntary DBA, and thinks about all the mistakes he made that a real DBA would have picked up on. One thing I think we sometimes lose sight of is that *everyone* started with zero SQL Server knowledge...

Josef Richberg (blog | @sqlrunner): T-SQL Tuesday 12: Why are DBA skills necessary?

Josef laments that developers are seen as THE all-knowing entities, which can lead unsuitable designs making it into production and causing problems - which then leads to angst about the database platform which fuels the NoSQL movement.

Jonathan Kehayias (blog | @SQLSarg): T-SQL Tuesday #12 - Why are DBA skills necessary?

Jonathan (extended events expert) uses some examples from his work to illustrate some of the problems facing companies without DBAs, and explains how it can take time for a new DBA to earn the respect of the incumbent IT staff.

Cameron Mergel (blog | @CameronMergel): T-SQL Tuesday – Why Are DBA Skills Necessary?

Cameron reiterates that as the operations you want to do on your database become more advanced, you need someone with higher skills - just like there's a point at which you stop doing maintenance on your car yourself and trust it to a mechanic.

Kelly Martinez (blog | @GreeleyGeek): Why are DBA skills necessary? #TSQL2sDay

Kelly talks about some of the things a DBA should know, but the most interesting point in his post is about how some vendors seem to have no clue about the database they're running on.

Sean Gallardy (blog | @SeanGallardy): T-SQL Tuesday – Why are DBA skills necessary?

Last but not least, Sean makes the very good point that DBA skills are really necessary when searching for problem solutions on the Internet. Without DBA skills, how do you tell if the advice/solution you're reading is correct and appropriate?

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!

Blog posts in this series:

• For the hardware setup I'm using, see this post.
• For an explanation of log growth and its effect on perf, see this post.
• For the baseline performance measurements for this benchmark, see this post.
• For the increasing performance through log file IO optimization, see this post.
• For the increasing performance through separation of data and log files, see this post.
• For my first (mostly unsuccessful) crack at network optimization, see this post.

The saga continues. Like a bad Star Wars prequel. But with better special effects and more believable dialog.

In the previous post in the series I messed around the network configurations - well, really I flailed a bit as it's not my specialty - and you can't run DBCC CHECKNETWORK with a REPAIR_MAKE_IT_FASTER switch.

The net effect of my flailing was no real increase in my benchmark performance. I managed to shave off a few hundred seconds from a 5.5 hour run-time. The best I could get for generating my 1-TB clustered index was 20317 seconds with the following being a picture of typical network utilization:

There's clearly a bottleneck there when the iSCSI NIC to the data array hits 100% utilization - when a checkpoint is occurring. I resolved myself to figuring this out.

Well, it turns out that in the Dell Modular Storage Manager, the iSCSI host port configuration window has a SAVE button that's off the bottom of my screen. So when I thought I'd enabled jumbo frames, I actually hadn't.

So, in these tests I made sure to save the configuration so that it actually applied. And, funnily enough, I saw some better performance. Who'd have thought, eh?

All these tests were with an unchanging log file size/auto-growth and data file size/auto-growth, as documented at the top of the previous post, so I won't repeat it here.

1) Enabling 4088-byte jumbo frames on the Intel server NIC to the data array

The Intel PRO/1000 PT Dual Port GigE NICs can handle 4088 or 9014 byte jumbo frames, and the iSCSI host ports on the data file MD3000i can only go up to 9000 bytes, so 4088 it is. The test ran in 18623 seconds, an 8.4% improvement over the baseline.

2) Enabling 9000-byte jumbo frames on the BroadCom server NIC to the log array

The BroadCom BCM5708C NetXtreme II GigE NICs can use a manually specified jumbo frame size so I set it to 9000 bytes, as well as the iSCSI host ports on the log file MD3000i. The test ran in 18284 seconds, a 1.8% improvement over the configuration in #1 above, and an overall 10% improvement over the baseline.

3) Using two Intel server NICs to the data array with least-queue-depth

Even with the jumbo frames enabled, I was still saturating the network link to the data array so I decided to use a pair of NICs cooperating on the same iSCSI channel, as several people had suggested. I configured two Intel NICs with 4088-byte jumbo frames, set them up as a paired set of connections to the same iSCSI controller (using two host ports on the same controller, so the NICs are essentially one-to-one), and let rip. The test ran in 18178 seconds, a 0.6% improvement over the configuration in #2 above, and an overall 10.5% improvement over the baseline.

4) Using two Intel server NICs to the data array with round-robin

I then changed the algorithm for the two Intel NICs to use round-robin instead of least-queue-depth. The test ran in 17719 seconds, a 2.5% improvement over the configuration in #3 above, and an overall 12.8% improvement over the baseline. You can see that in the perfmon capture lower down this post.

Clearly using paired-NICs with jumbo frames is the way to go. I also measured the throughput on the iSCSI arrays, and the 8-drive 15k RAID-10 SCSI array was pushing just under 125000 bytes per second - a 14% improvement over the best previous measurement.

My network utilization pretty-picture is as shown below:

Connections 1 and 2 are the Intel NICs going to the data file array and connection 3 is the BroadCom NIC going to the log file array. This screen shot is capturing the start of a performance run as the log gradually increases in size. The network spikes on connections 1 and 2 are when data pages are being flushed out during checkpoints. You can see that the checkpoint get longer and further apart as the log file size increases. I explain this in depth in the post Interesting case of watching log file growth during a perf test. But as you can clearly see - no more network bottleneck!

And here's a perfmon capture from the middle of one of the tests with paired-NICs:

Points to note:

• The Pages Allocated/sec (black highlighted line) is averaging a much higher number than before, with spikes up to almost 9000.
• The Disk Write Bytes/sec for the K: (the green line at the top) is off the scale, hitting near to the maximum capacity of the array as I described above.
• The Avg. Disk Write Queue Length for K: (the browny-red line) doesn't get much over 20, whereas previously it had been spiking in the mid 30s.

Now that I've removed the network bottleneck I'm going to experiment with the log file initial size and autogrowth rate, and then the data portion of the database.

One thing to bear in mind is that I'm tuning this for the optimal performance for the one task I'm trying to achieve right now. It'll be interesting to see whether this is the optimal configuration for large-scale reads, mixed workloads, index rebuilds, etc etc, and I'm also going to start throwing the Fusion-IO drives into the mix.

Hope you're enjoying learning along with me, stay tuned!

Blog posts in this series:

• For the hardware setup I'm using, see this post.
• For an explanation of log growth and its effect on perf, see this post.
• For the baseline performance measurements for this benchmark, see this post.
• For the increasing performance through log file IO optimization, see this post.
• For the increasing performance through separation of data and log files, see this post.

In the previous post in the series, I examined the effects of separating the data and log files (one file each) to different RAID arrays. It was very obvious that separation gave a performance boost, and that having the portion of the database with the highest I/O write throughput requirements on the faster array (the 8-drive 15k SCSI RAID10) produced the biggest gain.

Now - a confession.  In the last post, when I posted it I found that moving the data an 8-drive 7.2k SATA RAID10 array was the best approach. *But* during the testing for this post, I found that one of my tests had screwed up and only half the client threads had run. You'll notice in that post I went back in and edited it to explain that and update the graph and results. I've now augmented my test harness with a way to check that all client threads are running - to make sure the tests are apples to apples, rather than apples to pomegranates :-)

So - the best I've been able to do so far with the tests is creating 1TB of data using 128 connections (each creating 1/128th TB using inserts with default values) with the single data file on an 8-drive 15k SCSI RAID array (pre-created to 1TB) and the log file on an 8-drive 7.2k SATA RAID10 array (pre-created to 256MB with 50MB autogrowth) in 20842 seconds.

Lots of people have been asking how my network is setup in these tests. Here's what I've been running with (all 1GB ethernet):

• 1 NIC from a Broadcom BCM5708C NetXtreme II GigE card on the 10.x.x.x network
• 1 NIC from a Broadcom BCM5708C NetXtreme II GigE card on the 192.168.x.x network
• 2 x PowerConnect 5424 24-port iSCSI optimized switches , with no separation of traffic
• The 10.x.x.x server NIC connected to all iSCSI arrays

Over the last couple of weeks I've been playing around with the network setup to make sure things are optimized, and this post will describe what I did and what effect it had. In all the tests below, I kept the dat aon the faster SCSI array and the log on the slower SATA array.

I'm very grateful to the help I received from Wes Brown (twitter|blog) and Denny Cherry (twitter|blog) to the technical questions and WTF?s I sent (and to anyone else on twitter I may have forgotten!).

1) Separation of network traffic

I decided to make one of the 5424 switches dedicated to iSCSI traffic on the 10.x.x.x network and the other for general network activity, including connecting to the management ports on the MD3000s. Turns out that I didn't really need to, as each 5424 can handle 48GB of throughput, way more than I'm generating. But hey ho, at least the wiring in the back of the 42U rack is a little tidier now :-)

Running the 128-way test with the new configuration gave a test time of 21252 seconds, slightly slower than the best time without separation! This was the first of the WTF?s. Until I realized that I hadn't actually removed any network bottleneck at all. I can't explain why things are slightly slower here, so I decided to take the switches out of the equation. My suspicion is that if I ran the test ten times, I'd get ten different results, but within a standard deviation of the median. So - no cause for concern. (In fact, I'm going to try this as part of the next set of tests.)

2) Direct connections to the iSCSI arrays

I configured another NIC (one from an Intel PRO/1000 PT Dual Port GigE card) and then had one NIC directly connected to one of the RAID controllers on the SCSI MD3000 (only one configured volume, so no concerns about having multiple volumes suddenly switching over to a non-optimal RAID controller) and the other NIC directly connected to the SATA MD3000.

Running the 128-way test with the new configuration gave a test time of 21859 seconds, slower than test #1. Very surprising - I expected to get some *gain* so I looked at the peak throughput of the arrays:

• For test 1, peak SATA was 50500 bytes/sec and peak SCSI was 106012 bytes/sec.
• For test 2, peak SATA was 46923 bytes/sec and peak SCSI was 107708 bytes/sec.

Things are slower with the network bottleneck removed.

3) Upgrading 5424 switch firmware and reconfiguring

Although the 5424 switches are supposed to come iSCSI optimized, I thought I'd flatten them and reconfigure the. I got the latest version of the switch firmware and re-flashed both switches. I think configured the 10.x.x.x one specifically for iSCSI using this excellent Dell whitepaper.

Running the 128-way test with the new configuration gave a test time of 20745 seconds. Finally an improvement, but nothing major, and still possibly just a statistical variation.

4) Upgrading the NIC drivers

Next I figured I'd bring the NICs up to the latest driver versions so upgraded all the NICs on all the servers.

Running the 128-way test with the new configuration gave a test time of 21743 seconds. Hmmm.

5) Homogenizing the network paths

At this point I started wondering if the Broadcom and Intel NICs had different characteristics so I decided to use the two Intel NICs for the iSCSI traffic. I also enabled jumbo frames. The Intel NICs have three setting for jumbo frames - off, 4088 bytes or 9014 bytes. The MD3000s can only go up to 9000 bytes, so I chose 4088 bytes and configured the MD3000 iSCSI ports to use the same.

Running the 128-way test with the new configuration gave a test time of 21526 seconds - nothing to write home about.

None of the network configuration changes I made had much effect on performance, apart from removing the network bottleneck, which made performance slightly worse overall. I checked other stuff like TCP offloading, but that wasn't enabled. My suspicion was that by removing the network bottleneck, I unmasked a SQL Server contention issue with my 128-connection test. I decided to try fewer client connections.

Here are the results:

There's clearly a SQL Server bottleneck that's being alleviated by reducing the number of connections and allowing the throughput to each array to increase slightly. With 8 connections, SQL Server isn't being driven hard enough and the elapsed time increases again, and this is reflected in the array throughput measurements too (a 10-15% drop compared to the 16-way test). One thing I forgot to do was examine the distribution of wait types while these tests were running, but my guess would be the bottleneck was in the transaction log manager.

Summary

By separating the network traffic and moving to two iSCSI NICs, I removed the network bottleneck I had (see the image at the bottom of the last post) and replaced it with a SQL Server bottleneck. Here's a snapshot of network utilization with the new setup:

In the next set of tests, I'm going to look at the effect of altering the transaction log auto-growth size, and pre-allocation size. In all the tests so far, the log has grown from the pre-allocated 256MB to somewhere between 6.5-8GB.

Should be interesting - stay tuned.

PS Yes, I'll be doing a bunch of stuff with the Fusion-io drives too - be patient! :-)

Christmas comes but once a year... really? Then mine just came early on this afternoon's UPS truck.

The very nice folks at Fusion-io just sent me two of their fully-loaded top-of-the-line ioDrive Duos with 640GB of solid-state flash memory in each. This is really extra-nice of them because on Dell's Small Business website they're currently retailing for $12800 *each*. Expensive? Yes. Worth it? That's what I'm hoping to prove.

There's nothing like expensive, pretty hardware to get me excited... here's what they look like:

Now, above I said 'expensive', and these are, but they pack some pretty amazing specs in terms of read/write bandwidth so you're getting a lot of bangs for you bucks. But where does it really make sense to drop the bucks for the biggest bangs? To answer that I'm planning to do a whole series of blog posts as part of my benchmarking efforts to investigate which operations can benefit the most from these drives. With 1.2TB of SSD storage I'll be able to plug these into one of my test systems here and run comparisons against 15k SCSI and 7.2k SATA drives.

Anyway, there's a lot of hype about the speed of SSDs, and also a lot of angst about SSDs not being Enterprise-ready. I don't agree with them not being Enterprise-ready - in fact, fellow-MVP Greg Linwood, who runs (among other things) our partner company SQLskills Australia, already has a bunch of customers with Fusion-io drives deployed in their enterprises successfully. As with any critical hardware infrastructure (especially cutting-edge stuff like this), the key to success is having everything setup correctly so I'll be blogging about all my experiences with them.

To summarize, I'm very excited! I've been wanting to get my hands on some serious SSD hardware for a couple of years now so I can do some *real* testing - it doesn't get better than this!

Shoot me an email or leave a comment if there's something you're interested in seeing tested.

PS Full disclosure: yes, of course Fusion-io sent me these because they're getting publicity from me blogging about them, but we don't have any editorial/veto agreement. I want to be able to recommend these to our enterprise clients and the only way to honestly do that is to play with them myself - so it's a win-win for both of us. And you guys get to test them vicariously through me, so it's a win-win for you too :-)

Stay tuned...

I've been spending some time reorganizing my electronic components and came across a serious blast from the past...

(Click the image for a larger version - 354Kb)

Left to right, from top down:

• Zilog Z80 (popular in the Sinclair ZX computers)
• Intel 8085 from 1980
• AMD 8080 from 1977
• Intel 8086 from 1978
• Intel 8080 from 1979
• Intel 8088 from 1981
• Motorola 68000 (from the Atari ST and Commodore Amiga)

I guess I've kept these for nostalgia - I've got a bunch of 6502s but they're still in the computers. Checkout the Online Computer Museum (kind of slow) for a wonderful archive of nearly all old computers.

Enjoy!

I was reading a thread on SQL Server Central today where someone replied to a question and confused 823 with 832. Now, 823 is a nasty error to get - it says that an I/O operation failed at the OS level and the I/O subsystem is causing corruption - SQL Server didn't even get a chance to check the page checksum (and potentially raise an 824 error).

Error 832 is way worse. When you get one, you'll see an error like the following:

A page that should have been constant has changed (expected checksum: 1dcb28a7, actual checksum: 68c626bb, database 13, file 'E:\Program Files\microsoft sql server\MSSQL\data\BlahBlah.mdf', page (1:112644)). This usually indicates a memory failure or other hardware or OS corruption. 

This error occurs *AFTER* a page has been read into memory. Once the page passes all checks as it's read in from disk, it's a known clean page sitting in the buffer pool. When the time comes to update the page and mark it as a dirty page, the checksum is checked again, juuuust to make sure. If the checksum is no longer valid, error 832 is raised. This is saying that something apart from SQL Server stomped on the page while it was in SQL Server's memory - either hardware memory corruption (bad RAM), an OS memory-management bug, or a rogue process writing into SQL Server's memory space.

It's really bad. If memory diagnostics don't show up any issues, there's a way to start to track down what's going on, but only by enabling an expensive trace-flag at server startup that prevents untoward accesses to SQL Server's memory unless an exclusive page latch is held. And this should only be enabled under Product Support's supervision.

So - hopefully you'll never see this - they're very rare - I've only seen a handful in my time. If you do, run memory diagnostics, and if nothing shows up, call PSS to help you out.

Hope this helps.

(and yes, open invitation to send me any electronics/boards/drives to try if you work for a hardware manufacturer, I'll blog about it - address on our Contact page :-)

Part of being a Microsoft Regional Director is getting early access to information and private releases to play with. They also hand out free stuff occasionally. Yesterday Kimberly and I reach received a Windows 7 Sensor development board kit (and as Kimberly doesn't tinker with electronics, I effectively got two!) - these are the same ones that were being given out at PDC apparently.

The board looks pretty neat:

It has an ambient light sensor, a 3D accelerometer, and two touch-sensitive strips on. If you're interested you can buy these from FreeScale for $50 - see here (I have no affiliation with them at all). There's a slide deck from WinHEC that Gavin Gear presented that explains the new Sensor and Location Platform in Win7 - see here.

I'm looking forward to playing with mine and I'll blog about what I do with it.

PS You can get the latest Windows API Code Pack here.

Last week's survey was another two-fold one - when you buy new servers, what architecture to you predominantly buy, and why?; when you buy new servers, which Edition of SQL Server do you predominantly buy, and why? Here are the results as of 5/17/2009.

For the first survey, the 'other' values were basically that 64-bit is purchased only if required. For the second survey, nine of the 'other' values were basically that Enterprise Edition is purchased only if required, one person uses Web Edition, and one person uses Developer Edition as they're just a developer.

I don't have a huge amount to say about these two surveys, I really just wanted to confirm my gut feel (and give you all a view of what's happening out there in the field).

For the architecture survey, I was entirely unsurprised to see that almost 80% of respondents are using 64-bit. It's widely known that 64-bit can give you better performance through the availability of more memory for SQL Server to use. Although you can use more than 4GB using AWE on 32-bit, that extra memory is only available for the buffer pool to use - not for general query processing - and AWE access does incur a little overhead. Much has been written about this and I'm not going to duplicate it here. A selection of articles can easily be found at http://www.google.com/search?hl=en&q=sql+server+64+bit+vs+32+bit&aq=1&oq=sql+server+64  More interesting is the small number of respondents who are not able to use 64-bit, either because their corporate policy is 32-bit (maybe because of cost?), they have software that doesn't run on 64-bit systems, or specifically because 64-bit is too expensive.

A few people only use 64-bit if required, and (I imagine) prefer to save money if it not by sticking with 32-bit. I wonder how long it will be until 32-bit servers are not available at all? Certainly future versions of Microsoft server software are starting to become 64-bit only - for instance Exchange 2010 (see here) and SharePoint Server 2010 (see here), will the next version of SQL Server be 64-bit only?

For the Edition survey, again, unsurprising results. 55% use Enterprise Edition for one or a combination of the various "-abilities" (if you make performancability a new word), with almost 10% more having the option to use it if required, and 16% having to stay with Standard because of budgetary constraints. What you may be surprised to see (but I wasn't from my time working side-by-side with the SQL marketing team) is that 20% of respondents don't need Enterprise Edition. There were a lot of improvements to the database engine in SQL Server 2000 and SQL Server 2005 that meant that for intermediate workloads (hmm - just made that up, but you know what I'm trying to say), Enterprise Edition isn't needed. And with synchronous database mirroring available in Standard Edition, you can implement a great, low-cost high-availability plan without paying for Enterprise. As you can see, I don't get any kickbacks from the SQL team for selling Enterprise Edition  Saying that, however, there are a lot of very cool features in Enterprise Edition in the various versions - but if you don't *need* them, why pay for the higher Edition just for the sake of it?

One thing I will say to summarize, it's important to look at your requirements in terms of performance, availability, etc to make the right choice of server architecture and SQL Server Edition. If you make the wrong choice, do you think your company will pay to rectify your mistake before the next round of capital-expenditure? Probably not - but you'll definitely pay for the wrong choice with several years worth of hassle trying to make the system be more performant and easily recoverable than your choices allow.

As always, thanks for participating in the surveys - I've had a bunch of mail from people who like to see what other people are doing.

Next up - this week's survey!

I've just been setting up some of our new hardware, and wanted to do some background reading to ensure I use the correct disk partition offset, RAID stripe size, and NTFS allocation unit size to enable the best possible performance for the volumes I'm creating.

You may not of heard about this (or your disk admins may not have heard about this) but on Windows Server 2003 and before, the default partition offset typically causes worse-than-optimal performance - and correcting it can get gains of maybe as high as 30% in terms of IO latency and duration. The SQLCAT team have just published a *fantastic* whitepaper (written by Jimmy May and Denny Lee) which explains the issue simply and clearly and shows you how to correct it. You should checkout the whitepaper at Disk Partition Alignment Best Practices for SQL Server.

The summary is that on Windows Server 2003 and before, the default partition offset is 31.5KB (63 x 512byte disk sectors), which does not align nicely with the common RAID stripe sizes of 64K or 128K, or the optimal NTFS allocation unit size of 64KB. This can lead to having to read/write multiple stripes every so often and a big perf drop. It can be fixed, as detailed in the whitepaper. For volumes *created* on Windows Server 2008, the problem does not exist as it creates a default partition offset of 1024KB - although see the edit below...

In fact Jimmy just published a blog post to help you make the case to your disk admins/customers a few days ago: Disk Partition Alignment (Sector Alignment): Make the Case: Save Hundreds of Thousands of Dollars.

Luckily I'm using Windows Server 2008, which correctly sets the disk partition for the vast majority of cases.

[Edit 06/30/2011: Beware! Even with Windows Server 2008, it's been reported that some I/O subsystem vendors intercept what Windows is trying to do and *still* create partitions with the incorrect offset. Create them and then check - just to be sure.]

Next thing I considered was RAID stripe size and NTFS allocation unit size (previously known as 'cluster size'). Kendal Van Dyke just published an *excellent* blog post series that provides a lot of empirical evidence as to what the best numbers are for the RAID level you're using. This saved me a lot of time. Check out his series at Disk Performance Hands On Series.

The Dell MD3000i units I'm using don't go any lower than 128KB for a RAID stripe size, so the default is fine. Unfortunately, I forgot to set the NTFS allocation unit size to 64KB when creating the partitions in Windows, so I need to recreate the partitions.

A massive thank-you to these guys for saving me a lot of time and hassle. You should go read this stuff too.

At the last few conferences I've presented at, there have been questions about using SSDs (Solid-State Drives) for enterprise storage and whether that will change some of the database maintenance practices. My answer to that is "I don't know" (ha - bet you thought I was going to say "It depends!") because adoption of SSDs is very low. I haven't been able to find much info about using them, but the Microsoft Research group in Cambridge just published a research paper Migrating Sever Storage to SSDs: Analysis of Tradeoffs, which does a nice job of walking through the issues involved and concludes that for the majority of workloads, it makes more economic sense to host them on HDDs. The exception is for top-end OLTP databases. I'll warn you that this isn't a whitepaper - it's a research paper, and gets a bit deep into algorithms and mathematical analyses, but if you're up to the challenge it's a great read.

You can download the paper from http://research.microsoft.com/en-us/um/people/antr/ms/ssd.pdf. Enjoy!

PS I found it on James Hamilton's blog.