SQL Server Performance

WA_SYS Indexes

Discussion in 'Performance Tuning for DBAs' started by jgavern, Aug 12, 2005.

  1. jgavern New Member

    Recently I was trying to add a new index on a table that has more than 256 columns (I know I know, that is too many columns... I inherited the table and am working on a new database design). I am running SQL Server 2000

    Back to the issue. While trying to add the new index I got the following message:
    Error 1910 - Cannot create more than 256 nonclustered indices or column statistics on one table.

    I checked the indexes on the table and only saw two. Then I queried the sysindexes table and saw 256. Most of them were WA_SYS indexes. I read up on the WA_SYS indexes and found they are created automatically by SQL Server for optimization statistics.

    Do I really need these? Will they eventually hurt performace?

    I deleted the WA_SYS indexes from the sysindexes table for this one table. However I have other tables with many columns and I am considering doing the same. Would this be beneficial or does it not matter? FYI most of the columns on these large table are defined as CHAR(1). Some hack created these tables and we are trying to redesign. But in the meantime I want to increase performance as much as possible.
  2. satya Moderator

    Those are created by the query optimizer and you can use INDEXPROPERTY to identify hypothetical indexes and statistics. You can drop those indexes as they will be generated automatically.
    After all cleanup ensure to reindex the required indexes.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. jgavern New Member

    Satya, thank you for you reply. I would like to get rid of all the indexes. However I did initially try to drop them but got the following error from my DROP INDEX statement:
    Cannot drop the index 'tblexperience_test._WA_Sys_Archive_Date_164452B1', because it does not exist in the system catalog.

    That is why I physically deleted them from the sysindexes table. Is there another way to drop these indexes?

    Also what do you mean by "reindex the required indexes"? Do you mean to reindex the indexes I want to keep?

    Thank you.
  4. satya Moderator

  5. Luis Martin Moderator

    Using QA, tools, Manage Statistics, you can delete any statistics.

    Luis Martin

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    All postings are provided “AS IS” with no warranties for accuracy.

  6. merrillaldrich New Member

    Or try the Drop Statistics command <img src='/community/emoticons/emotion-1.gif' alt=':)' />

Share This Page