Thanks, yes I had forgotten the covering indexes.

In our situations most indexes are not covering so did not realy take that into account.

Below some additional script

Thanks,

Ben

-- As I understood, Scans are full table (index or main).

-- So a full scan on a large tables (index) should be prevented.

--

-- Code below produces a histogram.

-- The number of rows of a table determines the class.

-- Each order of ten covers 2 classes.

-- Min and Max give the actual number of rows of the tables concerned.

--

-- Histogram of scans.

-- delta_scans The number of scans during the period.

-- Class For each power of 10, two classes are created.

-- Min/Max Actual min and max values present in de rows column in the table

--

-- The Histogram gives a 'fair' view of the scans.

--

select sum(delta_scans) delta_scans

,round(2*log10(1.0*rows),0)

as 'Class'

, MIN(rows) min

, MAX(rows) Max

from ##Changed_counts2 where rows > 0

group by

round(2*log10(1.0*rows),0)

order by

round(2*log10(1.0*rows),0)

Example:

Notice class 15 could be a 'problem'. Scans on large tables.

delta_scansClassminMax

772011

1617125

4382922617

308332055

48248461153

4855205528

361167951761

254718335021

2668591614934

691791054143

01067884163249

77011201615545967

90125639531721911

371320356085209387

014600871016550054

6151842073921892755

016112091250112091250