_WA_ 'indexes'
-
Hello, Gurus!
I have a table, unfortunately, that contains 115 columns. ALL of the columns have a frequent potential of being part of a WHERE clause. Currently, 112 of these columns have been 'referenced' by the optimizer in '_WA_' 'indexes'. Per a previous thread, I understand that SQL Server creates these objects to help it determine the best method of looking through an unindexed column.
The environment in which this database resides won't really lend itself to partitioning this table... The development package used abstracts the table to the extent that massive heavy lifting would be required to partition it. So...
Do I have any serious exposure here? Performance has degraded over the lifetime of this database (12 months @ 544,000 records... anticipate 1.5 mil more over the next 6 - 9 months)... but not substantially. If I check the most frequently referenced columns and tack on a few indexes, is it 'good enough' in this context?
Any insight welcome, and if there's more light to be shed regarding these objects, it's also welcome.
Regards,
SJTerrill
-
Is the slow performance during reads or writes? If it's on reads then adding indexes can be beneficial, however this may hurt performance on writes. Also, is this running on a RAID configuration? If so, which type?
Lastly, what are the chances this table is normalized? Sounds like a redesign might be in order but that's a last option.
Oh 1 more, is there any purge/archive strategy? If this is historical information that is eventually out of date maybe you could start archiving old data to tapes?
Darren
Darren -
Thanks for the response!
When performance is lower (it's never really slow), it's on reads. That answers my primary question on indexing.
The database is stored on a single file on a RAID 5, probably not a big issue there considering the relatively small size of the table. And no, there's no way this table could be considered normalized!
It's the result of letting <duck and cover> programmers decide how to implement a database structure without either 1. consulting a qualified DBA or 2. listening to the DBA when they are consulted. In regards to redesign, we're in the process of selling it to local management.Your mention of archive strategy is well-taken, the business model only requires holding on to this stuff in production for about 6 months. There's an OLAP model included with the product platform, so anything older in production is a non-issue.
Thanks for the clarification, Darren.
Regards,
SJTerrill
-
You may have done this already but you should but the log file on a different disk than the database file. This can help performance also. Good luck on the redesign!
Darren
Darren -
After reading all the comments wanted to include my 2 cents. If its a SQL2000 box, maybe you can use having a view on the table and in turn index the view(new option with sql 2000).
-
I don't think a strategy of indexing these columns just because SQL Server has created statistic on them is necessary.
You should really examine the queries and make a judgement as to whether they would specifically benefit from the columns being indexed.
These 'auto created' statistics are just statistics. When SQL Server needs the statistics, it does a quick sample of the data to gather the distribution statistics. Those statistics are then available for other queries to use if relevant.
As soon as you put an index on the table, you have the overhead of maintaining the index on every insert, update and delete (added to which SQL Server is going to create and maintain statistics on the indexes anyway).
If you look at the columns that SQL Server has created statistics for, you may discover that the data in the columns is non-selective (e.g. Sex - M or F), and would be pointless to index anyway. SQL Server is creating statistics on the column just to find out what the selectivity is in order to decide on the query execution plan.
That's my view anyway!
-
Thanks for the input, Ian. Your take on this is appreciated. So, since the records in sysindexes don't equate to indexes... but in fact to statistics; there's really no harm in letting these things sit around and in fact they're helpful to the optimizer so they'll get created again anyway if I drop them. I'm not one to argue with success.
Hmmm... Great feedback, all. Thanks!
SJTerrill
It's the result of letting <duck and cover> programmers decide how to implement a database structure without either 1. consulting a qualified DBA or 2. listening to the DBA when they are consulted. 
In regards to redesign, we're in the process of selling it to local management.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply