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.
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 Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
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.
REferhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9198&SearchTerms=drop,index thread. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Using QA, tools, Manage Statistics, you can delete any statistics. Luis Martin Moderator SQL-Server-Performance.com 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.
