So you’re completely pissed off that your third party software isn’t working, and you’re getting ready to call the vendor’s support line and let loose a stream of obscenities.  I feel you: I’m using Symantec Antivirus too. <rimshot> But before you pick up the phone, stop and think about a few things that will help you get a much better support experience.

1. Did you check the Event Logs?

Don’t leap to conclusions and assume that everything else in the system is working absolutely perfectly.  Go into Control Panel, Administrative Tools, Event Viewer.  Look at the Application and System event logs.  If you see so many red and yellow icons that it looks like a pinata, then maybe you’ve got a bigger problem to troubleshoot.

Don’t just check the exact date/time of your problem, either: sometimes a long-running problem will affect what you’re trying to do.

Example: a user’s backups are taking far longer than expected.  Scrolling back through the event viewer, the RAID card noted that a drive had failed, and the system was running under degraded performance while the array was being rebuilt with the hot spare drive.

2. Does it work without the vendor’s product?

If you’re troubleshooting, say, a SQL Server backup product, and you’re getting all kinds of errors when you try to do a backup or a restore, try doing it without the third party product.  Do a native-only backup/restore/whatever, and see if the same issues happen.

If they do, don’t bother calling the vendor, because you’ve got a bigger problem.  But don’t call Microsoft yet, because you might not have removed enough unknowns from the equation.

3. Narrow it down as far as possible.

Make a list of everything involved with the issue, and then rule out each of those parts.  The more specific you can be about the problem, the faster you can get a fix.

The worst example of this is when someone calls in saying, “I get an error now and then.”  What error?  What are you doing when the error occurs?  What’s running?  What changed?  Let’s help by ruling things out.

Example: let’s say our nightly jobs to back up a database keep failing.  My list of moving parts might include:

• The backup script
• The SQL Server Agent job
• The backup software
• The database
• The SQL Server instance
• The server hardware

So then to rule out each of those, I would do the following:

• The backup script – try using a simple “BACKUP” command, outside of any other scripts, or use the SQL Server Management Studio GUI
• The SQL Server Agent job – try a different time of day.  True story – had a customer whose jobs failed every night, and we eventually figured out that somebody had an OS-level scheduled task to stop SQL Server at a specific time – which happened to be during the backup time window.
• The backup software – try using only native backups, not third party apps.
• The database – try backing up a different database.  If it’s a big database, try a small one, like model/msdb/master/etc.  If those work, then try another large database.
• The SQL Server instance – it can be SQL Server-level settings like service account permissions.  Try using another instance with the same database and see if it breaks.
• The server hardware – don’t just think the server itself, but break this down into pieces too.  If you’re using iSCSI as a backup target, try backing up to local disks.  If you’re using local disks and you’ve got SAN space available to test, try backing up to the SAN.

If you’re running something from your machine, then try it from another workstation.

4. Write down the exact steps to reproduce the problem.

To get the absolute fastest support possible with the best end result answer, boil the issue down to an exact set of steps that anyone can follow on their own machine.  Support organizations are huge, and these days people are working from home a lot.

When you write down the steps, your target audience is a developer who just got emailed about your case from his PDA and he’s curious to see if he can reproduce it on his laptop, which doesn’t have an internet connection at the moment.  If you can give him enough information to reproduce the issue without connecting to the internet, you won.

Bonus points if they don’t have to use any of your data – that way, they can’t blame your data as the problem. Even better, this lets people test that they’ve actually fixed the problem without getting your data involved.

Example: check out this problem I had in the Microsoft data mining forums.  I wrote up exactly how to reproduce the problem by entering random data in an Excel spreadsheet.  Anybody, from a developer to a support engineer, can test this case on their own machine.

5. Study the Art of War.

I love this book, and it comes in useful in so many business interactions.  You can read The Art of War for free online.  I recommend saving a copy in a text file and putting it on your PDA.  The next time you’re waiting in line somewhere, bored out of your gourd, open it up and start reading.

Some of the principles won’t seem obviously beneficial to IT workers, so stroll down to your local bookstore and you’ll probably find a version of The Art of War targeted at businesspeople.  There’s dozens of versions of this book with different interpretations of what it means for modern life.

In terms of a support call, here’s what it means: you don’t want to go to war. If you call support, you may not like the results.  I abhor calling support because I’ve had some ugly experiences with some unqualified staff.  As a result, if I can figure out how to fix the problem myself, I’ll probably learn things that will make me a better warrior – I mean, DBA.

If you have to wage war, bring overwhelming force to the battlefield. Come armed with every single event log for your system, documentation about its configuration, screenshots of errors, and the scripts that produced the error.  When the support call starts, I like to ask, “Can I email you the information I’ve gathered?  It’s all in a zip file.”  I start that email before I even start talking, because it’ll take a while for this data to course through the internet.  By the time I’ve finished my explanation, they’ve got my files.

If you didn’t bring enough force, you have to be fast. If support asks for something you didn’t get ahead of time, don’t say you’ll call them back – gather the information immediately. Don’t hang up, don’t arrange a later meeting, just get it right now.  Otherwise, the person you’re working with won’t be on duty later, or they’ll have forgotten about the specifics of your case.  Show that you’re willing to focus, and they’ll return the favor.

Sometimes your business managers won’t allow you to stop production to gather the information you need.  In that case, make it abundantly clear to management that you want to move forward, but they’re choosing not to.  That way, when a manager comes in and asks why you haven’t fixed the problem yet, you can point to the exact email where the business management said you had to delay until a given date.

I’m sure I’m missing more links between The Art of War and calling support – heck, I could have made an entire blog entry out of that – but I’m off to follow up with my next support call….

Bonus Tip from K. Brian Kelley: Know your escalation levels.

K. Brian Kelley (web site, Twitter) suggested another tip: ask what your options are for escalation, and exercise them.

Escalation is the process of moving things up the chain to get to a higher level of support.  There’s usually a documented process: first level will work on it a given amount of time or until they feel like they can’t make any more progress, and then they’ll escalate the call up to the second tier of support.  That second tier is more experienced and has more tools at their disposal.  If they can’t solve it, they escalate it up another tier, and eventually you’re talking to the product developers.

When the call starts moving, make it clear what your timeline requirements are with support.  When I have a production-down situation, I ask the support engineer to agree at the beginning that we’re going to escalate the call if we don’t have significant progress within one hour.  (That time can be different depending on how severe the outage is.)  Then every time we escalate, I repeat that process with the new engineer.

On the other hand, if I’m dealing with a non-urgent server, say a QA box where I’ve got a replacement available for my staff, then I’ll tell the support engineer that I can wait 24 hours before we need to escalate it.  Putting a time on it helps both of you work with appropriate urgency.

You don’t have to wait for the time to lapse, either: if you’re getting frustrated with the support engineer and you don’t feel like your case is going in the right direction, ask it to be escalated.  I feel horrendously guilty whenever I pull that, but the sad fact is, sometimes it’s the best thing for everybody involved.  The support engineer doesn’t want to waste your time – heck, they want you off the phone too!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:

Jason Massie and I had a steel cage blogmatch about whether to configure a SAN with shared or dedicated drives, and we both passed out for exhaustion.

This debate is starting up again but with a slightly different spin: virtualization.  In an environment with Microsoft Hyper-V or VMware ESX, more than one SQL Server may have its virtual drives on the same physical hard drives.

If we have two SQL Servers, are we better off keeping their virtual hard drives on separate physical hard drives, or using one larger pool of hard drives shared between the two?  Microsoft’s technical article “Running SQL Server 2008 in a Hyper-V Environment: Best Practices and Performance Considerations” attempts to answer that burning question with Figure 16 on page 24:

Shared Disks vs Dedicated Disks

This graph shows two things:

1. Nobody spell checked the graph title, and
2. Dedicated drives were faster.

However, they’re only 3.5% faster on average.  Management of the shared disk approach is much more than 3.5% easier, so I’d have to vote for the shared drive approach, much to Jason Massie’s glee.

Plus, keep in mind that your servers probably won’t all be under full load at all times.  During periods where only one server is under heavy load, that server should achieve higher performance throughput since they’ll have more idle spindles available to them.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:

