| Author |
Topic |
|
prospect00
Starting Member
5 Posts |
 Posted - 2005-12-21 : 12:41:53
|
| Hey Guys,I seem to be having an issue with an SQL Database where the index_size is two times the size of the data in the table.I am running SQL 2000 SP3a and I have a database that I use for Microsoft Operations Manager. In any event, we collect alot of data every 5 minutes, which then populates this one specific table in the database. When I did a 'sp_spaceused 'tablename'', i get the following output ....'tablename' Rows 440999591 Reserved 152132752 KB Data 51300248 KB Index_size 100832552 KB unused -48 KBI'm almost sure that the index_size should not be double the size of the data being used.My question now is ... how do i go about fixing this?Please let me know if you need more infoThanks, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-21 : 13:22:32
|
| Run DBCC UPDATEUSAGE to correct any inaccuracies in sp_spaceused.Tara Kizeraka tduggan |
 |
|
|
prospect00
Starting Member
5 Posts |
Posted - 2005-12-21 : 13:51:18
|
| Thanks for the response.Will this drop the index_size down?are you implying that the size of this table is being mis-reported? or not being reported accurately?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-21 : 13:53:53
|
| Please read DBCC UPDATEUSAGE in SQL Server Books Online for what it does. 1. possibly2. yes that is possible which is why DBCC UPDATEUSAGE existsTara Kizeraka tduggan |
|
|
|
prospect00
Starting Member
5 Posts |
Posted - 2005-12-21 : 14:22:42
|
| Hey tkizer,thanks for responding again. and i apologize if it seems as if i'm asking dumb questions ... its just I dont have much experience when it comes to SQL.I ran the DBCC UPDATEUSAGE command, and it reports the following ....DBCC UPDATEUSAGE: sysindexes row updated for table 'SC_SampledNumericDataFact_Table' (index ID 2): USED pages: Changed from (895572) to (1584043) pages. RSVD pages: Changed from (895568) to (1584137) pages.DBCC UPDATEUSAGE: sysindexes row updated for table 'SC_SampledNumericDataFact_Table' (index ID 3): USED pages: Changed from (911321) to (2049430) pages. RSVD pages: Changed from (911320) to (2049576) pages.DBCC UPDATEUSAGE: sysindexes row updated for table 'SC_SampledNumericDataFact_Table' (index ID 4): USED pages: Changed from (911158) to (2045289) pages. RSVD pages: Changed from (911160) to (2045434) pages.DBCC UPDATEUSAGE: sysindexes row updated for table 'SC_SampledNumericDataFact_Table' (index ID 5): USED pages: Changed from (1156876) to (2565244) pages. RSVD pages: Changed from (1156880) to (2565424) pages.DBCC UPDATEUSAGE: sysindexes row updated for table 'SC_SampledNumericDataFact_Table' (index ID 9): USED pages: Changed from (1868228) to (4535984) pages. RSVD pages: Changed from (1868224) to (4537309) pages.DBCC UPDATEUSAGE: sysindexes row updated for table 'SC_SampledNumericDataFact_Table' (index ID 1): USED pages: Changed from (19299619) to (19302157) pages. RSVD pages: Changed from (19299603) to (19304567) pages.DBCC execution completed. If DBCC printed error messages, contact your system administrator.When I run the sp_spaceused command again, it reports the same issue where the index_size is still twice the size of the data in the table ....'tablename' Rows 440999591 Reserved 152132752 KB Data 51300248 KB Index_size 100832552 KB unused 19280 KBIs there something else I should try? I know this can't be right, since this is the only table I have that has the index size to be twice the size of the data!Any help would be greatly appreciated |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-21 : 14:29:15
|
| The information being reported by sp_spaceused is correct if you ran DBCC UPDATEUSAGE.Tara Kizeraka tduggan |
|
|
|
prospect00
Starting Member
5 Posts |
Posted - 2005-12-21 : 14:45:22
|
| Ok ... sounds good! I guess my question now is ... is this optimal? should the index_size be this much bigger than the amount of data in the table?I'm asking, because we are seeing a severe performance hit on this sql server, and this is the only thing that seems to be wrong! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-21 : 14:49:33
|
| Since this is a third party product, there is nothing that you can do to change things. You will need to contact MS to find out if the performance that you are seein gis normal with MOM. If it is, then you'll need to upgrade your hardware. We also run MOM here. We run it on a very beefy box all by itself.Tara Kizeraka tduggan |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-21 : 16:15:27
|
| Did you happen to see if the index was fragmented? You could run dbcc showcontig ('tablename', 'indexname'). That would tell you Extent Fragmentation and Logical Fragmentation levels. These should be low (< 15). Also, you might have a FILLFACTOR set on that index which is bloating it. If you have say a 50% Fill it will only fill half the page. Take a look at this and post back.Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
prospect00
Starting Member
5 Posts |
Posted - 2005-12-21 : 16:22:38
|
| Hey,Thanks for responding. I executed a show contig earlier yesterday, and fragmentation was less than 2%, so i know fragmentation isn't the issue.I haven't checked the FillFactor. Can you detail how to do that? That could very well be it!Also, another thing i failed to note was that ... I created two maintenance jobs ... one to re-index this database, and another to check the integrity of this database. These jobs were scheduled to run once a week. After three weeks, I noticed that the jobs never completed successfully, and thats also when i noticed the bloating. I have since disabled the jobs.Hope this helps. thanks, |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-21 : 17:50:59
|
| When you setup the optimizations part in the maintenance plan, what options did you select? BTW, as part of the MOM install, it creates jobs to do the maintenance stuff already. So you were doing the work twice. Tara Kizeraka tduggan |
|
|
|
|
Index_size of a table is twice the size of table