SQL index changes in 5.0

I think this is related to what the SQL gurus call "selectivity". A field like source type has the same value hundreds of thousands of entries while a field source no. usually has only a few dozens or hundreds of entries.

Now I don't why is it important that the first field of the key should have high "selectivity" instead of the key itself, but it seems to be...

Thanks, that's interesting. So it means searching by indexes is something like a sequential lookup like we do in a phonebook and not something like the searching and sorting algorythms we learned in school? You know like the one if I remember correctly, quickfind or quicksort, to find an element in an array - if it's n elements long, look at the one at the n/2 position, then if it's too small then at the one at (n/2 +n)/2 etc. always halving the array. Such algorythms don't really care much how many fields are there in the key - I think they always take log 2 n time. Funny. Probably the main difference might be that all these algorithms worked on data in memory and the database is on a disk, with a sequential access. Hmmm.... So, I=in a sense, every database is sequential because the head reading the disk doesn't really undestand the nice tuple operations of SQL?

I have done extensive testing on the selectivity and density of standard indexes in NAV on SQL. In SQL 2000 rearranging the clustered indexes for better selectivity did help, but in SQL 2005 it did not seem to make much difference at all. SQL 2005 does not seem to lean so heavily on selectivity as 2000 did. In 2005 the default order works almost just as well as the rearranged order.

The real issue now with 2005 is having dynamic fields in the clustered index. Once a record is inserted into a table with a clustered index the fields in the clustered index should not be updated. This causes serious overhead because if the data in the clustered index changes all non-clustered indexes must be updated to reflect the change. If you look at some of the manf. table clustered index you will find "Status" as the first field, this is not good as the status will change over time and each time it changes the entire clustered index must be updated and all non-clustered indexes.

"getting on my soap box"

I am not sure where the whole "limit the amount of non-clustered indexes" on a table for better performance came from but it is my opinion that this is not accurate. The loss of performance form missing one necessary non-clustered index can have serious performance impact FAR greater than the few milliseconds it take to update and maintain non-clustered indexes. The extra over head from 10 extraneous non-clustered indexes tiny compared to the overhead and performance hit you will take from missing one necessary non-clustered index.

This should be slowly becoming a non-issue as SQL 2005 has a DMV that tracks index usage stats and can tell you exactly what indexes are being used and how often.

"Done Preaching"

Mark Brummel wrote:

The story about selectivity is strange, because when I was at teched in barcelona last year there was a session specialy about selective indexing on SQL 2005.

Really? Did your trip to Teched Barcelona affect selectivity? You're right that IS strange..... I have to get out more

Mark Brummel wrote:

TechEd is THE best way to learn a LOT about other MS technology than Navision.

Although Boston (not Barcelona) had a high quality NAV team I learned the most from the other sessions.

I did however got a change to learn other MS experts about NAV which was pretty cool.

Yeah I have to agree, Tech Ed (IT Forums) was very useful, I would recommend it to those Navision experts that want to know what else is going on out there.

And Mark, this is the topic you and I discussed after one of the SQL sessions.

He just wants to make 3000 as soon as possible