0

I am running an old production application which has a backend DB hosted on SQL2008R2 in Compatibility Mode 80 (SQL2000). When I run bits of TSQL to view actual execution plans SSMS is giving me suggested indexes which are only legal for SQL2005+ e.g. ones with included columns. How can I prevent it from doing this? I would like to view sensible indexes it might suggest.

NB: The Compatibility Mode can't be changed. Yes we are replacing the application, this will remove the need for the DB entirely but not for some time.

NB: Already tried, SSMS is configured "Script for server version" as SQL Server 2000.

2
  • 1
    Why do you think included columns are prohibited by older compatibility levels? Dec 13, 2016 at 11:47
  • @AaronBertrand technically I said they are only legal for SQL2005+ , not they are only legal for certain compatibility levels when hosted on SQL2005+. The production DB is hosted on a variety of SQL Server versions (SQL2000, 2008 and 2008R2)
    – Paul
    Dec 13, 2016 at 15:42

1 Answer 1

1

I also have databases stuck at CL80, also on 2008r2: I've just tested and they allow indexes with INCLUDEed columns to be defined. I've not explicitly tested that they are properly used though so before relying upon this check appropriate queries to see what plans get created.

If the included columns are not used properly to avoid lookups, you could translate them to used columns, i.e. CREATE INDEX ix_test_u1_i2 ON tbl_test (i1) INCLDUE (i2) would become CREATE INDEX ix_test_u1_i2 ON tbl_test (i1, i2). Do benchmark the result though, or account for it in your index maintenance plans, because if the later columns are fairly random you could increase the level of index fragmentation (through page splits on insert/update) noticeably. - edit: as per Paul's comment, indexes created with INCLUDEed columns are used correctly by the query planner so this comment is moot. Of course if your application has instances that are still actually running on SQL2000 rather than just having CL set to 80, that changes this back...

As an aside: the missing index information displayed by SSMS should only ever be taken as a suggestion that you consider something - the suggestions can potentially be detrimental to the application overall especially if you follow all of them (the extra space used could be rather large).

1
  • I've just been noticing the same on my tests. How bizarre. But yes I put a NonClustered index on the key column, then added every single other column as included. The execution plan became a simple 2 step Index Seek into a Select
    – Paul
    Dec 13, 2016 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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