Post ze skryptem pierwotnie opublikowany przez Sunil Agarwal na TEJ STRONIE.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
-- picking the tables that qualify CCI -- Key logic is -- (a) Table does not have CCI -- (b) At least one partition has > 1 million rows and does not have -- unsupported types for CCI -- (c) Range queries account for > 50% of all operations -- (d) DML Update/Delete operations < 10% of all operations select table_id, table_name from (select quotename(object_schema_name(dmv_ops_stats.object_id)) + N'.' + quotename(object_name (dmv_ops_stats.object_id)) as table_name, dmv_ops_stats.object_id as table_id, SUM (leaf_delete_count + range_scan_count + singleton_lookup_count + leaf_update_count) as total_ops_count, SUM (leaf_delete_count + leaf_update_count) as total_dml_count, SUM (range_scan_count + singleton_lookup_count) as total_query_count, SUM (range_scan_count) as range_scan_count from sys.dm_db_index_operational_stats (db_id(), null, null, null) as dmv_ops_stats where (index_id = 0 or index_id = 1) AND dmv_ops_stats.object_id in (select distinct object_id from sys.partitions p where data_compression <= 2 and (index_id = 0 or index_id = 1) AND rows > 1048576 AND object_id in (select distinct object_id from sys.partitions p, sysobjects o where o.type = 'u' and p.object_id = o.id)) AND dmv_ops_stats.object_id not in ( select distinct object_id from sys.columns where user_type_id IN (34, 35, 241) OR ((user_type_id = 165 OR user_type_id = 167) and max_length = -1)) AND dmv_ops_stats.object_id not in (select distinct object_id from sys.partitions where data_compression >2) group by dmv_ops_stats.object_id ) summary_table where ((total_dml_count * 100.0/NULLIF(total_ops_count, 0) < 10.0) and (range_scan_count*100.0/NULLIF(total_query_count, 0) > 50.0)) |
