Why would deleting a redundant index cause an application to perform unexpectedly?

Question

I have inherited an application (and associated MS SQL database) that was installed by a software vendor many years ago. We do not have any kind of vendor support at this point. Besides using the application, we also access the database independently of the application, often making updates to the database directly and querying data for reporting.

In an effort to improve performance, I deleted some unnecessary indexes and created others. This caused bugs in the application when it performs certain tasks. The application does not display any error messages of any kind, and seems to perform normally, but the database is not updated by the application as expected. I have no access to the source code.

My theory is that the application explicitly specifies a deleted index in a with(index) statement in one of its queries. This would cause the server to return an error instead of completing the query, and if the application suppresses the error, the user would be unaware that some tables were updated and others were not. What other ideas could cause this behavior?

Assuming my theory is correct, I still believe the index is actually unnecessary, and the application should never have specified the index. Perhaps a workaround would be to create a new index with the same name, but with the fewest amount of columns possible. Is there any way to create an index with no columns? Would creating an nonclustered index with the same column(s) as the clustered index be the most efficient? What does the optimizer do in this case - does it still make a plan to include these "bad" indexes that just reference the clustered index, or will it know to ignore the with(index) request?

I did run a trace to see what queries the application was running. I got a bunch of RPC calls like "exec sp_cursorfetch" and "exec sp_cursorexecute" (that I don't understand) instead of actual SQL. Perhaps I will ask about these in a separate question, but if you know how I can interpret or decode these statements into regular SQL then that would allow me to at least confirm my theory.

Answer 1

To verify your theory about the app code hiding errors, run an XE trace to capture error_reported events.

The sp_cursor* RPC calls are system cursor procs, usually called by the client driver for server-side cursors. There should be a preceding sp_prepare or similar RPC call on the same connection that passes the SQL statement and returns the handle for subsequent use.

Index changes won't change behavior of well-written applications but there can be unexpected side effects for those that make poor assumptions. For example, consider the missing ORDER BY in the query below may return any arbitrary row. The row returned will vary depending on the indexes used in the query plan.

SELECT TOP (1) Column1 FROM SomeTable WHERE Column2 = @SomeValue;

I suggest you do the following, in a test environment if possible:

1. reinstate the original indexes and validate correct behavior
2. remove redundant indexes and validate again
3. introduce new indexes incrementally

Answer 2

As an aside to what everyone else said, you may find RedGate's SQL Search tool helpful in finding references to WITH( or WITH(DroppedIndexName) in the database code for the application.

If your theory is correct, then these would probably be part of some non-transactional call stack, where an error occurs, isn't fully / properly rolled back, and suppressed on the application side.

Answer 3

Would creating an nonclustered index with the same column(s) as the clustered index be the most efficient?

To properly mimic the clustered index in this way you would need to INCLUDE all the non-indexes columns as well as covering the same one(s). This will be inefficient space-wise as you are adding a full extra copy of all the table's data in that index and will affect insert/update performance (though for many use cases, which are read-heavy this is not as significant as you might think).

If you don't INCLUDE everything with your fake clustered index then you will likely harm performance of any queries that specifically reference it because you now have lookups back to the clustered index for columns not covered by the new one.

What does the optimizer do in this case - does it still make a plan to include these "bad" indexes that just reference the clustered index, or will it know to ignore the with(index) request?

I assume it will do as it is told and use the specified index, only referencing the CI if you don't INCLUDE everything needed. That should be easy to setup a sample test environment to verify.

In an effort to improve performance, I deleted some unnecessary indexes

Removing unnecessary indexes will only usually help with inert/update performance issues (and reduce burden on maintenance burden by not taking space in backups or being rebuilt/re-orged if you regularly do that with all indexes) so if read performance was your concern then just put them back in as they were.

be the most efficient?

If there are not other complicating factors, putting the original indexes back as they were would be your most efficient solution IMO, whether or not you leave your new ones in place (as they may help other accesses).

What other ideas could cause this behavior?

Would a forced plan that references a dropped index still get used and therefore cause errors when the plan is enacted? This is a feature I've not used so just throwing it out there as possible cause to research.

---

One case I've seen where an apparently unneeded index is explicitly referenced is where a scan is going to happen anyway so an index containing just the required columns is created so something smaller than the full table is scanned, but the query planner tries something else anyway (hitting other tables first and replacing the scan with a great many seeks in this table) that turns out to be more time consuming. This is usually a sign of bad query design (something non-sargable that could be made sargable) or just a miss-match of the data structure and what reports are wanted from it, though could indicate something that causes bad index stats to be likely, but without reference to the code you can't check that (and likely couldn't safely do anything about it anyway). It could also be something that is no longer needed, as it was put in place for an older version of SQL Server and newer releases have improved the query planner such that it wouldn't need the index hint to make the better choice.

---

Also, as pointed out in Dan's answer, someone could be using the terrible antipattern of trying to control ordering by index choice rather than an explicit ORDER BY, or has unknowingly relied upon the order produced by particular index use (without an explicit hint) where an explicit ORDER BY should really be given but has never been needed before (in this latter case, things may break similarly in future even if you put the original indexes back in, now might be the time to consider sun-setting and replacing that piece of software!).

Answer 4

I guess that @DanGuzman suggestions are the right path to follow:

• resume the original indexes and validate correct behavior
• remove duplicated/redoundant indexes and validate again
• introduce new indexes incrementally

If a script explicitly refers to an index ( by a with ) the SQLEngine need it; that index (nothing else) will be used to access the table. If you delete that index, the execution fails.

If your theory is correct and you have no access to the source code, I can suggest to run a Trace to capture the Queries (or stored procedure) that contain explicit reference to an index you wont to delete (ignore the sp_cursor* calls for now). At this point, you can investigate each of those queries to find out the right index to use and then create this new index replacing the existing one (using the same name).

You can also search for a "with(index)" inside the DB''s Views and StoredProc; in this case you can change the View/SP script directly.

for my experience, you should avoid creating a NonClustered index with all the columns;it is just a waste of space. creating instead the right one will help performance (in my experience, it should use, at least, the columns that appears in the where/join/orderby statements).