Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.)

share|improve this answer
    
I'm using the same SSMS to connect to all of those instances, so this doesn't change. Checked and the Script for sever version is 2008. Compatibility level is 100 on all instances, matching for 2008. And as said before, the index fragmentation is negligible on these problem indexes (0.1 and 1.3 %). The copy is not identical however, just found out that the copy was restored from differential backups prior to the query that replaced those some 600+ indexes with filtered versions. So I have to compare the index-specific issues with an environment that has an older DB and isn't clustered. –  Kahn Mar 12 '13 at 15:03
    
So the scripting option is set to target 2008 and it is scripting filtered indexes without the where clause? Try a newer version of SSMS - this was a bug. The full version of SSMS in SQL Server 2012 SP1 is now free - I blogged about this here. –  Aaron Bertrand Mar 12 '13 at 15:09
    
This only appears on the problem DB in question. It shows the filtered indexes correctly on all other instances, using the same running SSMS. So I don't think this is the problem, if it is I'll order an update but that's not my decision so it won't happen overnight. :) –  Kahn Mar 12 '13 at 15:35
    
The same version of Management Studio running against two different instances, or different versions of Management Studio? What happens if you use the "bad" version of Management Studio against a different database where you're currently not seeing the issue? If you test local SSMS in each case you're not properly isolating only a single variable. –  Aaron Bertrand Mar 12 '13 at 15:49
    
Good point, I'll check this asap which will be tomorrow since everyone else is gone. Also, a colleague found a good article that might have something to do with this: codeproject.com/Articles/369886/… any ideas? I plan to check into both of these as soon as I'm able. –  Kahn Mar 12 '13 at 15:54
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.