Kendal Van Dyke

The Best Thing I Learned At PASS

noreply@blogger.com (Kendal Van Dyke) — Wed, 01 Jul 2009 23:00:00 +0000

Note: I'm writing this as part of the "Best Thing I Learned At PASS" contest. Why? Because if I win it's worth a free registration or hotel stay for the 2009 Summit, and I like free!

Let me start by making a small confession: I've never actually been to the PASS summit in person. So how in the world can I write about what I've learned? Because I've been there vicariously through the blogs and Twitter streams of people who were there last year. Thank goodness, because before the rise of blogging\social networking if you had asked me what happens at the PASS summit I would have said…I honestly don’t know what happens at PASS besides a bunch of technical presentations given by people who write books and magazine articles for a living. My ignorance about what PASS is became clear to me last November as I read about the daily keynotes, the community sessions, and the nighttime parties. I came to realize that PASS is about so much more than just sitting in technical presentations. It’s about learning from people just like you who are doing the same things as you do every day. It’s about uniting with your peers and meeting new people who share a common interest. It’s about escaping the daily workload to reenergize and reinvigorate the passion to do our jobs as best as we possibly can when we return. So even though I haven’t been to PASS in person, I’ve learned one important thing: For the sake of my career, I need to go to PASS. If I can figure that out without actually having been to PASS, just imagine what I’ll learn when I do go this year.

See You In Seattle For PASS 2009!

noreply@blogger.com (Kendal Van Dyke) — Mon, 15 Jun 2009 19:30:00 +0000

Last Friday I got the official notice that I've been selected to present a community session at the 2009 PASS summit in Seattle. I've also had 2 other abstracts selected as alternates. Ecstatic doesn't even begin to describe how I feel right now. I've never been to the summit before and now not only do I get to go but I get the chance to talk on a national stage about something I love doing. Very, very cool!

Here's what my session is about:

Transactional Replication: Beyond The Basics
At some point in every DBA's career they'll be probably be asked to work with transactional replication but most DBAs don't look under the covers once they're done walking through the setup wizards. In this session we'll take a deeper look at setting up, monitoring, and calibrating transactional publications plus share some tricks and tips gleaned from years of experience working with high volume, multiple datacenter topologies.

Session Goals:

Understand core concepts of transactional replication including: replication topologies, publishers, distributors, agents, & agent profiles. Learn how to configure advanced options such as vertical and horizontal filtering
Demonstrate how to monitor transactional replication performance
Learn advanced troubleshooting techniques to apply when problems occur

Speaking At Space Coast SQL Users Group On June 11

noreply@blogger.com (Kendal Van Dyke) — Tue, 09 Jun 2009 18:45:00 +0000

Just a quick note to mention that I'm speaking at the next Space Coast SQL Users Group on Thursday, June 11, 2009. I'll be presenting Useful T-SQL Statements You May Not Be Aware Of and I'll cover identity functions, OUTPUT, PIVOT\UNPIVOT, MERGE, and statements like OVER, ROW_NUMBER, etc. The meeting starts at 6:30 PM and is held in the Odyssey Conference room of Space Coast Credit Union headquarters at 8045 N. Wickham RD, Melbourne, FL 32940. Refreshments and snacks are provided some come out for an night of free SQL education and networking!

Inside A Datacenter

noreply@blogger.com (Kendal Van Dyke) — Wed, 20 May 2009 12:00:00 +0000

I spent the last 4 days in Denver helping my company move in a new datacenter and while I was there it occurred to me that a lot of people have probably never seen the inside of a datacenter. I've been in 8 (that I can recall), and while that might not seem like a lot to seasoned hardware folks that's enough to get a feel for what makes these places tick (and understand the difference between a good facility and a great one).

Whenever I visit a facility I take a few minutes to walk through the aisles of racks and look at what hardware other people are running. I bet people probably think that datacenters are full of high end servers and SANs, but I've found that it's exactly the opposite. At the 3 facilities I visited on this trip I saw only a handful of SANs, maybe 1 per 20-30 racks. On the other hand, 9 out of 10 racks had at least one 1U server, 3 out of 4 racks had at least one 2U server, and 1 out of every 3 racks had at least one DASD drive enclosure. Regular readers of my blog may recall that I've done a lot of testing on drive performance for this kind of equipment. There are thousands of these servers in a medium sized datacenter and given the number of people I've talked to who haven't heard about disk alignment I'm guessing that adds up to a lot of wasted performance.

I also like to look at how people install their hardware in racks. Some installations are messy – cables strung about like spaghetti, no thought given to heat dissipation, etc.. A few make you stop and look twice because they're so pristine. On average I'd say that most installs are OK – not bad and done well enough to keep things running cool. There's definitely an art to installing servers in a rack.

If you end up spending any more than an hour working in a datacenter there are a few things to keep in mind. First, datacenters are climate controlled; they are cool and dry inside. Make sure you bring ChapStick to keep your lips from getting chapped and lotion to keep your hands from getting too dry. You should make sure to stay hydrated; you can't bring liquids into the facility so you'll want to remember to take frequent breaks to walk outside and get a drink of water. As I said, there are thousands of servers in a datacenter. It's loud. Wearing earplugs or headphones won't hurt. Walk outside if you need to hold extended conversations (especially on the phone – the person on the other end will appreciate it!). You don't feel it while you're working inside, but the conditions can get to you. Between the dry air, lack of water, and near-shouting to talk to my coworker who was with me I just about lost my voice after day 2.

If you're interested in seeing a datacenter in person you can talk to your hardware\network admins and ask them to give you a tour; If you have even a mild interest in computers you'll probably enjoy the experience. You can always take a virtual tour too - the video below is from FORTRUST, one many facilities in Denver, and will give you a good idea of what's involved in running a datacenter. There are a lot more videos on YouTube of other facilities – just search for "Data Center Tour" (or save yourself the effort and just click here).

SQL Saturday Jacksonville Recap

noreply@blogger.com (Kendal Van Dyke) — Tue, 12 May 2009 22:15:00 +0000

Note: I intended to post this last week but life\work\burnout and installing Windows 7 RC got in the way.

Another weekend, another SQL Saturday – this time in Jacksonville, put on by JSSUG, at the University of North Florida campus. This particular SQLSaturday was scaled back from the norm (by design) – one sponsor, no attendee SWAG bags, hand written name tags, etc.. While SWAG is nice, the lack thereof didn't seem to bother people; can you really complain about free breakfast, lunch, and a day of SQL training?

Even though I was scheduled to give 4 presentations – more than enough to keep me busy - I came into the day with the goal of meeting new people. Brian Knight's crew had the same idea and began the day by challenging each attendee to meet 3 new people. I asked in one of my afternoon sessions if everyone had met their 3 people and almost all hands went up; when I asked if they had gotten contact information (e.g. business cards) very few hands stayed raised. I'm not too surprised since people spend most of their day going back and forth between sessions which leaves little time to strike up meaningful conversations. On the other hand, the after-party was a great chance to relax and talk in a more casual atmosphere. I ended up talking (and exchanging business cards) with several people I met earlier, so I'll call success on my goal for the day.

The only hiccup for me on the day was that I had to bail on one of my presentations (IIS security) because my demos weren't working. I ended up giving a 15 minute verbal summary and then gave my presentation on useful T-SQL statements. Otherwise I really enjoyed the opportunity to present, catch up with old friends, and meet some new ones. All in all it was another great SQL Saturday!

Slide decks and demos for my presentations are below:

Download: Disk Performance and Configuration How & Why.pptx
Download: Useful T-SQL statements you may not be aware of.zip
Download: Transactional Replication Deep Dive.pptx
Download: XML Features in SQL2005 - SQLSat 4.zip

SQL Saturday Atlanta Recap

noreply@blogger.com (Kendal Van Dyke) — Wed, 29 Apr 2009 16:30:00 +0000

I had a great time at SQL Saturday Atlanta last weekend. I drove from Orlando to Atlanta on Friday with Andy Warren and took advantage of the long ride to talk about PASS, user groups, volunteering, blogging, networking etc. Andy's a great guy to talk shop with because of the experience and perspective that he has on things. We arrived in time for the speaker's dinner Friday night where we had the chance to meet up with Stuart Ainsworth (@stuarta), Robert Cain (@arcanecode), Louis Davidson (@drsql), Geoff Hiten (@sqlcraftsman), Paul Waters (@pswaters), Joe Celko, and many others. After dinner we headed to another local spot for drinks with Peter Shire (@peter_shire) and the SQL Sentry crew to finish out the day.

Saturday's event was held at Microsoft in Alpharetta, GA. I thought the facility was perfect for the event: Plenty of parking right outside the doors, open spaces to mingle, a comfortable speaker's room, and rooms set up with projectors, lighting, and hardware designed for presentations. There were 3 smaller rooms for roundtable discussions and 3 larger rooms with walls that retracted to form one large room that was used for the opening comments, lunchtime presentation by Joe Celko, and closing raffles.

I spent most of my morning in the speaker's room preparing for two afternoon presentations and had the chance to meet up with K. Brian Kelley (@kbriankelley) and Kevin Kline (@kekline). I had a bit of a hiccup before my first session (XML in SQL Server) when my user profile on my laptop went corrupt and I couldn't log in. I lost about 10 minutes recovering but I still managed to cover the most important points. Fortunately my second session (Disk Performance) was all slides and I didn't need to jump through any hoops so it went better. I finished out the day by meeting up with Stuart and a couple of other attendees for dinner at an ale house a short distance from the event.

Other random thoughts about the weekend:

Stuart and the AtlantaMDF crew did a fantastic job putting on the event. I've been to several SQL Saturdays and this ranks near the top in terms of location and execution.
I bought and used a Logitech 2.5GHz Cordless Presenter based on Steve Jones's recommendation and really liked it. The built in timer and laser pointer are very handy. The forward\back buttons are great if your presentation is mostly slides but otherwise useless if you're doing a lot of demos because you tend to be behind a keyboard pushing buttons already.
SQLBatman's influence was ever-present (see picture on right)
I need to work on my networking skills. While I managed to meet up with several people I've talked to on Twitter I didn't do such a good job of meeting someone I've never talked to before.
It's a long drive from ORL to ATL. While the conversation with Andy was great, I'll probably consider flying next time.
Presentation tip: learn how to keep an audience captive in the event that something goes horribly wrong with your presentation. I did my best to interact with people while I worked out my corrupt user profile issue but it would have helped to have some discussion points in mind ahead of time.

3 days, 1,000+ miles, 20+ hours of driving, and good times: All in all a great weekend. Next up: SQL Saturday #15 in Jacksonville on May 2 where I'll be presenting all 4 topics I submitted for the 2009 PASS Summit.

Slide decks and demos for my presentations from ATL are below.

Download: Disk Performance and Configuration How & Why.pptx
Download: XML Features in SQL2005.zip

PASS Summit Sessions Submitted

noreply@blogger.com (Kendal Van Dyke) — Fri, 24 Apr 2009 02:45:00 +0000

It's official – I've submitted my abstracts for the 2009 PASS Summit and received my confirmation. Now I wait to see if the abstract selection committee decides if I am worthy. As I promised in my interview with Brent Ozar, I'm publishing my submissions for all to see. If you happen to be at SQL Saturday Atlanta this weekend you can catch my presentations on Disk Performance and XML. If you're headed to SQL Saturday Jacksonville on May 2 I'm on the schedule to present all four.

Disk Performance and Configuration: How & Why
Would you buy a million dollar race car then show up to the track on a clear day with rain tires that aren't properly inflated and wonder why you're only doing 180 when the manufacturer says it can do 220? Let's suppose that your million dollar race car is really your DB server and your tires are your hard drives. Do you know the right configuration to use to get the best performance out of them? This session will examine the results of extensive testing to reveal how partition offset, RAID stripe size, allocation unit size, controller caching, and failed disks affect OLTP performance on local disks and disk arrays.

Session Goals:

Identify optimal RAID stripe size, allocation unit size, and partition offset to use for OLTP data and log files
Understand how failed drives impact OLTP performance
Understand how controller caching impacts OLTP performance

<title>Working With XML in SQL Server</title>
XML in SQL 2000 wasn't very exciting and it was easy to dismiss it altogether by just relying on developers to handle any XML needed within the application. SQL 2005 changes the game and this session will show you how to use the XML data type, do basic operations on XML data, and showcase the changes to FOR XML that make it so much more useful. This isn't a session to convince you to use XML, but to show you what you can do with XML if you do need to use it!

Session Goals:

Learn how to use the FOR XML PATH and WITH XMLNAMESPACES clauses
Understand the use of the XML data type, XML schema collections, how to use XML DML to manipulate XML data, and how to use FLWOR to query XML data
Learn how to create indexes on XML data

Session Goals:

Understand core concepts of transactional replication including: replication topologies, publishers, distributors, agents, & agent profiles. Learn how to configure advanced options such as vertical and horizontal filtering
Demonstrate how to monitor transactional replication performance
Learn advanced troubleshooting techniques to apply when problems occur

Configuring Secure SQL Access for the Web Developer\Administrator
By now we all know that we should use Windows Authentication to connect to SQL Server from our IIS applications...but most of us don't know how. This session aims to clear up the confusion by demonstrating the ways that ASP & ASP.NET applications can be configured to make secure connections from different versions of IIS so that we can keep our connections secure and our DBAs happy.

Session Goals:

Understand the concept of impersonation
Learn how ASP and ASP.NET applications can be configured to use impersonation to make secure connections to SQL Server
Learn how to configure IIS 5, 6, & 7 and Windows XP, 2003, and 2008 to support impersonation

Register A Server More Than Once In SSMS

noreply@blogger.com (Kendal Van Dyke) — Tue, 21 Apr 2009 17:00:00 +0000

In yesterday's post about using SQL 2008 Express as a Central Management Server I mentioned that you can't register a server more than once in Object Explorer. Technically that's correct, but I meant to say Registered Servers. It turns out that you can register a server more than once in Registered Servers - the catch is that you have to add the server to different groups when you register it. Thanks to Mladen Prajdić (@MladenPrajdic on Twitter) for pointing out my mistake.

Use SQL 2008 Express As A Central Management Server

noreply@blogger.com (Kendal Van Dyke) — Mon, 20 Apr 2009 18:45:00 +0000

I had the chance to catch Brent Ozar's webcast last week on creating a poor man's CMDB. In it he talked at length about the Central Management Server feature in SQL 2008. While I use SSMS 2008 my only SQL 2008 database installs so far are running inside Virtual PCs that aren't always on. Fortunately, there is another option – SQL 2008 Express. It's lightweight, free, and best of all it can be used as a CMS. The only catch is that if you put your install on anything other than your local machine (which you're going to do since you're using it as a CMS, otherwise what's the point?) you'll have to enable remote connections. No worries, it's actually an easy task to do – just follow these step by step instructions and you'll be all set.

Update – It didn't take me long after I posted this to realize that there are cases for running SQL Express locally in order to take advantage of a CMS. One example is if you frequently run multi-server queries and you want to create logical groups of servers where a server belongs to more than one group. Another is if you're a one person shop and you want to use Brent's CMDB scripts to gather data. ~~That makes me wonder, though – why didn't Microsoft just add the ability to register a server in more than one group into Object Explorer in the first place? Hmm, I smell a~~ ~~Connect~~ ~~request cooking!~~

Update 2 – OK I am a dolt. In my first update I meant to say Registered Servers, not Object Explorer. I've posted a correction here.

Full Text Catalog Scripting "Gotcha" in SSMS 2008

noreply@blogger.com (Kendal Van Dyke) — Wed, 15 Apr 2009 16:00:00 +0000

With every new version of SQL Server comes a set of deprecated features. Usually Microsoft gives fair warning that these features will be removed in a future version of SQL Server but they continue to work for the time being. In some cases, though, arguments for statements are left in place but have no effect on the current version of SQL Server. For example, take a look at the syntax for CREATE FULLTEXT CATALOG in SQL 2008. It's clearly stated that the ON FILEGROUP and IN PATH clauses have no effect in SQL 2008.

I use SSMS 2008 to manage my servers so that I can take advantage of the UI improvements that it offers. A common task I perform is to script objects from Object Explorer; since most of my production servers are running SQL 2005 I set my scripting options to target 2005 (Tools –> Options –> SQL Server Object Explorer –> Scripting). I haven't run into any problems with this until today. Even though my target is SQL 2005, SSMS 2008 is ignoring the ON FILEGROUP and IN PATH clauses when scripting out a full text index since they don't matter in 2008.

Here's what my scripted index looks like when I use SSMS 2008:

CREATE FULLTEXT CATALOG [Example Search Catalog] WITH ACCENT_SENSITIVITY = OFF AS DEFAULT AUTHORIZATION [dbo] GO

And here's what the index looks like when I use SSMS 2005:

CREATE FULLTEXT CATALOG [Example Search Catalog] IN PATH N'E:\FTData' WITH ACCENT_SENSITIVITY = OFF AS DEFAULT AUTHORIZATION [dbo] GO

The same behavior occurs if you use the Scripting Wizard as well. As a general practice I do not run auto generated scripts without reviewing them first, and in this case I'm glad I did because my catalog wouldn't have been created where I wanted it to. Fair warning - there are probably more of these "gotchas" in SSMS 2008. If I find any more I will post them.

Undocumented Trace Flag Improves Performance By Up To 25%

noreply@blogger.com (Kendal Van Dyke) — Wed, 01 Apr 2009 17:30:00 +0000

As a production DBA I love to tweak things to optimal perfection. After all, if I paid for the hardware and software shouldn't I get the best possible performance out of it? In that vein, I've shifted my focus in the last few weeks from disk performance to tweaking SQL by playing with trace flags. While some trace flags can let you do some pretty ridiculous things (e.g. trace flag 1807 – do you really want to create a database on a UNC share?), there are others which are quite useful and can improve performance in certain conditions.

There also happens to be a number of undocumented trace flags. I Googled to find as many as I could but I was unable to get a comprehensive list of all of them so I decided to do some testing and create a list of my own. I had a server handy and a trace of a few thousand commands that had been run on one of our more heavily used production servers. I used the following SQL to create a list of startup commands:

SELECT 'DBCC TRACEON (' + convert(varchar(10), number) + ')' AS [TraceCmd] FROM MASTER.dbo.spt_values GROUP BY number ORDER BY number

Then I tested each option out by turning on the trace flag and replaying the trace from my production server. I had another trace running to keep track of the total reads, writes, CPU activity, and duration of the replay. As expected, I hit on some already documented undocumented trace options, but surprisingly I came across one trace flag that improved my overall performance by 25%. I was so astonished that I restarted the server and re-ran the test to make sure it wasn't a fluke; sure enough, I was able to reproduce my results.

Here's the trace flag that I found:

DBCC TRACEON(040109)

I haven't figured out exactly what the flag causes the query engine to do differently, but whatever it is I'm not going to argue with the free performance I'm seeing. I'm so stoked about finding this that I'm going to start rolling it out to our production boxes ASAP. I would normally give our my standard disclaimer here and advise that you test this out in a non-production environment first, but this is so huge that you might want to consider going straight to prod with it. And by the way, Happy April Fool's Day! :-P

Orlando .NET Code Camp 2009 Recap

noreply@blogger.com (Kendal Van Dyke) — Tue, 31 Mar 2009 19:00:00 +0000

On Saturday, 3/28, I presented Useful T-SQL Statements You May Not Be Aware Of at the 2009 Orlando .NET Code Camp. Despite some glitches with the projector I think the presentation went well. About 20 people came and there was a lot of good dialog, a few laughs, and even even something pretty unique – right in the middle of the presentation the space shuttle Discovery flew over Orlando on its way to landing at Kennedy Space Center and we heard the double sonic booms.

A lot of great questions were asked, including a few that stumped me. I promised to find out the answers and blog about them, but unfortunately I made the mistake of not writing them down at the time. If you were at my session and I didn't\couldn't answer your question, please leave a comment and I will make sure to get you the answer.

Because of a scheduling conflict I was only able to be there for my presentation, but from what I've read elsewhere – see here, here, here, and here – it was a great day for everyone who came.

I've posted my slide deck and samples for download below.

Download: Useful T-SQL Statements You May Not Be Aware Of.zip

Launch Replication Monitor Without SSMS

noreply@blogger.com (Kendal Van Dyke) — Wed, 25 Mar 2009 20:00:00 +0000

I manage a lot of replication publications\subscriptions and when I get a latency or agent failure alert it always bugs me that I have to go through 5 steps to launch Replication Monitor: launch SSMS, go to my server in object explorer, expand the treeview, right click on the Replication node, and choose the menu option to launch Replication Monitor. To make things easier I did what I think Microsoft should have done in the first place and created a shortcut to the Replication Monitor executable in my start menu and on my desktop.

To create a shortcut for yourself open up Windows Explorer and navigate to C:\Program Files\Microsoft SQL Server\100\Tools\Binn if you're running SSMS 2008 or C:\Program Files\Microsoft SQL Server\90\Tools\Binn if you're running SSMS 2005. Right click on sqlmonitor.exe and choose Send To > Desktop (Create Shortcut). Now you've got a shortcut on your desktop that will launch Replication Monitor with a simple double click! (You'll probably also want to rename the shortcut to something like "Replication Monitor")

Unfortunately the default icon doesn't look great in anything bigger than 16x16 so I replaced it with a really nice (and free!) icon from iconspedia.com that looks much better:

Happy monitoring!

Visit: Activity Monitor Icon on iconpedia.com

Ada Lovelace Day – Celebrating Women In Technology

noreply@blogger.com (Kendal Van Dyke) — Tue, 24 Mar 2009 21:30:00 +0000

Today is Ada Lovelace Day, an international day of blogging to draw attention to women excelling in technology. I don't normally post off-topic things, but this particular subject hits home for me. No, not because the first computer language I learned in college was Ada 95. It's because I admire what a certain rocket scientist whom I call my sister has done.

A mechanical engineer by degree, she worked as an intern for a summer at NASA on the shuttle's solid rocket boosters and knew exactly who she wanted to work for when she graduated. Unfortunately politics and the federal budget left her hanging for 2 years, but she finally got the call and went to work at Marshall Space Flight Center in Huntsville, AL. She spent many years working on nuclear propulsion systems for probes destined for the outer planets of our solar system while working her way up the NASA ladder and earning a Master of Mechanical Engineering degree to boot. Today, after 18 years, she's a Branch Chief in NASA's Propulsion Thermal Analysis group and won't tell me what she's working in - though in all fairness she can't because it's classified.

Not only do I admire what she's accomplished technically, but that she's excelled in what is normally regarded as a a male dominated field and a NASA management culture that has a reputation for…well, lots of things that prompt talent to depart for the private industry. She's worked hard to get where she is and is the kind of role model that young women interested in science can look up to.

Great job, sis!

Data Mining With Google Insights For Search

noreply@blogger.com (Kendal Van Dyke) — Mon, 23 Mar 2009 16:30:00 +0000

I recently read a NYT article that highlighted how Google search data is being used to predict flu outbreaks at a regional level and in some cases a week to 10 days before they're reported by the CDC. It turns out that a good portion, if not all, of Google's search data is available for anybody to query using Google Insights for Search BETA. You can search on multiple terms and filter by time, geographic region, and category.

For example, look at the worldwide results for cloud computing. Interest has steadily gained in the last 18 months(follow the link and you'll see that surprisingly the peak interest comes from India - the US ranks 6th on the list):

What about social networking sites? Here's the almost exponential rise in popularity of Twitter in the last few months:

Putting it into perspective, look at how Twitter compares to more established sites like MySpace and Facebook:

In the graph above MySpace is the red line, Facebook is the yellow line, and Twitter is the little tiny blue bump in the lower right corner. Three things stand out: MySpace's popularity has peeked and it's on a slow decline, Facebook is red hot, and Twitter has a long way to go before it's as popular as the others.

Given that Google is the de facto search engine for most people it's easy to see how useful – and powerful - this tool can be.

Upcoming Speaking Engagements

noreply@blogger.com (Kendal Van Dyke) — Thu, 26 Feb 2009 22:00:00 +0000

I've got three speaking engagements lined up in next two months:

Wed, March 11 @ oPASS: The Truth About Disk Performance & Configuration. This presentation is based on the results from my recent Disk Performance Hands On series
Sat, March 28 @ Orlando .NET Code Camp: Useful T-SQL Statements You May Not Be Aware Of. This session will highlight several useful T-SQL statements that most people may not be aware of. Examples include: PIVOT, MERGE, ROW_NUMBER, RANK, OUTPUT, SCOPE_IDENTITY(), and more
Sat, April 25 @ SQLSaturday #13 (ATL): The Truth About Disk Performance & Configuration

If you make it out to any of these be sure to drop by and say hello!

My First Computer

noreply@blogger.com (Kendal Van Dyke) — Mon, 23 Feb 2009 22:00:00 +0000

Denis Gobo started another quiz, this time mostly unrelated to SQL. The question: What was your first computer and what were some of your favorite games? Brent Ozar tagged me last week but I was so wrapped up in finishing my disk performance series that I didn't get around to answering this until now…

My first computer was a Tomy Tutor. "A what?" you're probably saying to yourself. Introduced to the US in 1983, the Tomy Tutor had a CPU than ran at a blazing 2.7 MHz. It sported 16K RAM (expandable to 64K!), 48K ROM, and with a 256 x 192 resolution at a stunning 16 colors. It ran the Tomy BASIC OS and had built in paint and GBASIC programs. It had a useless 55 key rubber keyboard (think rubber = waterproof? Uh, no, think again). Peripherals included a cassette tape drive for saving drawings and programs, a joystick, and paddles for playing games.

The games were cartridges that plugged into a slot above the keyboard. My favorites – which happened to be the only ones I owned - were Traffic Jam, Deep Six, and Pooyan.

I managed to find this Google video which shows off how high-tech this was (For some reason embedding it blew up IE so I'm linking to it instead).

My Second Computer
OK, the Tomy Tutor was lame, but it was my first computer. It didn't take me long before I graduated to the C64, and oh what a difference that was! It had this nifty thing called a modem that you could use to call BBSs and share stuff. And don't forget about a floppy disk drive that could load and save data so much faster than those cassette tapes! How cool was that? OK, still lame by today's standards – but that was 1984 and it was all the rage.

Favorite games include: BC's Quest For Tires, Choplifter, Impossible Mission, Moon Buggy, and the classic M.U.L.E. Wow, I think I just got a tear in my eye remembering all the good times I had playing those games!

After That Came…
There were a couple of others I used afterwards worth mentioning for the fun of it. Yes, I am a geek.

Franklin (Apple clone, loved programming BASIC on it!)
KayPro 2X (I still remember typing papers on that green monochrome screen)
286 (many gaming memories here – King's Quest, Loom, and Links 286 - "Looks like he…hit the tree, Jim")
386 (with a math coprocessor!)
486

Once the Pentiums came out everything was a blur. But I will always look back fondly at those first couple of computers. Hindsight is 20\20, and it's my early days tinkering around with those machines that led me to a career in computer science.

I'm pretty late on my response so I'm not going to tag anyone else since everyone else I know has already been tagged.

Disk Performance Hands On: Series Recap

noreply@blogger.com (Kendal Van Dyke) — Fri, 20 Feb 2009 21:45:00 +0000

This is Part 8 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

Now that we’ve come to the end of the series let’s look back at what we’ve learned:

OLTP Operations

For data files SQL Server reads and writes are 8KB and random in nature
For transaction logs SQL Server writes are between 512 bytes & 64KB and sequential in nature

RAID Stripe, Partition Offset, & Allocation Unit Size

Optimal settings for OLTP data operations on local disks and DASD
- 64 KB RAID stripe
- 64 KB partition offset
- 64 KB allocation unit size
For OLTP transaction log operations on RAID 1 choice of partition offset and allocation unit size make little to no difference in performance
64 KB partition offset vs. 32 KB partition offset
- For RAID 10 with a 64 KB RAID stripe and 64 KB allocation unit, using a 64 KB offset resulted in an 11% improvement in IOs/sec and MBs/sec, and a 10% improvement in average latency for 8 KB random reads. For 8 KB random writes using a 64 KB offset resulted in a 10.6% improvement in IOs/sec, MBs/sec, and average latency
- For RAID 5 with a 64 KB RAID stripe and a 64 KB allocation unit, using a 64 KB offset resulted in a 5.7% improvement in IOs/sec and MBs/sec and an 8% improvement in average latency for 8 KB random reads. For 8 KB random writes using a 64 KB offset resulted in a 5.5% improvement in IOs/sec, MBs/sec, and average latency

RAID 10 vs. RAID 5

For a RAID 5 array using the same number of physical disks as a RAID 10 array, RAID 5 offers 10-15% better performance for 8 KB random reads but a whopping 60-65% worse performance for 8 KB random writes
To get RAID 10 write performance out of RAID 5 will require a RAID 5 array to use roughly 2x the number of physical disks than are present in the RAID 10 array

RAID 10 vs. RAID 1

If you take a RAID 10 drive which holds a single OLTP data file and reconfigure the physical drives as multiple RAID 1 drives which each holding a data file of equal size and belonging to the same filegroup, expect to see roughly equivalent performance as the RAID 10 configuration

PowerVault 220S (SCSI) vs. PowerVault MD1000 (SAS)

For both RAID 10 and RAID 5 on a PowerVault MD1000 expect to see 5-10% better performance for 8 KB random reads and 10-12% better performance for 8 KB random writes than the equivalent configuration on a PowerVault 220S

Misc Takeaways

Make sure you test against your target workloads. As I was writing this series I initially looked at 64 KB random reads. After I re-evaluated my results using 8 KB random reads I changed my recommendation for the optimal RAID stripe size to use.
My findings apply to local disks and DASD. If you’re on a SAN, things like partition offset are still applicable but ultimately you should consult your vendor or enterprise storage architect about optimizing disk performance.

I’ve enjoyed writing this series, and I hope that you have likewise enjoyed reading it! If you have any questions, comments, etc. please leave a comment below or feel free to contact me at kendal.vandyke at gmail dot com.

Disk Performance Hands On, Part 7: PowerVault 220S vs. PowerVault MD1000

noreply@blogger.com (Kendal Van Dyke) — Thu, 19 Feb 2009 22:00:00 +0000

This is Part 7 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

For the last 7 or so years the PowerVault 220S was Dell's offering in DASD enclosures. It supports 14 SCSI drives and can run in split bus mode with 7 drives per SCSI bus. It's since been replaced by the PowerVault MD1000 which supports 15 SAS\SATA drives and can also run in split bus mode with 7 drives on one bus and 8 drives on the other. I recently had the opportunity to run tests against both models to see how they compare against one another. I didn't have too long with them so I was only able to test RAID 10 and RAID 5 before I had to put them back into action in production.

The PowerVault 220S tests were run using 12 146 GB 15K RPM Ultra320 SCSI drives and the enclosure was connected to a PowerEdge 2850 via a PERC 4/DC controller card. The PowerVault MD1000 tests were run using 12 146 GB 15K RPM SAS drives and the enclosure was connected to a PowerEdge 2950 via a PERC 5/E controller card. Tests were done against each enclosure in split bus mode using a 64 KB RAID stripe, 64 KB allocation unit size, and partition offsets of 32 KB, 64 KB, and 1024 KB. To see how each system scaled under load I ran separate tests with 4, 8, and 16 threads with an IO queue depth of 8 against a 512 GB data file. You can view the specifics of each setup here.

PowerVault 220S – RAID 10
Here's what 8 KB random reads look like:

And here's what 8 KB random writes look like:

PowerVault 220S – RAID 5
Here's what 8 KB random reads look like:

And here's what 8 KB random writes look like:

PowerVault MD1000 – RAID 10
Here's what 8 KB random reads look like:

And here's what 8 KB random writes look like:

PowerVault MD1000 – RAID 5
Here's what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

Head To Head - RAID 10
Now for fun I’ll look at a head to head RAID 10 matchup of the 220S against the MD1000. These comparisons use the same testing parameters I outlined at the top of this post except that I am focusing only on a 64 KB partition offset this time.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

Head To Head - RAID 5
Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

Conclusion
No surprises here, the newer controller card and faster drives in the PowerVault MD1000 outperformed the PowerVault 220S. The chart below shows the MD1000’s performance metrics as a percentage relative to the 220S. Recall that for average latency lower values mean better performance so a negative number indicates a decrease in latency (which is good)

In Part 8 I will wrap things up and recap the conclusions reached in Parts 1-7.

Disk Performance Hands On, Part 6: RAID 10 vs. RAID 1

noreply@blogger.com (Kendal Van Dyke) — Wed, 18 Feb 2009 22:00:00 +0000

This is Part 6 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

Pop quiz: If you had to configure 4 physical disks to hold data, which of these two options will result in the best performance:

One RAID 10 array which will hold a single data file.
Two RAID 1 arrays, each holding a data file that belong to the same filegroup.

I asked this same question recently on SQLServerCentral.com (read the thread here – free registration required). The resulting debate showed me that the answer is not as obvious as it might seem. Recall that in RAID 10 a hardware based controller card handles writing data evenly to all drives (unless you’re using software based RAID, which is beyond the scope of this series). SQL MVP Tony Rogerson did some tests to show that SQL Server will write data evenly to multiple files if they are in the same filegroup. That makes the two RAID 1 drive scenario, in principle, operate in a similar way as RAID 10.

Walks Like RAID 10, Talks Like RAID 10…
To find out which is the better choice I’ll use the results from Test Harness #1 – a Dell PowerEdge 2950 (view the test harness specifics here). I’ll look at the optimal settings first: 64 KB raid stripe (for RAID 10), 64 KB partition offset, and 64 KB allocation unit. On the RAID 10 setup I tested a 128 GB data file using 8 threads with an IO queue depth of 8. On the RAID 1 setup I tested a 64 GB data file using 4 threads with an IO queue depth of 8. That’s half the file size and load I put on RAID 10; my voodoo math reasoning is that since I am using two data files on RAID 1 I can double the IOs/sec and MBs/sec results for the single data file and arrive at something that’s comparable to the RAID 10 load. Note I’m doing it this way because SQLIO can’t measure the load on multiple data files simultaneously. Also, since latency is an average value I’m not doubling it for RAID 1 – instead I’m just doing a direct comparison to RAID 10.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

By the numbers it looks like the two RAID 1 drive scenario not only walks and talks like RAID 10, but behaves just like RAID 10 too.

Can Multiple RAID 1 Drives Really Outperform RAID 10?
I’ll admit, before I knew about using the right partition offset and allocation unit size I set up my servers using multiple RAID 1 drives. My gut feel was that it just seemed to perform better than if the same drives were set up in RAID 10. Let’s say for the sake of argument that you’re in the same situation - using multiple RAID 1 drives built using the defaults and after reading this series you’re thinking you need to rebuild everything as RAID 10. Is it really worth it? To find out, I’ll compare an optimal RAID 10 setup (64 KB stripe, 64 KB offset, and 64 KB allocation unit) against two RAID 1 drives built on the Windows defaults (32 KB offset and 4 KB allocation unit). On the RAID 10 I’ll use a 128 GB data file with 8 threads and an IO queue depth of 8. For RAID 1 I’ll use a 64 GB data file with 4 threads and an IO queue depth of 8, doubling the IOs/sec and MBs/sec to simulate the same load as the RAID 10 tests.

Here’s how 8 KB random reads look:

And here’s how 8 KB random writes look:

Once again the two RAID 1 drive scenario doesn’t look so bad. Obviously that’s not a very compelling argument to reconfigure your existing RAID 1 drives as RAID 10!

Conclusion
The point of these comparisons is to show that, in the most simplistic way, if you are already using multiple RAID 1 drives which hold data files in the same filegroup you’re not as bad off performance-wise as you may think. That said, if you’re setting up a server from scratch I’d recommend RAID 10 over multiple RAID 1’s for two reasons:

Today’s hardware based RAID controllers are pretty sophisticated and designed for reading from and writing data to disks in the most efficient way possible. By comparison, SQL Server’s proportional fill algorithm but doesn’t understand much about the physical disks on which files reside, nor does SQL Server employ the hardware based caching techniques that are built into controllers (and which were not factored into any of these tests).
Towards the end of Tony Rogerson’s post he mentions that SQL will only round robin writes if there is equal free space in each file in the filegroup. As soon as the free space is different you’ll have one set of disks taking more IO than the other. That won’t happen with a single data file on a RAID 10 disk.

Next, In Part 7 I will compare a PowerVault 220S with a PowerVault MD1000.

Disk Performance Hands On: Correction #2

noreply@blogger.com (Kendal Van Dyke) — Tue, 17 Feb 2009 14:30:00 +0000

Argh! Shame on me. Last night as I was reviewing my test results I realized that although I caught my mistake the first time, somehow I didn’t actually update my RAID 10 numbers. And of course it turns out there’s a small problem – I had previously said a 128 KB RAID stripe was optimal but the results indicate that a 64 KB RAID stripe is the better choice. Numbers (usually) don’t lie, so I’ve updated the post (again). And triple checked it. It’s right this time, I promise!

Now that I’ve made the correction you may want to re-read part 2 of the series on RAID 10.

Disk Performance Hands On, Part 5: RAID 10 vs. RAID 5

noreply@blogger.com (Kendal Van Dyke) — Mon, 16 Feb 2009 22:30:00 +0000

This is Part 5 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

In part 2 and part 3 of this series I looked at RAID 10 and RAID 5 performance, respectively. Now I’ll show how the two rate against each other. For this comparison I’ll look at all three of my test harnesses for this post (view the test harness specifics here). For all comparisons I am using a 64 KB RAID stripe with a 64 KB partition offset and a 64 KB allocation unit size. As with my previous posts I’ll focus on OLTP data activity (8 KB random reads\writes).

Test Harness #1: Dell PowerEdge 2950
These tests were performed using 4 physical SCSI drives with 8 threads against a 64 GB data file at an I/O queue depth of 8.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

Test Harness #2: Dell PowerVault 220s
Unlike the PowerEdge 2950 which uses local drives, the 220s is a standalone enclosure commonly run “split bus”, a mode which splits the drives in the enclosure into two groups and dedicates an independent SCSI bus to each group. This set of tests was run in split bus mode using 12 physical SCSI drives (i.e. 6 drives per SCSI bus). I tested workloads of 4, 8, and 16 threads against a 512 GB data file; each set of tests used an I/O queue depth of 8.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

Test Harness #3: Dell PowerVault MD1000
The PowerVault MD series is Dell’s current offering in DASD enclosures. The MD1000 is the replacement for the 220s and supports both SAS and SATA drives, and just like the 220s it can operate in split bus mode. Similar to my tests against the 220s, this series was run in split bus mode using 12 physical SAS drives with workloads of 4, 8, and 16 threads against a 512 GB data file using an I/O queue depth of 8.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

Conclusion
Generally speaking, it’s common knowledge that RAID 5 offers better read performance while RAID 10 offers better write performance. The performance graphs from each test illustrate that, but to drive the point home I’ll translate the numbers into percentages. The chart below shows the RAID 10 metrics relative to RAID 5. Red numbers indicate where RAID 10 performed worse; black numbers indicate where RAID 10 performed better. (Higher values for IOs/sec and MBs/sec are better so negative numbers mean a drop in performance. Lower values for latency are better so negative numbers in this column mean a drop in latency which is an increase in performance.)

I knew that RAID 5 has slower write performance than RAID 10, but roughly 65% slower for IOs/sec and MBs/sec? Wow! Arm yourself with this knowledge next time somebody suggests that it isn’t so bad to use RAID 5 instead of RAID 10, especially if your database is write intensive.

RAID 10 Performance From RAID 5?
As I was writing this series my friend Andy Warren asked me an interesting question: What would it take to get RAID 10 performance from a RAID 5 configuration? (Obviously he was talking about write performance since RAID 5 already offers better read performance) Honestly, I had never thought of that angle before, but I can see how it might make sense in some circumstances.

I’ll pretend that I’m being asked to set up a RAID 5 array on a PowerVault MD1000 that gives me performance equivalent to the RAID 10 array I configured on the PowerEdge 2950. For the sake of this exercise I’ll ignore latency and focus on IOs/sec and MBs/sec. Here comes the voodoo math: At 8 threads on a 12 disk RAID 5 configuration on the MD1000 8 KB random writes come out to 71.23 IOs/sec and 0.56 MBs/sec per disk (that’s just the total numbers divided by 12). The RAID 10 results for the 4 drive configuration on the PowerEdge 2950 were 617.12 IOs/sec and 4.82 MBs/sec; to get to those numbers using the voodoo math I just did means I’d need to use 9 disks in a RAID 5 configuration, or over twice the number of disks required for the RAID 10 array to begin with.

So can you get RAID 10 performance out of RAID 5? Sort of. Granted, my math was simplistic at best, but it’s obvious that you can’t just add a few extra disks to a RAID 10 array, reconfigure them as RAID 5, and expect to see equivalent performance; it’s probably going to take 2X the disks and practically speaking the numbers just don’t add up to make it worthwhile.

Stay tuned, in Part 6 I will take a look at how RAID 10 compares against RAID 1.

Disk Performance Hands On, Part 4: RAID 1 Performance

noreply@blogger.com (Kendal Van Dyke) — Fri, 13 Feb 2009 21:00:00 +0000

This is Part 4 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

RAID 1 is typically recommended for transaction log files, though I have known several people (including myself, I’ll admit) who have also used RAID 1 to hold data files. Unlike RAID 5 & 10 there is no stripe size to factor into performance since RAID 1 only utilizes mirroring. Still, it’s important to use the right offset and allocation unit size – or is it? For these tests I used Test Harness #1 (Dell PowerEdge 2950) and Test Harness #3 (Dell PowerVault MD1000). You can view the test harness specifics here.

Partition Offset & Allocation Unit Size – Dell PowerEdge 2950
Based on the results from my RAID 10 and RAID 5 tests I chose to focus on a 64 KB allocation unit. In addition to 32 KB and 64 KB offset values I also tested a 1024 KB offset to see if a larger offset made a difference. As with previous tests I’ll present the results for 8 KB random reads and writes, the two operations SQL performs on data files most of the time.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

For both reads and writes the numbers are so close together that there’s no practical difference between them. I’ll point out that Test Harness #1 was using (somewhat) older 72 GB 15K RPM SCSI drives. Let’s take a look at a slightly newer system that uses 146 GB 15K RPM SAS drives.

Partition Offset & Allocation Unit Size – Dell PowerVault MD1000:
Let’s look at the same tests against a Dell PowerVault MD1000 (Test Harness #3). I’m also going to add one more thing to the mix – in my tests against the PowerEdge 2950 I configured SQLIO to use 4 threads. To see if the results were the same across increased workloads I also ran my tests using 8 and 16 threads.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

Now we see at least a minimal difference in the numbers and it appears that using a 64 KB offset or higher results in a small increase in performance. Just how much? For 64 KB vs. 32 KB offset it’s a ~10% improvement in IOs/sec, a ~5% improvement in MBs/sec, and a ~10% improvement in average latency for 8 KB random reads. For 8 KB random writes improvements are ~10.5% in IOs/sec, ~11% in MBs/sec, and 10-11% in average latency. Going from a 64 KB offset to a 1024 KB offset resulted in a very small improvement (about 1%), but an improvement nonetheless.

Transaction Log Write Performance – Dell PowerEdge 2950
Back to Test Harness #1 for a second. Remember that RAID 1 drives are typically used for transaction logs. Unlike data reads\writes, transaction log writes are single threaded, sequential operations that range between 512 bytes and 64 KB. To see how performance varied for this kind of activity I ran a set of tests on a 2 GB file using 8 KB and 64 KB writes on 4 KB, 8 KB, and 64 KB allocation units on both 32 KB and 64 KB offsets.

Here’s what sequential writes look like for a 32 KB offset:

And here’s what sequential writes look like for a 64 KB offset:

I’m not going to bother to show any comparisons of 64 KB vs. 32 KB because the numbers are pretty much dead-on the same. Would there be any difference on newer hardware? Perhaps, but my guess is that it wouldn’t be anything dramatic.

Conclusion
For OLTP data files on RAID 1 my tests showed the optimal configuration to be a 64 KB allocation unit and at least a 64 KB offset. For OLTP transaction log files on RAID 1 it doesn’t matter what offset or allocation unit size you use, there doesn’t appear to be any combination of settings that result in a significant performance improvement.

In Part 5 I’ll take a look at how RAID 10 performance stacks up against RAID 5.

Disk Performance Hands On: Correction

noreply@blogger.com (Kendal Van Dyke) — Thu, 12 Feb 2009 22:00:00 +0000

DOH! I made a mistake. Somehow I had it in my head that for OLTP databases SQL Server read data 64 KB at a time. While I was working on the next post for the series I asked a question on Twitter about transaction log write sizes. Jason Massie, a.k.a. @statisticsio, pointed me to this post from the SQL Customer Advisory Team. While I was reading it I noticed a small problem – OLTP data reads are done 8 KB at a time. Then I came across Linchi Shea’s post on OLTP workloads which confirmed it. The icing on the cake was when I opened perfmon on one of my servers and looked at the PhysicalDisk\Avg. Disk Bytes/Read counter. There it was, plain as day – 8 KB reads.

The good news is that the there was only a minor change in the outcome. For RAID 10 the optimal configuration came out to be a 64 KB RAID stripe (instead of a 128 KB stripe as I previously posted), 64 KB offset, and 64 KB allocation unit size; for RAID 5 the argument could be made for a 128 KB RAID stripe, but the 64 KB offset and 64 KB allocation unit size are still the clear winners. I have updated the text and images in the original posts to reflect the right read size. So fortunately, relatively no harm done (except to my ego).

Disk Performance Hands On, Part 3: RAID 5 Performance

noreply@blogger.com (Kendal Van Dyke) — Wed, 11 Feb 2009 16:15:00 +0000

This is Part 3 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

RAID 5 is recommended for read intensive databases where you need to maximize space while retaining fault tolerance. Just like a RAID 10 drive achieving optimal performance depends on the right combination of stripe size, partition offset, and allocation unit size. For this set of tests I used the same hardware as my RAID 10 tests - Test Harness #1, a Dell PowerEdge 2950 (view the test harness specifics here).

Stripe Size & Allocation Unit Size
I’m following the same methodology and conditions as in part 2 of this series. To recap, I used RAID stripe sizes of 4 KB, 8 KB, and 128 KB along with allocation unit sizes of 4 KB, 8 KB, and 64 KB on a partition with a default 32 KB offset. I focused on 8 KB random reads writes, which are the two types of operations SQL server performs on data files most of the time.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

The results are in line with what we saw for RAID 10. A 128 KB stripe size offers the best overall performance. However, allocation unit size makes no difference when using a 32 KB partition offset.

Partition Offset
Now let’s look at how a 64 KB partition offset changes things.

Here’s what 8 KB random reads look like:

And here’s what 8 KB random writes look like:

Interestingly, with a 64 KB offset there’s very little difference between a 64 KB and 128 KB RAID stripe size for both read and write operations.

What about how a 32 KB offset stacks up against a 64 KB offset? Since a 128 KB RAID stripe and a 64 KB allocation unit offered the best performance for a 32 KB offset, let’s go with the same for a 64 KB offset so that we can do an apples to apples comparison of the two.

8 KB random reads:

8 KB random writes:

Just as we saw with RAID 10, a 64 KB offset results in better performance than a 32 KB offset. Put into percentages, using a 64 KB offset results in a 5.7% improvement in IOs/sec and MBs/sec and an 8% improvement in average latency for 8 KB random reads. For 8 KB random writes using a 64 KB offset resulted in a 5.5% improvement in IOs/sec, MBs/sec, and average latency.

Conclusion
As I expected and the numbers showed, using a 64 KB offset results in the best performance. What I was surprised at, however, is how little of a difference allocation unit size made for 64 KB and 128 KB RAID stripes. We’re talking +\- 5 IOs\sec and MBs\sec. On paper that means for a 64 KB offset as long as you go with at least a 64 KB RAID stripe and a 4 KB allocation unit you’re going to be getting close to optimal performance. Unfortunately, although I had two other test harnesses I didn’t get the chance to see if this holds true when you throw more drives into the mix. My guess is with more drives the gap between numbers will become more apparent. Therefore I recommend you play it safe, go on the high end, and stick with a 128 KB RAID stripe, 64 KB offset, and 64 KB allocation unit size for your RAID 5 drives.

Next, in Part 4 I will take a look at RAID 1 performance.