sql server - Apparently massive primary key index -

sql server - Apparently massive primary key index -

disclaimer: not mssql dba....

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

[1] claims show total space used table , indexes [2] claims show total space used index

so looking @ largest table:

[1] gives usedspacekb = 58757504 . table , indexes 56gb

[2] gives indexsize(kb) = 55166168 largest index on same table (there others on same table - tiny in comparison). largest index 52gb

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

so if [1] , [2] right , have understood output have 4gb table 52gb primary key index on it.

questions:

have understood [1] , [2] correctly? if how can have pk index 52gb on table 4gb? utilize nightly batch jobs remove old rows in table (its audit log), possible index not beingness reduced when rows removed table? what's simplest & quickest way clear index - drop , build or there more efficient mechanism?

the ddl table , pk index

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)

have understood 1 , [2] correctly?

no. have table clustered index of size 52 gb , non-clustered index of size 4gb. total size of indexes: 56 gb. not have other indexes. there no 'table', there indexes.

i suggest read table , index organization first. tables can organized heaps, clustered index b-trees (and clustered columnstores or hekaton indexes in sql server 2014 , later). searching 'table' base of operations heap, since declared primary key had become clustered index of table not have base of operations heap.

sql-server database