PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Wednesday, November 2, 2022

[FIXED] Why is the index size so unjustifiably large?

 November 02, 2022     indexing, sql, sql-server, sql-server-2017     No comments   

Issue

I have a table with 280k rows. It has a clustered primary key. When I look at the size of the primary key index, it reports 5.8 GB. Far too big for 280k rows.

SELECT i.name AS IndexName, SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.SessionSignIn'), NULL, NULL, 'DETAILED') AS s
    JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE i.name = 'PK_SessionSignIn'
GROUP BY i.name

enter image description here

So I ran the Index Physical Stats report from SSMS and got the following:

enter image description here

It looks like there are 2 parts to this index and the latter part appears to take all the space. I tried rebuilding the index, then reorganizing it, but it didn't change anything.

What is the portion that is taking up all the space? How do I get rid of it?


Solution

Clustered index is the table.

You can have a look at this article to see how the size is calculated:

https://learn.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-clustered-index?view=sql-server-ver16



Answered By - Alex
Answer Checked By - Katrina (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing