SQLRockstar (Thomas LaRock) has another interesting suggestion to blog about for today’s meme monday. He suggested we share some the things that go wrong with our database servers that aren’t related to the disk.
Memory Settings
Up until Sunday morning, our servers were running with 8gb of RAM, and had “max server memory” set to default. After our maintenance windows our servers how have 12 GB of ram, and 10 GB dedicated to SQL Server. No more memory usage exceeded 99% warnings. No more odd behavior when someone connects to the server using RDP. Hopefully this is the last time (for the next few months) I see this issue.
The Overpromise
I understand why sales people do what they do. They want to land that contract so they get their commission. That shouldn’t mean we have to work all hours in order to get it done. Share the responsibility of putting together a time line. You’ll get more buy-in from your technology team by inviting us in. You’ll also get to show off your staff during those sales meetings. (hopefully your team shines like mine does!)
Lack of Referential Integrity
I haven’t lost track of the number of database systems I’ve worked on that didn’t have foreign keys. I’ve even worked on some where the primary key wasn’t defined in the database layer, but in the application layer. Really, you want to build your own referential integrity checks?
If you have that much free time, I believe we could use you in rebuilding some pyramids.
Active Directory
This is the newest member of my…list. Due to active directory objects becoming corrupt, my primary database cluster went offline two weeks ago. It cost me a 9 in my 99.99% uptime score. As a result, I’ve decided I’m going to study that, after I finish my MCITP Database Administrator exams. I will not rely on someone else to explain it to me any more. It’s time I learned it for myself.
Bad Database Design
5000 tables in a single database. 1000 views. 350 stored procedures. And a partridge in a pear tree.
No, this isn’t a new horrible Christmas carol. It’s the actual design of a database I worked on not too long ago. Needless to say, I did roll my eyes at this one. Developers, please come to me and let’s discuss what you’re trying to do. I’ll make the database go, you make the code go…deal?
Deadlocks
These used to take forever to diagnose. Now I have just 6 words for you: SQL Sentry Performance Advisor for SQL Server. Now it takes 20 seconds, tops, to find and resolve the problem. Get a demo of this software today! You have no idea what you’re missing out on, until you use this product.
Backups
Not only do you want to backup your databases, but you want to make sure everyone else in your systems team is backing up their stuff too! If you can’t restore your Active Directory objects, you’re cluster isn’t coming back online. Enough said?
Trusting the Tuning Advisor or the Query Optimizer Blindly
There’s no real problem using these tools, but when you trust their findings and apply the changes without testing them first. Wow. I really feel for you. Always test the suggestions somewhere other than production before applying them to production.
NOLOCK Is The Answer!
NO, it isn’t. And no, you don’t know kung-fu! Now stop using this in production before I revoke your login.
I bet you thought I was going to use another Matrix reference, eh?
So, what problems are you having with SQL Server?
Feel free to share yours below!