SQL Server 2008 R2 – Inefficient Indexes on Primary Keys

sql serversql-server-2008-r2

So I was cleaning some indexes,with some queries I made, and some that I found over the internet with ineficient indexes ( lots of updates and no seeks, scans, etc.)

But something that I'm not sure is, almost all primary keys have no seeks, scans or lookups and thousands of updates.

I think this is a normal behavior since we have most of these keys only to have an ID on a table.

Am I right?

Like this one:

ID_X Seeks=0, Scans=12, Lookups=8, Updates=31453

Best Answer

This does not seem wrong. Though because most people define private keys as their clustered indexes by default unless they have specific reason to chose something else as the clustering key, it might be unusual. It very much depends on the other indexes that are in place, and how the table is used by your application(s).

If a query can be entirely satisfied using a non-clustered index, then there is not need to query the clustered index (or heap, if there is no clustered index defined) for extra information. If I have a table T with columns a, b, c, d, e, f, g, h, i, with a being defined as the primary key and there being a non-clustered index on b,c,d, the query SELECT b,c,d FROM T WHERE b='some-value' ORDER BY b,c,d can be satisfied by the index and nothing else needs to be referred to. On the other hand SELECT * FROM T WHERE b='some-value' ORDER BY b,c,d will need to refer to the data pages (and therefore the primary key if it is the clustered index for this table) in order to read the columns other than b, c, d, and a.

Also the primary key might not be the clustering key. In this case even if another index is used that is not entirely sufficient so that other information from the main data pages is needed, the clustered index (or heap if there is no clustered index) will be used not the primary key's index.

So if you have a sufficient selection of covering indexes defined, it might be the case that is it rare for your queries to need to touch the clustered index, and if your PK isn't the clustered index it might not be touched even if there are no other useful indexes for a statement to use.

In the example that you give, the index does see some action. It could also be that this table is usually write-only and only occasionally read from. This is not uncommon for logging and auditing tables where data is constantly written as users take actions but is only read once in a while when someone runs off an audit report.

Related Question