DBCC SHOWCONTIG inconsistency????
This is a discussion on DBCC SHOWCONTIG inconsistency???? within the SQL Server forums, part of the Microsoft SQL Server category; --> I have a table where I store around 1 million rows for 7 days. I run a DBCC SHOWCONTIG ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 07:07 AM
| ||||
| ||||
 DBCC SHOWCONTIG inconsistency???? I have a table where I store around 1 million rows for 7 days. I run a DBCC SHOWCONTIG every week and I noticed that the number of Rows and the Average Free Bytes are stable but the number of Extents and the Average Record Size keeps increasing. Any idea how to explain this incrase? and how to stop it? Will DBCC DBREINDEX will help? Here is my data: Rows: 1166273 (same as last week) Extents: 147099 (+10% since last week) Max RecordSize: 7050 Average Record Size: 7688 (+10% since last week) AverageFreeBytes: 372 Average Page Density: 95 Scan density: 30      |
|
02-29-2008, 07:15 AM
| |||
| |||
| Re: DBCC SHOWCONTIG inconsistency???? Guillaume (loizeau@hotmail.com) writes: > I have a table where I store around 1 million rows for 7 days. > I run a DBCC SHOWCONTIG every week and I noticed that the number of > Rows and the Average Free Bytes are stable but the number of Extents > and the Average Record Size keeps increasing. > Any idea how to explain this incrase? and how to stop it? Will DBCC > DBREINDEX will help? > > Here is my data: > Rows: 1166273 (same as last week) > Extents: 147099 (+10% since last week) > Max RecordSize: 7050 > Average Record Size: 7688 (+10% since last week) > AverageFreeBytes: 372 > Average Page Density: 95 > Scan density: 30 If he row size is growing that indicates that the newer data has higher row size. But it cannot increase with 10% for long, since 8060 bytes is the max row size. (Unless that row size includes text/image data.) > Around 150,000 new rows are inserted every day and 150,000 rows (older > than 7 days) are deleted every day. That can certainly do a fragmentation, and you should run DBCC DBREINDEX at least once a week. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 07:15 AM
| |||
| |||
| Re: DBCC SHOWCONTIG inconsistency???? Erland, The Average Record Size has reached 7984 bytes and it's still increasing. The first time I had the problem my filegroup was full and I didn't have time to investigate the issue so I just truncated the table. Now, I have plenty of space in the filegroup and I'd like to understand the problem before running any DBCC DBREINDEX. Could the statistics for the table be wrong? or not updated? *** Sent via Developersdex http://www.developersdex.com *** |
|
02-29-2008, 07:15 AM
| |||
| |||
| Re: DBCC SHOWCONTIG inconsistency???? Guillaume (anonymous@devdex.com) writes: > The Average Record Size has reached 7984 bytes and it's still > increasing. > The first time I had the problem my filegroup was full and I didn't have > time to investigate the issue so I just truncated the table. Now, I have > plenty of space in the filegroup and I'd like to understand the problem > before running any DBCC DBREINDEX. > Could the statistics for the table be wrong? or not updated? If the average record size keeps increaseing, it's probably because of your data. If that is abnormal, you will need to look at the data you load. Or do you have any reason to assume that the number is incorrect? What is your maximum record size? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 07:16 AM
| |||
| |||
| Re: DBCC SHOWCONTIG inconsistency???? Erland The table contains only "events" monitoring my system and the data I store in that table should not grow over time. I have several systems running in parallel and this is the only DB where I have the problem. Here is what I get from the SHOWCONTIG on the suspicious DB: Minimum Record Size: 116 Maximum record Size: 7050 Average Record Size: 7984 Do you know why the Average Record Size is bigger then the Max Record Size? it doesn't make sense. Here is another SHOWCONTIG result from a different system. Minimum Record Size: 111 Maximum record Size: 7231 Average Record Size: 420 *** Sent via Developersdex http://www.developersdex.com *** |
|
02-29-2008, 07:16 AM
| |||
| |||
| Re: DBCC SHOWCONTIG inconsistency???? Guillaume (anonymous@devdex.com) writes: > The table contains only "events" monitoring my system and the data I > store in that table should not grow over time. I have several systems > running in parallel and this is the only DB where I have the problem. > Here is what I get from the SHOWCONTIG on the suspicious DB: > Minimum Record Size: 116 > Maximum record Size: 7050 > Average Record Size: 7984 > > Do you know why the Average Record Size is bigger then the Max Record > Size? it doesn't make sense. That's undoubtedly a little funny. Maybe Paul Randal knows about some reason how this can arise. (And if Paul does not know, no one else is likely to know.) Then again, while the number is funny, it's not likely to be a major problem in itself, or do you have other problems with the table? A DBCC CHECKDB could reveal some corruption. I would try a DBCC DBREINDEX and see what happens. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 07:17 AM
| |||
| |||
| Re: DBCC SHOWCONTIG inconsistency???? >Then again, while the number is funny, it's not likely to be >a major problem in itself, or do you have other problems with >the table? No I don't. The only problem I have is the size of this single table growing every day. >A DBCC CHECKDB could reveal some corruption. Yes I will try to run it. >I would try a DBCC DBREINDEX and see what happens. I haven't done it yet because I was trying to figure out why the table size was growing steadily. If there is nothing I can do right now, I'll try the DBCC DBREINDEX Thanks for your help *** Sent via Developersdex http://www.developersdex.com *** |
|
02-29-2008, 07:17 AM
| |||
| |||
| Re: DBCC SHOWCONTIG inconsistency???? The only think I can think of is a bug in showcontig where we're dividing the cumulative row size count by the wrong number of pages, but I find it hard to believe that such a bug has existed unnoticed for 6 years (since I rewrote most of showcontig in Spring '99). Can you post the full showcontig output for the two tables (one on the suspicious system and the comparable table on another system)? Thanks -- Paul Randal Dev Lead, Microsoft SQL Server Storage Engine This posting is provided "AS IS" with no warranties, and confers no rights. "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns96477EB94FE49Yazorman@127.0.0.1... > Guillaume (anonymous@devdex.com) writes: > > The table contains only "events" monitoring my system and the data I > > store in that table should not grow over time. I have several systems > > running in parallel and this is the only DB where I have the problem. > > Here is what I get from the SHOWCONTIG on the suspicious DB: > > Minimum Record Size: 116 > > Maximum record Size: 7050 > > Average Record Size: 7984 > > > > Do you know why the Average Record Size is bigger then the Max Record > > Size? it doesn't make sense. > > That's undoubtedly a little funny. Maybe Paul Randal knows about some > reason how this can arise. (And if Paul does not know, no one else is > likely to know.) > > Then again, while the number is funny, it's not likely to be a major > problem in itself, or do you have other problems with the table? A DBCC > CHECKDB could reveal some corruption. > > I would try a DBCC DBREINDEX and see what happens. > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 07:17 AM
| |||
| |||
| Re: DBCC SHOWCONTIG inconsistency???? Here is the SHOWCONTIG for the same table on 3 Databases. I have NOT done a DBCC DBREINDEX on any of those tables. Suspicious table 1.3 million rows ObjectName Events ObjectId 1221579390 IndexName IX_Time IndexId 1 Level 0 Pages 1372708 Rows 1323293 Min RecordSize 116 Max RecordSize 7050 Average RecordSize 7984.698 Extents 173093 ExtentSwitches 640050 AverageFreeBytes 370.9679871 Average Page Density 95.41675568 Scan Density 26.80864494 Best Count 171589 Actual Count 640051 Logical Fragmentation 12.87914085 ExtentFragmentation 6.509795189 Normal table 1.1 million rows ObjectName Events ObjectId 1221579390 IndexName IX_Time IndexId 1 Level 0 Pages 63520 Rows 1170832 Min RecordSize 111 Max RecordSize 7231 Average RecordSize 420.889 Extents 7981 ExtentSwitches 8036 AverageFreeBytes 301.098999 Average Page Density 96.27997589 Scan Density 98.793082 Best Count 7940 Actual Count 8037 Logical Fragmentation 0.127518892 ExtentFragmentation 33.07855988 Normal table 9 million rows ObjectName Events ObjectId 1221579390 IndexName IX_Time IndexId 1 Level 0 Pages 467440 Rows 9048200 Min RecordSize 130 Max RecordSize 7059 Average RecordSize 375.19 Extents 61,950 ExtentSwitches 115,936 AverageFreeBytes 794.7390137 Average Page Density 90.18113708 Scan Density 50.39806102 Best Count 58430 Actual Count 115937 Logical Fragmentation 11.53752327 ExtentFragmentation 12.42292213 *** Sent via Developersdex http://www.developersdex.com *** |
|
02-29-2008, 07:17 AM
| ||||
| ||||
| Re: DBCC SHOWCONTIG inconsistency???? I looked through the code and found the problem. In SQL Server 2000, showcontig uses the size information from all the rows, but only counts non-ghost rows in the rowcount. For an index with a bunch of ghost rows, the avg rowsize will be higher than the real value, as the total rowsize is being divided by the wrong (smaller) number of rows. In SQL Server 2005 this problem has been fixed. I checked back through the internal bug database and this issue was found internally in 2002 and the decision was made not to fix it in SQL Server 2000. Shows how good my memory is. If you heavily rely on this value I recommend rebuilding the index as we won't provide a fix for it at this stage of SQL Server 2000's lifecycle. My apologies for the time you've spent trying to figure this out but I'm glad we finally did. Let me know if you have an further questions. Thanks and regards. -- Paul Randal Dev Lead, Microsoft SQL Server Storage Engine This posting is provided "AS IS" with no warranties, and confers no rights. "Guillaume" <anonymous@devdex.com> wrote in message news:Nvzce.442$zX2.13250@news.uswest.net... Here is the SHOWCONTIG for the same table on 3 Databases. I have NOT done a DBCC DBREINDEX on any of those tables. Suspicious table 1.3 million rows ObjectName Events ObjectId 1221579390 IndexName IX_Time IndexId 1 Level 0 Pages 1372708 Rows 1323293 Min RecordSize 116 Max RecordSize 7050 Average RecordSize 7984.698 Extents 173093 ExtentSwitches 640050 AverageFreeBytes 370.9679871 Average Page Density 95.41675568 Scan Density 26.80864494 Best Count 171589 Actual Count 640051 Logical Fragmentation 12.87914085 ExtentFragmentation 6.509795189 Normal table 1.1 million rows ObjectName Events ObjectId 1221579390 IndexName IX_Time IndexId 1 Level 0 Pages 63520 Rows 1170832 Min RecordSize 111 Max RecordSize 7231 Average RecordSize 420.889 Extents 7981 ExtentSwitches 8036 AverageFreeBytes 301.098999 Average Page Density 96.27997589 Scan Density 98.793082 Best Count 7940 Actual Count 8037 Logical Fragmentation 0.127518892 ExtentFragmentation 33.07855988 Normal table 9 million rows ObjectName Events ObjectId 1221579390 IndexName IX_Time IndexId 1 Level 0 Pages 467440 Rows 9048200 Min RecordSize 130 Max RecordSize 7059 Average RecordSize 375.19 Extents 61,950 ExtentSwitches 115,936 AverageFreeBytes 794.7390137 Average Page Density 90.18113708 Scan Density 50.39806102 Best Count 58430 Actual Count 115937 Logical Fragmentation 11.53752327 ExtentFragmentation 12.42292213 *** Sent via Developersdex http://www.developersdex.com *** |
Linear Mode
