SQL On The Side

A few months ago, some colleagues and I tried a new BBQ place in the neighborhood. We could not find any reviews, but how bad could it be, right? I mean, these guys must know what they are doing, or they wouldn’t have gone into business. Wow, the place was amazingly bad. We just wanted some decent BBQ – our expectations had started out hopeful, but low, but we were still disappointed. Who goes into the BBQ business with sauce that tastes like ketchup and grape jelly mixed with some brown food coloring? And how was the meat beyond bad – no matter what we each ordered?

A couple of months later, I cautiously agreed to trying another new BBQ restaurant in the neighborhood with my spouse, Mr Grinch. We ran into friends who were leaving as we approached the building. After being assured by all 4 members of the family that we would definitely love it, I asked the 5-year-old what he recommended. He carefully thought over the array of choices offered by the restaurant, before his eyes lit up and he told me, “I know. You should have the meat!” I loved the simplicity of his answer, and knew I was likely to love everything there. I was right – this place had it figured out – the meat, the sauce, even the side-dishes.

So, what’s my point here, right? How many of us “professional” developers approach a new project in a similar way? Paying special attention to the UI, to the color scheme, the platform, the hardware, while giving very little thought to the database. And somehow, when the application is slow, it is always the database’s fault – and never the developer’s fault. I know that software developers never deliberately create a bad database, but do tend to think choosing a specific database technology will magically take care of any and all data storage considerations. In reality, choosing a specific database technology is like choosing whether or not your BBQ sauce will be vinegar or mustard based, and stopping there.

May we all become developers who can have the humility to admit to the things we don’t know, and allow the appropriate professional to take care of the details in the sauce. Here’s the secret: you ultimately end up looking smarter because people trust you only mix up the sauce when you know what belongs in it.

In addition to spending time honing my database skills, there are 2 other things that have really paid off for me professionally.

1. I always spend a lot of time as a new employee or on a new project learning and understanding the business and its customers.
2. I have spent a lot of time learning about personality types and how that affects communication styles, then consciously applying it to my interactions with others.

For the technical side of things – I started out doing to web development, where I focused on HTML, JavaScript, and ASP. I found databases interesting, but had no idea one could specialize in them. Over the years, the DBAs I worked with allowed me to design and modify pieces of the database that matched up with development I was doing. They would offer constructive criticism, which allowed me to learn even more. I frequently referred to SQL Server Books Online, so I could figure out as much as possible on my own before going to the DBAs with (hopefully) intelligent questions.

One of those DBAs eventually moved on to another company, and encouraged me to apply there as a database developer when a position opened. I was very hesitant, but he assured me I could do the work. It turned out that I was actually good, and I really enjoyed the puzzles I was solving. I continued to avidly study but now it was all focused on SQL Server.

I am forever grateful to those who were so patient with me. Databases and all they include are now a full-time passion of mine. I continue to spend time by synthesizing my experience with new information from books, blogs, and colleagues.

Ultimately, I believe that both my professional success as well as my personal work satisfaction is a result of developing areas of knowledge outside of technology, so I could become more than a code monkey.

Leadership’s Two Keys: Service and Sacrifice

     Photo by Robert Huffstutter

Yesterday evening, I was looking through my LinkedIn feed and saw the latest Ken Blanchard article on the “Four Leader Behaviors That Build Trust”. I like to read that kind of business/leadership stuff, so I clicked on the link and what I read prompted this post. Over the last few years, I’ve read a lot of leadership material, whether from Blanchard, Covey, or Welch. Some stuff they have to say has been helpful, but I think in all respects they somewhat miss the mark. You see, I’ve also had the opportunity to experience some really good leaders and some not so good. Heck I’ve been both types of leader over that time as well. From those experiences, I have learned that ultimately leadership comes down to two things: service and sacrifice.

Service Shows Your Genuine Concern

At the end of the day, leadership is about getting people to follow you. You can’t be out there alone charging the hill, you’ve got to have a team to back you up. Regardless of what techniques or systems you’ve learned, if the people you’re trying to lead don’t feel that you care for them, you aren’t going to lead them anywhere. So, the first key is to help them feel your care. You can try to win and influence your way around this, but ultimately that just comes off as insincere. The only way they’re going to feel you care is for you to develop that concern for each team member’s well being. This isn’t about making them admire you, this is about you developing genuine concern for the people that are slogging through the muck to make you look good. And the only way to develop that genuine concern is to forget about yourself and start serving them. Be on the look out for their pain points. Watch out for their careers and help them progress to what they want to become. Invest your time in them and their aspirations. That will build loyalty faster than any other technique or system. Care enough to find out where your team members are struggling and work to fix those issues and they won’t just follow up the hill, they’ll carry you up it.

Sacrifice Leads to Effectiveness

Now, you may be telling yourself that service like that can take a lot of extra time and you’ve already got too much work and not enough day. Well, that’s where the second key comes in and that is sacrifice. Of the leaders I’ve worked with over the last few years, the ones that I believe were truly effective were the leaders exemplified this trait. They realized that the success or failure of the team was their responsibility and they showed it by making it a priority over other things. The least effective leaders are the ones that are chronically absent. For you, this kind of sacrifice means being the first one in the office and the last one to leave. It means telecommuting less and communicating more, a lot more. The effective leaders slog through the mud too, when the team needs that kind of support. They make sure things are set up for success and that success is rewarded and celebrated. And above all they give the credit to the team over themselves. It’s a lot of extra work, but they’re willing to do it because they care about the welfare of the team.

Real Leadership is Worth The Struggle

Like I said, over the last few years, I’ve seen a lot of leaders and I’ve been a leader for a time myself. I haven’t always been successful at these two things. I know there have been times that I have been anything but service-minded to certain team members. I hope that one day they can forgive me for that. In the meantime, as I start another leadership role, I hope I can keep service and sacrifice in mind as I go forward. Because, to me the reward isn’t in completion of a job well done, but in celebrating the success with the team that made it happen.

Remember, if you like the posts you find here, you can always subscribe to our RSS or via email subscription.

–Dandy

Update: I received some feedback from Phil Brammer that it might be a good idea to provide some recommendations at the end of the post on what you can do as you work with the SSIS Catalog. I think that was good advice, so I’ve updated the post to reflect my recommendations for you, and a couple of recommendations for Microsoft. Enjoy, and thanks Phil!

Microsoft’s Integration Services Management Offering

     Photo by Chris Gladis

With the release of SQL Server 2012, Microsoft has added some nice features. Recently I’ve spent time with one of those, the SQL Server Integration Services (SSIS) Catalog and its supporting database. For those of you unfamiliar with it, the SSIS catalog is the new model for logging in SSIS as well as a new deployment model.

If you are new to the SSIS Catalog and want a good overview of how it works, hit up this post by Jamie Thomson (blog|twitter) over at SQLBlog.com. Jamie walks you through the key concepts and how to access the data stored in the SSIS Catalog database. Today’s post gives you some highlights of the feature, as well as points out some issues we’ve seen working with the catalog.

Now With Logging Baked Inside

In ETL, the more logging you have, the easier it is to troubleshoot issues. Prior to 2012, you could accomplish this in a couple of ways. You could either include logging tasks within your control flow, or configure the event handlers for existing tasks within your packages. In either case, the logging mechanism of choice would write out to custom logging tables in your database¹.

With the new catalog, SSIS tracks all events during the execution of a package and writes them out to the catalog database. This means that you don’t have to customize each database and package in order to support logging. Logging is now baked into Integration Services by default.

This idea bears repeating. SSIS logging to the catalog means you don’t have the overhead and complexity of maintaining logging code across your entire enterprise deployment. Integration Services is doing it for you. While you may only have one or two packages, larger organizations often have hundreds of packages to maintain. This feature saves a lot of time.

The SSIS Catalog also provides configuration of the logging levels. You can choose between Verbose, Performance, Basic, and None. Each level provides different levels of detail in your logs. Just be aware that your choice can affect package execution performance. Fortunately, you can configure the level at run time if you choose. This means you can set your packages to Basic as the default and run Performance or Verbose when you need to troubleshoot issues.

Although It May Be Undercooked

Of course the first version of any feature is going to have some issues and our first is performance. Working with the catalog, some members of the SQL Server community noticed slow query performance and deadlocking issues. Ultimately, this led Phil Brammer (blog|twitter) to post recommendations for optimizing the SSIS Catalog by adding neglected indexes. On our team, we implemented Phil’s recommendations to great effect. However, I did extend them to more fully cover some of our queries. As a disclaimer, modifying the catalog database may put you into an unsupported configuration.

Even with the additional indexes, you need to know that querying the data may not be as fast as you’d like. We had to be careful querying from certain tables to avoid excessive query times, or worse blocking executions from completing. One of our key findings was to make sure we weren’t locking on these tables at all in our queries. We did this by adding nolock hints, but it is possible that changing to a Read Committed Snapshot Isolation level could help as well, we just haven’t tested that out yet.

Also note, the SSIS Catalog retains data for a year by default. This setting could cause problems if you do a lot of processing. Not only could the size of your catalog database grow faster than you expect, but your queries will get progressively slower as the data increases. At present, we have queries that return quickly for small sets of packages but never return past a certain threshold. You should work out the best retention policy for your organization and set the configuration value appropriately. You may also want to replicate data out into a separate reporting database for trending analysis.

Finally, the built in reports are very basic in nature. SSIS includes some canned reports showing package executions, runtime settings, and event messages from each package execution. Unfortunately, these reports aren’t really responsive, and the layout often makes it hard to interpret the data. To answer this, Jamie Thomson created a reporting pack that makes the information much easier to consume. It also includes executions over time, which is nice since that is missing from the reports included with the catalog.

Don’t Throw It Out Just Yet!

The SSIS Catalog is a much needed feature for the management of large SSIS deployments. Embedded logging, without having to create your own for each package, is a huge benefit. Unfortunately, there are performance issues with this first version of the feature. To deal with those issues, you can follow these recommendations:

1. Be concious of pulling data from the catalog views as efficiently as possible in your queries. This means filtering down your sets as quickly as possible and making sure you are only pulling data you really need.
2. Perform any analysis of the data in a separate workspace. You could use temporary tables or a separate database, but you shouldn’t be performing the analysis directly on the catalog views. It will be slow for you and can cause executions to be blocked.
3. Index the catalog tables for performance where necessary. Phil’s post on the catalog indexes is excellent and will work for most cases. Just remember that implementing them may put you in an unsupported situation.
4. Use query hints or database settings to minimize the locks placed on the catalog views while querying. In our situation we used nolock hints to make sure we were interfering with the catalog as little as possible. You may also be able to use Read Committed Snapshot Isolation, although that would take a lot of thorough testing.
5. Finally, you should define a retention policy for the data in the catalog that is tailored to your organizations needs. Most of us probably don’t need to keep 365 days of executions in the database to get the information we need. If you do, consider archiving off that data on a regular basis into a separate database to reduce the load on the catalog database.

These recommendations will help make the current version usable. There are a couple of things that I would like to see Microsoft respond to as possible upgrades in the next version:

1. While the execution_path is extremely cool, at times it is unwieldy to work with. The datatype is large as an nvarchar(max) and requires string manipulation to follow the descendant components in the execution tree. I would be interested to see if this information couldn’t be stored using a more suitable datatype such as hierarchyID. That may help walk the execution tree more efficient while also helping when joining to other tables within the catalog database. I am not sure if this is even feasible, but I think it’s worth looking into.
2. The idea of using Read Committed Snapshot Isolation appeals to me. It would mean we could query the catalog views without fear of adversely affecting package execution. That being said, it would require some very thorough testing to make sure that the change in isolation levels didn’t affect processing itself. I would like to hear whether the SSIS development team have considered this change and whether it is even a viable option. If they are planning on testing it, or already have, I would also love to see them publish their results.

Ultimately, your organization must determine whether the benefits of embedded logging outweigh the required workarounds for performance. After spending time with the catalog over the last few weeks, we have decided use both custom logging and the SSIS catalog for the time being. We feel the need to do more testing before committing to transition completely over. Hopefully we’ll have an answer before Microsoft releases the next version.

Remember, if you like the posts you find here, you can always subscribe to our RSS or via email subscription.

–Dandy

1. Again, Jamie has a really good post on implementing event handlers for logging, if you’re interested. As a side note, if you’re doing SSIS work and not following Jamie’s blog, you should be.