Our contacts at Microsoft offered us the services of a Microsoft Technology Center to do a two-week lab exercise on our data warehouse. We’d kept piling one application after another on our data warehouse SQL Server until the poor thing was plain out of maintenance windows and couldn’t keep up, and we needed info from the pros to keep scaling it up.

Before my visit to the MTC, I tried searching the internet for other peoples’ experiences with the labs, but I came up emptyhanded. I resolved to write up a recap after the trip was over so that other DBAs heading to an MTC engagement would understand how it works, what to expect, and things to look out for.

What A Microsoft Technology Center Is

For us, the Microsoft Technology Center was really all about two things: getting insight from Microsoft experts and experimenting with different system-wide architecture options.

Microsoft staffs the labs with gurus who have extensive knowledge of MS products. When we ran into trouble with the testing functionality of Visual Studio, our MS guy quickly summoned the relevant expert. We got tricky questions answered in a matter of minutes instead of having to reinvent the wheel. As a result, we could make a lot of progress in a short amount of time.

Walking around the MTC, I was struck by how all of the place’s value is the technical people and the gear. The office could have been anywhere in the world, just a typical New Horizons-looking place with conference rooms and whiteboards. The building was completely worthless – until the people walked in, and suddenly it was priceless. Out of nowhere, I understood the Microsoft “People Ready” ads.

A minor drawback, though, is that the experts aren’t always available at a moment’s notice. They may be in other meetings, working with other clients at the lab, or gone for the day, or just didn’t have experience in that one particular aspect of the product.

The MTC datacenter is stocked with relevant hardware ahead of time for the client’s lab engagement. They did a great job of simulating our environment: while it wasn’t an exact match, it was more than close enough to simulate what we were doing in production. We were able to experiment with adding more memory, changing SAN configurations, changing SQL settings and other things we couldn’t possibly do in rapid fire succession in our production environments.

What An MTC Isn’t

The MTC doesn’t have every piece of software, particularly non-Microsoft software. I needed to bring a copy of our data warehouse, and they had Quest LiteSpeed available to do database restores. However, they couldn’t have copies of our reporting software (BusinessObjects), our ETL software (IBM DataStage) or our load testing software (Mercury LoadRunner) due to licensing restrictions. We were warned about this ahead of time, and we could have rented or borrowed trial licensing from our vendors, but we would still have to build that complete environment in another location from scratch. That kind of setup turns a two-week lab engagement into a four-week project with more risk and more planning. That fact alone made the Microsoft BI stack more appealing.

Since we couldn’t use our reporting, ETL or load test software, we had to have Plan B’s for those, and that brings in the next problem: the Microsoft Technology Center is not the place to learn new products. For example, we needed to be able to load test an IIS application without our normal Mercury LoadRunner, so we used Microsoft Visual Studio Team Test. VSTT met all of our needs and was a lot of fun to work with, but it came with a one week learning curve: we didn’t get a useful benchmark out of it until the fifth business day of the lab. Our QA staff had to rebuild their use case tests with a new product, we had to interpret IIS errors we’d never seen before, and we couldn’t compare the lab environment test results from VSTT with the production test results from LoadRunner.

I walked away with a lot of valuable QA experience and a great impression of Visual Studio Team Test, but looking back, that’s probably not the best thing for a database administrator to learn, which brings up the next weakness.

The Microsoft Technology Center isn’t staffed with everyone from the client – only with the people who are sent to the remote lab. During the lab setup, I wish we could have had at least one of our own developers and a QA staffer on hand. These people would have gained much more from the lab setup experience, the load testing and the code mitigation ideas. We would have had lower setup time, faster test iteration times, and faster mitigations. Instead, we had to do a lot of conference calls back and forth with the home office to get everybody on the same page and get status updates on the work.

The MTC also isn’t a silver bullet for application problems: during testing, I uncovered a few horrifying bugs that we just couldn’t fix fast enough to learn from the lab. We wanted to optimize one particular set of nightly processes, but while reviewing the code, I found business logic errors that required a rewrite of a major stored procedure. Since that stored proc represented the vast majority of the nightly load, we couldn’t take full advantage of the lab experience for that one objective – the code mitigation would have to wait until we returned to the office and met with our entire staff.

What We Gained From Our Experience at the MTC

On one poster at the MTC, a client quote said that they got frank, honest and valuable technical feedback from the Microsoft staff. That one thing sums up the biggest benefit in my view. Client managers, client technical staff and Microsoft staff can sit in a conference room, hash out a process, and be honest about what’s worked and what hasn’t worked at other clients.

For example, I’d talked to our BI manager in the past and emphasized that we couldn’t run ETL loads during business hours without a major impact to the end users. The system would still work, but reporting performance would degrade significantly. I couldn’t put a pricetag on hearing that same opinion from the Microsoft side: here was the vendor telling us that no, you can’t do that the way your system is engineered now, but that if you’d like to do that, it can be done with well-designed modifications to the ETL process.

At the Microsoft Technology Center, these kinds of recommendations and opinions carry more weight because they come from independent advisors, people who have a good interest in seeing the product succeed. They won’t overpromise something that the product can’t do, and they know the signs of a project that will fail.

Who To Send to an MTC Engagement

When planning an MTC lab session, a company should send the staff who will do the best job of listening to Microsoft, implementing Microsoft’s recommendations, and then conveying the lessons learned to the rest of the staff.

Notice that I didn’t say to send the best developers or the best DBAs.

The lab isn’t about being extremely good at what you do: the lab is about being a good listener, giving the right answers to company-specific questions, and helping Microsoft work together with the company to deliver an improved implementation.

I think our company sent the right mix of people (although more would always be better), but sitting through the sessions, I can see how that would easily go wrong. During the first couple of days, our main MS lab contact said the same thing several times: “I know this is going to be a tough conversation, but we need to talk about doing this process a different way.”

I responded the same way every time: “This is not going to be a tough conversation. We’re not here because we’re doing things right – we’re here because we need help and guidance! Tell us what to do.”

I can totally see that conversation going different ways, though, because as we related Microsoft’s plans back to our home office over the phone, we had some ugly talks. Some folks can be pretty entrenched in the ways they’ve always done things, and they’re not receptive to new ideas.

At the same time, I wouldn’t recommend sending new staff, either: send the staff with the most possible experience with the company’s internal applications and the most in-depth knowledge of how the company does business. For example, one performance improvement that was tossed around briefly was to disable our indexes during ETL, and then rebuild them after ETL finished. Because I’m familiar with how our ETL runs across different time zones, I was able to explain why it wouldn’t work for our business.

Send a project manager, or designate one person to be the PM for the lab. That person is accountable for making sure the lab stays on track, that it meets objectives, and that the staff back at the home office deliver on urgent needs that come up as a part of the lab engagement. I initially thought a PM was a crazy addition to a technical lab, but it was a great idea.

What To Ask Microsoft Beforehand

Find out exactly who will be involved from Microsoft’s end, the extent of their involvement, and their schedules. Our particular engagement was arranged at the last minute, and as a result, we didn’t get the quantity of Microsoft staff that we’d expected. The onsite experts had already been booked for other client engagements, and when we ran into problems, we couldn’t always get the help we needed. Getting the exact list of MS people who will be in each day’s activities helps to set the right expectations as to how much work will be done by the client, versus how much will be done by Microsoft.

Ask Microsoft to set up VPN access ahead of time to the lab for offsite team members who can’t go to the MTC. These team members, like people who can’t leave the office, will still be a big part of the lab engagement and they’ll need to contribute in order for the lab to keep moving forward. We had difficulties with our corporate firewall blocking VPN access to the MTC lab, and to get around it, I had to resort to installing the LogMeIn.com client on all of our MTC lab machines.

Consider a Microsoft SQL Server Health Check First

You can save a lot of time & money by doing a Microsoft SQL Server Health Check beforehand.  Microsoft sends a SQL Server expert to your site, observes your hardware & configuration in your environment, and delivers a set of recommendations.

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:

Before we start looking at SQL Server backup compression software, we need to spend a few minutes looking at the weaknesses of the native SQL Server backup process. In order to judge the fixes, we have to know what’s broken.

Native SQL Server backups take the same disk space as the data.

When we back up 100gb of data with a native backup, we’ll end up with a 100gb backup file. If a database has 100gb allocated, but it’s half empty (like in the case of unused log files), then the backup size will be roughly 50gb – the size of the data.

Large amounts of data take a long time to write to disk.

The slowest thing in the backup process is usually writing the backup file, whether it’s over the network or to local disk. Reads are typically faster than writes, so unless the database is under heavy transactional load at the time of the backup, the reads won’t be the bottleneck. As a result, the more data that has to get written to disk, the longer the backup will take.

We could alleviate that by purchasing faster and faster arrays for our backup targets, but that gets pretty expensive. Our managers start to ask why the DBA’s fastest raid array is being used for backups instead of the live data!

Large amounts of data take a REALLY long time to push over a network to a DR site.

This affects log shipping or just plain copying backup files over the WAN. Compressing the data as little as 25% cuts transmission times by that same amount, and cuts the amount of bandwidth required to replicate the application data. In a large enterprise where multiple applications are competing for the same WAN bandwidth pipe, other teams will ask why the SQL DBA can’t compress their data before sending it over the wire.

We can work around that problem by installing WAN optimization hardware like a Cisco WAAS appliance, but these have their own drawbacks. They must be installed on both ends of the network (the primary datacenter and the DR site), require a lot of management overhead, and they’re expensive. Really expensive.

Another workaround is to compress the backup files with something like WinZip after the backup has finished, but that’s a manual process that has to be automated by the DBA, actively managed, and adds a lag time for the compression before the data can be sent offsite.

SQL Management Studio doesn’t come with reports about the backup process.

Business folks like to say, “You get what you measure.” The idea is that if you start numerically measuring something in an objective way, that number will start to improve simply because you’re focusing on it and talking about it with others. SQL Server native backups are something of a black box: there’s no quick report to show how long backups are taking per database, how often they’re failing, and how long it would take to do a full restore in the event of an emergency.

I find it hilariously ironic that my job as a database administrator revolves around storing precise metrics for others, enabling them to do dashboards and reports, but SQL’s native backup system doesn’t offer any kind of dashboard or report to show its own backup & restore times and successes. SQL 2005 SP2 started to offer some database performance reports inside of SQL Server Management Studio, but they still don’t address the backup/restore metrics.

An ambitious DBA could build their own reports, but they have to manually consolidate data from all of their database servers across the enterprise and keep it in sync. Whew – I get tired just thinking about that. (I should probably subtitle my blog as “The Lazy DBA” come to think of it.)

Cross-server restores are a ton of manual work.

If the DBA wants to bring a development server up to the most current production backup, including transaction logs, they either have to write a complicated script to parse through a list of available backups, or they have to do a lot of manual restores by picking files.

Even worse, in the event of a disaster, the database administrator has to scramble through directories looking for t-logs, writing restore scripts, and hoping they work. Really good DBA’s plan this scenario out and test it often, but let’s be honest: most of us don’t have that much time. We write our DRP scripts once, test them when we have to, and cross our fingers the rest of the time.

That frustrates me because the restore process has been the same since I started doing database administration back in 1999 as a network admin. For years, I looked for the most reliable restore scripts I could find, played with them, spent time tweaking them, and wasted a lot of time. In my mind, this is something that should be completely integrated with the shipping version of SQL Server just because it’s so central to a DBA’s job.

Enough Backup Problems: Show Me Solutions!

So now we’ve seen some of the weaknesses in SQL Server 2005′s native backups. In my next couple of blog posts, I’ll talk about how third party backup compression software gets around these obstacles and offers better features with more capabilities.

Continue Reading with Part 2: Quad Cores are Changing the Game

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:

Hot fresh downloads, get your hot fresh downloads here.

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:

When I’m doing performance tuning on an application in the early stages of its lifecycle (or any app that’s never had DBA attention before), I end up with a ton of recommendations within the first day of performance tuning.  The resulting to-do list can seem overwhelming to project managers and developers, so I include one of the following two sentences as a part of each recommendation:

• This change will improve performance by a percentage, or
• This change will improve performance by an order of magnitude

I know, I know, that phrase doesn’t come up too often, so it helps to check out Wikipedia’s definition of order of magnitude:

“Orders of magnitude are generally used to make very approximate comparisons. If two numbers differ by one order of magnitude, one is about ten times larger than the other.”

So the two sentences translate into:

• This change will improve performance by 10-90%, or
• This change will improve performance by 10-100x

I could use those latter two sentences instead of the “percentage versus order of magnitude” sentences, but those latter sentences make me sound like I’m taking wild, uneducated guesses.  In reality, sure, I am taking wild, uneducated guesses, but on an informed basis – I’m just not putting a lot of time into categorizing the improvements.

Jeff Atwood’s excellent Coding Horror blog has a two-part post about estimation that should be required reading for every DBA.  Part 1 is a quiz, and Part 2 explains the answers.

So why am I leaving so much gray area in my recommendations?  Why break suggestions into such widely varied categories?  Is it smart to lump a 10% improvement in with an 80% improvement?  In the early stages of performance tuning, yes, because the DBA can’t necessarily predict which changes will be the easiest to implement, or the order in which they’ll be implemented.  When a database administrator first looks at an application, queries, stored procedures and database schema, some things pop out right away as massive opportunities for performance gains.  These recommendations are so instrumental to application performance that they often have wide-ranging impacts across the entire app.  After those changes are made, everything speeds up so much that the other recommendations have even less of an impact than they might have originally had.

For example, in a project I’m currently tuning, I found that the three largest tables in a database (which had ten times more records than all of the remaining tables combined) were constantly queried by a single field, the equivalent of a DivisionID integer field.  All of the queries hitting those tables included the DivisionID, and the application frequently did huge update statements that affected all records with a single DivisionID number.  Partitioning those three tables by DivisionID and putting each DivisionID on its own set of disks would result in a staggering performance improvement and a tremendous increase in concurrent nightly ETL processing, since more divisions could run simultaneously.

I made other performance recommendations as well, but frankly, if the developers implemented every other recommendation except the partitioning, they would still have been struggling with their nightly windows, and the implementation time would have put the project way behind on their deadlines.  On the other hand, if they just implemented the partitioning, they would sail through their nightly windows and make their project delivery deadline.  That’s the definition of an “order of magnitude improvement.”

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: