October 1, 2018 at 9:11 pm
Comments posted to this topic are about the item Are Indexes Actually Changes to the System?
October 2, 2018 at 1:41 am
To my mind, the answer is simple: Yes, Indexes are changes and should be governed by change control.
Indexes have a cost (not least disk space)
Indexes can have negative as well as positive impact on performance, as mentioned in the article
Index changes DO need to go through non-functional (and functional) testing
Indexes are part of the database schema and should therefore be stored in source/version control like everything else, not added ad-hoc by anyone.
Closer ties between dev & ops is certainly worth aspiring to, i.e. "devops" if your organisation is suited to it.
October 2, 2018 at 1:45 am
If I was worried about disk space then I'd be asking how I could determine the amount of disk space required through automated means. Similarly could I predict locking/blocking impact mechanically?
I would design a good process, automate as much of it as possible, document it both as an operational playbook and separately as an educational piece.
In my experience change review boards have a nasty habit of descending into ego stroking committees for yesterday's men. The worst of them add bureaucracy and distraction that actually increases the likelihood of a failed change.
October 2, 2018 at 4:44 am
In the code world, an index could still be a source file that is part of the source system when compiled and or executed as part of the application. Therefore, it would be added to the repository and tracked line anything else. So, for example. The index file would be a configuration file that fed into a function within the application to help the application make a decision on what to do with the data. That function may be the actual algorithm that defines how the data is stored, how it's written, and how it's searched when the user wants to query the data.
However, there are plenty of cases where configuration files are provided outside the source that are part of the repository that can be altered or allowed to provide additional configuration from the end user to add more functionality. The issue here is that configuration file has no default in the source with SQL Server and is not required to run.
October 2, 2018 at 6:00 am
If your index changes are not put into a script and attached to a change request, how can you put a database back to the same state if you later have to restore from an older backup?
If there is a complaint about performance later, you could go back through the latest changes to see if it all began when an index was created, dropped or modified.
October 2, 2018 at 6:22 am
Any Organization I've been working with in the past few years did consider Indexes as something to be change controlled, and I agree. It's a change in the code base for the DB so it should be change controlled.
Wether a particular Index will be relevant to CAB is to be determined by potential impact, especially in 24/7 Usage OLTP Systems.
October 2, 2018 at 7:07 am
I definitely believe that indexes should be treated just as code and be controlled as such. Index changes can affect performance in a severe way either positively or negatively. Because of that, you do not want a poor index to impact production due to a lack of controls. As with anything else, emergency protocols can get a much needed fix into production as soon as possible, but at least there will be accountability.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
October 2, 2018 at 7:10 am
Our process for an index on a non-SOX system would be:
- Add the index to the db project (create a HF branch if applicable)
- Deploy to QA/UAT environments
- Run expected tests (minimal, regression typically not required).
- Enter a change notification
- Deploy to PROD environment (merge HF branch if applicable)
Typical time for process end-to-end is ~20minutes
What's new to me in our process is the change notification. For smaller changes that don't require an outage and have been confirmed transparent we don't follow the normal change control approval process. However since any change has the potential to impact performance or user experience we follow a change notification process. The notification process is to post a message with the details and schedule of the change being made the "Change Notification Channel" that we setup in MS Teams. If someone has concerns they can raise them in the channel up to the change's scheduled time (no less than 15 minutes from notification). When users report issues, the help desk or system admins can search the change notifications for related changes and report the issue in the thread and get help. This has made us much more agile and has sped up the process of "little" changes.
-
October 2, 2018 at 8:13 am
If your organization is like mine and includes TB scale tables, dozens of developers coding SQL, and hundreds of end users, then adding a new index should definitely be something that gets promoted through the formal change control process. An index creation process can run for hours, hog resources, and block user activity. Also, a new unique index based on invalid assumptions about key columns can break the application to the point where it's at a dead stop. However, when indexes are added post-production, it's typically in response to a performance issue, so it's not something like a new feature where folks mull it over for weeks and it sits on a back burner waiting for multiple approvals.
When I create a new index, it's created in development to confirm it's usable and doesn't break anything. Next, it's deployed to UAT which has a similar data volume as production and where I perform some additional tests to document that metrics like query duration, io, and cpu have improved, and also provides some expectation about how much resources and time the index creation itself will require for a production deployment. The result of that evidence based unit testing in development and UAT can then be used as the basis for getting sign off from management for the production deployment. Any process where you're creating indexes and deploying them to production in less than 24 hours is probably too risky, even if it's considered critical. There needs to be methodology in place.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 2, 2018 at 12:16 pm
I am of the opinion that index changes should go through change control. I know in some places it can be big and boxy. I have spent many hours waiting on the conference call for everyone to run through their list of changes to PROD on a change control management meeting before release. I think the undesirable parts of that could be dealt with but it is a necessary thing to have them for index changes. Especially with large data sets they can have implications that you don't want to be surprised by.
October 2, 2018 at 1:34 pm
Does it touch production? Yes
Could it have an impact on performance? Yes
Does your development team have source control around code? Yes, change should be checked in so that it can be tracked, utilized, or removed in future releases.s
Is it a change (Add, Change Delete)? Yes
Then it should be tracked as part of any basic change control process. Right or wrong, fast or slow follow the process. Should the process ever need to catch up get in the habit of creating indexes with an IF NOT EXISTS or WITH (DROP_EXISTING = ON)
Others have outlined a process:
- Add Development task/work item. Add the index to Source code. Have peer review. Deploy to Dev environment and have Dev management approve.
- Deploy to QA/UAT environments for QA approval
- Run tests as need with process specific or regression testing specific to the change or full regression time permitting
- Enter a change request for approval (creator can not be an approver or implementor)
- Deploy to PROD environment once all approvals are completed (Automated or Manually)
- Run tests as need with process or regression testing specific to the change (implementer should not be the tester)
- Close out change
October 2, 2018 at 1:56 pm
Thanks for the editorial, it's good to have these discussions.
To my mind, the answer is in the title itself: "Should Index Changes Require Change Control?"
They are changes to the database structure, so yes, they should be governed by change control.
I know we live in the real world, so there are sometimes some cases where such a change would have to be made before getting the change approval. But that still means the change should be communicated to the right people as soon as possible along with the reasons why. And also that a proper change request would still be submitted and approved to have a record of the change in the change management system.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 3, 2018 at 7:21 am
Jason- - Tuesday, October 2, 2018 7:10 AMFor us, indexes are a change to source controlled objects. We use visual studio database projects so since the database project needs to be updated and deployed, an index is technically a regular change. I like the process we follow where I work because it adds some flexibility in cases where changes are anticipated to be transparent to end users and other systems.Our process for an index on a non-SOX system would be:
- Add the index to the db project (create a HF branch if applicable)
- Deploy to QA/UAT environments
- Run expected tests (minimal, regression typically not required).
- Enter a change notification
- Deploy to PROD environment (merge HF branch if applicable)
Typical time for process end-to-end is ~20minutesWhat's new to me in our process is the change notification. For smaller changes that don't require an outage and have been confirmed transparent we don't follow the normal change control approval process. However since any change has the potential to impact performance or user experience we follow a change notification process. The notification process is to post a message with the details and schedule of the change being made the "Change Notification Channel" that we setup in MS Teams. If someone has concerns they can raise them in the channel up to the change's scheduled time (no less than 15 minutes from notification). When users report issues, the help desk or system admins can search the change notifications for related changes and report the issue in the thread and get help. This has made us much more agile and has sped up the process of "little" changes.
I rather like that
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 3, 2018 at 9:20 am
I'm all for change processes like that which Andrew posted and we have it all documented in our WIKI, which also includes the development process and the peer review process. We generally follow the same process even for "urgencies" but at a greatly accelerated rate (everyone is ready to take the handoff).
The problem with indexes is that the optimizer must be dealt with and while everyone tests for performance of queries, they almost never test all the rest of the stuff that indexes affect. I know this because you're looking at the guy that brought Expedia.com to its knees for 2 minutes.
There are multiple problems associated with testing indexes in Dev, Test, UAT, whatever.
The first problem is that they usually have a sometimes significantly lesser amount of data to test against so even testing for performance of queries can be in error simply because the optimizer may (and frequently does) select different execution plans based on stats which reflect the row counts.
The second problem is that such non-production environments never have the same amount of activity/load and so INSERTs and UPDATEs might actually be crippled by the bad page splits that most indexes suffer. That's what happened with the index I built for Expedia.com that I was talking about.
The third problem is the optimizer itself and almost no one checks for the kinds of problems that can crop up there. For example, you might build some sweet index that solves a wealth of problems but not realize that some other critical code might also start using that index instead of a wider index that it's supposed to use and the row lookups and scans can be crippling. I've seen such a thing happen where the optimizer decided to start using a scan on the second column of the new index instead of a very fast seek/range scan of the larger existing index, crushing performance not only with the scan but with additional row lookups. Or, the opposite may be true where the optimizer sees that it can now do seeks with the new index instead of scans using an existing index. It's a real surprise to many when such a thing happens and 800,000 seeks are done instead of a comparatively mild manner 800,000 index row scan.
The bottom line is that no matter how you treat indexes (carefully controlled SDLC or (yech! {IMHO}) automated deployments or ad hoc addition to production), indexes can break a lot of stuff in production while giving no clue that they might break anything in lesser environments and I've not even begun to address the issues with bad page splits causing extreme bloating of the log file!
With all that being understood, when you deploy indexes, someone has to know what the baseline of performance looks like and monitor it carefully when the index is deployed. They also have to monitor its effect when batch runs occur.
Oh... as a bit of a sidebar, if you use REORGANIZE during your index maintenance, stop it. You're killing your system by removing critical free space in your indexes without fixing page density in the most used "silos" that occur in your indexes meaning that you're also wasting a shedload of extremely valuable memory and disk space and backup time and restore time and time to do the REORGANIZE and the log file explosions to support it and the fact that it can easily take up to 20 times longer to execute than a rebuild... and all for naught. It's like a bad drug habit... the more you do it, the more you need to do it. That includes you folks that think you have to do it just because you "only" have the Standard Edition.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2018 at 9:26 am
Don't hold back, Jeff. Tell us how you REALLY feel! 😛
You raise good points and to my mind that only reinforces the need for control of index implementation.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply