T-SQL Tuesday #92: Lessons Learned the Hard Way
This post is a response to this month's T-SQL Tuesday prompt. T-SQL Tuesday was created by Adam Machanic and is a way for SQL users to share ideas about interesting topics. This month's topic is Lessons Learned the Hard Way.
Watch this week's video on YouTube
"Is this your query that's killing the server?"
It was my first week on the job and I was learning to query one of our major databases.
Up until that point, my SQL experience was limited to working on a *tiny* e-commerce database. Query performance was never something I had to deal with because any query I wrote, no matter how poorly written, would always execute quickly.
This new database I was working on though had tables with a billion+ rows. I should have been more conscious about how I was writing my joins and filtering my records, but I wasn't. I wrote my query and executed it in SQL Server Management Studio.
About 20 minutes into my query's execution, I received an email from my new DBA, and it looked something like this:
"Is this your query that's killing the server?"
Oops.
I don't think my mouse ever moved to the stop execution button as quickly as it did that moment.
I was incredibly embarrassed to have brought our production server to a crawl. I was also incredibly embarrassed to have had my first interaction with my new DBA be about a query that created major problems for him.
Although there were no long-term damages from my server-crushing query, it was a scenario that I definitely didn't want to relive again in the future.
Next time: don't do that again
Obviously, this was an experience where I learned that maybe I shouldn't write queries against unfamiliar data in production.
- I should have been practicing on a dev database.
- I should have looked at table meta data and made sure I understood relationships between tables better.
- I should have done some more preliminary querying with more restrictive filters to be able to catch performance problems earlier on with smaller result sets.
- I should have examined what indexes were available and made sure I was attempting to use them.
- I should have used a
(NOLOCK)if I absolutely had to test on the production data so that at the very least I wouldn't prevent the high transaction ETLs from modifying data in that database.
All of those "should haves" quickly became my checklist for what to do before running any query in an unfamiliar environment.
I've still written plenty of ugly and inefficient queries since then, however none of them ever caused me to bring the SQL server to a halt like I did in my first week. That was one lesson that I learned the hard way.
