Ajarn's SQL Corner

How to Manage Technical Employees

Ajarn Mark Caldwell — Fri, 02 Mar 2012 06:28:45 GMT

In my current position as Software Engineering Manager I have been through a lot of ups and downs with staffing, ranging from laying-off everyone who was on my team as we went through the great economic downturn in 2007-2008, to numerous rounds of interviewing and hiring contractors, full-time employees, and converting some contractors to employee status. I have not yet blogged much about my experiences, but I plan to do that more in the next few months. But before I do that, let me point you to a great article that somebody else wrote on The Unspoken Truth About Managing Geeks that really hits the target. If you are a non-technical person who manages technical employees, you definitely have to read that article. And if you are a technical person who has been promoted into management, this article can really help you do your job and communicate up the line of command about your team. When you move into management with all the new and different demands put on you, it is easy to forget how things work in the tech subculture, and to lose touch with your team. This article will help you remember what’s going on behind the scenes and perhaps explain why people who used to get along great no longer are, or why things seem to have changed since your promotion.

I have to give credit to Andy Leonard (blog | twitter) for helping me find that article. I have been reading his series of ramble-rants on managing tech teams, and the above article is linked in the first rant in the series, entitled Goodwill, Negative and Positive. I have read a handful of his entries in this series and so far I pretty much agree with everything he has said, so of course I would encourage you to read through that series, too.

Broken Views

Ajarn Mark Caldwell — Tue, 27 Dec 2011 07:16:30 GMT

“SELECT *” isn’t just hazardous to performance, it can actually return blatantly wrong information.

There are a number of blog posts and articles out there that actively discourage the use of the SELECT * FROM …syntax. The two most common explanations that I have seen are:

Performance: The SELECT * syntax will return every column in the table, but frequently you really only need a few of the columns, and so by using SELECT * your are retrieving large volumes of data that you don’t need, but the system has to process, marshal across tiers, and so on. It would be much more efficient to only select the specific columns that you need.
Future-proof: If you are taking other shortcuts in your code, along with using SELECT *, you are setting yourself up for trouble down the road when enhancements are made to the system. For example, if you use SELECT * to return results from a table into a DataTable in .NET, and then reference columns positionally (e.g. myDataRow[5]) you could end up with bad data if someone happens to add a column into position 3 and skewing all the remaining columns’ ordinal position. Or if you use INSERT…SELECT * then you will likely run into errors when a new column is added to the source table in any position.

And if you use SELECT * in the definition of a view, you will run into a variation of the future-proof problem mentioned above. One of the guys on my team, Mike Byther, ran across this in a project we were doing, but fortunately he caught it while we were still in development. I asked him to put together a test to prove that this was related to the use of SELECT * and not some other anomaly. I’ll walk you through the test script so you can see for yourself what happens.

We are going to create a table and two views that are based on that table, one of them uses SELECT * and the other explicitly lists the column names. The script to create these objects is listed below.

IF OBJECT_ID('testtab') IS NOT NULL
   DROP TABLE testtab
go
IF OBJECT_ID('testtab_vw') IS NOT NULL
   DROP VIEW testtab_vw
go   
IF OBJECT_ID('testtab_vw_named') IS NOT NULL
   DROP VIEW testtab_vw_named
go   
CREATE TABLE testtab (col1 NVARCHAR(5) null, col2 NVARCHAR(5) null)
INSERT INTO testtab(col1, col2)
VALUES ('A','B'), ('A','B')
GO
CREATE VIEW testtab_vw AS SELECT * FROM testtab
GO
CREATE VIEW testtab_vw_named AS SELECT col1, col2 FROM testtab
go

Now, to prove that the two views currently return equivalent results, select from them.

SELECT 'star', col1, col2 FROM testtab_vw
SELECT 'named', col1, col2 FROM testtab_vw_named

OK, so far, so good. Now, what happens if someone makes a change to the definition of the underlying table, and that change results in a new column being inserted between the two existing columns? (Side note, I normally prefer to append new columns to the end of the table definition, but some people like to keep their columns alphabetized, and for clarity for later people reviewing the schema, it may make sense to group certain columns together. Whatever the reason, it sometimes happens, and you need to protect yourself and your code from the repercussions.)

DROP TABLE testtab
go
CREATE TABLE testtab (col1 NVARCHAR(5) null, col3 NVARCHAR(5) NULL, col2 NVARCHAR(5) null)
INSERT INTO testtab(col1, col3, col2)
VALUES ('A','C','B'), ('A','C','B')
go
SELECT 'star', col1, col2 FROM testtab_vw
SELECT 'named', col1, col2 FROM testtab_vw_named

I would have expected that the view using SELECT * in its definition would essentially pass-through the column name and still retrieve the correct data, but that is not what happens. When you run our two select statements again, you see that the View that is based on SELECT * actually retrieves the data based on the ordinal position of the columns at the time that the view was created. Sure, one work-around is to recreate the View, but you can’t really count on other developers to know the dependencies you have built-in, and they won’t necessarily recreate the view when they refactor the table.

I am sure that there are reasons and justifications for why Views behave this way, but I find it particularly disturbing that you can have code asking for col2, but actually be receiving data from col3. By the way, for the record, this entire scenario and accompanying test script apply to SQL Server 2008 R2 with Service Pack 1.

So, let the developer beware…know what assumptions are in effect around your code, and keep on discouraging people from using SELECT * syntax in anything but the simplest of ad-hoc queries.

And of course, let’s clean up after ourselves. To eliminate the database objects created during this test, run the following commands.

DROP TABLE testtab
DROP VIEW testtab_vw
DROP VIEW testtab_vw_named

SQL Server Developer Tools – Codename Juneau vs. Red-Gate SQL Source Control

Ajarn Mark Caldwell — Wed, 26 Oct 2011 06:14:11 GMT

So how do the new SQL Server Developer Tools (previously code-named Juneau) stack up against SQL Source Control? Read on to find out.

At the PASS Community Summit a couple of weeks ago, it was announced that the previously code-named Juneau software would be released under the name of SQL Server Developer Tools with the release of SQL Server 2012. This replacement for Database Projects in Visual Studio (also known in a former life as Data Dude) has some great new features. I won’t attempt to describe them all here, but I will applaud Microsoft for making major improvements. One of my favorite changes is the way database elements are broken down. Previously every little thing was in its own file. For example, indexes were each in their own file. I always hated that. Now, SSDT uses a pattern similar to Red-Gate’s and puts the indexes and keys into the same file as the overall table definition.

Of course there are really cool features to keep your database model in sync with the actual source scripts, and the rename refactoring feature is now touted as being more than just a search and replace, but rather a “semantic-aware” search and replace. Funny, it reminds me of SQL Prompt’s Smart Rename feature. But I’m not writing this just to criticize Microsoft and argue that they are late to the party with this feature set. Instead, I do see it as a viable alternative for folks who want all of their source code to be version controlled, but there are a couple of key trade-offs that you need to know about when you choose which tool set to use.

First, the basics

Both tool sets integrate with a wide variety of source control systems including the most popular: Subversion, GIT, Vault, and Team Foundation Server. Both tools have integrated functionality to produce objects to upgrade your target database when you are ready (DACPACs in SSDT, integration with SQL Compare for SQL Source Control). If you regularly live in Visual Studio or the Business Intelligence Development Studio (BIDS) then SSDT will likely be comfortable for you. Like BIDS, SSDT is a Visual Studio Project Type that comes with SQL Server, and if you don’t already have Visual Studio installed, it will install the shell for you. If you already have Visual Studio 2010 installed, then it will just add this as an available project type. On the other hand, if you regularly live in SQL Server Management Studio (SSMS) then you will really enjoy the SQL Source Control integration from within SSMS. Both tool sets store their database model in script files. In SSDT, these are on your file system like other source files; in SQL Source Control, these are stored in the folder structure in your source control system, and you can always GET them to your file system if you want to browse them directly.

For me, the key differentiating factors are 1) a single, unified check-in, and 2) migration scripts. How you value those two features will likely make your decision for you.

Unified Check-In

If you do a continuous-integration (CI) style of development that triggers an automated build with unit testing on every check-in of source code, and you use Visual Studio for the rest of your development, then you will want to really consider SSDT. Because it is just another project in Visual Studio, it can be added to your existing Solution, and you can then do a complete, or unified single check-in of all changes whether they are application or database changes. This is simply not possible with SQL Source Control because it is in a different development tool (SSMS instead of Visual Studio) and there is no way to do one unified check-in between the two. You CAN do really fast back-to-back check-ins, but there is the possibility that the automated build that is triggered from the first check-in will cause your unit tests to fail and the CI tool to report that you broke the build. Of course, the automated build that is triggered from the second check-in which contains the “other half” of your changes should pass and so the amount of time that the build was broken may be very, very short, but if that is very, very important to you, then SQL Source Control just won’t work; you’ll have to use SSDT.

