Ever wonder how someone else does it? There’s no right way or wrong way, but in this 20-minute session, you can peer over Brent’s shoulder (virtually) while he takes a few Stack Overflow queries, tries various techniques to make them faster, and shows how he measures the before-and-after results.
For the links and scripts, check out the Watch Brent Tune Queries page.
17 Comments. Leave new
Excellent way to start the week, just wish it could have been longer as I was interested to see how/when you make the decision that enough is enough, and what factors you have to balance to make that decision.
I know in my earlier days I wasted time fettling queries that were rarely used just because I was excited by the possibilities of performance tuning :o)
Thanks! I’m thinking about submitting it as a session for the fall conferences this year. In the live one, we ended up spending about an hour going through two queries, and even then I didn’t go into the full detail that I show in the script. With some tweaking, it’ll end up around a 90-minute session. I had a great time with it.
Along those lines one of the key parts of my performance tuning process is finding what to tune…I have a number of things I use to isolate stuff to tune (Ignite, queries of the plan cache, managers hovering over my cubicle crying about slow processes) and the plan cache in particular is a vital way to avoid spending all that time getting a query down from 1000 reads to 3 reads, when it gets called only once a month. So Brent if you do an expanded session that might be an interesting inclusion…the process that leads you to the pain points, to prioritize what to spend time on.
Great webinar – but I missed the second half of the live session as I’d only budgeted for 30 minutes. I was hoping to be able to watch the rest on YouTube but see you’ve only got the first part – I left on a cliff hanger:)
Any chance you’re going to upload a recording of the rest?
Glad you liked it! No, not at this time – it was so popular that now I’m rethinking how I want to deliver it. It’s easily 90 minutes worth of material, and people don’t usually want to sit down still for 90 minutes online. I’ll need to break it up into topics, and that requires more work. I’ll definitely think through it over the coming weeks though.
You teased us with this 20 minute video! You definately got the hang of “keep the audience wanting more” method of presenting 🙂
Going to start looking at doing this demo at work for some colleagues. I’ve put together a Bi-Weekly “Performance Tuning Face Off” this will definately help track our statistics to see who did a better job query tuning.
Great webinar Sir,
As you stressed again and again in this presentation that performance tuning is an art in itself and a methodical algorithm needs to be built to conquer these type of challenges. After I\O, it could be parallelism or on the fly implicit conversions or looking for the place where the filter is placed or the wait stats or the locks and this list goes endless. A lot of my friends are pure infrastructure DBAs managing backups, restore, availability and recovery but for someone who wishes to look into improving the performance from the code level needs to understand T-SQL well and as he understands the bigger picture clearly, he will find that all these rivers(hardware, code) meet in the sea and a master needs to be atleast a jack in all these basics be it code, or CPU or memory.
BTW, as soon as you mentioned re-writing the code with CTEs, I was thrilled and waiting for the next set of improvements, the very mention of ‘happy query tuning left me wanting more at the very moment and felt unsatisfied:-)
PS: when you sneeze or cough, you manage it well and make it look like that it was a planned one and part of presentation itself 🙂 !!!
I have table with columns like AutoID, Number, Name, City, State, Country.
What I wanted is the maximum number entered in the “Number” column with the combination of Name, City, State and Country.
Example:
Name City State Country
Smith NY NY USA
John NY NY USA
John NJ NY USA
Now smith should get “Number” 1, John 2, and again John(in NJ) 1 as he is the first from NJ.
I can simply put a where clause in query and get the max number + 1. But the problem is that when I have huge amount of data and the number of users increases, my query will be really slow. I am also inserting data in the same table so it will keep on piling.
I hope I have made my self clear.
Hi. You’re commenting on a blog post about watching Brent Ozar tune some queries. As much as he’d like to tune your queries too, this isn’t really the right place to come to get your query problems solved for free. It’s a place to listen to Brent write about himself in the third person.
You might try asking for help in conventional Q&A sites like http://DBA.StackExchange.com or http://SQLServerCentral.com.
Thanks!
Brent
Really great one thanks for this article.
Regards,
Gangadhara
Very nice explanation Brent. I like the message “Tuning Queries is not about changing DB for that query but changing/rewriting the query in most possible appropriate way.”
About Query tuning I did learn somewhere that we should not change DB for 100 slow queries but we should try to make those 100 work best with DB.
Even on S.O. forum when I answer such query performance questions I always explain that what query writer should note is how much data query will process in each statement and how much it is passing on to next level.
You are also right about the immediate reaction from people when they see a missing index warning. I have seen instances where just slow retrieval of data immediately results in to “we need to add index” kind of conversation……
Brent, I must say as a programmer who has recently taking an interest in the mechanics of SQL Server and SQL performance a have a man crush on you! I love your site, your team and most of all your content. Your work is exceptional and very much appreciated.
Awww, thanks!
JP – awww, thanks sir!
Wow! This was so, SO helpful! I really appreciate that you took the time to explain your thinking so clearly. I was able to follow what you were saying and for the first time have a sense of what query tuning really is.
FYI: I would be happy to sit through a 90 minute video on-line if one available. I would probably break up my viewing, but the length of the video would not deter me.
Thanks.
Sandra – thanks, glad you liked it!
Thanks for this – it was *very* useful !