Is SQL Tuning worth in Production Database Environment?

In my opinion the DB/Instance Tuning is good for all DBA as most of the time we do not have the right to change the SQL in the application as application team is not willing to change he code and its style. Mostly the Dynamic SQL's.


SQL tuning generally delivers the most significant performance gains. Lack of experience or time, inability to change SQL and other barriers are mostly excuses. It might not be easy at time but it needs to be done. I think SQL Tuning is worth in every environment, not just in Production. My point is that there is good and not so good SQL Tuning. And tuning one SQL can un-tune several other SQLs.


SQL tuning is too good while the application is still under development where development team members and database analyst have to know how much data needs to be fetched and time statistics. In most of the production databases environments, I also see that the time spend for a query tuning is too much as compared to tweaking any parameter subject to the resource where he/she is having good knowledge of it. But again SQL Tuning should be taken care by Development team members where they know more about the application behavior. I could also see in my previous teams that they have pressure to release the change request (CR) and in a hurry they release the untuned code and ultimately when it is on production then blame game starts from here on.


In my opinion, whether it's developers of development DBAs or production DBAs, they should be armed to access production environments to assist with SQL diagnostics and tuning. How the changes to SQL are made is subject to your change management and development life cycle.


Again, SQL tuning is one of the biggest performance tweaks you can make. It's true, you don't always have the ability to change SQL if it's dynamically generated, comes from a vendor product, or developers just don't have time to work on it. Nonetheless, I believe it can have some of the biggest impact on databases, production or not, and provide some of the biggest performance improvements you might see. 


Certainly there can be other areas of tuning that can have a significant impact on performance - memory, CPU, buffer space, etc. But, after many years of being a DBA, the thing I've seen more times than anything else is inefficient SQL. It's worth knowing how to find such code and determine if anything can be done to improve it. While you may be limited to throwing hardware at performance issues, that's certainly not the best nor most cost effective way to improve performance. 


In our shop, we can look for areas in which SQL might be improved and if we see changes that need to be made to the SQL statement itself, we work with the development group to make and test such changes before pushing them into production.