One important step of database maintenance is maintaining indexes, rebuilding or defragmenting them at scheduled intervals.
This step is obvious to everyone, but has an “hidden” requirements that sometimes is underestimated. Indexes MUST exists!
I these last days I’ve been working on a “problematic” database server and one of the script that helped be to understand how big was the problem, is a simple script that shows, for each table, if it has a clustered index or nor and if it has nonclustered indexes.
Thanks to this script it’s very easy to find all the tables (along with the number of rows contained within) that doesn’t have an index at all, so you can explain to the customer that their performance problems are not related to poor index maintenance or badly written queries (which, of course, on a database without indexes are somehow expected…), but, in first place, by the total absence of indexes!
Here’s the script:
with cte as
table_name = o.name,
sys.objects o on i.[object_id] = o.[object_id]
o.type in ('U')
o.is_ms_shipped = 0 and i.is_disabled = 0 and i.is_hypothetical = 0
i.type <= 2
), cte2 as
(count(type) for type_desc in ([HEAP], [CLUSTERED], [NONCLUSTERED])) pv
[rows] = max(p.rows),
is_heap = sum([HEAP]),
is_clustered = sum([CLUSTERED]),
num_of_nonclustered = sum([NONCLUSTERED])
sys.partitions p on c2.[object_id] = p.[object_id] and c2.index_id = p.index_id
As usual the script can be found also in the sys2 collection on CodePlex: