Post reply

Index Size Question

  • July 8, 2019 at 5:16 pm

    #3660874

    I'm trying to track down storage usage on our SQL Server 2012 instance.  I have a table with 11,703,018 rows and 10 indexes (9 non-clustered + PK) on it.  The PK is a clustered uniqueidentifier (I know not the greatest but i'm stuck with it for now) that only has the uniqueidentifier column included (size 16B) in it.

    The odd thing is it's using 898,677 pages, almost 7GB of storage space.  When I do the index size calculation MS lists on their site (https://docs.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-clustered-index?view=sql-server-2017) i get a much smaller number of required pages, like 80k pages or just over 500MB.

    Is it that each non-clustered index row has a copy of the clustered key value in it and that's being counted in the PK storage size?  Or did  I mess up the calculation?

    If I script the PK as create I get the following (slightly edited to remove table name) script so there's not a bunch of included columns in the table:

    ALTER TABLE [dbo].[table_detail] ADD CONSTRAINT [PK_table_detail] PRIMARY KEY CLUSTERED

    (

    [c35_key] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    It seems to think the row size is much larger than I expect it to be:

    Snag_f019f1

    Any ideas what I'm missing?

  • July 8, 2019 at 6:33 pm

    #3660895

    The clustered index is the table itself.  That is, all columns are stored in the clustered index.  Thus, the width is the total width of the entire row.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply