Sql-server – SQL Server weird issue with indexes and query planner

clusteringperformancesql-server-2008

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:

  1. 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.
  2. Another index is correct, but produces millions of nested loops where in the copy of this server we only get 3.
  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.

Best Answer

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.

"For some reason" indeed. So weird and strange. You probably have "Script for server version: SQL Server 2005" set in SSMS Tools > Options > SQL Server Object Explorer > Scripting:

enter image description here

2005 didn't support filtered indexes, so it leaves out the WHERE clause on purpose. Make sure that is set to 2008 or better and try again.

For the queries that are performing differently, are you sure the databases are identical? Do they use these indexes, that are filtered on one system and not on the other? Are both databases set to the same compatibility level? Have you updated statistics on the copy? Have you performed any index maintenance? Has the data skewed? Have you tried recompiling the queries with the largest behavioral changes?

(These are all formed as questions but you can interpret them as rhetorical and quasi-suggestions.)

Related Question