I spent last week onsite with a client teaching performance tuning and it was really a good week. Great company and great people eager to learn. It's always interesting to see what 'aha' moments happen during a class and this one was more interesting than usual because it was half DBA's and half developers. Note that 'aha' often just means that someone said something in a different way where it finally clicked.

On the developer side, there were a few items:

• Using temp tables (or table variables) to solve a query in intermediate steps isn't always a good idea. The challenge is that there is no perfect rule for when to use them or not.
• Unicode data types require double the space. Using unicode isn't wrong, but MS - unfortunately in my opinion - pushes/defaults to unicode, and not all our applications need it. This also goes back to teaching people to fish; as DBA's we can't get in the habit of saying 'do it this way' without also explaining at least a little bit about why.
• Views can be used for abstraction far beyond just hiding a complex query. They are also good for letting us move/change a base table, or to mask bulk loading by pushing data into a new table, then altering the view post load to use it.

There were fewer aha's on the DBA side, but still interesting.

• One was about how to move from reactive to proactive in terms of performance. My recommended technique is whack-a-mole(query), where I monitor once a week for queries that exceed x reads or x duration to identity tuning opportunities.
• The other was the idea that they needed to shift from just applying changes via the normal change control process to include a performance evaluation of every change. All it takes is adding a column or one more filter to the where clause to tremendously impact performance. Catching performance issues at deploy time (even if just scheduled to be addressed later) can really reduce performance entropy that seems to often happen by magic.