SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Are Indexes Actually Changes to the System?


Are Indexes Actually Changes to the System?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (602K reputation)SSC Guru (602K reputation)SSC Guru (602K reputation)SSC Guru (602K reputation)SSC Guru (602K reputation)SSC Guru (602K reputation)SSC Guru (602K reputation)SSC Guru (602K reputation)

Group: Administrators
Points: 602364 Visits: 21101
Comments posted to this topic are about the item Are Indexes Actually Changes to the System?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Russ G
Russ G
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 40
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.
Dave Poole
Dave Poole
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60706 Visits: 3986
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.

LinkedIn Profile
www.simple-talk.com
xsevensinzx
xsevensinzx
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21186 Visits: 5792
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.
Ricky Lively
Ricky Lively
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 2066
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.
DinoRS
DinoRS
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 480
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.
Aaron Cutshall
Aaron Cutshall
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7532 Visits: 1324
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.


...when ye are in the service of your fellow beings ye are only in the service of your God. -- Mosiah 2:17
Jason-
Jason-
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2174 Visits: 588
For 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 ~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.


-
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108453 Visits: 14551
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Jeff Mlakar
Jeff Mlakar
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1924 Visits: 611
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search