May 24, 2018 at 2:37 am
Hi
I'm looking for ideas\opinions\experience when it comes to testing new indexes.
Previously I've been able to create indexes if I think it's reasonable. I'd make changes on a test system and make sure they did what I intended.
Where I am now, if I want to add an index to a production system the change has to go through the test team. The test team will then run automated test pack against it and then, if appropriate, run and overnight batch process against it.
If there is a production issue that needs resolving this process takes a day. If it's just an improvement and not addressing an "issue", I'll probably never get the test resource to put it into production, its a bit frustrating.
This all feels a bit heavy handed to me. What are other peoples experience of implementing indexing changes? How can I allay managers fears whilst being able to make changes.
I realise this is an "it depends" question and really dependant on company culture and personalities. I'm looking for ideas from the community not answers 🙂
Cheers
Alex
May 24, 2018 at 4:59 am
alex.palmer - Thursday, May 24, 2018 2:37 AMHiI'm looking for ideas\opinions\experience when it comes to testing new indexes.
Previously I've been able to create indexes if I think it's reasonable. I'd make changes on a test system and make sure they did what I intended.
Where I am now, if I want to add an index to a production system the change has to go through the test team. The test team will then run automated test pack against it and then, if appropriate, run and overnight batch process against it.
If there is a production issue that needs resolving this process takes a day. If it's just an improvement and not addressing an "issue", I'll probably never get the test resource to put it into production, its a bit frustrating.
This all feels a bit heavy handed to me. What are other peoples experience of implementing indexing changes? How can I allay managers fears whilst being able to make changes.
I realise this is an "it depends" question and really dependant on company culture and personalities. I'm looking for ideas from the community not answers 🙂
Cheers
Alex
Do you have a test instance to work with? Documenting before and after would be the easiest way.
😎
More elaborate would be to catch the execution plans of the affected queries, do a rough calculation of the impact and then create a document that highlights the benefits, i.e. preventing sort operations etc.
May 24, 2018 at 6:04 am
alex.palmer - Thursday, May 24, 2018 2:37 AMHiI'm looking for ideas\opinions\experience when it comes to testing new indexes.
Previously I've been able to create indexes if I think it's reasonable. I'd make changes on a test system and make sure they did what I intended.
Where I am now, if I want to add an index to a production system the change has to go through the test team. The test team will then run automated test pack against it and then, if appropriate, run and overnight batch process against it.
If there is a production issue that needs resolving this process takes a day. If it's just an improvement and not addressing an "issue", I'll probably never get the test resource to put it into production, its a bit frustrating.
This all feels a bit heavy handed to me. What are other peoples experience of implementing indexing changes? How can I allay managers fears whilst being able to make changes.
I realise this is an "it depends" question and really dependant on company culture and personalities. I'm looking for ideas from the community not answers 🙂
Cheers
Alex
While I agree that running new indexes through the same gauntlet as code might seem heavy handed or even totally ridiculous, it's actually not, although it will always seem frustrating... until you run into the same problem I did. I single handedly brought Expedia.com to its knees about 8 or 9 years ago for about 5 minutes just by adding what I thought (and could prove at the time because I tested it on a test system) was a good index.
The index was an absolute miracle of performance for SELECTs but when applied insitu on production, the number of bad page splits on the index due to the large volume of inserts on the table absolutely crushed performance even though the index was only 3 columns wide.
Very fortunately for me, we had performance monitoring alerts setup and as I was in the process of soiling my britches, I was able to quickly remove the index once I realized that it was all my fault.
Embrace QA for everything. They'll save your life even if it does take time to do so.
--Jeff Moden
May 24, 2018 at 7:56 am
Jeff Moden - Thursday, May 24, 2018 6:04 AMalex.palmer - Thursday, May 24, 2018 2:37 AMHiI'm looking for ideas\opinions\experience when it comes to testing new indexes.
Previously I've been able to create indexes if I think it's reasonable. I'd make changes on a test system and make sure they did what I intended.
Where I am now, if I want to add an index to a production system the change has to go through the test team. The test team will then run automated test pack against it and then, if appropriate, run and overnight batch process against it.
If there is a production issue that needs resolving this process takes a day. If it's just an improvement and not addressing an "issue", I'll probably never get the test resource to put it into production, its a bit frustrating.
This all feels a bit heavy handed to me. What are other peoples experience of implementing indexing changes? How can I allay managers fears whilst being able to make changes.
I realise this is an "it depends" question and really dependant on company culture and personalities. I'm looking for ideas from the community not answers 🙂
Cheers
Alex
While I agree that running new indexes through the same gauntlet as code might seem heavy handed or even totally ridiculous, it's actually not, although it will always seem frustrating... until you run into the same problem I did. I single handedly brought Expedia.com to its knees about 8 or 9 years ago for about 5 minutes just by adding what I thought (and could prove at the time because I tested it on a test system) was a good index.
The index was an absolute miracle of performance for SELECTs but when applied insitu on production, the number of bad page splits on the index due to the large volume of inserts on the table absolutely crushed performance even though the index was only 3 columns wide.
Very fortunately for me, we had performance monitoring alerts setup and as I was in the process of soiling my britches, I was able to quickly remove the index once I realized that it was all my fault.
Embrace QA for everything. They'll save your life even if it does take time to do so.
He he, as mentioned in the OP, it all depends. I'm not surprised that you found the right spanner to insert into the works Jeff!
😎
A proper test system can sometimes pay for itself in less than a minute 😉
May 24, 2018 at 8:09 am
Eirikur Eiriksson - Thursday, May 24, 2018 7:56 AMJeff Moden - Thursday, May 24, 2018 6:04 AMalex.palmer - Thursday, May 24, 2018 2:37 AMHiI'm looking for ideas\opinions\experience when it comes to testing new indexes.
Previously I've been able to create indexes if I think it's reasonable. I'd make changes on a test system and make sure they did what I intended.
Where I am now, if I want to add an index to a production system the change has to go through the test team. The test team will then run automated test pack against it and then, if appropriate, run and overnight batch process against it.
If there is a production issue that needs resolving this process takes a day. If it's just an improvement and not addressing an "issue", I'll probably never get the test resource to put it into production, its a bit frustrating.
This all feels a bit heavy handed to me. What are other peoples experience of implementing indexing changes? How can I allay managers fears whilst being able to make changes.
I realise this is an "it depends" question and really dependant on company culture and personalities. I'm looking for ideas from the community not answers 🙂
Cheers
Alex
While I agree that running new indexes through the same gauntlet as code might seem heavy handed or even totally ridiculous, it's actually not, although it will always seem frustrating... until you run into the same problem I did. I single handedly brought Expedia.com to its knees about 8 or 9 years ago for about 5 minutes just by adding what I thought (and could prove at the time because I tested it on a test system) was a good index.
The index was an absolute miracle of performance for SELECTs but when applied insitu on production, the number of bad page splits on the index due to the large volume of inserts on the table absolutely crushed performance even though the index was only 3 columns wide.
Very fortunately for me, we had performance monitoring alerts setup and as I was in the process of soiling my britches, I was able to quickly remove the index once I realized that it was all my fault.
Embrace QA for everything. They'll save your life even if it does take time to do so.
He he, as mentioned in the OP, it all depends. I'm not surprised that you found the right spanner to insert into the works Jeff!
😎A proper test system can sometimes pay for itself in less than a minute 😉
It was a "proper" test system that I started with. 😉
--Jeff Moden
May 24, 2018 at 8:50 am
That's what my management are scarred of. A process we're not aware of battering the table and the new addition making performance collapse.
Have you ever used any tools to simulate impact of new indexes on SELECT, UPDATE and INSERTs
May 24, 2018 at 9:43 am
alex.palmer - Thursday, May 24, 2018 8:50 AMThat's what my management are scarred of. A process we're not aware of battering the table and the new addition making performance collapse.Have you ever used any tools to simulate impact of new indexes on SELECT, UPDATE and INSERTs
In my experience, only a proper testing system with the "same" data and simulated activity will provide an answer accurate enough.
😎
Proper test system means same hardware, down to the make and model, data with the same profile and activities being replayed on network level (packet capture/replay) or equivalent replay mechanism.
May 24, 2018 at 2:15 pm
Jeff Moden - Thursday, May 24, 2018 8:09 AMEirikur Eiriksson - Thursday, May 24, 2018 7:56 AMJeff Moden - Thursday, May 24, 2018 6:04 AMalex.palmer - Thursday, May 24, 2018 2:37 AMHiI'm looking for ideas\opinions\experience when it comes to testing new indexes.
Previously I've been able to create indexes if I think it's reasonable. I'd make changes on a test system and make sure they did what I intended.
Where I am now, if I want to add an index to a production system the change has to go through the test team. The test team will then run automated test pack against it and then, if appropriate, run and overnight batch process against it.
If there is a production issue that needs resolving this process takes a day. If it's just an improvement and not addressing an "issue", I'll probably never get the test resource to put it into production, its a bit frustrating.
This all feels a bit heavy handed to me. What are other peoples experience of implementing indexing changes? How can I allay managers fears whilst being able to make changes.
I realise this is an "it depends" question and really dependant on company culture and personalities. I'm looking for ideas from the community not answers 🙂
Cheers
Alex
While I agree that running new indexes through the same gauntlet as code might seem heavy handed or even totally ridiculous, it's actually not, although it will always seem frustrating... until you run into the same problem I did. I single handedly brought Expedia.com to its knees about 8 or 9 years ago for about 5 minutes just by adding what I thought (and could prove at the time because I tested it on a test system) was a good index.
The index was an absolute miracle of performance for SELECTs but when applied insitu on production, the number of bad page splits on the index due to the large volume of inserts on the table absolutely crushed performance even though the index was only 3 columns wide.
Very fortunately for me, we had performance monitoring alerts setup and as I was in the process of soiling my britches, I was able to quickly remove the index once I realized that it was all my fault.
Embrace QA for everything. They'll save your life even if it does take time to do so.
He he, as mentioned in the OP, it all depends. I'm not surprised that you found the right spanner to insert into the works Jeff!
😎A proper test system can sometimes pay for itself in less than a minute 😉
It was a "proper" test system that I started with. 😉
I worked at a GDS so I may be one of the few who completely believes that absolutely can happen with an expensive, well integrated totally proper test system. 🙂
But most systems don't have issues where a stored procedure suddenly running 0.25 ms longer can cost thousands in a matter of minutes. Things are quite different in that industry.
Sue
May 24, 2018 at 2:59 pm
alex.palmer - Thursday, May 24, 2018 8:50 AMThat's what my management are scarred of. A process we're not aware of battering the table and the new addition making performance collapse.Have you ever used any tools to simulate impact of new indexes on SELECT, UPDATE and INSERTs
The folks on the front end for our company built a custom load test for our system. It works fairly well but it's not perfect.
To be sure, having a goof test system that can simulate the load is great but should be great Will it catch everything? Nope. Its still very worthwhile to do.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply