December 7, 2017 at 11:24 am
Hi All,
Its a general query performance expectations setting question for a non-tech or adamant TL's/mgr's/customer.
If someone comes to your desk and comes and say, my query is taking 10 secs to run and I want to reduce the response time to 2-3 secs. How do we set the expectations on performance issues to such people who don't listen. How can we make them understand what it takes nicely & politely to make them shut.
Thanks,
Sam
December 7, 2017 at 11:49 am
vsamantha35 - Thursday, December 7, 2017 11:24 AMHi All,
Its a general query performance expectations setting question for a non-tech or adamant TL's/mgr's/customer.
If someone comes to your desk and comes and say, my query is taking 10 secs to run and I want to reduce the response time to 2-3 secs. How do we set the expectations on performance issues to such people who don't listen. How can we make them understand what it takes nicely & politely to make them shut.
Thanks,
Sam
Since neither of you actually know if it can be done or not, you need to say that you'll take a look and, if the code is documented well enough to easily understand and they've provided a test case to measure by, you'll see what (if anything) can be done to improve the performance. Then, have them "open a ticket" so you can track your time on it so that when they ask "What is it you do?", you can show them.
To be honest, you should love the opportunity to show your stuff. Yesterday, two people showed up in my office at different times for similar things. I took the time to teach them how I analyze such problems and how I make the decision as to what to do. One of them was a 40 second query of about 14 lines of simple code that was consuming over 331MB of logical IO and a ton of CPU... when we were done, it was running in 250MS and consumed about 33K of logical IO. They were tickled to death to see how it was done, how to avoid such problems in the future, and now have an appreciation of what I actually do for a good part of the day.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2017 at 6:40 am
It's all about understanding what you're doing well enough to educate people. If they have a query that is SELECT * FROM Table and it returns 20 terabytes of data and they're asking you to make it run in 3 seconds, you educate them on how fast disks work, how much bandwidth the network can support, etc., so that they understand that what they're asking for isn't possible. However, be sure you're right. If necessary do research to figure out what's up.
Further, always question what they've done and why they've done it. We had a situation once where the requirement was 10 million rows in 2 seconds. We went into the whole spiel about how that physically wasn't possible. We got an answer from them "Well, we do it now." We go to their desks. They're returning 10 rows, not 10 million, and they have a count on the side that shows 10 million rows as a count. So yeah, we could return 10 rows and a count... and we did it in a lot less than 1 second.
Tuning isn't just adding or not adding an index. There's a lot of knowledge and understanding involved. As Jeff says, this is your chance to show off. Plus, maybe you can tune their query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 8, 2017 at 6:52 am
Thanks Jeff & Grant for nice words. I feel good.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply