The Rambling DBA: Jonathan Kehayias : SQL Quiz

5 Things SQL Server should get rid of

Jonathan Kehayias — Wed, 12 May 2010 02:12:42 GMT

Paul Randal ( blog | twitter ) started a new meme last night with his blog post " What 5 things should SQL Server get rid of? " A few bloggers have posted their top 5 lists, so here is mine. Creating Foreign Keys without mentioning Indexes This...(read more)

What three events?

Jonathan Kehayias — Tue, 02 Feb 2010 04:14:50 GMT

Two weeks ago Paul Randal started a chain blog series on What three events brought you here? He tagged Brent Ozar, who in turn tagged me in his post You may ask yourself, How did I get here? So here’s my own take on this topic. Event #1:...(read more)

SQL Quiz: My relative path to Geekdom

Jonathan Kehayias — Tue, 08 Sep 2009 04:14:07 GMT

I got tagged in another SQL Quiz going around, this time by Mr. Denny ( Blog / Twitter ). I tried to come up with some kind of witty title for this post and as usual I lacked the ability. My wife recommended using a story like the guy on The...(read more)

SQL Quiz: Here on Gilligan's Isle

Jonathan Kehayias — Mon, 15 Jun 2009 01:39:29 GMT

It happened again, only this time Tim Ford is the ~~antagonist~~ initiator, instead of Chris Shaw. Once again a chain blog question is circulating around the SQL Server community. This time I was tagged by Jeremiah Peschka. I have to admit, that I really find these questions interesting and it is neat to read other peoples responses to topics like the following:

So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?

For me, this actually took a bit of time to think about. If I had an uninterrupted month on a deserted island with WIFI, I actually probably wouldn't get all that much farther in accomplishing things. This is simply due to the fact that I still have WIFI, and my uncontrolled ADD would have me browsing the MSDN Forums and Google Reader, and the rest of the Internet, and I would invariable get distracted by something else if it wasn't something work related. I am a realist. However, given that this is a fictitious scenario and not likely to happen here is what I would like to accomplish:

Hardware Configuration

Ever since I got my own testing server for home, this has been a big thing for me. I can't believe how little I knew about configuring and installing a server from the bare bones hardware up to having a completely running SQL Server. Granted, I have learned a whole lot in the last month from a lot of trial and error, but I know that there is a whole lot more out there to learn, and if I had the money and hardware I'd be all over it right now. I'm really interested in how really large servers like a half dome and IA64 with Hardware NUMA get configured, proper SAN configuration for high IOPS throughput, and how queue depth actually affects performance. Sure I have read about these things on blogs, but I am a hands on kind of person. You can show me all day long, and I can read about it until the cows come home, but until I actually get my hands dirty and perform the task, I haven't really learned all that much. I know where to look for information for reference should I ever actually need it, but I don't really know it, know it.

*nix Administration and Management

This one really leads into the next one, but when I got my first job in the corporate world, working in a ATT Wireless call center, I got my first experiences with *nix environments though it wasn't really much of an experience building kind of job. It was a few years later when I was working in an Internet Help Desk job that actually got to learn about Linux and work inside of a shell environment. I still remember a bit from those days, and this was beneficial in my current position where I back fill for the Oracle DBA when he is on vacation or out of the office for training. Having at least a rudimentary knowledge of the *nix environment is definitely a plus in my opinion. It also helped when I was recently configuring my test server to build a SQL Cluster on since the first iSCSI target I worked with was an open source Linux package.

Other RDBMS Systems (Oracle and PL/SQL, MySQL, etc..)

Ever since I got my first looks at Oracle and how it is used, I've noticed a big difference in programming paradigms between the Oracle developers I know, and how things are generally done in SQL Server. I'd like to be able to put the time in working with PL/SQL to understand the fundamental differences in how operations are performed between the two environments. For example, a lot of PL/SQL developers make heavy use of cursors and rowsets in their PL/SQL code, a common No-No in Transact-SQL. I'd like to fundamentally understand whether cursors in PL/SQL really perform up the standard that they have been portrayed to, or if good set based logic in Oracle will outperform just as it does in Transact SQL.

I'd also like to dig into MySQL and figure out what exactly all the muss and fuss is about. Just looking at web site hosting providers, one can't deny or argue that MySQL has its heels dug into the web hosting world. I'd really like to understand the fundamental differences between a "free" (its not really when you get down to the nitty gritty of things) database server and the licensed database servers in most common usage.

Data Warehousing and Business Intelligence

If you follow me on Twitter, this one shouldn't be a big shock. I am very interested in the Business Intelligence side of databases currently, simply because its a focus point of certain projects at my company currently, and I am learning the more I get into it, that it is a completely different way of thinking about and working with data. I've bought and read Ralph Kimball's book, and as I stated previously, I can read about it all day, but until I have done it, it just doesn't make sense. With that in mind, I have been working on a data warehouse project recently and even as a project for work, I have only been able to dedicate 2 days in the last 2 weeks to it. However, I was able to make some really good headway the last time I was able to work on it, though it will probably have to be scrapped and rebuilt from scratch at some point in the future simply because it was my first stab at it.

