I do very little consulting these days, perhaps 4-5 weeks per year, and that only to make sure I maintain 'real world' skills that I need to stay credible in the classroom. Much of that consulting is basic performance reviews of existing code, and today I've got a couple notes about things I've noticed over the past few years:

• Most clients will prefer that you identify index changes or simple tweaks to existing code (hints for example) that require minimal testing to verify the performance improvement and that the results are still correct
• Most clients do NOT want to rearchitect their databases, don't want to change data types, and don't want to change their business processes in the name of performance. They want you to make what they have work if at all possible.
• It's common to find queries that do a lot more work than needs to be done, bringing back all rows without a where clause for example. Fixing them is easy technically, but it means the DB manager has to sell the change to the business (or analyst) - and they tend to be averse to change, "we might need to see the order history from 1995 some day".
• Clients fall into two categories, those that want you to figure out the changes and implement them for testing, and those that prefer you spend time identifying problems but not necessarily writing the code to correct them. Neither is wrong, just make sure you know which type you have.
• Document your recommendations/changes as you go, leave them with a nice roadmap of what you did and why - with luck it will help you get your head in the game faster if you get invited back.
• The only thing the client cares about is duration. We typically tune to lower reads and that results in lower duration, but they only care about duration because that is all the user sees!

I learn something every time I do these engagements, and actively look for those aha moments.Much of is mundane, getting rid of scans and key lookups. More fun - but harder - is digging through complex processes (especially ETL) to see where you can make a nice gain. A day of it is fun, a week is exhausting.

Good stuff to think about if you're bringing in a consultant, make sure you know and they know your expectations.

One of the things I teach in our admin course is that it's not enough to just be the gatekeeper/central point of change when it comes to being a DBA, we should be adding value:

• Look at each change and ask ourselves 'is this obviously stupid?'. For example, we might see a case where a cursor is used when clearly a a set based update would work, or we might notice a cross join
• Verify that it meets our coding standards (this is an easy check if you don't have any!) 
• Compare the performance of the change with the previous code, and fixing if needed. This avoids the slow and steady entropy that can otherwise result

You'll notice that I don't put in the list verifying the change is correct - that's a QA task!

One sub bullet point I've added to 'is it stupid' recently is checking queries for unused tables. I ran across a couple instances lately of what was probably copy/paste code reuse and I was tuning the queries, realized that several tables were included that had no effect on the results. Easy to miss something when you see things like this so it definitely needs to go back for testing, but it's worth taking the deeper look right then, before you deploy.

I was visiting with a client recently when they asked me to come take a look at an incident in progress, the number of connections to the server had just about doubled over what they were normally. As far as troubleshooting this scenario I always start by checking for blocking. If you've got spids blocked they can't go back into the connection pool, forcing the connection pool manager to open up new threads.

No blocking? The next step is to run two different group bys against sysprocesses, one by hostname, the other by program name. I'm looking for a clue that will show all the connections are being generated from one machine or one application, or both! In this case we could see that the connections were evenly distributed across the web farm, but trying to break down by application wouldn't work because they didn't set the application name in their connection string. Fixing this is easy, just add the following to the current connection string:

;Application Name=My App v1.1.1

We couldn't identify anything obviously wrong on the server, in the error/event log, or in their application logging. Within a few minutes we saw a nice reduction in connections and soon things were back to normal. Would having the application name have made a difference? Unknown to unlikely in this case, but given that it's so easy to do, why not make the change so that next time you have one more piece of data?

Rebuilding Stats was published yesterday on SSC, some nice comments posted to it as well. The main point of the article was that if you're rebuilding indexes with the default options you're automatically getting stats update on those columns as well.

I wrote this article on computed columns to support some of the teaching I do for my beginner to intermediate level performance tuning class. It's not a technique you'll apply that often, but I think it's used less often than perhaps it should be. Hope you'll take a look and add a comment to the article.