DBCC SHOWCONTIG inconsistency?? ?? - Microsoft SQL Server

Guillaume

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)
AverageFreeByte s: 372
Average Page Density: 95
Scan density: 30

Jul 23 '05 #1

Subscribe Reply

5134

Erland Sommarskog

Guillaume (lo*****@hotmai l.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)
AverageFreeByte s: 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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #2

Guillaume

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 ***

Jul 23 '05 #3

Erland Sommarskog

Guillaume (an*******@devd ex.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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #4

Guillaume

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 ***

Jul 23 '05 #5

Erland Sommarskog

Guillaume (an*******@devd ex.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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #6

Guillaume

>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 ***

Jul 23 '05 #7

Paul S Randal [MS]

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" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Guillaume (an*******@devd ex.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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #8

Paul S Randal [MS]

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" <an*******@devd ex.com> wrote in message news:Nv******** *********@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
AverageFreeByte s 370.9679871
Average Page Density 95.41675568
Scan Density 26.80864494
Best Count 171589
Actual Count 640051
Logical Fragmentation 12.87914085
ExtentFragmenta tion 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
AverageFreeByte s 301.098999
Average Page Density 96.27997589
Scan Density 98.793082
Best Count 7940
Actual Count 8037
Logical Fragmentation 0.127518892
ExtentFragmenta tion 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
AverageFreeByte s 794.7390137
Average Page Density 90.18113708
Scan Density 50.39806102
Best Count 58430
Actual Count 115937
Logical Fragmentation 11.53752327
ExtentFragmenta tion 12.42292213

*** Sent via Developersdex http://www.developersdex.com ***

Jul 23 '05 #9

Paul S Randal [MS]

BTW - forgot to say - the reason you're seeing the avg row size increase is that (ironically) you're deleting more records and so the number of ghost records is growing, further distorting the calculation.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Paul S Randal [MS]" <pr*****@online .microsoft.com> wrote in message news:42******** @news.microsoft .com...
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" <an*******@devd ex.com> wrote in message news:Nv******** *********@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
AverageFreeByte s 370.9679871
Average Page Density 95.41675568
Scan Density 26.80864494
Best Count 171589
Actual Count 640051
Logical Fragmentation 12.87914085
ExtentFragmenta tion 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
AverageFreeByte s 301.098999
Average Page Density 96.27997589
Scan Density 98.793082
Best Count 7940
Actual Count 8037
Logical Fragmentation 0.127518892
ExtentFragmenta tion 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
AverageFreeByte s 794.7390137
Average Page Density 90.18113708
Scan Density 50.39806102
Best Count 58430
Actual Count 115937
Logical Fragmentation 11.53752327
ExtentFragmenta tion 12.42292213

*** Sent via Developersdex http://www.developersdex.com ***

Jul 23 '05 #10

Similar topics

4247

"Order by" error running DBCC DBREINDEX

by: John Morris | last post by:

I'm running a simple DBCC DBREINDEX ('myTable') and I receive the following error: "Server: Msg 169, Level 15, State 2, Line 2 A column has been specified more than once in the order by list. Columns in the order by list must be unique. DBCC execution completed. If DBCC printed error messages, contact your system administrator." I can successfully reindex other tables in this database. I thought

Microsoft SQL Server

6839

Help! Index corrupting? DBCC CheckTable failing?

by: David Schwartz | last post by:

We are having quite a time since moving a large database to a new server (actually built new server, renamed as old to make seamless for users, etc.) Import 104 million row database (5 column) into table (CD_Assets_bad2) from existing (CD_Assets): Account(varchar(8)) TransactionDate(datetime(8) Flow(varchar(1)) Category(varchar(7))

Microsoft SQL Server

5645

Fixing my table based on Dbcc Showcontig results

by: SR | last post by:

Can someone please help me interpret this result set below and suggest on way I can speed up my table? What changes should I make? DBCC SHOWCONTIG scanning 'tblListing' table... Table: 'tblListing' (1092914965); index ID: 1, database ID: 13 TABLE level scan performed. - Pages Scanned................................: 97044 - Extents Scanned..............................: 12177 - Extent Switches..............................: 13452 -...

Microsoft SQL Server

6732

DTCXact transaction in DBCC openTran

by: Kalvin | last post by:

I keep seeing this return from running a DBCC OpenTran: Transaction information for database 'Live_App'. Oldest active transaction: SPID (server process ID) : 92 UID (user ID) : 1 Name : DTCXact LSN : (12837:1924:1) Start time : Oct 4 2004 8:54:03:570AM

Microsoft SQL Server

4624

Cannot empty a data file to another using DBCC SHRINKFILE EMPTYFILE

by: Auday Alwash | last post by:

Hi, A month ago, we were running out of disk space on a drive that stores the data file for a SQL Server 7 database. So I added a new drive and created a new data file on the primary filegroup and restricted the growth of the first data file. We have a new server now with heaps of space and I want to merge the two data files into one but am running into huge issues. The reason why I want to do this is to eliminate the possibilty that...

Microsoft SQL Server

2577

DBCC DBREINDEX not working

by: jon_brookins | last post by:

I have a stored proc that identifies indexes that need to be defragmented ( LogicalFragmentation > 20% ) using DBCC SHOWCONTIG. This works fine and shows me all of the candidate indexes to be defragged. I am using DBCC DBREINDEX to rebuild the indexes. What I noticed yesterday is that it seems that DBREINDEX is not actually doing anything. I would check the target indexes, run DBREINDEX, then check again. The same list of indexes came...

Microsoft SQL Server

8026

Errors on DBCC SHRINKFILE(2)

by: Scarab | last post by:

Hi, When I use dbcc shrinkfile to shrink LOG file, following error occurs: DBCC SHRINKFILE(2) ---------------------------------------------------------------------------- ----------- Cannot shrink log file 2 (myDB_log) because all logical log files are in use. (1 row(s) affected)

Microsoft SQL Server

8064

dbcc reindex issue - - I don't understand!!

by: Raziq Shekha | last post by:

Hi Folks, SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran the command : dbcc dbreindex ('tablename') go for all tables in the database. Then I compared the dbcc showcontig with all_index output from before and after the reindex and on the

Microsoft SQL Server

4978

dbcc shrinkdatabase

by: David Greenberg | last post by:

Hello We use SqlServer 2000. I'm trying to run the "dbcc shrinkdatabase" command on a specific database. I keep on getting the message: DBCC execution completed. If DBCC printed error messages, contact your system administrator. I don't see any error messages but its obvious that the command never ran. There is no change in the size of any of the data base files. There

Microsoft SQL Server

9579

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10575

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10319

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

10076

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6851

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5520

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4297

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3816

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2990

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General