Refactoring and Migrations

If you work on a mature system, or on a not-so-mature but also not-so-well-designed system, where you want to refactor the database schema as you go along, but you can’t have data suddenly disappearing from your target system, then you’ll probably want to go with SQL Source Control. As I wrote previously, there are a number of changes which you can make to your database that the comparison tools (both from Microsoft and Red Gate) simply cannot handle without the possibility (or probability) of data loss. Currently, SSDT only offers you the ability to inject PRE and POST custom deployment scripts. There is no way to insert your own script in the middle to override the default behavior of the tool. In version 3.0 of SQL Source Control (Early Access version now available) you have that ability to create your own custom migration script to take the place of the commands that the tool would have done, and ensure the preservation of your data. Or, even if the default tool behavior would have worked, but you simply know a better way then you can take control and do things your way instead of theirs.

You Decide

In the environment I work in, our automated builds are not triggered off of check-ins, but off of the clock (currently once per night) and so there is no point at which the automated build and unit tests will be triggered without having both sides of the development effort already checked-in. Therefore having a unified check-in, while handy, is not critical for us. As for migration scripts, these are critically important to us. We do a lot of new development on systems that have already been in production for years, and it is not uncommon for us to need to do a refactoring of the database. Because of the maturity of the existing system, that often involves data migrations or other additional SQL tasks that the comparison tools just can’t detect on their own. Therefore, the ability to create a custom migration script to override the tool’s default behavior is very important to us. And so, you can see why we will continue to use Red Gate SQL Source Control for the foreseeable future.

Summit Time!

Ajarn Mark Caldwell — Mon, 03 Oct 2011 06:06:11 GMT

Boy, how time flies! I can hardly believe that the 2011 PASS Summit is just one week away. Maybe it snuck up on me because it’s a few weeks earlier than last year. Whatever the cause, I am really looking forward to next week. The PASS Summit is the largest SQL Server conference in the world and a fantastic networking opportunity thrown in for no additional charge. Here are a few thoughts to help you maximize the week.

Networking

As Karen Lopez (blog | @DataChick) mentioned in her presentation for the Professional Development Virtual Chapter just a couple of weeks ago, “Don’t wait until you need a new job to start networking.” You should always be working on your professional network. Some people, especially technical-minded people, get confused by the term networking. The first image that used to pop into my head was the image of some guy standing, awkwardly, off to the side of a cocktail party, trying to shmooze those around him. That’s not what I’m talking about. If you’re good at that sort of thing, and you can strike up a conversation with some stranger and learn all about them in 5 minutes, and walk away with your next business deal all but approved by the lawyers, then congratulations. But if you’re not, and most of us are not, I have two suggestions for you. First, register for Don Gabor’s 2-hour session on Tuesday at the Summit called Networking to Build Business Contacts. Don is a master at small talk, and at teaching others, and in just those two short hours will help you with important tips about breaking the ice, remembering names, and smooth transitions into and out of conversations. Then go put that great training to work right away at the Tuesday night Welcome Reception and meet some new people; which is really my second suggestion…just meet a few new people. You see, “networking” is about meeting new people and being friendly without trying to “work it” to get something out of the relationship at this point. In fact, Don will tell you that a better way to build the connection with someone is to look for some way that you can help them, not how they can help you.

There are a ton of opportunities as long as you follow this one key point: Don’t stay in your hotel! At the least, get out and go to the free events such as the Tuesday night Welcome Reception, the Wednesday night Exhibitor Reception, and the Thursday night Community Appreciation Party. All three of these are perfect opportunities to meet other professionals with a similar job or interest as you, and you never know how that may help you out in the future. Maybe you just meet someone to say HI to at breakfast the next day instead of eating alone. Or maybe you cross paths several times throughout the Summit and compare notes on different sessions you attended. And you just might make new friends that you look forward to seeing year after year at the Summit. Who knows, it might even turn out that you have some specific experience that will help out that other person a few months’ from now when they run into the same challenge that you just overcame, or vice-versa. But the point is, if you don’t get out and meet people, you’ll never have the chance for anything else to happen in the future.

One more tip for shy attendees of the Summit…if you can’t bring yourself to strike up conversation with strangers at these events, then at the least, after you sit through a good session that helps you out, go up to the speaker and introduce yourself and thank them for taking the time and effort to put together their presentation. Ideally, when you do this, tell them WHY it was beneficial to you (e.g. “Now I have a new idea of how to tackle a problem back at the office.”) I know you think the speakers are all full of confidence and are always receiving a ton of accolades and applause, but you’re wrong. Most of them will be very happy to hear first-hand that all the work they put into getting ready for their presentation is paying off for somebody.

Training

With over 170 technical sessions at the Summit, training is what it’s all about, and the training is fantastic! Of course there are the big-name trainers like Paul Randall, Kimberly Tripp, Kalen Delaney, Itzik Ben-Gan and several others, but I am always impressed by the quality of the training put on by so many other “regular” members of the SQL Server community. It is amazing how you don’t have to be a published author or otherwise recognized as an “expert” in an area in order to make a big impact on others just by sharing your personal experience and lessons learned. I would rather hear the story of, and lessons learned from, “some guy or gal” who has actually been through an issue and came out the other side, than I would a trained professor who is speaking just from theory or an intellectual understanding of a topic.

In addition to the three full days of regular sessions, there are also two days of pre-conference intensive training available. There is an extra cost to this, but it is a fantastic opportunity. Think about it…you’re already coming to this area for training, so why not extend your stay a little bit and get some in-depth training on a particular topic or two? I did this for the first time last year. I attended one day of extra training and it was well worth the time and money. One of the best reasons for it is that I am extremely busy at home with my regular job and family, that it was hard to carve out the time to learn about the topic on my own. It worked out so well last year that I am doubling up and doing two days or “pre-cons” this year.

And then there are the DVDs. I think these are another great option. I used the online schedule builder to get ready and have an idea of which sessions I want to attend and when they are (much better than trying to figure this out at the last minute every day). But the problem that I have run into (seems this happens every year) is that nearly every session block has two different sessions that I would like to attend. And some of them have three! ACK! That won’t work! What is a guy supposed to do? Well, one option is to purchase the DVDs which are recordings of the audio and projected images from each session so you can continue to attend sessions long after the Summit is officially over. Yes, many (possibly all) of these also get posted online and attendees can access those for no extra charge, but those are not necessarily all available as quickly as the DVD recording are, and the DVDs are often more convenient than downloading, especially if you want to share the training with someone who was not able to attend in person.

Remember, I don’t make any money or get any other benefit if you buy the DVDs or from anything else that I have recommended here. These are just my own thoughts, trying to help out based on my experiences from the 8 or so Summits I have attended. There is nothing like the Summit. It is an awesome experience, fantastic training, and a whole lot of fun which is just compounded if you’ll take advantage of the first part of this article and make some new friends along the way.

Multiple Line Comments in SQL Source Control

Ajarn Mark Caldwell — Tue, 09 Aug 2011 05:39:36 GMT

Another complaint that I made in Part 2 of my previous series on Red-Gate’s SQL Source Control tool was that the textbox to enter your check-in comments was only a single-line box. I wished it had a multi-line comment area for check-in because I like to be more explicit and informative than the average bear. Well, I don’t know when it happened but I just noticed today that it’s in there. You can drag down the resize bar on the screen to make the comment box larger.

Maybe it was in there from the actual PROD release of the version with Vault integration, or maybe I just picked it up with a recent update. I don’t know, but I’m glad it’s there. Thanks!

EDIT: Fixed layout errors and picture size.

SQL Source Control and Custom Change Scripts

Ajarn Mark Caldwell — Tue, 09 Aug 2011 05:19:33 GMT

In part 2 of my previous series regarding Red-Gate’s tool, SQL Source Control, I warned about an aspect of the tool that could cause you to lose data if you were not careful. A few days ago I was allowed a sneak peak* at the upcoming fix which Red-Gate has termed SQL Migrations.

To recap, because SQL Source Control does a point-in-time comparison of your database with the current version in your source control system, there are some types of changes that you can perform which SQL Source Control will interpret differently and which could result in loss of data if their script is used for deployment. The easiest example of this type of change is the renaming of a column or table. For example, suppose you read my rant from several years ago about separating your singular from your plurals, and you have bought into the idea that all table names should be singular, but you have in your database a table named in the plural form of bacon and you want to rename it in its singular form of bacon. No, wait a minute, that doesn’t work. Okay, how about this one, you have a table named People, and now you want to rename it to Person. So, you go ahead and use whatever is your favorite method to rename the table; whether that is using the right-click menu in the object explorer or simply issuing an sp_rename command, and voila it is done. You switch over to the Commit Changes tab and discover that SQL Source Control does not know that you simply renamed an existing table. Instead, all it can tell is that the table People is no longer there, and the table Person now is there, so it sets up the changeset as DROP TABLE People, and CREATE TABLE Person… The problem with that is if you run that in a database that had data in the People table, you just lost all of that data, even though the change that you actually made in development, simply a rename, did not lose any data at all. That would be bad.

But the good news is that the folks at Red-Gate have been working on a way to address that issue, and it is scheduled for an upcoming release (version 3.0, I believe). The solution is an ingenious technique that allows you to override their generated script for the changeset with your own custom script. Now, you still have to be smart enough to realize you need to do this yourself (and the tool reminds you that this might be a good idea) and when you do, the functionality to support you will be there. This is a manual process because there is no way for the tool to know whether you really wanted to drop one table and create a new one, or whether it was this rename issue. So, on the Commit Changes window, there will be an option to mark the changes that SQL Source Control has identified, and tell it that you want to use your own conversion script (or as Red-Gate is calling it, a migration script) in place of their generated scripts (see figure 1 below for example).

Figure 1: Create a Migrations Script from the Commit Changes window.

This will lead to a new editor window (figure 2) where you can write your own T-SQL to make the changes (or presumably paste in a copy of the script you originally wrote for the change in development). There is no significant difference in what you could put in here as compared to what you could put in any query editor window, so if you have a truly complex set of data conversion and migration commands that need to be implemented, just go for it.

Figure 2: Migration Script Editor

One of the keys to using this migration script is that you are custom writing all of the commands that have to take place to convert from one version of your source controlled system to another. That is, if you look at the history of changes checked-in to your source control repository, you will see each check-in has incremented an internal version number. The custom migration script must contain ALL of the commands that need to execute in order to get from one version to another. Therefore, I strongly encourage you to be conservative in the scope of your check-ins, and only include the items that require custom changes. Then any other changes can be checked-in normally, and you have limited the scope that the custom script must cover.

Figure 3: Commit Changes list with Migration Script. Notice the changes that were highlighted when you chose
to create the migration script are now indented underneath it to show that the one overrides the others.

There are two additional really cool attributes of these migration scripts which Red-Gate is implementing. The first, is that you can make changes to the change script after you have checked it in, and your changes to the migration script retain a history of their own. The second really cool attribute is that you can tell the system which versions (from and to) of source control that your script overrides (see figure 4). So, if you have changes that span multiple check-ins, as long as they are consecutive, you can tell SQL Source Control that your migration script actually covers all the changes represented by the changesets from version A to version E, not just A to B, then a separate one for B to C, and so on.

Figure 4: Migration Scripts and Source Control Versions Covered by each

The value of this is immediately apparent to anyone who has ever made a mistake. Suppose the change you were making was to drop the Employees and Managers tables, in order to replace them with a single Person table, and furthermore suppose that when you did the Commit of your change script, you had only marked the DROP of Employees and CREATE of Person for the changeset. Then, you realize that Managers is still in your pending changes display and you need to commit it. Go ahead and commit it, and then go back and edit the migration script settings, telling the system that your migration script actually spans those two check-ins.

I am really looking forward to this new functionality. We have fully embraced SQL Source Control for all of our SQL Server development, and we have one large system in particular where we are performing some significant refactoring of the database. For now, we have to do some coordinated management of manually produced and tracked custom change scripts. But when SQL Migrations make their appearance in the next major version, that will eliminate one more headache for me and simplify the development efforts of the whole team.

Before I wrap this all up, I would like to thank the folks at Red-Gate for authorizing me to talk about an upcoming feature like this, and for providing the screen shots. This is a refreshing difference compared to the handful of experiences I have had with Microsoft where I was sworn to secrecy over usability studies. It shows once again that Red-Gate understands the value of the community, and of being a good partner within the community. Yet another reason that I am happy to continue to mention them and their products in this blog. I make no money from doing so, but their products do make my job easier. A nice little win-win, if you ask me.

*If you would like to see it and try it for yourself, sign up for the SQL Source Control Early Access Program.

EDIT: Fixed layout errors and image sizing.

EDIT: Updated with screen images from the Early Access release which is now available for download.

The Tipping Point - DISTINCT Causes Timeouts

Ajarn Mark Caldwell — Sat, 23 Jul 2011 07:10:43 GMT

Everything worked fine for the last six years…and then today we hit the tipping point. I lost many hours today due to the misuse of DISTINCT which was the root cause of timeout errors in our web application.

In one of my most frequently read posts, Why I Hate Distinct, I talk about how this SQL keyword is often abused by SQL developers who are just trying to get their job done, but don’t know enough about either the SQL language or the data architecture to really do it right. Now, I realize that in the development world (of which I am a part, not just a spectator) that using a phrase like “do it right” can be fighting words. In software development, there are often many different ways to accomplish the same goal and what may be right in one situation or environment may not be right in another. But there are also certain principles which should not be undermined or overridden without some serious analysis. One set of those principles in the relational database world are the rules of Normalization, “the key, the whole key, and nothing but the key, so help me Codd.”. These principles are important for preventing duplicates in your data. What does the DISTINCT keyword do? It removes duplicates from your query result set. This question that should now be coming to mind is, “If I have a normalized (no duplicates) database, why would I end up with duplicates in my result set?”. An excellent question! The simple answer is that you shouldn’t, and if you do, I think you need to analyze your query, not just slap the DISTINCT Band-Aid on it.

But as I mentioned in my opening paragraph, today this became an issue about much more than undermining principles or my sense of right and wrong. Today it became an issue of query performance, and I spent several hours trying to figure out what went wrong, including working late on one of the most beautiful Friday nights we have had in a long, long time, and missing dinner with my family or getting to see my kids before they went to bed. For me, this was a large price to pay, all brought about by one developer’s carelessness or ignorance. (Hey, what can I say? Once I got married and had a family, I suddenly didn’t think staying at work all night working on a puzzle was all that cool any more.) This performance effect of the misuse of the DISTINCT keyword was actually mentioned in a comment on that original post by Ivan Budiono, but I had not seen it in action until now.

In my story, it was an odd, but fortunate, coincidence that this problem showed up when it did. This internal web application has been running for more than six years with very little change to this particular web page, which normally only takes about 4 or 5 seconds to gather its data and render, so it was very surprising this morning when it started throwing SQL Timeout errors; all the more so, knowing that we had nightly jobs to address index fragmentation and to update statistics. The business users first jumped to the conclusion that something went wrong with the newest version release which we deployed last night, but I knew that there were no changes in there related to this particular web page nor the stored procedure that was timing out. We had also purged a little bit of bad data from one of the related tables, and so some users thought the problem was caused by that, but again I was skeptical of their guesses. I find it a good practice, when you are playing the role of troubleshooter, to be skeptical of guesses and people’s memory, and simply focus on the things that you can prove to be fact.

Fortunately, since I had just done the software deployment the night before, I had before and after backups of the database. So, I restored the “before” image under a different name (we are fortunate to have sufficient hardware that I could put this on the same instance as the live system to eliminate that variable in my analysis and not significantly impact the live system). Sure enough, executing the stored procedure in the “before” ran without trouble, returning results in a few seconds, but executing it in the current live database it ran nearly a minute. So then I turned on Actual Execution Plans on both and they were remarkably different. The before image was full of index seeks and put the tables together one way, but the after plan had a couple of time-consuming Clustered Index Scans, and interestingly, arranged the pieces in a very different way.

Now the research starts to get fun. Since we have the very handy before and after copies of the database, let’s see if there is something different between them that might be causing the issue like an index got dropped. So, I fire up Red Gate’s SQL Compare tool. Everything looks good. There are differences, but only the few changes I was expecting from the software update we released, and nothing that stood out as an issue. Specifically, there was no difference in the tables involved, index definitions for those tables, or the stored procedure. Hmmm…what else could it be? Well, I knew that the business was doing some bulk importing of data into tables related in the queries, but they have done that before without a problem. Nonetheless, I’m running out of ideas, so let’s see just how much has changed in the data. So, I open up Red Gate’s SQL Data Compare tool and take a look.

Well, there were about 2900 rows added to a table that has 40,000 rows (about 7% growth). That doesn’t sound too bad. And another table involved had 4400 rows added to its nearly 100,000 rows, again a small percentage of change. Seems hard to imagine that this little amount of data could make such a big difference, but it is one of the few things that I can verify are different between the databases and related to the query. If only there was a way to replay that data change to see if it caused the problem…Wait! There is! SQL Data Compare will not only show you the difference between two databases, it also allows provides a Synchronization wizard in order to make the data changes necessary to make them the same. Even better, you can do it step by step, one table at a time, one execution type (Insert / Update / Delete) at a time. So I did that; I methodically ran through the data changes on only the tables involved in the query, and sure enough, when the before now looked like the after from the bulk loaded data perspective, the stored procedure there suddenly started taking much longer to run and gave the ugly execution plan instead of the efficient one. And one of the weird things that the execution plan was showing was that two sources of a Hash Match Join brought 43,000 and 2,900 rows respectively, and the output was 2.1 million rows. But the final result set was only 365 rows. I figured that was not a good sign.

Armed with that information, I knew I was going to have to tackle the stored procedure to see if I could rewrite it in a more efficient manner. It really only had two queries to it, each with about 8 tables JOINED together. My first thought was that it was fairly ugly, so I ran SQL Prompt’s Format SQL feature to clean up the layout. (With all these references to Red Gate tools, you might think I get paid for promoting their stuff. I don’t. It just happens to be that they make good tools that really helped me today.) After the code was formatted the way I like to see it, it was still ugly, but this time strictly from a structural or syntactical point of view. I noticed the DISTINCT clauses right away, but also spent quite a bit of time reading the code and running mental checks on all of the JOINs and WHEREs. Along the way, I made a note to myself (commented the code) that one of the tables would normally be joined on two fields, and not just one. After doing that study, I decided it was time to break this thing apart and see what was in it. The first thing I wanted to do was get rid of that DISTINCT and see what came out. That immediately changed my result set from 365 rows to nearly 17,000 rows with a whole bunch of “duplicates”. I did some counting of how many duplicates of one key I was getting and thinking about what may cause that when I saw that note to myself on the JOIN criteria and decided to clean that up, too while I was there. So, I quickly added the second field to the JOIN, re-ran the query to see how much difference it made, and voila! I was back to my target of 365 rows, and performance was dramatically better. I made that change to the stored procedure in the before snapshot (with data loaded) that I was using for testing, and sure enough, results came back in just a few seconds and the execution plan was back much closer to the original, so I did an emergency update of the live system with that change and everything was back to working like it was supposed to.

I’m still surprised that such a relatively little amount of data would cause such a drastic change, but I guess that is explained by the multiplying effect of that bad JOIN syntax. There are still some improvements that I think could be made in that procedure to make it more maintainable, and probably more efficient still. And there are a couple of new indexes that I am going to explore to tune this up even more, but this was enough to get us back in business and allow me to get out the door to go have a late dinner with my wife.

And as you can imagine, I still hate DISTINCT.

Interview Tip: Do Some Basic Research

Ajarn Mark Caldwell — Tue, 12 Jul 2011 06:32:09 GMT

A lot of job candidates out there are wondering how they can set themselves apart from the crowd and increase the odds of landing the job they want. Well here’s a really easy way…

Do your basic research and due diligence on the prospective employer before your first interview. This is good practice whether you’re looking for full-time employment or just a little contract work. Show the interviewer that you are at least interested enough in the work to hit a search engine or two.

I have conducted numerous interviews for application and database developers over the past year, and one of the questions that I always ask is, “How do you stay up-to-date on the latest developments in technology or programming?” Now this is a fairly easy question as there is no exact right answer, I’m just trying to learn more about what type of person you are; asking about your habits and willingness to invest even a little energy in your own professional growth. Typical answers I would expect to receive are, “I read blogs on my lunch break (or on my own time).” or “I just picked up this book on query tuning” or “I read the ________ newsletter/magazine” or “I go to SQLSaturdays and the PASS Community Summit.”. You don’t have to be pursuing a college degree or Master’s certification, although those are both noble goals; I’m just looking to see if you are doing anything on your own to stay current, or do you just drift along like a dead fish floating downstream waiting to get dumped out at the lowest point.

But it’s also a test to get a feel for how much effort you put into researching this job. You see, I’m on LinkedIn, and you can find my profile easily by entering my name and the name of my company into any of the big search engines; and I know you have at least that much information when we have scheduled that first phone interview and you are waiting for my call. And if you were to go to my LinkedIn profile, you would find a link to my blog here. Now I don’t expect you to claim to have read everything I wrote and that it changed your life (although it could). Hey, you could even say, “I read what you wrote and I disagree with you on these three main points…” That is fine. In fact that is probably even better because it shows me that you have an opinion and are willing to discuss it. But what really surprises me is that nobody in the last year has even said to me, “hey, I saw your profile on LinkedIn and noticed that you have a blog.” much less commented on any of its content, or even lack thereof.

Believe me, it makes a huge positive impression if you can throw something into the conversation that shows me you did a little research on your own. It shows initiative, creativity, and an interest in the job; all of which are attractive attributes to an employer. And all the more so, given how rare this seems to be. So here you go…a really easy way to stand out from the crowd.

Regaining SysAdmin Access after a Lockout

Ajarn Mark Caldwell — Tue, 12 Jul 2011 05:54:47 GMT

Today I had to find a way to regain SysAdmin access to an instance of SQL Server when I technically had no permissions. Here is how I did that.

Every developer on my team is setup to be able to work 100% stand-alone. That is, they have everything on their local machine, including a recent copy of the database, to do all their work even if the network goes down, which it used to do on occasion. For SQL Server development, each has a copy of SQL Server 2008 R2 Developer Edition installed, and it was one of these that I had to regain access to. Fortunately this did not happen to a production instance, but in a pinch, these techniques would work there, too. Normally, I am setup with SysAdmin privileges on each of my team’s SQL Server instances so that if anything goes sideways, I can help them recover or pick up work-in-progress. They are also SysAdmins on their own instances so they have full capability to do anything they might need to in order to get their job done. But recently, one of my team members left the company, and as I was reviewing his machine to determine whether it needed a complete rebuild before the next developer could use it, or just a little cleanup, I discovered that I no longer had any access to the SQL instance. And because this was SQL 2008, we had, as a matter of normal installation technique, left out the Built-In/Administrators group, so even though I was also a member of the local Administrators group, that did not get me anywhere by default.

Somewhere in the back of my mind I had a faint recollection of having heard or read about a technique where you could regain full control of a server even if you didn’t have normal access to it. I was thinking that maybe it was related to the Dedicated Administrator Connection, but couldn’t remember and was pressed for time so I was not inclined to spend a bunch of time searching online. So, I went for a very handy shortcut…the #sqlhelp hashtag on Twitter. I quickly described my scenario and within just a few minutes I had multiple responses back with instructions about how to do it and links to this blog entry by Raul Garcia and this one by Argenis Fernandez that describe two different approaches to getting yourself back in the SysAdmin role. Since this was a local developer edition and it is easy to stop and restart, I went with the first approach which involves putting the SQL Server into Single-User mode and then as a member of the local Administrators group, I was able to use SQLCMD to add myself back into the SysAdmin role. I suggest that you read both articles to be aware of the features and risks associated with them, including the potential security risk that #2 reveals is inherent in the normal, and recommended-by-Microsoft configuration.

And an extra thanks to the awesome SQL Server community that is active on Twitter as well as blogs and forums, and truly helps one another. You guys really helped me today when I was in a crunch. You’re the best!

SQL Source Control with Vault Support Officially Released

Ajarn Mark Caldwell — Thu, 31 Mar 2011 06:30:00 GMT

HOORAY! It is officially here!

Today, Red-Gate officially released SQL Source Control version 2.1 with support for Vault.

While we have been happily and successfully running the beta version (a.k.a. the Early Access release) of Red-Gate SQL Source Control with support for Vault for quite a while, it is good to have the official RTM (or GOLD, or PROD, or whatever you call your “no-longer-in-beta”) release of the product.

As a courtesy to those who have not already read the series, allow me to provide you with these links to my previous posts about this fantastic tool.

Using SQL Source Control with Fortress or Vault – Part 1 – Introduction and initial thoughts about the tool and source controlling SQL code in general.