November 2, 2015 at 1:31 pm
Good afternoon
I ran across an issue that I don't understand. I have a DB that is 177gb. When I dug through the DB, I found a table that said it had 8gb of data space used and 90gb of index space used. How does that happen?
November 2, 2015 at 1:42 pm
How many indexes? How are you measuring the space used? What's the fragmentation like?
Gerald Britton, Pluralsight courses
November 2, 2015 at 1:54 pm
Thank you for responding. I'm looking at Object Explorer Details in SSMS. The column names are Data Space Used (KB) and Index Space Used (KB). I didn't check the fragmentation on the indexes - yet. Would fragmentation product that kind of a result?
November 2, 2015 at 2:02 pm
Fragmentation can increase the space an index uses. It's more likely though that the table just has lots and lots and lots of indexes each with lots of columns.
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
November 3, 2015 at 10:12 am
Good afternoon
Thank you for your response Gail. I looked at the amount of indexes on the table - there are seven in total. All are listed as non-clustered, although there is one that has a prefix of PK_xxx (Unique, Non-Clustered). Could I be looking at a corrupt index??
November 3, 2015 at 10:27 am
Unlikely. Corruption causes high-severity error messages.
You may have an incorrect page space usage. Run DBCC updateusage
Also check the columns in those indexes. If they have lots and lots of columns, they can be quite large.
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
November 4, 2015 at 8:26 am
Ok - I'll fast forward to the end of the story. After working with the vendor, they decided the data in the table was not need. They truncated the table and now the DB is down to 18gb. I wish they would have told me what they were looking at BEFORE they truncated the table but it is a vendor system...
Thank you to both of you for offering your advice - I appreciate your assistance.
The Gug
November 5, 2015 at 9:49 am
Easy ways to create indexes using multiple times the actual table space:
1. Create multiple indexes on MANY columns in the table (I have actually seen indexes defined that were the entire set of columns of the table - and there were a lot of columns).
2. "Include" inappropriate columns from the table (either with "Include ()" or just as part of a covering index). ("Inappropriate" = columns that will rarely if ever help queries to find the desired data.)
3. Create duplicate indexes. (I once found a table with THREE identical indexes on it. BTW - an index on "Tbl(a,b)" is essentially the same as "Tbl(a) include (b)", so if you see two indexes like that, one of them can be dropped.)
4. Use GUIDs for your primary index columns and watch your index pages split.
5. Don't ever rebuild your indexes.
They may have truncated the table, but you can still look at the index definitions and see how many of the above occurred... 😉
November 27, 2015 at 3:58 am
The Gug (11/2/2015)
Good afternoonI ran across an issue that I don't understand. I have a DB that is 177gb. When I dug through the DB, I found a table that said it had 8gb of data space used and 90gb of index space used. How does that happen?
For future reference, you may find the query in the article below helpful
http://www.sqlservercentral.com/scripts/Index+Size/128428/[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply