Let me start by saying that I am NOT a DBA, by choice. I am a developer. I am a SQL Server developer with experience in application development. I have worked with databases for almost 10 years. I only briefly worked with ACTUAL DBAs when I worked in the healthcare industry, which only lasted 9 months. I have worked with several senior developers who were assigned DBA duties.Being near databases you pick up things. You observe and get a general gist of what an actual DBA would do all day. I think for a brief second I aspired to be a DBA- (that’s where the money is, right?) but quickly saw the error of my ways. I really have very little interest in being away from the code.
At my day job, I do all things IT. I support an accounting ERP that is on SQL Server 2008r2. I use TSQL, SSIS, and SSRS mainly. Since I am literally ALL THINGS IT, I am the de facto DBA. Brent Ozar was right! All I had to do was be a developer and stand near a database! Brent Ozar’s Advice
Great, so now that those three letters are officially on my resume, people want me to do actual Database Administrator things. This is fine at my day job, because the data is relatively small. My two biggest tables, which are archive tables, hold 2 and 3 million records. The company I’m at now is also really good about spending money on IT, so my SQL Server box is pretty freaking fabulous. It runs on VMware, and has plenty of RAM and space dedicated to it. If I wanted to reindex all of the tables in the application, it might take 15 minutes. But not everyone is as lucky as I am.
I also freelance. My main freelance client runs the same software that I support, also in a 2008r2 environment. However, they are a much bigger company than where I’m at now. If I were to compare sizes, I’d guess they’re 10 times bigger. Most of the time, they only ask me to code thank goodness, but occasionally they’ve asked me to do some DBA things.
Enough background. Here are my top 4 most painful moments involving SQL Server. Things I wish someone had told me years ago, but I’m not sure these lessons would be burned into me like they are now.
- Forgetting the WHERE Clause– No big deal if you’re running a SELECT statement. It’s taking too long? Just hit the cancel button. On an UPDATE though? Yeah, be sure to wrap that inside of a BEGIN TRANSACTION. I ran an UPDATE where I was supposed to change a transaction counter inside ONE record in a table with only maybe 100 records, but by missing the WHERE clause, I changed ALL of the transaction numbers in their entire accounting system TO THE SAME NUMBER. I had to call them, and ask nicely but in an urgent voice “You need to get all of the users out of the system RIGHT NOW!” I knew full well what the application would do. It would begin throwing errors- not real errors, just messages- that it can’t get a transaction number and you can’t save your transaction. No Bueno. Users would begin freaking out and all hell would break lose. Luckily, I had an awesome coworker at the time who knew the data so well that we quickly fixed the bad transaction numbers and everyone was able to resume work. Phew. That was stressful. Moral of the story? Use the ‘BEGIN TRANSACTION/ROLLBACK/COMMIT’ commands
- Disabling All Indexes on a Table- This was my freelance client, which was the same client affected by #1. Once a month, they’d have me perform the “archive” process, which would move records from the current tables to the history tables, but it was done through the UI. I’d only archive one or two months at a time, but gradually this process ended up taking all night. I researched indexes, and inserts/deletes, and learned that you can greatly speed up the INSERT process by disabling the indexes. So one night I decided to try a new approach- I disabled the indexes on the main table, which was roughly 30 MILLION rows, and then ran the archive process. It was incredibly fast! What took 7 hours now took 5 minutes! I had disabled the indexes through the UI in SSMS, so I thought I could just re-enable them the same way. That didn’t work. The UI flaked out on me. Luckily though, I could tell the indexes weren’t back. I quickly figured out how to reindex the entire table using TSQL commands, but the process took all night. I had no idea how long it would take, so it was a very stressful night indeed. I really wish there was a decent way to calculate how long it’s going to take to reindex-WITHOUT having to TEST it! Who has a spare SQL Server configured EXACTLY as their production server? I don’t. Next time, I think I’d reindex ONE index and time it, and then do some sort of wild projection. Moral of the story? Disabling indexes makes INSERTS go faster, but rebuilding the indexes may take time! Being on Standard Edition SUCKS.
- Do NOT Do This In Production! A little more background- since I’m the DBA and all things IT, and I work for a smallish company, I have no need for a “test” SQL Server. I still have a test environment, but those databases are on my production server. So when a friend of mine who works for a much bigger company suggested that I run DBCC FREEPROCCACHE, he didn’t realize the kind of power I wield. When the command took a little longer than I was thinking it would, I freaked out and cancelled it while it was beginning to occur to me that this might be a SERVER specific command, and not a DATABASE specific one. “Don’t do that in production” he says, afterwards. Ooops. It pretty much wiped out my entire plan cache. I’m sure the system slowed down, but no one said anything. Sometimes it’s a good thing I’m the only one at my company capable of understanding the mistakes I make. Glenn Berry, you are a wise man. Wish I had read this first: Glenn Berry- Fun With DBCC FREEPROCCACHE Moral of the story? Some DBCC commands may be database specific, but others are SERVER specific. Know what you’re doing BEFORE you hit EXECUTE!
- Don’t get married to BEGIN TRANSACTION! After I forgot the WHERE clause, I used BEGIN TRANSACTION everywhere. Most of the time, it’s fine. Except for that one time I was trying to DELETE a ton of records from a very big table, before a major server upgrade. That night was full of mistakes, but at least I suggested that a full backup be taken prior to the migration. I should’ve INSISTED. Anyways, it was asked of me to DELETE a bunch of records, but the fact that I only had so long to do it wasn’t clearly communicated to me. I had wrapped the DELETE statement inside of a BEGIN TRANSACTION, not realizing that when you do that SQL Server logs things differently so it can roll everything back. The DELETE was taking forever. Who knows how long it would’ve taken, because I had to CANCEL it. And then I crashed SSMS because it wouldn’t close. That threw the database into RECOVERING state, which meant no server migration could happen until that ended. That was a very stressful night for me and the IT manager of that company. I called Microsoft on that one, hoping there was something we could do to get it out of that state sooner. Nope, they said. Just let it finish. Eventually it finished, and by some miracle, the server migration finished on time too. Luckily, Microsoft didn’t bill me. Moral of the story? You don’t need to ROLLBACK everything! Use BEGIN TRANSACTION wisely.