sp_MSTablespace

Question

Post reply

sp_MSTablespace

Farrell Keough

SSCoach

Points: 17414
More actions
February 6, 2007 at 8:19 am

#92198

We are using this "undocumented" SP to check db size and changes in tables...
We are finding that "IndexSpaceUsed" is, on the odd occassion, larger than the DataSpaceUsed.
I am looking at the proc now, but just wanted to check if anyone else has used this and knows info they can give me.
tia

I should give you more information - sorry. For instance, we have an old table, (from the mainframe) and we need to keep just in case someone needs information from the 1990's. It is rarely used, but the IndexSpaceUsed can be larger in presentation than the DataSpaceUsed.
CREATE TABLE [dbo].[AX1] (
[a_id] [int] NOT NULL ,
[AX1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AX1PCT] [float] NOT NULL ,
[AX1POS] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_AX1_1__27] ON [dbo].[TB_AX1]([award_id], [AX1]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Number of records 285,222.
I wasn't born stupid - I had to study.

Viewing 10 posts - 1 through 9 (of 9 total)

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

David McFarland SSChampion Points: 11815 More actions · Answer 1

That proc uses sysindexes to get its data. Have you updated statistics and then looked at the results?

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Yeah - that was my first attempt. I should have noted that as well. Sorry.

I wasn't born stupid - I had to study.

EdVassie SSC Guru Points: 60476 More actions · Answer 3

The figures for space used in sysindexes appear to get updated automatically when new extents are added to the object, but are not updated automatically when extents are released. This can mean that over time the space used as shown in sysindexes is higher than what is actually used.

The quickest way to correct this is to run the process to update space used (I think this is a DBCC command, I do not have access to BOL). It may be worth having a weekly SQL Agent job to update space used for all objects so that the sysindexes values remain close to reality.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

The command is DBCC UPDATEUSAGE, and there's actually a second parameter for sp_spaceused to run it for you.

EXEC sp_spaceused @objname = '', @updateusage = 'TRUE'

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Thank you all. Oddly enough, we can see a difference immediately when we drop an Index in Development. That would leave me to believe that the statistics are being updated. Hence, I cannot understand how an Index can take up more space than the Data...

I wasn't born stupid - I had to study.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

How many indexes does the table have, Farrell?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Thanks Jeff. We've seen it with just one index! That is why I found it odd? (and to further complicate this, it was an "archived" table - data from the mainframe kept in a non-nomaralized structure, but needed in case information from the 1970-90's was requested... rarely used).

It may be an artifact of the structure - I dunno.

I am on vacation right now and will not have access to the internet in a day - so I will not be able to see any replies. If you do come across anything, I will be very interested in the findings and check in later...

I wasn't born stupid - I had to study.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

I'm thinking "structural artifact" myself. If the FILL FACTOR isn't set to 100, I could see where an index on a small table could out strip the size of the actual data especially if the "rows" of the index cause a little wasted space on each page.

Anyway, have a great time on vacation... not touching a computer for a while may prove to be a vacation all by itself

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MohammedU SSCoach Points: 19644 More actions · Answer 9

Check this...

http://vyaskn.tripod.com/code/sp_show_huge_tables.txt

MohammedU
Microsoft SQL Server MVP