-
Keymaster
What makes you a SQL Superhero? Share a story of how you saved the day to win awesome prizes. Find and fix an unexpected performance issue? Stop a potential security threat? Whatever it may be, we want to hear!
The participants with the top three stories will win an Amazon gift card.
1st place – $75 Amazon gift card
2nd place – $50 Amazon gift card
3rd place – $25 Amazon gift card
All winners will also receive an Idera SuperDuck T-shirt and limited edition SuperDuck.How to enter:
Submit your story in the comment section below (don’t have an account? You can create one here
Check back to see stories of how your fellow SQL community members have saved the day
Three participants will be chosen as the winners at the end of the contest (June 30, 2015) and will be announced in this forum.
? Sandi Warren
Idera Staff / Director, Product Marketing
ParticipantMy SQL super hero power is query tuning. I take ugly queries that were written 4 years ago for 100s of rows that took milliseconds to run that now take 20-30 minutes to run parsing through 100s of millions of rows and turn them into efficient data retrieval tools.
Using the cache plan I analyze the READS and row estimates and usually the problem come from the fact the temp table they are building will eventually hold 100s of millions of rows because there are no filters on the SELECT statement. Filtering the initial SELECT statement to get the temp table usually solves my problem. You don’t need all the rows of a table loaded into a temp table just to join it to a small subset to get 20 rows as the final result!
ParticipantMy SQL super hero power is index consolidation and tuning.
Unused and duplicate indexes were disabled. Overlapping indexes were consolidated.
Infrequently used indexes were disabled when other indexes were available to cover the query.
After consolidation, storage reduced 25% and dramatically improved overall query performance.Most expensive queries were then tuned with indexes.
Key Lookups are expensive and columns were added to covering index.
For composite indexes columns were ordered with comparison expressions in the WHERE clause listed first.
Subsequent columns listed based on the uniqueness of their values, with the most unique listed first.ParticipantWhat makes me a SQL superhero is my work to improve the BI stack in my company. By playing nicely with my SAN admin and working with the hardware, BI dev, and Windows server team, together we’ve taken a daily ETL process that ran for 15 hours and condensed it down to just 3 hours. Today, we’re doing twice the amount of ETL work than 2 years ago when the process started, but doing it in 20% the time.
All of the team members contributed to the cause, improving queries, designing and implementing a robust infrastructure, refining data architecture, and coming up with new and innovative ways to use and present the data. Using the unique software of our All-Flash array, we are able to copy the entire 10 TB BI database to two reporting servers in about 10 seconds in a way that’s seamless to users. The business is happy that yesterday’s operational data is available at 8:00 AM and the IT department looks good for making it possible. The data being available a day sooner than it was before means that the company is more responsive and prepared to make better decisions.
Idera SQL Safe Backup fans will be happy to hear that SQL Safe was a piece of the puzzle that helped us achieve our goal. We used SQL Safe to do backups that compressed at least twice as tight as native SQL compression would allow and ran in about half the time (using the iSpeed option). This saves us money on storage and makes restores to Dev and QA a lot easier to manage.
ParticipantThe first week on a previous job I had a monumental report overhaul.
About four people were spending at least an entire day, every week, editing a huge report. They were removing bad data and basically having to reformat the entire thing. This work was so boring that they had to rotate people off the task because employees had quit over the futility. The company was profitable enough that their plan of action had been “throw more people at the problem” instead of fixing it.
This report was old, originally written in Visual Basic and Access SQL. It had nested cursors and variable usage that I still don’t fully understand. I had a self-taught crash course in Visual Basic while also trying to learn the new job’s database while trying to fix the report. The users didn’t even realize the full extent of the problem until I sent them a few rough drafts. Afterwards, it became apparent that trying to mimic the original report was less useful than working with the users to supply what the vendor actually needed.
In the end, I saved many people hours of frustration and tedious work after automating the report. What was taking 30-40 hours a week to process and could only be ran early in the morning to avoid contention now ran in a fraction of the time and took a few minutes to review for anomalies before sending to a much happier vendor.
KeymasterThanks for participating in our contest. Here are the winners.
1. Chris Hinson
2. Daniel Mellor
3. Ken KimWe will reach out to the winners via email to get your info to send you the prize.
? Sandi Warren
Idera Staff / Director, Product Marketing
