March 12, 2020 at 12:36 pm
Is query tuning the responsibilty of a Developer or is it a combined responsibility of DBA and SQL Developer.
March 12, 2020 at 1:50 pm
This varies from one organisation to the next. I've seen all combinations.
Having said that, you can usually apportion all of the "why is it so slow?" blame solely to DBAs, which is why I am a developer.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 12, 2020 at 2:35 pm
IMHO, both roles should understand how to tune queries. Of course, depending upon your organization you might be limited on the tools available, but understanding proper query writing should be a shared responsibility.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 12, 2020 at 3:22 pm
from my point of view it should be primarily on the developer - and organizations that do not give the tools to their developers to improve their code are loosing big time.
A developer with knowledge of how to do query tuning can and should make changes as required to improve its performance.
I can easily write a SQL that by looking at it it will seem all good but SQL engine will "misbehave" or not do it as well as it can - if I have visibility of what it is doing and the performance impact, I can make a call to, for example, split the SQL into 2 blocks, using a intermediary temp table, and improving the whole lot - and many times without the need for any index changes.
and while some DBA's would be able to "improve" SQL again many do not know how, neither are they allowed to change code and are only able to get back to developers and say "change it ... as it is slow" but without being able to say how to change it
March 12, 2020 at 3:25 pm
As a former DBA and now Data Architect, the way I view this is that while the developers are writing the queries and code, the DBA should guide them in best practices, and help them learn what patterns are bad and what patterns are good.
March 12, 2020 at 5:17 pm
This is akin to asking "whose responsibility is it to provide a quality product"?
Everyone's responsibility should be to make their work the best they can, not depend on other people to fix your issues. Code it right, and if it runs badly, ask for help to fix it. If you just code it and pass the buck to someone else? Don't come work where I do.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 12, 2020 at 5:27 pm
This is akin to asking "whose responsibility is it to provide a quality product"?
Everyone's responsibility should be to make their work the best they can, not depend on other people to fix your issues. Code it right, and if it runs badly, ask for help to fix it. If you just code it and pass the buck to someone else? Don't come work where I do.
yes... but I have been in places where they would not allow developers to do a Showplan on the dev box - so even a simple query with 3 or 4 simple joins/outer applies that for any developer/dba would look ok if it misbehaved in Prod the developer would have no way of tuning it.
how do you tune the code below if you don't know what SQL Engine is doing with it? assume that there are covering indexes
select a.keyfield
, a.field1
, b.field2
from tbl1 a
inner join tbl2 b
on b.keyfield = a.keyfield
and a.filter1 = 20
and b.filter2 = 300
March 12, 2020 at 6:39 pm
You simply do your best. If you are not allowed to use all the tools, then you use what you can and ask for help when you need it. but it's not a question of "who should do it", EVERYONE should do it.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 12, 2020 at 10:55 pm
Is query tuning the responsibilty of a Developer or is it a combined responsibility of DBA and SQL Developer.
You simply do your best. If you are not allowed to use all the tools, then you use what you can and ask for help when you need it. but it's not a question of "who should do it", EVERYONE should do it.
Amen to that, Jonathan!
If privs are setup correctly, the Developers don't even have read privs in Production. The best tuning they can do is from whatever they have in the Development environment. Of course, if they don't follow demonstrated best practices when writing code, their code might not be able to be "tuned"... it might have to be replaced. And, of course, they actually have to know what the best practices for writing performant code actually is.
Since the Developers (hopefully) don't have privs to Prod, the DBA is going to have to get involved. And I want to emphasize that DBAs and Developers all work for the same company and so there should be no bickering when it comes to performance tuning.
To Jonathan's point, it should be EVERYONE's responsibility.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2020 at 10:40 am
You simply do your best. If you are not allowed to use all the tools, then you use what you can and ask for help when you need it. but it's not a question of "who should do it", EVERYONE should do it.
This, this, this, this!
However, that said, if I'm RoleX (you decide if that's DBA, Dev, Database Dev, Report writer, analyst, etc.) and I've been tasked by the organization with performing query tuning (or any other task), I'm going to demand the necessary tools to get the job done. I'd raise the roof. Yes, compromises can be made, of course, but I still need some level of access to certain stuff. It's not really any different than what we try to do here, helping people out. However, we need a certain amount of information to help, or it's a waste of time. Same thing in the organization.
"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
March 13, 2020 at 2:39 pm
coolchaitu wrote:Is query tuning the responsibilty of a Developer or is it a combined responsibility of DBA and SQL Developer.
jonathan.crawford wrote:You simply do your best. If you are not allowed to use all the tools, then you use what you can and ask for help when you need it. but it's not a question of "who should do it", EVERYONE should do it.
Amen to that, Jonathan!
If privs are setup correctly, the Developers don't even have read privs in Production. The best tuning they can do is from whatever they have in the Development environment. Of course, if they don't follow demonstrated best practices when writing code, their code might not be able to be "tuned"... it might have to be replaced. And, of course, they actually have to know what the best practices for writing performant code actually is.
Since the Developers (hopefully) don't have privs to Prod, the DBA is going to have to get involved. And I want to emphasize that DBAs and Developers all work for the same company and so there should be no bickering when it comes to performance tuning.
To Jonathan's point, it should be EVERYONE's responsibility.
If the Devs can't read in prod, how can they troubleshoot prod errors? Do Devs have to wait for all current prod data to be relayed to the dev env first? I agree NO modification of prod data or code for sure. But not being able to read it? I don't see the point of that specific restriction, assuming the data itself isn't secret in some way.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 14, 2020 at 2:34 am
Jeff Moden wrote:coolchaitu wrote:Is query tuning the responsibilty of a Developer or is it a combined responsibility of DBA and SQL Developer.
jonathan.crawford wrote:You simply do your best. If you are not allowed to use all the tools, then you use what you can and ask for help when you need it. but it's not a question of "who should do it", EVERYONE should do it.
Amen to that, Jonathan!
If privs are setup correctly, the Developers don't even have read privs in Production. The best tuning they can do is from whatever they have in the Development environment. Of course, if they don't follow demonstrated best practices when writing code, their code might not be able to be "tuned"... it might have to be replaced. And, of course, they actually have to know what the best practices for writing performant code actually is.
Since the Developers (hopefully) don't have privs to Prod, the DBA is going to have to get involved. And I want to emphasize that DBAs and Developers all work for the same company and so there should be no bickering when it comes to performance tuning.
To Jonathan's point, it should be EVERYONE's responsibility.
If the Devs can't read in prod, how can they troubleshoot prod errors? Do Devs have to wait for all current prod data to be relayed to the dev env first? I agree NO modification of prod data or code for sure. But not being able to read it? I don't see the point of that specific restriction, assuming the data itself isn't secret in some way.
The way we do it is that if a Developer or Developers (presumably, SMEs) are needed to troubleshoot a problem in production, then their production account is enabled, which generally only has READ privs. As soon as the troubleshooting is completed, then their production account is disabled.
Performance tuning isn't something that we enable a production account for. We will sometimes restore a database to a safe dev box for such a thing but it generally falls on the DBA to identify performance issues down to the individual query. For code that handles large amounts of data (like for imports, exports and other nightly runs), there's a requirement to actually time and report on each section of code.
We do maintain a nightly restore that can be used for certain things like performance tuning, as well. It's used for other things but, in a pinch, it can be used for rapid troubleshooting development. I don't have a problem with giving Developers write privs there on a needed basis.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply