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

Are Indexes Actually Changes to the System?

By Steve Jones,

I haven't thought about this in some time, but Brent Ozar raises an interesting question: should index changes require change control? I've worked in an organization that didn't consider an index change to be an item that was presented as an official change. They were seen somewhat like adding new users or changing a firewall rule. These were ticket requests that could be triaged and made by an individual group without notifying the rest of IT.

I've also worked in organisations where an index change was viewed as a deployment and subject to change control procedures. Not that the network or security people cared about the details of the index, though the help desk might. These just wanted to be aware as any deployment change could affect their departments.

In today's world where many companies seek to adapt faster to changing customer demands or market opportunities, I think the idea of meeting every week to discuss any upgrades or changes to applications is somewhat silly. Certainly large changes, like major application upgrades to ERP systems, new security hardware, and alteration of core infrastructure ought to be debated in a larger group. Deploying changes to an application isn't one I'd think deserves the debate. At least not if your organization is trying to become more efficient.

I do think a way to avoid issues is to work more closely with the development group if you are in operations or vice versa. I'd be sure to let other app teams know about index changes? Certainly Brent lists a number of potential problems, many of which could occur. A new feature might break and prevent inserts, exports could use more disk space, imports might eat up data and backup space, queries could cause slowdowns or blocking. These are all possible, maybe even probable.

I agree with Brent in that any changes need to be tested. I don't know about any sort of formal change control, but there ought to be a process that evaluates the changes and mimics them in an environment that allows you to determine the production impact. Using a known, automated process with testing and well documented deployment actions will reduce the risk, but perhaps not eliminate it. However, when you find issues, if you have an automated process, it's easy to add tests or alter the process to ensure that it will succeed in the future. Especially if you examine the effects of these changes and learn to avoid problematic development or deployment patterns in the future.

Total article views: 71 | Views in the last 30 days: 4
Related Articles

SSIS: Case sensitivity may expose issues with change deployment

Because SSIS is case sensitive, random VS_NEEDSNEWMETADATA errors on case-insensitive databases may ...


Monitoring After Deployment

This Friday's poll looks at the time after deployment. Can you tell if things were successful? Do yo...


Analysis Services deployment wizard

Analysis Services deployment wizard


Could not obtain information about Windows NT group/user

Could not obtain information about Windows NT group/user


SQL Server Edition aware SSDT deployment scripts

Another day another deployment contributor for the project: https://github.com/DacFxDeploymentContri...