Don't be greedy in T-SQL queries

I am working on improving the performance of some stored procedures.  The queries all make use of index seeks and index scans to get to the data, so, nothing I can improve there.
The procs call other procs which call some more procs.  A join to a large table (+/-2million records) gets repeated in several of these procs.

So, I changed the deepest nested proc to return all the data which will be needed by the other procs.  I removed the repeated join; and the performance problem is solved.

So, two very important tips for performance tuning queries:

1. Only return the records you are interested in; and
2. Only touch a record once if possible.

CATCH block not executing after an error in the TRY block

Today we had trouble with the CATCH block in a T-SQL script which did not execute after an error in the TRY block. It turned out that there are times when your TRY block can fail and the CATCH block will get bypassed altogher.

Click here or on the title of this article to see the article by Alexander Kuznetsov about exactly this problem.

Our problem was a compile error: a column name we referenced in a join statement no longer existed.

SSIS Indirect Configurations

With indirect configurations SSIS enables you to store the path to the dtsConfig file in an environment variable.  At runtime the package looks up the location to the config file, load the config file at that location, and does whatever it needs to do with the information.

If your SSIS package gets executed from a SQL Job, it is important to restart the SQL Agent after creating the environment variable - the SQL Agent caches the environment variables and won't pick up new ones unless restarted.