snippetsqlModerate
How to answer why suddenly we need indexes or query needs to be changed
Viewed 0 times
whyanswerneedneedsqueryindexessuddenlyhowchanged
Problem
I am junior DBA with 3 years of experience. Our job is to fine-tune queries or advise developers that particular code should be re-written or indexes are needed.
One simple question the dev team asks frequently is: "Yesterday it ran fine, what changed suddenly?" and we will be asked to check the infrastructure side. The first reaction on any problem always appears to be to put maximum blame on the infrastructure which is always the first thing being validated.
How should we answer to "what changed" questions by the development team? Did you guys ever faced the same situation? If so, please share your experience.
One simple question the dev team asks frequently is: "Yesterday it ran fine, what changed suddenly?" and we will be asked to check the infrastructure side. The first reaction on any problem always appears to be to put maximum blame on the infrastructure which is always the first thing being validated.
How should we answer to "what changed" questions by the development team? Did you guys ever faced the same situation? If so, please share your experience.
Solution
How to answer what changed question by dev ?
This is a very common question not only with DEV, it applies to every team in IT and business.
What changed ? ==> can be answered by facts and figures.
Facts refer to for example
Figures can be derived if you have data to show. For example :
(you have to workout based on your environment and needs, on how often to collect the data/ what data to collect and how much will be the retention period) or (you can invest in a third party software like sqlsentry or idera's diagnostic manager that will do the above work for you).
This is a very common question not only with DEV, it applies to every team in IT and business.
What changed ? ==> can be answered by facts and figures.
Facts refer to for example
- increase the amount of users that are accessing the database ?
- Any change in server configuration parameter ?
- Database Maintenance - update stats, reorg/rebuild of indexes not being performed ? Due to this the plans are being incorrectly generated !
- Amount of data has increased ?
- Changes were made on the network side, OS was patched and/or a new service pack or CU for sql server was deployed - without doing a full regression testing of your application business cycle ?
- The underlying SAN has become suddenly slow ?
Figures can be derived if you have data to show. For example :
- Baselining your server is critical during this situation. This will alleviate the blame game since you can support the facts with solid figures.
- Start collecting data using DMVs or sp_whoisactive to a table, so that the data gets persisted after a sql server reboot.
(you have to workout based on your environment and needs, on how often to collect the data/ what data to collect and how much will be the retention period) or (you can invest in a third party software like sqlsentry or idera's diagnostic manager that will do the above work for you).
Context
StackExchange Database Administrators Q#111335, answer score: 10
Revisions (0)
No revisions yet.