Almost a year ago, we had a major database migration issue, actually it was one issue that caused another issue that cause performance issues – nevermind the specifics, it was complicated. The bottom line is that I knew I could fix our SQL server issues, so for a couple of weeks, I was deep, deep, deep into not only SQL issues, but presenting the issues to management, other developers – technical and non-technical, my boss’s boss’s boss was hanging on every word I’d say. In fact, the days were so full of talking, I ended up doing most of the technical work in the evenings after the kids were in bed (it’s been a re-occurring theme/frustration for me).
After the issues were solved, which I merely assisted in solving, but I ran a lot of the meetings – there were a lot of others who stepped up and really impressed me with there determination – I wrote some notes on what I’d learned from those weeks. Here’s the list I came up with:
Five Technical tips that should be kept in the back of your mind when troubleshooting MAJOR issues to avoid the HACK title:
- Temp tables vs table variables – be consistent, I didn’t think the code would last, so we had scripts with Temp tables and table variables – HACK!.
- Insert statement – use full insert statements when using Excel or a SQL Script to create a script. There is a 1000 row limit to using the insert …. values (…), (…), etc.
- Instantiate variables – especially if you are using them in loops
- Update stats as a performance boost
- Check missing indexes
Four Non-Technical Issues to be kept in the back of your mind when troubleshooting MAJOR issues to avoid the Hack title:
- Stay focused on the issues.
- Project your screen so people can see your logic – you can be the show and they will learn from watching you – at least they will learn that you are smart!
- Know the data don’t do extra work just to feel good – don’t waste time checking everything, at times there needs to be the leap of faith that things will work – if there are issues, baby steps is a good method for troubleshooting.
- Sometimes quick hacks turn into permanent/repeatable fixes, keep TSQL clean/consistent/use best practices even in your quick fixes – you never know.
That last one was really in my notes – unedited! I said I was going to post a blog about it, but haven’t until now.
So, keep in mind, your quick hacks may turn into permanent fixes, therefore consistently follow best practices and you will avoid the HACK title!