Index space discrepancy

Question

Index space discrepancy

shahgols

SSCrazy Eights

Points: 9874
More actions
May 21, 2009 at 10:24 am

#132565

Hi everyone,
My database's index file is 158GB at the moment. However, when I run the following script and add up the index sizes, I only get 95GB. If I change the query to include clustered indexes, I get 280GB total index size. Any idea why SQL 2005 reports 95GB whereas my file is 158GB...forgot...there is no empty space in the filegroup. Thanks in advance.
SELECT used AS "# of Pages", rows AS "# of Rows", (used * 8) / 1024 AS "# of MB",
name as IndexName, object_name(id) AS TableName
FROM sysindexes WHERE indid >= 2 and indid <= 250
order by used DESC

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think it's this WHERE statement:

WHERE indid >= 2 and indid = 1 would include the clustered index of each table, and certainly makes a huge difference when i ran your script on my server;

also, isn't any Heap index 254 if there is no PK, so if you had any heap tables, they'd be underreporting their size as well? or is it heap index is 0 and TEXT/NTEXT index is 254?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

shahgols SSCrazy Eights Points: 9874 More actions · Answer 2

I think in SQL 2000 the indid 255 is for TEXT/IMAGE, but not in SQL 2005.

I ran the query and changed the where clause to:

WHERE indid >= 2

And yet, the same result. 🙁

Lowell SSC Guru Points: 323518 More actions · Answer 3

no, my point was indid=1;

in my case the difference between the totals was 3x the number of rows, but 6 times the mb size:

SELECT SUM([# of Pages]) as NumPages,

Sum([# of Rows]) as TotalRows,

Sum([# of MB]) as TotalMb

FROM (

SELECT used AS "# of Pages", rows AS "# of Rows", (used * 8.0) / 1024.0 AS "# of MB",

name as IndexName, object_name(id) AS TableName

FROM sysindexes WHERE indid >= 1 and indid = 2 and indid = 0, heap?

3717 224071 29.0390625 --indid >= 1 with PK whether 250 or 255...same value

661 97473 5.1640625 --indid >= 2 No PK

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

You really should be looking at sys.dm_db_partition_stats instead of sysindexes. There are known issues with sysindexes not being updated appropriately and requiring update usage (dbcc updateusage) to be run to fix them.

I have also seen an issue where updating usage does not update sysindexes.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

shahgols SSCrazy Eights Points: 9874 More actions · Answer 5

Hi there,

I see your point. When I change the where clause to indid >= 1, then I get 237MB whereas the file is 158MB.

I have also tried querying the DMVs, with the same results. This is very odd.