1

Topic: New index - productivity select vs insert

As it is known, at creation of a new index it is required to weigh productivity of readings vs updates. But something I anywhere did not meet specific approaches actually as it to do.
Therefore for an example took one table and typical  with  and supporting this  an index.
That does this index - it replaces  tables on seek.
The table of small, 150 records. In the presence of an index, there will be a following statistics:

select->>> Table ' MyTable '. Scan count 2, logical reads 15, physical reads 0...
insert->>> Table ' MyTable '. Scan count 0, logical reads 4, physical reads 0...

Without an index:

select->>> Table ' MyTable '. Scan count 2, logical reads 18, physical reads 0...
insert->>> Table ' MyTable '. Scan count 0, logical reads 2, physical reads 0...

I.e. we have such situation that   on 3 logical reads it is less, and  - on 2 it is more. I.e.  from an index in 1 logical read as a whole.
Two questions:
- I correctly understand, what when the table becomes big, hundred thousand records - that the difference (because of  in ) becomes where , after all  will be all table, and index logical reads at  increase slightly? Adjusted for an amount  vs
- So-whether it is necessary to estimate usefulness of indexes generally?

2

Re: New index - productivity select vs insert

hey112;
Without a ratio chtenie/zapis to do an estimation - it is senseless. If at you the data is once interposed, and then  time is read - an index very much even  server life. And if on the contrary - to the table goes  , but  from it there is time in a year (for example - the table of dens of operation of application to which always write, but read it only in the presence of any problems in application operation) the index  is not necessary.

3

Re: New index - productivity select vs insert

[quote =] without a ratio chtenie/zapis to do an estimation - it is senseless. If at you the data is once interposed, and then  time is read - an index very much even  server life. And if on the contrary - to the table goes  , but  from it there is time in a year (for example - the table of dens of operation of application to which always write, but read it only in the presence of any problems in application operation) the index  is not necessary.

I know, but we assume "the typical" situation where we have is considered we tell 60-80 % , and remaining  when users view the data, and sometimes them interpose/update