January 1, 2012 at 9:55 pm
Ok now this is quite unusual.
The data size is 6 GB while the index size which is a NC composite index is almost 36 GB.There were some amount of large data deletes done about 2 weeks back.
I thought that forwarded pointers might be the issue but that does not seems to be the case as seen from the output of showcontig.The table has only one single NC index on it and has no clustered index.
I cannot get my head around this.
Attached are the screenshot for the data size and the output for DBCC showcontig.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 2, 2012 at 1:34 am
Can you run DBCC UPDATEUSAGE please?
Forwarding pointers are only found in heaps (so there can't be any in the NC index) and they seldom make the table much larger than it would be without.
Once you've run updateusage, can you query sys.dm_db_index_physical_stats for that table (pass it db_id and object_id) and post the number of pages in both, the average page usage for the leaf level of both indexes (heap and nonclustered).
Also, what's the definition of the index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2012 at 2:36 am
After running updatusage I get the following
DBCC UPDATEUSAGE: counts updated for table 'Tbl_FaoTrades' (index 'IDX_TD_LOGINID_TD_DATE', partition 1):
USED pages (In-row Data): changed from (138250) to (138235) pages.
RSVD pages (In-row Data): changed from (138288) to (138274) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The index actually is a combination of date and int columns.
I have attached the screenshot for the the output for sys.dm_db_index_physical_stats.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 2, 2012 at 2:47 am
GilaMonster (1/2/2012)
Forwarding pointers are only found in heaps (so there can't be any in the NC index) and they seldom make the table much larger than it would be without.
Also since my table does not have a clustered index doesnt that makes my table a heap ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 2, 2012 at 2:51 am
Sachin Nandanwar (1/2/2012)
GilaMonster (1/2/2012)
Forwarding pointers are only found in heaps (so there can't be any in the NC index) and they seldom make the table much larger than it would be without.Also since my table does not have a clustered index doesnt that makes my table a heap ?
Yes, it does. But you can only have forwarding pointers in the heap (data size), not in the index, hence they can't be related to the index appearing larger than expected.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2012 at 2:54 am
Right, so if you check the page count of the heap (the table) it's 1008150 pages (7.7GB) and the index is in total 138 225 pages (total across all levels), making it 1.05 GB.
So your index is actually only 1/7th the size of the data.
Where space_used got the 36GB, I can't say, but it's way wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2012 at 3:24 am
Yes it does looks surprising.
But the amazing thing is that the overall DB size and the back up size also is much larger.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 2, 2012 at 3:36 am
I am not very sure how ‘sys.dm_db_index_physical_stats’ gets its data & how you retrieved 36 GB on Index space. But it’s very common that a file’s actual size & file’s size on disk would be different. I file of size 1 MB, will consume more than 1 MB size on disk.
But this huge difference I am noticing first time.
January 2, 2012 at 4:20 am
Dev (1/2/2012)
I am not very sure how ‘sys.dm_db_index_physical_stats’ gets its data & how you retrieved 36 GB on Index space. But it’s very common that a file’s actual size & file’s size on disk would be different. I file of size 1 MB, will consume more than 1 MB size on disk.But this huge difference I am noticing first time.
The data in the table was actually around 36 GB.But when we deleted the records the data size went down to 6 GB but the index size is still 36 GB.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 2, 2012 at 4:24 am
Then you should rebuild / reorganize your index. Its fragmentation is consuming 30 GB of space (however not visible in query results :unsure:).
January 2, 2012 at 4:36 am
Sachin Nandanwar (1/2/2012)
The data in the table was actually around 36 GB.But when we deleted the records the data size went down to 6 GB but the index size is still 36 GB.
How long ago was the deletion? When SQL deallocates pages, it doesn't always do it immediately, the deallocations can be deferred to a later date, especially if there are lots of them. This won't show in index_physical_stats (because the pages are no longer allocated to the index), but they're still classified as allocated. Nothing you need to do to force the deallocation, SQL will process it in the background (called deferred drop).
Note that it's not a case where you have lots and lots of mostly empty pages that require an index rebuild to fix. Your index pages are all mostly full (99%) and if there were lots of partially full pages, the page count in index_physical_stats would show the 36GB, which it does not.
Also, you should be aware that deletions from a heap may not deallocate the pages at all, even though they are empty. It's not uncommon to delete lots of data from a heap, be left with hundreds of completely empty pages that are not deallocated at all. There are reasons for this, it's not a bug. I can get you the details if you like.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2012 at 4:38 am
Sachin Nandanwar (1/2/2012)
But the amazing thing is that the overall DB size and the back up size also is much larger.
Deleting data won't reduce the size of the DB files. Only a shrink does that, and that should only be done if you don't expect the space to be reused in a reasonable amount of time (say 6 months).
As for the backups, again, how long ago was the delete, have you done more than one backup since then? What recovery model?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2012 at 8:00 am
Ok the data was deleted about 2 weeks back and I think in these 2 weeks approximately 100000 records might have been added as data from this table is archived after every 3 months or so.
So lets assume deallocations are not made immediately(for heaps and indexes) and are deferred to the later stages but then how come at this stage the data size for the heap is 6 GB and index size is 36 GB ?
Also will Alter table 'Mytable' Rebuild help in my case.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 2, 2012 at 8:13 am
The alter table, probably not. That affects the heap, not the index and it's the index that's got the strange size. The deallocation from indexes isn't immediate, but it's reasonably quick, so not two weeks
I assume, from the very low fragmentation and very high page usage that the indexes have been rebuilt since the delete at least once?
No other indexes on the table? No indexed views? You haven't dropped columns from the table when you did the deletion?
Can you query sys.dm_db_index_physical_stats for the entire DB? (so same as before but leave out the object_id in the function's call)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2012 at 9:23 pm
GilaMonster (1/2/2012)
I assume, from the very low fragmentation and very high page usage that the indexes have been rebuilt since the delete at least once?
Yes we have this weekend job that does all this.
No other indexes on the table? No indexed views? You haven't dropped columns from the table when you did the deletion?
Nope..
Can you query sys.dm_db_index_physical_stats for the entire DB? (so same as before but leave out the object_id in the function's call)
Will check and post the results.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply