So You Want to Work at Microsoft?

I love SQL Server. ^[1]

I love working for Microsoft. ^[2]

For me, these two loves are two things that go well together.

I also love competition.

If you love SQL Server as much as I do (and if you don’t mind competition) you could have the opportunity to see if you love working for Microsoft as well, and have a fun experience while making it happen, by participating in the “Be the Next Microsoft Employee” show. I won’t go into the details – you can click on the link for that – but these are the highlights that leap out at me as being particularly cool:

• The show is focused on real-world SQL Server professionals
• Finalists get to attend TechEd North America in Orlando (to get introduced to the public audience as a finalist) and to Microsoft campus in Redmond (for job interviews) with Microsoft picking up the tab
• The winner has a shot^[3] at a Service Engineer position with Microsoft’s internal IT organization, MSIT^[4]
• There are many foodie tie-ins to the show: Not only does the theme remind me of one of my family’s favorite TV shows – The Next Iron Chef^[5] – but finalists will get to eat at Elemental while they’re in town for their interviews^[6]

Are you interested yet?

If so, follow the instructions to enter, and post a comment to let me know.

 

[1] This should surprise no one.

[2] Hopefully this won’t surprise too many people either.

[3] Since this is a real job for the real Microsoft, there’s no guarantee that anyone will get a job offer, just like with any interview.

[4] MSIT is one of biggest IT organizations in the world, and they work on some of the coolest projects I’ve seen. They’re often early adopters of Microsoft products and technologies, participating in TAP/”dogfood” programs and regularly deploy pre-release versions of products like SQL Server into production environments. Speaking of a member of the SQL Server team this is super valuable, because it provides us with real-world feedback that makes the RTM versions of the product better. Speaking as an ex-BI consultant this is super exciting, because how many clients or projects let you stay at the cutting edge of technology and make a real difference in the products you use and love.

[5] Not unlike the Iron Architect competition from way back in 2007, for those who remember back that far.

[6] They should feel free to invite me along, especially if the show organizers are paying for dinner ;-)

Indexes and SSIS Data Flow Performance

Wow – it’s been a while, hasn’t it?

As you may know, I took a job with Microsoft back in October. It’s been a fun (and insanely busy) five months; the work is challenging and diverse, the team I work with is talented and motivated and there is always something new and interesting to learn and/or do. What’s not to like?

In fact, even though BI isn’t a core component of my job role, I have been working with SSIS and the rest of the SQL Server 2008 BI tools to get easier access to some of the data I need to make better decisions and to support the recommendations I make with some “real numbers.”[1] It’s an easy way to add some value to existing data, and for me it’s a fun way to keep my BI skills from getting too rusty.

So… I re-learned an interesting lesson the other day: Don’t load data into indexed tables.

One of the SSIS packages I’ve built extracts data from a set of text files and loads it into a set of SQL Server tables.[2] There is around 20 GB worth of text file data that ends up in one particular table. When I ran this package Monday evening, I found it was still running Tuesday morning, which was not expected. I looked around in SSMS and noticed that there was one non-clustered index on this table. I had added it recently and had not dropped or disabled it before running the package. So when the package finished running, I took a look at the execution results and found this:

DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  6:03:50 PM
Finished: 9:54:20 AM
Elapsed:  57028.7 seconds

That’s right – the package took nearly 16 hours to run. Ugh!

So I re-ran the package this morning, after having ensured that all indexes on this table had been dropped. And the result?

DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  9:49:21 AM
Finished: 10:41:15 AM
Elapsed:  3114.05 seconds

That’s right – the package completed in well under an hour, taking roughly 5.5% of the time it took to execute the package against the indexed table.

But wait, you say – that’s not a fair comparison. You still need to rebuild that index!

You’re right, of course – the time to rebuild the index needs to be considered before we can make any apples-to-apples comparison. So what I did was to re-create all of the indexes on this table (four indexes, each with included columns, for a total index size of 15.8 GB) which took an hour and 39 minutes. Even though this is longer than the package execution itself, the total end-to-end execution time is still only around 15.9% of the package execution time with one index on the target table. In the end we achieved an 84% reduction in execution time.

So what’s the lesson?

Drop or disable your indexes before loading data into your tables, and then re-create or enable them once you’re done. If you don’t, then SQL Server will keep incrementally rebuilding the indexes while your package executes, thus killing your performance.

This isn’t new advice – this has been a best practice for bulk loading data into SQL Server for as long as I can remember. But it is something that I have not blogged on before, and since failing to follow this advice bit me this week I thought I would take the time to share my pain with the world in hopes that someone else can avoid it.

And, of course, it’s good to blog on SSIS again. Hopefully I’ll be posting again before another few months have passed…

[1] Please note that I’m not calling this a “BI solution” here. At this point I am more in a discovery phase than anything else. The components I’ve developed, while functional, will probably get thrown away once they have served their purpose as prototypes and learning tools. All of the performance numbers come from running the packages on a laptop, with both the target data database’s data file and the source text files on the same external 5,400 RPM USB hard drive. No, I wouldn’t do this in production either. ;-)

[2] The astute reader will note that this is in flagrant violation of the “each package should only load data into one table” best practice that I talk about whenever I have someone who will listen. The scenario in which I’m working here provides an interesting exception to this guideline, and please trust that I did think hard before going against my own advice.

One More Reason to Attend

I've posted a few times already[1] about the SSWUG Business Intelligence vConference that I have been helping to organize. Well, the conference is now less than a month away, and there is more news to share:

We're giving away a copy of Microsoft Visual Studio Team System 2008 Team Suite with MSDN Premium to one lucky attendee.

That's right - the big one. This is the ultimate version of Microsoft's MSDN subscription, with a suggested retail price of $10,939. If you're a software developer or BI professional, this package has everything that you need to develop for the Microsoft platform, and then some.

If you'd like a chance to win this MSDN subscription, just register for the SSWUG Business Intelligence vConference. For just $100 you get:

• 30 amazing technical sessions from 10 amazing speakers.
• The ability to watch the sessions either during their multiple scheduled timeslots, or on demand for up to 14 days after the conference is over.
• Online chat and Q&A with the speakers during the scheduled sessions.
• Downloadable slides, transcripts and demo files so you can continue the learning once the session is done.
• And here's the one that blows my mind - a $150 discount on the Fall SQL Connections conference in Las Vegas. So if you're planning to attend the SQL Connections conference, the SSWUG Business Intelligence vConference more than pays for itself.

So what are you waiting for? This vConference is going to be amazing, and we'd love to see you there!

[1] For example:

• BI for the Virtual Guy
• Check Out This Lineup!
• And also a mention in What Happens in BIDS, Stays in BIDS

Check Out This Lineup!

I've posted before about the Business Intelligence Virtual Conference I'm helping to organize. Even though I have not had much to say about this exciting event in the last few weeks, this doesn't mean that I haven't been feverishly busy making sure that the conference will be great. We're still finalizing the session schedule, but we have the speaker list nailed down[1]. Check this out:

• Donald Farmer: Donald is the Principal Program Manager for SQL Server Data Mining at Microsoft and was the Program Manager for SQL Server Integration Services for the SQL Server 2005 RTM release. Donald is always a much sought-after and highly rated speaker, especially when he's talking about his favorite topics like data mining and fish farming.[2]
• Brian Knight: Brian is a SQL Server MVP and the author of multiple books on SQL Server Integration Services. He's presented regularly at major conferences like TechEd and PASS, and is a great speaker all around.
• Ted Malone: Ted is a Visual Studio Team System MVP, but knows more about the Microsoft BI stack than most SQL Server MVPs I know. Ted is also a great speaker who has presented at various conferences on lots of SQL Server related topics.
• Matt Masson: Matt is a developer on the SQL Server Integration Services team at Microsoft, and worked at Cognos before joining Microsoft. As an SSIS insider, Matt has great insight into the inner workings of the product, and will be sharing them during his sessions.
• Sonya McNeal: Sonya is a Microsoft Certified Trainer and consultant who specializes in the Microsoft BI stack. She presented some of the highest rated instructor led labs at the TechEd conference in Orlando this June, and will be bringing her many years of training and presenting experience into play for the virtual conference.
• Scot Reagin: Scot is a SQL Server MVP and a mentor with Solid Quality Mentors with more than 20 years experience in the database and BI field. Scot has presented at many major conferences including TechEd, PASS and SQL Connections.
• Matthew Roche: If you're reading my blog hopefully you have some idea who I am, but just in case, I'm a SQL Server MVP, MCT and experienced BI speaker and consultant. I'm honored to be the conference chair for this conference, and will be doing everything in my power[3] to ensure that this conference sets the bar for BI conferences to come.
• Craig Utley: Craig is a mentor with Solid Quality Mentors, and used to be a Program Manager on the SQLCAT team at Microsoft and is the author of several books. These guys are the best of the best - they're the ones that get called in when no one else can solve the problems. Craig is also a regular presenter who can make even the most complex BI topics easy to understand.
• Erik Veerman: Erik is a SQL Server MVP and a mentor with Solid Quality Mentors who has co-authored several books on SQL Server Integration Services and is responsible for the SSIS ETL best practices in Microsoft's Project REAL. Erik is a regular author and presenter on all facets of the Microsoft BI stack.
• John Welch: John is a SQL Server MVP and is the Chief Architect at Mariner, where he is responsible for the full end-to-end Microsoft BI stack. John is an experienced presenter with deep insight into all of Microsoft's BI products.

What an amazing lineup - I can't adequately express how excited I am to be working with this team. Each speaker will be presenting three sessions (and I'm just as excited about the session list as I am about the speaker list - I can't wait to share it with you) for a total of 30 sessions plus three keynote presentations - one for each day of the conference.

And remember - the entire virtual conference is just $100 for the full three days, and as I mentioned in an earlier post, if you attend the virtual conference you also get a $150 discount off the Dev Connections Fall 2008 Conferences this November in Las Vegas.

How could it get any better than this?

[1] As of this writing, the speaker list on the conference web site isn't complete - we're still waiting on a photo from Matt Masson, but everything else is there.

[2] Don't ask. Trust me. ;-)

[3] And of course, because I listen to Manowar, my power is pretty much limitless.

What Happens in BIDS, Stays in BIDS

That's right - it's time to start planning for the Fall 2008 SQL Server Connections Conference in Las Vegas. It's going to be held at the Manadalay Bay Resort and Casino from November 10th through November 13th, and there are lots of excellent sessions scheduled. In fact, I will be delivering two SQL Server Integration Services sessions:

SQL Server Integration Services Development Best Practices
Are you tired of feeling like you’re making the same mistakes over and over again? Would you like to have a roadmap that outlines the pitfalls you’re likely to encounter when building ETL solutions with SSIS? Then this session is for you! You’ll learn how to get the most from the SSIS tools and platform through a set of SSIS development best practices from a battle-scarred database and BI consultant who has survived the rough projects and lived to tell the tale.

SQL Server Integration Services Performance Tuning and Optimization
SSIS packages have many capabilities, from control flow to event handlers to scripting. But the SSIS data flow is where the decisions you make will have the greatest impact on the performance of your packages. In this session, you’ll learn what’s going on under the hood in the SSIS data flow pipeline, and how to take advantage of that knowledge to make your packages perform better. You’ll also learn general tips and tricks to improve SSIS package performance and how to get the most out of your packages.

Sound like fun? Well, it gets even better!

Do you remember the Business Intelligence Virtual Conference I mentioned a few weeks back? I'll post more information about the virtual conference once we get the session schedule finalized, but for now you should know that if you attend the Business Intelligence Virtual Conference - $100 for three days worth of amazing content - you will get a $150 discount for the Fall SQL Server Connections conference.

How cool is that? If you're planning on attending the SQL Server Connections conference (or any of the Dev Connections conferences being held at the same time, because a ticket to one gets you admission to all of them) then we're essentially paying you $50 to attend the Business Intelligence Virtual Conference. That's like $50 better than free, which in my book is pretty darned cool.

So start planning now, and I'll look for you in Las Vegas!

Good Times, Bad Times

You know SQL Server 2008 has its share...

First, the bad news - Microsoft has made the following statement related to a bug in pre-release builds of SQL Server 2008 that prevent the SQL Server service from starting (or being installed!) on February 29th:

We have recently discovered an issue with SQL Server 2008 CTPs that result in SQL Server 2008 not starting or installing on Feb 29 GMT only. We recommend that you do not run, install or upgrade this CTP on Feb 29 GMT to minimize any impact in your environment. You can install starting on March 1 GMT. If you have an immediate issue that cannot wait until march 1st GMT contact sqlbeta@microsoft.com before taking any further steps.

Ugh. Talk about bad timing. The official launch for the product was two days ago, so it's likely that quite a few people are attempting to install and use SQL Server 2008 for the first time today.

Still, it could be worse, right? Imagine the chaos in Los Angeles if the launch were today and all of the demos that depend on SQL Server 2008 were blowing up in front of a live studio audience. I shudder (and yet I also laugh at the same time - go figure) to think of that possibility.

And the good news? Microsoft and Unisys have announced new, world-record ETL performance using SQL Server Integration Services 2008. The bottom line is that they've loaded 1.18 TB worth of flat file data in 1794 seconds - just under half an hour. And yes, that's correct - TB and not GB. This would average out to over 2 TB worth of data per hour.

Wow.

I won't try to reproduce all of the details here - instead, check it out on the SQL Server Performance blog here: http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx.

If you ask me, the good news far outweighs the bad. The bug only matters today and will be fixed before RTM. The amazing performance improvements in SSIS, SQL Server and Windows Server will last a lifetime.

SSIS Performance Tuning Strategy

Similar to my post on SSIS Performance Tuning Techniques, this resource is neither mine nor new, but it looks like a lot of people don't know that it exists. Check out this "SQL Server 2005 Integration Services: A Strategy for Performance" white paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssisperfstrat.mspx#E6G

According to the abstract, "This white paper covers several interesting and unique methods for managing metadata using built-in features in SQL Server 2005 Integration Services, Analysis Services, and Reporting Services. The paper explains such concepts as data lineage, business and technical metadata, auditing, and impact analysis. " This is really weird to me, because there's nothing about SSAS or SSRS in here at all, especially since there's also nothing about data lineage or metadata management. Copy and paste gone wrong, perhaps?

To me the most interesting thing here is the "OVAL" (Operations , Volume, Application, Location) approach for evaluating and tuning SSIS performance. Lots of people on the MSDN SSIS Forums have been asking lately about SSIS performance, and either no one is bothering to look, or else this white paper is difficult to find.

And for people too lazy (or should that be "visually oriented") to read through the whole thing, Donald Farmer (then the SSIS Program Manager) did an excellent presentation at TechEd last year about this OVAL approach, and you can view it on demand here: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032298087&CountryCode=US

But remember: Performance tuning is a complex art that is a superset of all of the other things that go into building software on any platform, and SSIS is no exception. In order to do it right, you need to understand how the platform works and how your software interacts with it, and have a methodical approach for identifying and refining the scope of performance problems. Although it's wonderful to have an approach like OVAL to use, without an understanding of how SSIS works under the hood, it's not going to do you much good...

SSIS Performance Tuning Techniques

This article is neither new nor my own, but I've shown it to a few people lately and despite their having worked with SSIS for months, none of them had seen it.

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

This is a white paper that describes how the SSIS run time engine and data flow engine work, and how to take advantage of that knowledge to build better performing SSIS packages. It has excellent coverage of execution trees and memory buffers, and how they are used "under the hoods" in your data flow.

If you haven't read it before, please check it out today and tell a friend. It's required reading for anyone building SSIS packages in the real world.

Put a Consultant out of Business

Performance tuning is something of a black art. It's one of those things that before you can do it, you need to understand - really understand - what's going on, not only in your application, but in each of the platform components that makes up your application.

SQL Server has gotten easier to tune with every release. SQL Server 2005 has lots of features built in to make it easier to diagnose performance problems and to build better performing applications from the beginning. But SQL Server 2005 also has lots of architectural changes under the hood, which means that in order to dig deep, you need to understand those changes.

But Microsoft has recently "stealth released" a set of SQL Server Reporting Services reports that provide deeper and more actionable insight into the performance of your SQL Server than has ever been possible before without having access to a top-tier performance consultant. The SQL Server 2005 Performance Dashboard Reports report pack integrates directly into SQL Server Management Studio (using the new "Custom Reports" feature in SP2) and use realtime performance data from SQL Server's dynamic management views (DMVs) to identify poorly-performing queries and processes, show you the details on the problem, and (this is the big one) identify steps to take to resolve them. This often includes SQL DDL code to create indices, statistics, or whatever changes that can make your system run better.

Realistically, as a SQL Server consultant I'm not really afraid of being made obsolete by these amazing tools. There are some decisions that can only be made, and some problems that can only be solved by a truly deep knowledge of the platform. These reports are a major advance in moving the point at which the average SQL Server DBA needs to ask for help, and that means that the consultant is more likely to have a really interesting problem to solve when he is called in. And that can only be a good thing!