桃子视频

Tonight was our monthly meeting and our featured presenter this evening was Plamen Ratchev () presenting on performance tuning and query optimization. First off, he has an awesome accent. I think he鈥檚 of Croatian descent from what he mentioned but he rolls his R鈥檚 something fierce. I should鈥檝e had him say the phrase 鈥渞eporting services鈥� a bunch of times just to make me giggle. Anyways, I鈥檓 way off topic鈥�

He opened with a quote from that stated

We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil

This was a good point to open with in that if you try to focus on only performance in the development phase you鈥檙e more than likely going to perform an epic fail. He then went on to show the differences between being reactive and proactive in terms of performance tuning. His take was that in Europe their development processes focused more on being proactive and trying to take care of issues before they become major problems as opposed to trying to run around putting out fires all the time like many a DBA (myself included) is forced to do on a daily basis. In an interesting story he relayed to us he told us about how he had come to the U.S. and pitched a European software that allowed a major car manufacturer to improve their processes. The software would basically alert you if anything in production fell 鈥渙ut of the norm鈥� and would advise actions on how to fix it. The American company had a different approach to their process. They basically hired a team of high-priced consultants to come in for a week, measure everything nuts to bolts about what鈥檚 wrong in the production process, produce a report and leave (whether or not problem got fixed). What surprised me most was that he told us the manager told him if there鈥檚 a problem they pretty much just build another assembly line somewhere else rather than fix current issue. If you鈥檙e wondering why they need Federal bail-out money, this story should give you a slight clue.

The presentation continued on with things such as common performance issues you鈥檙e likely to find such as inefficient queries, retrieving too much data, inefficient or missing indexes and a few other things. This was a nice list to see for both devs and DBA鈥檚 alike so everyone is aware of these common mistakes. This lead to topic of problematic designs such as the 鈥渙ne-true lookup鈥� table issue. This is when rather than normalizing your data someone decides its easier to just throw everything in to one large table and add columns as needed later on. I can see some of you cringe when you read that since you鈥檝e probably seen that in production somewhere at some point.The surprising thing that came out of this example though (to me anyways) was that sometimes this setup actually makes sense for very specific applications such as a simple survey or a medical application that is only storing straight facts (i.e. patient monitor). Another oldie but a goodie is mismatched data types on join columns or filters. While this may work without a problem, when you throw a heavy work load at something like this you鈥檒l see performance tank because behind the scenes the data engine is having to do lots of implicit conversions to process that query for you. So remember that little tidbit next time you鈥檙e planning with your devs and database developers.

The next interesting thing I learned was regarding data type optimization. Do you know what the one of the is between VARCHAR and NVARCHAR besides one taking up twice as much space as the other? NVARCHAR handles multiple while VARCHAR is more for single so if your application is only going to be delivered via a single, default collation then stick with using VARCHAR.

I could go on and on but needless to say this was an extremely insightful and useful presentation. Another user group member, Ron Dameron (), noted in Twitter this evening

鈥�seen this deck twice now. Learned new stuff both times. Thx Plamen

If you ever get a chance to attend one of Plamen鈥檚 presentations at a live event I highly encourage you to do so as he鈥檚 a brilliant guy and presents well. If he鈥檚 not coming anywhere near you, you can still check out his presentation stylings by over at . In closing here鈥檚 some book recommendations he threw out at the end of his slide deck. Basically this is just an excuse for me to use the cool Carousel feature from !