woensdag 7 april 2010

Statistics revisited

Currently, i am working on a short project. In SQL Server 2000 a intensively used table is used with a lot of columns and there a lot of indexes created on this table(about 80). On top of that SQL Server 2000 has used all the 'slots' of the indexes for Statistics. In 2005 and 2008 indexes and statistics are seperated but in 2000 it isn't.

I've searching for this quite long and i found on Kimberly Tripp site (http://www.sqlskills.com/blogs/Kimberly/post/Indexes-in-SQL-Server-20052008-Best-Practices-Part-1.aspx#ixzz0kP811y1Z) a confirmation for this. The vendor wants to add indexes on this table but can't because the index slots are being used by the statistics.

So this mean:
Clustered index                     1
Non clustered index            80
Statistics used by the index  81
Totaal                               162

And there is a maximum of 250 indexes/statistics in SQL Server 2000 and this would mean there 250-162 = 88 statistics being used and we have to determine whether they should be dropped. How? i haven't found out yet. When i found the answer i'll come back to blog about it.

Geen opmerkingen:

Een reactie plaatsen