So that is it for me. Not necessarily a lot SQL Server related, but that's what I would do, though I'd probably need to be stranded a lot longer than a month to make any kind of headway on any of these things. I'm going to tag Kevin Kline and Louis Davidson with this.

Tagged: Who has been a great leader in your Career?

Jonathan Kehayias — Tue, 05 May 2009 14:26:25 GMT

While I was gone, I was tagged by a number of the chain Quizes that have been going around the SQL Server Community, so I am working to answer each of them as I get around to it. Aaron Alton tagged me for this one, and it is actually one that I already answered for the most part in a previous blog posting:

SQL Quiz: The most influential people in your Technical Success?

If you are interested in who influenced me along the way, you can read about it there.

SQL Quiz: The most influential people in your Technical Success?

Jonathan Kehayias — Wed, 18 Feb 2009 10:06:27 GMT

Lately there have been a series of SQL Quizes that have circulated the blogging community. Some of them are silly, like the Wordle one, and some have a purpose behind them, like the two greatest mistakes one. I decided that since my nature is to give credit where it is due, I would be most interested in reading about the most influential people in the lives of successful SQL Server professionals.

I have had a number of very influential people in my life along the way, but since I am starting this project, I guess I would have to pick:

Dave Weldy / Virginia Roberts / Marshall Sutherland

This treo of developers/DBA's are the group responsible for me being involved with SQL Server at all. While I was attending college at the University of Florida, I worked for a natural gas company as a Applications and Business Analyst. My primary job was working with the QA team researching bugs in the billing system and writing technical specifications for code fixes. I realized pretty quickly that unless I was able to look at the data in the database, I wasn't going to be very effective at finding problems, and determining how/why they existed. Dave loaned me a TSQL book and gave me a couple of basic SELECT statements to learn from in mid-2004. Little did he know that he created a monster that would begin asking thousands of questions of not only him, but the other senior SQL Staff in the company as well. Virginia and Marshall helped Dave answer countless questions along the way, and probably kept me from being the annoying new to SQL forums/newsgroup poster who failed to do any kind of Google search to find the solution to the problem.

As a group they helped provide the foundation that I have built upon over the years. I like to learn by reading existing code, and these guys were very open to allowing me to see any and all of the code that was written in the database, and they were willing to help explain complex problems like NULL which was an impossible thing for me to understand what a NULL actually represented, or didn't represent.

Arnie Rowland

This might seem kind of funny to some people, but one of the most influential people in my technical success and development as a SQL Server profession is actually someone that I have only had the pleasure of meeting face to face twice. For those who know Arnie this won't be any sort of a surprise. Arnie is a fellow MVP and Moderator on the MSDN Forums. Last year Arnie personally took it upon himself to organize a group from the frequent contributors to the SQL Server Forums, to include MVP's, Moderators, and Answerers and create a email distribution list where we could communicate with each other when problems arise, or to get assistance on complex questions on the forums.

When I was asked to join this list, I wasn't a Answerer, Moderator, or MVP. Joining the list allowed me to learn from the group, and Arnie personally provided mentorship to develop me from a contributor to an Answerer, and then up to Moderator. The increased answer rate of posts on the SQL Server forums is a testament to the success that this kind of organization has had in improving the forums experience for people. In addition to forming the email list, Arnie also created the SQL Examples site as a place for Answerers/Moderators/MVP's contributing to the forums to publish articles and content that answer common problems encountered on the forums. For some of us, this was the first place that we were able to contribute/publish article content online, and was a stepping stone to other opportunities to publish information.

I first happened to be able to meet Arnie at last years TechEd during the SQL Saturday between events where a group of forum members got together for dinner after the event. It was a extremely fun time, and it was good to be able to put names to faces, not only with Arnie, but others as well. The second opportunity was at PASS 2008, where I got to attend a few MVP events with Arnie. One thing that Arnie did was make sure that new MVP's like Jacob Sebastian and myself, got introduced to other MVP's and members of the SQL Development team at Microsoft. Arnie also made sure that we were aware of events that were happening after hours during the Summit to ensure that we were able to maximize our visit to Seattle and have more opportunities to meet people face to face.

One thing that I tell people who ask questions online about how to become a good DBA or Developer is to find a good mentor locally, and get under their wing. I certainly have had quite a few mentors in the last 4 years and I still try and get under the wing of industry experts and glean anything I can from them. However, lately I have found that rather than being mentored, that I have slowly changed over to the role of being a mentor to others. One thing I learned from my mentors is that there are times to give someone the answer and provide a in depth explanation along with it, and there are times to provide enough information to allow someone to find the solution themselves. For example, I once asked Dave how do I use the output from one query to query another table. Now he could have easily just typed out some code showing me how to do it, but instead he recommended that I look up what a JOIN was, or how to use a subquery with the IN operator. These are fundamentals in TSQL development, so I feel that I was best served by having to go find this information on my own. If you are a mentor, don't always give the answer to someone immediately, and if you are being mentored, be willing to take the information provided and do a little leg work. In the end you'll be better for it.

So who was influential in your success as a SQL Server professional? I am not going to call specific people out, but I am curious how people have influenced some of the current influencers in the community.

SQL Quiz #1

Jonathan Kehayias — Wed, 10 Dec 2008 03:54:31 GMT

Ok so I made a mistake last time around, and I actually was tagged by Kendal Van Dyke, though I missed it back in November. So in the spirit of the exercise here are my two biggest blunders in SQL Server based on the Two mistakes post started by Chris Shaw that circled the rounds of SQL bloggers in November.

Biggest Mistake...

While upgrading the second largest database in SQL Server at my job from SQL Server 2000 to SQL Server 2005, one that holds important financial's data for credit card reconciliation's, it was decided by the business users that a large portion of the data in the database would be archived off, and then deleted from the database. At the time the database was just under 140GB in size with the majority of the data in one very large table.

The end users worked with the vendor to get a script to handle this operation, and in testing it, the process was going to run over 30 hrs which was not going to be possible. So after some review, and discussion with the vendors support and development staff, I realized that it would be faster to create a new copy of the table, insert the rows to be kept into it, drop the old table and rename the new one, and rebuild all the constraints and indexes. This took the process down to about 1/2 hr in testing.

The problem came when I loaded the script on production to run the purge process. In testing it, I did the operation in an explicit transaction, and initially I had left the SET IDENTITY INSERT statements out of the batch by mistake. It wasn't a problem on development because I had blocked the code in an explicit transaction, so when the inserts failed because I didn't SET IDENTITY_INSERT ON, I issued a rollback, waited, and then fixed the problem. For some reason, I never saved the script.

Onto production, load the script, run it, and watch a 88GB table disappear in mere seconds. Now I know that everyone has made a mistake at some point, and we are all familiar with that sinking hot feeling that you get in the seat of your pants when you realize it. Take the worst case of that feeling you have ever had, and multiply it by a factor of 10.

The good thing was I had backups, and the database was fully logged, so backup the tail log (first thing I did was let out a string of words that can not be repeated and caused the consultants in the cube next to mine to look over the wall and then take a break, but the log backup immediately followed) and then I took a quick walk to my directors office to explain the disaster I had just created, how long it would take to fix, and that I needed to take a quick walk around the parking garage to clear my head before attempting to fix it, which he agreed was probably a good idea.

Lesson Learned: Save early and save often. Really, I was fine, my backups existed and had been tested numerous time recently in performing mock upgrades so I knew I was able to recover from it. The total impact was about 20 additional minutes of downtime over my initial estimate which had provided me ample wiggle room for a major problem requiring a restore of the database to a SQL Server 2000 instance if need be.

Plan for the worst, hope for the best, but never be without a good backup.

Second biggest mistake...

The second worst thing I have ever done in SQL Server involves a table with an audit trigger doing a SELECT * FROM Inserted. This was at my previous job, and just one of a few messes I created along my road to learning SQL. I was initially a Business Analyst, and in order to track problematic accounts in a new billing system I created a table that exempted accounts from billing during normal billing batches. Anything kicked out during post billing QA or any new accounts were immediately inserted into this table. To maintain a history of problems to ensure no account consistently had problems, an audit table was created using a revisioning trigger that tracked when the account was inserted into the table, when it was updated and then later deleted from the table, and it tracked who did it.

While I was away on Army training this table was changed by a new BA who took over the job I was doing and a column was added to the table to hold the account number which existed elsewhere and was easily gotten with a join. This broke every report that used this table because the column was originally distinct and did not use two part naming (mistake one). When I returned my first task assignment was to fix the reports since they were important to the business users and QA department. After looking at them, the problem was immediately apparent, so to fix it I just dropped that new column in production.

Remember above where I said all new accounts were put immediately into this table. Well dropping the column on the table without dropping it on the audit table caused an error in the trigger execution because you can't INSERT INTO AuditTable SELECT * FROM inserted without a column specification if the column lists are different. This caused the transaction that created all new accounts to rollback and raise an error to the Account creation system which was in a different database.

It only took about 10 minutes before I got a phone call asking if I had touched that table by one of our DBA's. Total Damage 14 new accounts that had to be reentered manually in the Account creation system to send them back to the billing system.

Lesson Learned: First this taught the DBA an important lesson. Jon had no business having any kind of privileges in a production database environment, least of all sysadmin like I did back then. Second, never change something in production that has not been completely evaluated and reviewed by your DBA staff. Third, always fully qualify naming of your columns and tables in your code. A simple change can break so much if you don't. Forth and lastly, never do a INSERT INTO without specifying the column list, and never do a SELECT * in code like this. It will break when you least expect it.

Since this was from so long ago, I won't call anyone else out on it, but if you are interested, call yourself out, and post your two biggest mistakes. Leave me a comment, and I'll update to link to your blog, as a self call out.