We had a bunch of crashes and restorations a few days ago and following those, the SQL Server DB has been acting strangely. We know that there were some issues with the failover clusters due to which we've had to boot the servers again to finally get the database seemingly working.
One of the problems following that is that, we ran a big script that dynamically dropped existing indexes and re-created them with the only difference being that they were now filtered with WHERE column NOT NULL. For some reason however, when I pick the SCRIPT INDEX -> CREATE TO -> NEW QUERY WINDOW from SSMS Object Explorer, it offers the basic index creation script where the index is NOT filtered. When our customer with full permissions does the same, the creation script correctly shows that it's filtered.
Could this be a permission issue (didn't find any such on Google), or is there a possibility that while the script logged the changes correctly, the nodes or the query optimizer or whatever is somehow out of sync?
Similarly, several queries that previously performed fine (and still perform fine in a different DB that's a copy of this one), now show through execution plan that they're behaving differently. One of them has for example the following issues:
- By default, execution planner shows that SQL Server uses the wrong index, producing millions of rows in the nested loops as opposed to 2 as it should.
- Another index is correct, but produces millions of nested loops where in the copy of this server we only get 3.
- When the copy is forced to use the same wrong index as the problem DB in section 1, it still only returns 2 rows in nested loops.
What could possibly be the cause of these issues and how to even begin to diagnose the problem? As said, the databases are copies of each other. The only difference is that the problem DB crashed where the DB was moved to a failover cluster and was then returned to the correct node again. The indexes are not fragmented, the statistics have just been updated, and there seems to be an extreme load from SQL Server's query planner.
I would really appreciate some expert advice on what could be the cause and how I would go about to diagnose the actual problem, thanks.