We have a massive MS SQL Server production environment with thousands of tables in dire need of index tuning & pruning. Any suggestions on the best ways to test proposed changes outside of production?
Obviously, we can (and do) stand up a DEV database server where we can make changes, but that DEV copy won't reflect the real-world demands being made on the tables. We also have a production mirror that uses transaction log shipping, but mirrors are always read-only (unless you fail them over to primary), so we cannot test index changes there.
Is there any way to ship transaction logs to an instance that isn't a read-only mirror? Obviously that could encounter problems, especially if schema changes are made in the test environment. Even with only index changes, you could have query timeouts and other inconsistent behavior.
But that's exactly what we are looking to do: Given a set of index changes, how do real-world queries, under real-world load, perform? Are they faster? Do they fail?
A few test queries won't suffice. We run all kinds of nightly, weekly, and monthly processing jobs that hit the database hard. We also have a lot of external scripts and services that query and update the database in ways we don't always anticipate.
Given the scope of the changes that are needed, I am very hesitant to make any live production index changes without verification.
