Apparently massive primary key index

Question

Disclaimer: I am not an MSSQL dba....

I have used two sql queries presented elsewhere on stackoverflow [1]: https://stackoverflow.com/a/7892349/2195559 [2]: https://stackoverflow.com/a/17600911/2195559

[1] claims to show the total space used by a table and its indexes [2] claims to show the total space used by an index

So looking at my largest table:

[1] gives UsedSpaceKB = 58757504 . So the table and indexes are 56GB
[2] gives Indexsize(KB) = 55166168 for the largest index on that same table (there are some others on the same table - but they are tiny in comparison). So the largest index is 52GB

[1] - [2] (for indexes on the same table) = 4GB.

So IF [1] and [2] are correct and I have understood the output then I have a 4GB table with a 52GB primary key index on it.

Questions:

have I understood [1] and [2] correctly?
if so then how can I have a pk index that is 52GB on a table that is itself just 4GB? We use nightly batch jobs to remove old rows in this table (its an audit log), is it possible that the index is not being reduced when rows are removed from the table?
what's the simplest & quickest way to clear this index - drop and build or is there some more efficient mechanism?

The ddl for the table and pk index are

CREATE TABLE "YYY"."XXX_audit"
(
   id numeric(19,0) PRIMARY KEY,
   version numeric(19,0),
   compressed_response image,
   date_created datetime,
   duration numeric(19,0),
   request text,
   response text,
   session_id varchar(255),
   uid varchar(255),
   webservice_uri varchar(255),
   event_id varchar(36)
)
GO
CREATE UNIQUE INDEX PK__XXX_audit__6CC31A31 ON "YYY"."XXX_audit"(id)

Thanks for the rapid responses - I was pretty sure I had not understood properly ;-) — user2195559, Oct 13 '14 at 13:11

score 1 · Accepted Answer · answered Oct 13 '14 at 12:48

have I understood 1 and [2] correctly?

No. You have a table with a clustered index of size 52 GB and a non-clustered index of size 4GB. Total size of indexes: 56 GB. You do not have anything other than indexes. There is no 'table', there are only indexes.

I suggest you read Table and Index Organization first. Tables can be organized as heaps, as clustered index B-Trees (and as clustered columnstores or as Hekaton indexes in SQL Server 2014 and later). What you are searching for as the 'table' would be a base heap, but since you declared an primary key that had become the clustered index of the table you do not have a base heap.

To add to what Remus said, the primary key index is clustered by default unless a clustered index already exists. One can only have one clustered index because the clustered index leaf nodes is the actual table data, and data can be logically ordered only one way at a time. — Dan Guzman, Oct 13 '14 at 12:58

Apparently massive primary key index

1 Answers1