Jason Massie and Brent Ozar work with SQL Server for a living and write blogs for fun, but that’s where the similarities end.  Jason’s a fan of shared storage SANs: putting SQL Server data and logs on the same set of physical hard drives.  Brent’s a cheerleader for dedicated configurations where the arrays are separated by purpose.  We locked the two of them in a virtual room to see what would happen.  No database administrators were harmed in the making of this article.

Brent Says: You’re crazy.  Well, I mean, you’re crazy just in general – anybody who writes a comic about SQL Server needs some medication – but I mean you’re wrong on this issue.  Since the dawn of time, we’ve been telling SQL Server DBAs to put their data, logs and TempDB on different arrays for a reason: they get better performance.

Jason Says: We you say “need medication” I take it you mean more medication. Anyway…. Yes, that SQL Server 6.5 book we read a decade ago is, in fact, outdated. We once thought the earth was flat and we were the center of the universe. You can now file “dedicated raid 1 for logs” along with those busted myths.  That book was written back when servers had a small number of locally attached drives, and we were lucky if we got two hard drives in a mirrored pair for the logs.  Today, SQL Servers are attached to huge SANs with large numbers of hard drives.  Are you saying a shared piece of fifty or a hundred drives isn’t enough?

Brent Says: For small servers, you’re completely right, but let’s talk about an extreme example: established multi-terabyte data warehouses need very high I/O for long periods of time, several hours a night, during their nightly load windows.  They need to load as much data as possible, as fast as possible, and these windows often coincide with the nightly backup windows of other systems on that same storage.   They have dedicated DBAs who monitor performance closely, and they want to get every last dollar out of their SAN.  They carefully partition the data to balance load across different arrays and make it easier to do simultaneous ETL and reporting.

Jason Says: In situations like this when money for hardware and manpower is not an object, you can just throw enough resources at it that it is hard to mess up. However, you should spend some resources on testing and make sure you’re not flushing money down the toilet.  Separate drives for data, logs and ETL load files might best but having a higher total spindle count might still be better. The proof is in the test numbers, and blanket statements are not safe.  Not to mention by designing for the ETL, you will have idle drives when you are not loading – and you don’t want to have expensive SAN hardware sitting idle, do you?

Brent Says: You have a point there.  It’s easy to recommend dedicated drives in situations where there’s a consistently high utilization rate, like for 24×7 OLTP systems, email archiving databases or very heavily used data warehouses.  It’s harder to recommend them when the database servers just aren’t being that heavily used around the clock.  But talking about virtualization reminds me – I don’t want any SQL Servers sharing spindles with virtual servers, ever.  Virtual servers have such a high I/O utilization already that it’s like playing with fire.

Jason Says: It’s like buying a new house – you want to check out the neighborhood first and find out what your neighbors are like.  You wouldn’t build a brand new mansion in a sketchy part of town, and you wouldn’t put a data warehouse and a bunch of VMware guests on the same spindles.  I would suggest going to the Mayor (SAN Admin) and demanding a performance SLA. I have found by doing that, with executive sign off, that the Mayor makes sure the streets are clean and the riff raff out of sight.

Brent Says: Getting a good performance SLA is the first step to peace and harmony in the datacenter.  Data warehouses are a special case: they have lots of dedicated staff, generally experienced people, who can focus on things like IO throughput and latency.  But what about the rest of the databases, like your typical OLTP or application databases?  A single DBA has to manage dozens, sometimes a hundred instances.  They don’t have time to benchmark.

Jason Says: Hey, just because they’re a DBA doesn’t mean they spend every weekend in the datacenter, alone with their servers.  Not all of them are single.

Brent Says: Don’t be so sure.  My girlfriend refused to even think about marrying me until after I got out of the production DBA on-call rotation.   She says it’s a coincidence, but I dunno.

Jason Says: If you don’t have the time to benchmark, then you don’t have the time to micromanage storage performance, and you really don’t have the time to build a good estimate of how many individual drives you need for TempDB, logs, and so on to begin with.  Using a shared set of drives is the easiest way to achieve high performance.  If you’re not getting enough performance, add more drives, and it’ll even out.  Don’t try this with, say, 6 hard drives and expect to get good results.

Brent Says: That’s the same with dedicated drive setups, too.  I’ve been at shops where the developers swore SQL Server couldn’t scale – only to find out they had the data, logs and TempDB all on a handful of drives.  Whether you’re using dedicated drives or shared drives, don’t expect to scale a database server on any software platform without adding storage.  If you want a real eye-opener, go read the TPC benchmark records and look at the number of hard drives that were used.  It’s not unusual to see drive utilization rates of 1-5% – meaning, on a 146gb drive, they’re only storing a few gigs of data.  More spindles equals more speed, period.

Jason Says: Another problem is changing app requirements like adding audit trails or replication. These adding read IO to you dedicated log drives. There is also reporting mayhem(large sequential scans) being added to your OLTP app. These are the bad elements but once they are in your ‘hood, how do you move them out?

Brent Says: That’s actually a problem with some dedicated spindle SAN configurations.  When each array is configured on just a few disks, management can be a nightmare.  I’ve worked with one major SAN vendor who could only add drives two at a time to arrays, no matter how large the array was, and all drive management had to stop while that array was restriped – sometimes taking hours.  Adding more drives for more performance to handle new load was a real pain point, and I would have gladly traded off some performance in exchange for easier management.

Jason Says: Easier management is a big selling point for shared drive configurations.  When multiple servers draw out of a common pool, it’s easy to allocate space in smaller increments.  You can build multiple pools with different tiers: a Solid State Disk tier, a 15k RAID 10 tier, a SATA RAID 5 tier, and so on.

Brent Says: Speaking of Solid State Disk, when SSDs go mainstream, all SANs will just use shared drive configurations.  With SSDs, the random access problem goes away, because there’s no heads to move around, no platters to spin.  We’ll be able to gauge SAN utilization strictly on the number of queued-up requests instead of worrying about drive latency, and then it’ll be easy to tell when we need to add more drives.

Jason Says: Are you saying that maybe then you’ll get around to admitting the earth isn’t flat and shared drive configurations are always the best?

Brent Says: Get off my lawn, whippersnapper.

Jason Says: This is me getting in the last word. ?

Updated November 2008…

Now there’s more – we revisit it by taking a look at how virtualization affects SAN configuration.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:

Got a bumper crop of webcasts and videos up this week courtesy of the fine folks who refill my checking account:

Consolidation & virtualization roundtable with me, Kevin Kline and Ron Talmage – we raced through about twenty slides at a breakneck pace – about an hour.  We seriously could have talked for two hours easily about these topics, because there’s so much to cover even at a high level.  If you’re thinking about doing your first consolidation or SQL Server virtualization project, we talk about what makes a successful one and what to watch out for.  There’s no hard-core technical stuff here, just project planning info, but it’s good stuff.  I actually learned things while we were presenting, and that’s kinda scary.

CSI:DBA – ways to find out who deleted data & how to get it back – this was originally supposed to be me & Jason Hall, but he went on vacation (lucky devil) and you get an hour of non-stop Brent.  I covered the native options for auditing database activity, and then demoed how to do it with LiteSpeed v5′s new Log Reader.  I gotta be honest: the native methods for doing this totally suck.  I know, I work for Quest, but seriously, I wish the LiteSpeed v5 Log Reader would have been out when I worked in the field.  It could have saved my bacon a couple of times.

C.Y.A. – Cover Your Apps – DBAs often get called in to do backups before an application upgrade.  Wouldn’t it be nice if you could back up the database AND the application files at one time and package them all in a single backup file?  LiteSpeed does this with the Attached File Backup.

Kevin Kline’s first videoblog entry is online at SQLServerPedia, and while it doesn’t feature Yours Truly, it’s still worth a watch.  I’m looking forward to his next videos.

In a mildly related Backstage note, I’m shopping for rockstar clothes this week in California in preparation for my rockstar photo session with Tracy Manford (Blog – Flickr – Twitter).  I’ve been using the same crappy head shot for the last five years, and it’s time to update the photos to reflect my personality.  Tracy’s hilarious, and if you want to understand why she’s the right person to photograph me, just check out this one photo of hers.  End of story.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me: