bcp Reload Doubles database size..why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

bcp Reload Doubles database size..why?

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2010-03-25 : 10:13:21

Ok...we have to sanitize production Data

I export tables that have privacy info..35 tables out of 100

We scramble those files (as fixed width export), and then import them back in (also fixed width), using bcp and format files

The database doubles in size from 5GB to 10 GB

I then do

1. update in place to RTIM any spaces from the privacy tables
2. DBCC REINDEX of thos Tables
3. BACKUP LOG Tracker_Data WITH TRUNCATE_ONLY
4. Doing each operation in it's own transaction, and doing the Truncate Log after each
5. DBCC SHRINKDATABASE (APO, 1);
6. DBCC SHRINKFILE (APO_Temp_Log);
7. DBCC SHRINKFILE (APO_temp_Data);

And the database comes down to 8GB

Anyone know what else I can do to get the size back down to 5GB???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 11:01:01

Note that "3. BACKUP LOG Tracker_Data WITH TRUNCATE_ONLY" is deprecated, and not available in SQl 2008 - you may want to change procedure to switch to SIMPLE Recovery Model ...

No FILL FACTOR on REINDEX is there? That might be padding the index pages

Check table sizes (Rows and MB - both Data and Indexes) before and after to see which tables / indexes have grown in case that sheds any light on it?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-25 : 13:22:16

the .mdf doubles in size?

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 13:56:31

Yeah, good point. Presumably there is a TRUNCATE or somesuch before the re-import?

Although, Russell, as I understand it the import-back has trailing spaces that are then Trimmed with SQL commands, so that may be the cause of the doubling of space, and then after Trim the database cannot be got back to its original size.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-25 : 14:48:24

yeah...i was wondering if the update is growing the log. i like your advice to look at the index and table sizes before and after.

i'd like to know the actual row sizes too. might be the rtrim() has no effect on the data layout in page.

one last thing...when shrinking data and log files, i always specify target size

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 14:57:55

"i always specify target size"

There's Brett's solution then

Just tell SQL to shrink the file to 5GB and leave SQL to work out how to cram the data in

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-25 : 15:13:58

only if it can. else it'll grow anyway lol

X002548
Not Just a Number

15586 Posts

Posted - 2010-03-26 : 09:00:00

Sorry...can you move this..this is sql server 2000...3rd party vendor product...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-03-26 : 09:30:45

REINDEX

Fillfactor if not specified is 100..doesn't that mean fill the entire page?

And

DBCC SHRINFILE

If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created, or the last size set by using ALTER DATABASE.

Are these bad practices?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-26 : 10:15:13

correct on both.

fillfactor 100 is not a bad practice. Tara did some benchmarking and said it performs better in fact.

shrinking is a bad practice if it's just going to grow again. for a one time import i don't see any problem shrinking after. but i don't unless i'm running out of disk space

X002548
Not Just a Number

15586 Posts

Posted - 2010-03-26 : 10:57:17

OK, so what happens if the default size is too small?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-03-26 : 12:35:42

It will shrink to "exact fit"

Must be some slack / wasteage somewhere

You **sure** the TRim is hitting every column?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-26 : 13:48:42

i don't suppose these 35 tables have the same schema do they? can we see the DDL for one of em at least? and any indexes on it?

when you import 'em back in, do they go to the same original tables?

what are the results of DBCC ShowContig?

X002548
Not Just a Number

15586 Posts

Posted - 2010-03-26 : 15:58:59

quote:
Originally posted by russell

i don't suppose these 35 tables have the same schema do they? can we see the DDL for one of em at least? and any indexes on it?


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TRK_ASI_C_Detail]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TRK_ASI_C_Detail](
	[PropIDNumber] [nvarchar](20) NOT NULL,
	[PropIDCode] [nvarchar](5) NOT NULL,
	[LastName] [nvarchar](42) NULL DEFAULT (''),
	[FirstName] [nvarchar](15) NULL DEFAULT (''),
	[MiddleInitial] [nvarchar](1) NULL DEFAULT (''),
	[Prefix] [nvarchar](3) NULL DEFAULT (''),
	[Suffix] [nvarchar](3) NULL DEFAULT (''),
	[CareOfName] [nvarchar](30) NULL DEFAULT (''),
	[Address1] [nvarchar](30) NULL DEFAULT (''),
	[Address2] [nvarchar](30) NULL DEFAULT (''),
	[City] [nvarchar](20) NULL DEFAULT (''),
	[State] [nvarchar](2) NULL DEFAULT (''),
	[County] [nvarchar](3) NULL DEFAULT (''),
	[Zip] [nvarchar](9) NULL DEFAULT (''),
	[Country] [nvarchar](20) NULL DEFAULT (''),
	[PropDesc] [nvarchar](65) NULL DEFAULT (''),
	[QuickPState] [nvarchar](2) NULL DEFAULT (''),
	[QuickPType] [nvarchar](5) NULL DEFAULT (''),
	[OwnerSeq] [smallint] NULL DEFAULT (0),
	[StateOwnerCode] [nvarchar](8) NULL DEFAULT (''),
	[ExtraDescription] [nvarchar](68) NULL DEFAULT (''),
	[BusinessIndicator] [nvarchar](1) NULL DEFAULT (''),
	[StatusIndicator] [nvarchar](1) NULL DEFAULT (''),
	[PreliminaryIndicator] [nvarchar](1) NULL DEFAULT (''),
	[RemovalIndicator] [nvarchar](2) NULL DEFAULT (''),
	[LastModDate] [nvarchar](8) NULL DEFAULT ('00000000'),
	[LastTranDate] [nvarchar](8) NULL DEFAULT ('00000000'),
	[DateLoaded] [nvarchar](8) NULL DEFAULT ('00000000'),
	[DatePayable] [nvarchar](8) NULL DEFAULT ('00000000'),
	[DatePaid] [nvarchar](8) NULL DEFAULT ('00000000'),
	[StatePaid] [nvarchar](2) NULL DEFAULT (''),
	[LastContactDate1] [nvarchar](8) NULL DEFAULT ('00000000'),
	[LastContactType1] [nvarchar](1) NULL DEFAULT (''),
	[LastContactDate2] [nvarchar](8) NULL DEFAULT ('00000000'),
	[LastContactType2] [nvarchar](1) NULL DEFAULT (''),
	[LastContactDate3] [nvarchar](8) NULL DEFAULT ('00000000'),
	[LastContactType3] [nvarchar](1) NULL DEFAULT (''),
	[LastContactDate4] [nvarchar](8) NULL DEFAULT ('00000000'),
	[LastContactType4] [nvarchar](1) NULL DEFAULT (''),
	[LastContactDate5] [nvarchar](8) NULL DEFAULT ('00000000'),
	[LastContactType5] [nvarchar](1) NULL DEFAULT (''),
	[LastContactDate6] [nvarchar](8) NULL DEFAULT ('00000000'),
	[LastContactType6] [nvarchar](1) NULL DEFAULT (''),
	[CheckNum] [nvarchar](10) NULL DEFAULT (''),
	[PolicyNumber] [nvarchar](20) NULL DEFAULT (''),
	[AtiCode] [nvarchar](24) NULL DEFAULT (''),
	[SafeDepBox] [nvarchar](15) NULL DEFAULT (''),
	[InterestRate] [float] NULL DEFAULT (0),
	[InitialAmount] [float] NULL DEFAULT (0),
	[DeductAmount] [float] NULL DEFAULT (0),
	[DeductCode] [nvarchar](2) NULL DEFAULT (''),
	[AdditionAmount] [float] NULL DEFAULT (0),
	[AdditionCode] [nvarchar](2) NULL DEFAULT (''),
	[AmountDue] [float] NULL DEFAULT (0),
	[AmountPaid] [float] NULL DEFAULT (0),
	[Cusip] [nvarchar](9) NULL DEFAULT (''),
	[Series] [nvarchar](15) NULL DEFAULT (''),
	[DateOfIssue] [nvarchar](8) NULL DEFAULT ('00000000'),
	[Shares] [float] NULL DEFAULT (0),
	[ParValue] [float] NULL DEFAULT (0),
	[MarketValue] [float] NULL DEFAULT (0),
	[Confirmation] [nvarchar](1) NULL DEFAULT (''),
	[MethodOfTran] [nvarchar](1) NULL DEFAULT (''),
	[StockType] [nvarchar](3) NULL DEFAULT (''),
	[StockStatus] [nvarchar](1) NULL DEFAULT (''),
	[IssuerName] [nvarchar](40) NULL DEFAULT (''),
	[CalOwnerDate] [nvarchar](8) NULL DEFAULT ('00000000'),
	[OriginalIssueName] [nvarchar](30) NULL DEFAULT (''),
	[OriginalIssueAmount] [float] NULL DEFAULT (0),
	[OriginalIssueShares] [float] NULL DEFAULT (0),
	[BeginPeriodDate] [nvarchar](8) NULL DEFAULT ('00000000'),
	[EndingPeriodDate] [nvarchar](8) NULL DEFAULT ('00000000'),
	[AgentCode] [nvarchar](20) NULL DEFAULT (''),
	[UserDefined1] [nvarchar](15) NULL DEFAULT (''),
	[UserDefined2] [nvarchar](15) NULL DEFAULT (''),
	[UserDefined3] [nvarchar](15) NULL DEFAULT (''),
	[UserDefined4] [nvarchar](15) NULL DEFAULT (''),
	[UserDefined5] [nvarchar](15) NULL DEFAULT (''),
	[UserDefined6] [nvarchar](15) NULL DEFAULT (''),
	[UserDefined7] [nvarchar](15) NULL DEFAULT (''),
	[UserDefined8] [nvarchar](15) NULL DEFAULT (''),
	[HolderID] [nvarchar](6) NULL DEFAULT (''),
	[DateOfBirth] [nvarchar](8) NULL DEFAULT ('00000000'),
	[AccountNumber] [nvarchar](24) NULL DEFAULT (''),
	[DormantYear] [nvarchar](4) NULL,
	[TimeLoaded] [nvarchar](4) NULL,
	[ID] [float] NOT NULL,
	[OwnerID] [nvarchar](10) NOT NULL,
	[UserDefined9] [nvarchar](30) NULL DEFAULT (''),
	[UserDefined10] [nvarchar](30) NULL DEFAULT (''),
	[UserDefined11] [nvarchar](30) NULL DEFAULT (''),
	[UserDefined12] [nvarchar](30) NULL DEFAULT (''),
	[UserDefined13] [nvarchar](30) NULL DEFAULT (''),
	[UserDefined14] [nvarchar](30) NULL DEFAULT (''),
	[UserDefined15] [nvarchar](30) NULL DEFAULT (''),
	[UserDefined16] [nvarchar](30) NULL DEFAULT (''),
	[AltState] [nvarchar](2) NULL DEFAULT (''),
	[DriverLicenseNumber] [nvarchar](15) NULL DEFAULT (''),
	[DriverLicenseState] [nvarchar](2) NULL DEFAULT (''),
	[EmailAddress] [nvarchar](50) NULL DEFAULT (''),
	[UnpaidRent] [int] NULL DEFAULT (0),
	[DrillingFees] [int] NULL DEFAULT (0),
	[OpenedDate] [nvarchar](8) NULL DEFAULT ('00000000'),
	[OpenedBy] [nvarchar](25) NULL DEFAULT (''),
	[ExpiredDate] [nvarchar](8) NULL DEFAULT ('00000000'),
 CONSTRAINT [PK_TRK_ASI_C_Detail_ID] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[TRK_ASI_C_Detail]') AND name = N'IX_TRK_ASI_C_Detail_OwnerID')
CREATE CLUSTERED INDEX [IX_TRK_ASI_C_Detail_OwnerID] ON [dbo].[TRK_ASI_C_Detail] 
(
	[OwnerID] ASC
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[TRK_ASI_C_Detail]') AND name = N'IX_TRK_ASI_C_Detail_LastName')
CREATE NONCLUSTERED INDEX [IX_TRK_ASI_C_Detail_LastName] ON [dbo].[TRK_ASI_C_Detail] 
(
	[LastName] ASC
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[TRK_ASI_C_Detail]') AND name = N'IX_TRK_ASI_C_Detail_PropIDNumber')
CREATE NONCLUSTERED INDEX [IX_TRK_ASI_C_Detail_PropIDNumber] ON [dbo].[TRK_ASI_C_Detail] 
(
	[PropIDNumber] ASC
) ON [PRIMARY]

quote:

when you import 'em back in, do they go to the same original tables?

Yes

quote:

what are the results of DBCC ShowContig?

I don't know what I am looking at here...but fragmetations in the 99% range can'tr be good


DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 110
- Extents Scanned..............................: 19
- Extent Switches..............................: 109
- Avg. Pages per Extent........................: 5.8
- Scan Density [Best Count:Actual Count].......: 12.73% [14:110]
- Logical Scan Fragmentation ..................: 49.09%
- Extent Scan Fragmentation ...................: 68.42%
- Avg. Bytes Free per Page.....................: 4551.8
- Avg. Page Density (full).....................: 43.76%
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 111
- Extents Scanned..............................: 24
- Extent Switches..............................: 84
- Avg. Pages per Extent........................: 4.6
- Scan Density [Best Count:Actual Count].......: 16.47% [14:85]
- Logical Scan Fragmentation ..................: 42.34%
- Extent Scan Fragmentation ...................: 95.83%
- Avg. Bytes Free per Page.....................: 4028.9
- Avg. Page Density (full).....................: 50.22%
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 101
- Extents Scanned..............................: 27
- Extent Switches..............................: 90
- Avg. Pages per Extent........................: 3.7
- Scan Density [Best Count:Actual Count].......: 14.29% [13:91]
- Logical Scan Fragmentation ..................: 47.52%
- Extent Scan Fragmentation ...................: 92.59%
- Avg. Bytes Free per Page.....................: 4795.5
- Avg. Page Density (full).....................: 40.75%
DBCC SHOWCONTIG scanning 'systypes' table...
Table: 'systypes' (4); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6712.0
- Avg. Page Density (full).....................: 17.07%
DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 49
- Extents Scanned..............................: 12
- Extent Switches..............................: 40
- Avg. Pages per Extent........................: 4.1
- Scan Density [Best Count:Actual Count].......: 17.07% [7:41]
- Logical Scan Fragmentation ..................: 44.90%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 4582.7
- Avg. Page Density (full).....................: 43.38%
DBCC SHOWCONTIG scanning 'sysfiles1' table...
Table: 'sysfiles1' (8); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6508.0
- Avg. Page Density (full).....................: 19.59%
DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6996.0
- Avg. Page Density (full).....................: 13.57%
DBCC SHOWCONTIG scanning 'sysusers' table...
Table: 'sysusers' (10); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6344.0
- Avg. Page Density (full).....................: 21.62%
DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 11
- Extents Scanned..............................: 7
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 1.6
- Scan Density [Best Count:Actual Count].......: 20.00% [2:10]
- Logical Scan Fragmentation ..................: 45.45%
- Extent Scan Fragmentation ...................: 71.43%
- Avg. Bytes Free per Page.....................: 2896.0
- Avg. Page Density (full).....................: 64.22%
DBCC SHOWCONTIG scanning 'sysfilegroups' table...
Table: 'sysfilegroups' (96); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8058.0
- Avg. Page Density (full).....................: 0.44%
DBCC SHOWCONTIG scanning 'TRK_PBT_L_Deletes' table...
Table: 'TRK_PBT_L_Deletes' (17851576); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1593
- Extents Scanned..............................: 202
- Extent Switches..............................: 202
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.52% [200:203]
- Logical Scan Fragmentation ..................: 99.44%
- Extent Scan Fragmentation ...................: 0.50%
- Avg. Bytes Free per Page.....................: 491.6
- Avg. Page Density (full).....................: 93.93%
DBCC SHOWCONTIG scanning 'TRK_PBC_C_Detail' table...
Table: 'TRK_PBC_C_Detail' (23423603); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 10
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.5
- Scan Density [Best Count:Actual Count].......: 50.00% [2:4]
- Logical Scan Fragmentation ..................: 90.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 640.2
- Avg. Page Density (full).....................: 92.09%
DBCC SHOWCONTIG scanning 'TRK_PRIAC_L_Operation' table...
Table: 'TRK_PRIAC_L_Operation' (63755730); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 27
- Extents Scanned..............................: 13
- Extent Switches..............................: 12
- Avg. Pages per Extent........................: 2.1
- Scan Density [Best Count:Actual Count].......: 30.77% [4:13]
- Extent Scan Fragmentation ...................: 92.31%
- Avg. Bytes Free per Page.....................: 1482.6
- Avg. Page Density (full).....................: 81.68%
DBCC SHOWCONTIG scanning 'TRK_PRIAC_L_OwnerInfo' table...
Table: 'TRK_PRIAC_L_OwnerInfo' (95755844); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 3072
- Extents Scanned..............................: 386
- Extent Switches..............................: 385
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.48% [384:386]
- Extent Scan Fragmentation ...................: 35.49%
- Avg. Bytes Free per Page.....................: 3449.9
- Avg. Page Density (full).....................: 57.38%
DBCC SHOWCONTIG scanning 'TRK_PRUINS_C_Detail' table...
Table: 'TRK_PRUINS_C_Detail' (127755958); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 47455
- Extents Scanned..............................: 5959
- Extent Switches..............................: 6787
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 87.39% [5932:6788]
- Logical Scan Fragmentation ..................: 99.99%
- Extent Scan Fragmentation ...................: 9.33%
- Avg. Bytes Free per Page.....................: 440.3
- Avg. Page Density (full).....................: 94.56%
DBCC SHOWCONTIG scanning 'TRK_PBT_C_Comments' table...
Table: 'TRK_PBT_C_Comments' (133327985); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 438.0
- Avg. Page Density (full).....................: 94.59%
DBCC SHOWCONTIG scanning 'TRK_PLNJ_L_Changes' table...
Table: 'TRK_PLNJ_L_Changes' (146516001); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 585
- Extents Scanned..............................: 77
- Extent Switches..............................: 77
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 94.87% [74:78]
- Logical Scan Fragmentation ..................: 97.26%
- Extent Scan Fragmentation ...................: 24.68%
- Avg. Bytes Free per Page.....................: 763.3
- Avg. Page Density (full).....................: 90.57%
DBCC SHOWCONTIG scanning 'TRK_ASI_L_Operation' table...
Table: 'TRK_ASI_L_Operation' (168088085); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 14
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 1.4
- Scan Density [Best Count:Actual Count].......: 20.00% [2:10]
- Extent Scan Fragmentation ...................: 90.00%
- Avg. Bytes Free per Page.....................: 1779.6
- Avg. Page Density (full).....................: 78.01%
DBCC SHOWCONTIG scanning 'TRK_ASI_L_OwnerInfo' table...
Table: 'TRK_ASI_L_OwnerInfo' (200088199); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 201
- Extents Scanned..............................: 28
- Extent Switches..............................: 27
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 92.86% [26:28]
- Extent Scan Fragmentation ...................: 53.57%
- Avg. Bytes Free per Page.....................: 3395.5
- Avg. Page Density (full).....................: 58.05%
DBCC SHOWCONTIG scanning 'TRK_PLNJ_L_Deletes' table...
Table: 'TRK_PLNJ_L_Deletes' (226516286); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 496
- Extents Scanned..............................: 65
- Extent Switches..............................: 65
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 93.94% [62:66]
- Logical Scan Fragmentation ..................: 99.60%
- Extent Scan Fragmentation ...................: 6.15%
- Avg. Bytes Free per Page.....................: 597.9
- Avg. Page Density (full).....................: 92.61%
DBCC SHOWCONTIG scanning 'TRK_PEG_C_Detail' table...
Table: 'TRK_PEG_C_Detail' (232088313); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 13
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 4.3
- Scan Density [Best Count:Actual Count].......: 66.67% [2:3]
- Logical Scan Fragmentation ..................: 84.62%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 664.5
- Avg. Page Density (full).....................: 91.79%
DBCC SHOWCONTIG scanning 'TRK_PRI_H_Detail' table...
Table: 'TRK_PRI_H_Detail' (268996485); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7266.0
- Avg. Page Density (full).....................: 10.23%
DBCC SHOWCONTIG scanning 'TRK_PLAZ_C_Detail' table...
Table: 'TRK_PLAZ_C_Detail' (336420668); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1721
- Extents Scanned..............................: 218
- Extent Switches..............................: 218
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.63% [216:219]
- Logical Scan Fragmentation ..................: 99.83%
- Extent Scan Fragmentation ...................: 0.92%
- Avg. Bytes Free per Page.....................: 496.7
- Avg. Page Density (full).....................: 93.86%
DBCC SHOWCONTIG scanning 'TRK_PLNJ_C_Comments' table...
Table: 'TRK_PLNJ_C_Comments' (341992695); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 14.29% [1:7]
- Extent Scan Fragmentation ...................: 85.71%
- Avg. Bytes Free per Page.....................: 987.0
- Avg. Page Density (full).....................: 87.81%
DBCC SHOWCONTIG scanning 'TRK_PBT_H_Detail' table...
Table: 'TRK_PBT_H_Detail' (373328840); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1726
- Extents Scanned..............................: 219
- Extent Switches..............................: 219
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.18% [216:220]
- Logical Scan Fragmentation ..................: 99.54%
- Extent Scan Fragmentation ...................: 0.46%
- Avg. Bytes Free per Page.....................: 418.6
- Avg. Page Density (full).....................: 94.83%
DBCC SHOWCONTIG scanning 'TRK_PLNJ_H_Detail' table...
Table: 'TRK_PLNJ_H_Detail' (581993550); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 533
- Extents Scanned..............................: 71
- Extent Switches..............................: 70
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 94.37% [67:71]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 5.63%
- Avg. Bytes Free per Page.....................: 453.7
- Avg. Page Density (full).....................: 94.39%
DBCC SHOWCONTIG scanning 'TRK_PBC_L_Operation' table...
Table: 'TRK_PBC_L_Operation' (640473806); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 7387.7
- Avg. Page Density (full).....................: 8.73%
DBCC SHOWCONTIG scanning 'TRK_PBC_L_OwnerInfo' table...
Table: 'TRK_PBC_L_OwnerInfo' (672473920); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 3
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 3820.0
- Avg. Page Density (full).....................: 52.80%
DBCC SHOWCONTIG scanning 'TRK_PRI_C_Detail' table...
Table: 'TRK_PRI_C_Detail' (704474034); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 18
- Extents Scanned..............................: 8
- Extent Switches..............................: 7
- Avg. Pages per Extent........................: 2.3
- Scan Density [Best Count:Actual Count].......: 37.50% [3:8]
- Logical Scan Fragmentation ..................: 94.44%
- Extent Scan Fragmentation ...................: 62.50%
- Avg. Bytes Free per Page.....................: 474.7
- Avg. Page Density (full).....................: 94.14%
DBCC SHOWCONTIG scanning 'TRK_PRIAC_L_Changes' table...
Table: 'TRK_PRIAC_L_Changes' (723234077); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1970
- Extents Scanned..............................: 251
- Extent Switches..............................: 255
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 96.48% [247:256]
- Logical Scan Fragmentation ..................: 92.84%
- Extent Scan Fragmentation ...................: 32.27%
- Avg. Bytes Free per Page.....................: 713.7
- Avg. Page Density (full).....................: 91.18%
DBCC SHOWCONTIG scanning 'TRK_PRUINS_L_Operation' table...
Table: 'TRK_PRUINS_L_Operation' (744806161); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 720
- Extents Scanned..............................: 100
- Extent Switches..............................: 99
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 90.00% [90:100]
- Extent Scan Fragmentation ...................: 99.00%
- Avg. Bytes Free per Page.....................: 411.1
- Avg. Page Density (full).....................: 94.92%
DBCC SHOWCONTIG scanning 'TRK_PRUINS_L_OwnerInfo' table...
Table: 'TRK_PRUINS_L_OwnerInfo' (776806275); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 85549
- Extents Scanned..............................: 10697
- Extent Switches..............................: 10696
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.97% [10694:10697]
- Extent Scan Fragmentation ...................: 0.17%
- Avg. Bytes Free per Page.....................: 348.1
- Avg. Page Density (full).....................: 95.70%
DBCC SHOWCONTIG scanning 'TRK_PRIAC_L_Deletes' table...
Table: 'TRK_PRIAC_L_Deletes' (803234362); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 762
- Extents Scanned..............................: 97
- Extent Switches..............................: 96
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.97% [96:97]
- Logical Scan Fragmentation ..................: 99.74%
- Extent Scan Fragmentation ...................: 2.06%
- Avg. Bytes Free per Page.....................: 622.5
- Avg. Page Density (full).....................: 92.31%
DBCC SHOWCONTIG scanning 'TRK_PBT_C_Detail' table...
Table: 'TRK_PBT_C_Detail' (808806389); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 15
- Extents Scanned..............................: 4
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 3.8
- Scan Density [Best Count:Actual Count].......: 40.00% [2:5]
- Logical Scan Fragmentation ..................: 40.00%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 696.8
- Avg. Page Density (full).....................: 91.39%
DBCC SHOWCONTIG scanning 'TRK_ASI_L_Changes' table...
Table: 'TRK_ASI_L_Changes' (827566432); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 219
- Extents Scanned..............................: 33
- Extent Switches..............................: 33
- Avg. Pages per Extent........................: 6.6
- Scan Density [Best Count:Actual Count].......: 82.35% [28:34]
- Logical Scan Fragmentation ..................: 82.19%
- Extent Scan Fragmentation ...................: 45.45%
- Avg. Bytes Free per Page.....................: 628.1
- Avg. Page Density (full).....................: 92.24%
DBCC SHOWCONTIG scanning 'TRK_PEG_L_Operation' table...
Table: 'TRK_PEG_L_Operation' (849138516); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 3
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 6057.7
- Avg. Page Density (full).....................: 25.16%
DBCC SHOWCONTIG scanning 'TRK_PEG_L_OwnerInfo' table...
Table: 'TRK_PEG_L_OwnerInfo' (881138630); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 21
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 5.3
- Scan Density [Best Count:Actual Count].......: 75.00% [3:4]
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 3196.4
- Avg. Page Density (full).....................: 60.51%
DBCC SHOWCONTIG scanning 'TRK_ASI_L_Deletes' table...
Table: 'TRK_ASI_L_Deletes' (907566717); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 35
- Extents Scanned..............................: 8
- Extent Switches..............................: 7
- Avg. Pages per Extent........................: 4.4
- Scan Density [Best Count:Actual Count].......: 62.50% [5:8]
- Logical Scan Fragmentation ..................: 77.14%
- Extent Scan Fragmentation ...................: 12.50%
- Avg. Bytes Free per Page.....................: 651.1
- Avg. Page Density (full).....................: 91.96%
DBCC SHOWCONTIG scanning 'TRK_PRIAC_C_Comments' table...
Table: 'TRK_PRIAC_C_Comments' (918710771); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 1.2
- Scan Density [Best Count:Actual Count].......: 20.00% [1:5]
- Extent Scan Fragmentation ...................: 80.00%
- Avg. Bytes Free per Page.....................: 2919.7
- Avg. Page Density (full).....................: 63.93%
DBCC SHOWCONTIG scanning 'TRK_PLAZ_L_Operation' table...
Table: 'TRK_PLAZ_L_Operation' (953470871); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 14
- Extent Switches..............................: 13
- Avg. Pages per Extent........................: 2.2
- Scan Density [Best Count:Actual Count].......: 28.57% [4:14]
- Extent Scan Fragmentation ...................: 92.86%
- Avg. Bytes Free per Page.....................: 1412.5
- Avg. Page Density (full).....................: 82.55%
DBCC SHOWCONTIG scanning 'TRK_PLAZ_L_OwnerInfo' table...
Table: 'TRK_PLAZ_L_OwnerInfo' (985470985); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 2401
- Extents Scanned..............................: 305
- Extent Switches..............................: 304
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.69% [301:305]
- Extent Scan Fragmentation ...................: 63.93%
- Avg. Bytes Free per Page.....................: 3321.9
- Avg. Page Density (full).....................: 58.96%
DBCC SHOWCONTIG scanning 'TRK_PLNJ_C_Detail' table...
Table: 'TRK_PLNJ_C_Detail' (1017471099); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 676
- Extents Scanned..............................: 87
- Extent Switches..............................: 87
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 96.59% [85:88]
- Logical Scan Fragmentation ..................: 99.85%
- Extent Scan Fragmentation ...................: 2.30%
- Avg. Bytes Free per Page.....................: 500.7
- Avg. Page Density (full).....................: 93.81%
DBCC SHOWCONTIG scanning 'TRK_ASI_C_Comments' table...
Table: 'TRK_ASI_C_Comments' (1023043126); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 3518.5
- Avg. Page Density (full).....................: 56.53%
DBCC SHOWCONTIG scanning 'TRK_PRIAC_H_Detail' table...
Table: 'TRK_PRIAC_H_Detail' (1158711626); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 786
- Extents Scanned..............................: 101
- Extent Switches..............................: 101
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 97.06% [99:102]
- Logical Scan Fragmentation ..................: 99.75%
- Extent Scan Fragmentation ...................: 3.96%
- Avg. Bytes Free per Page.....................: 189.8
- Avg. Page Density (full).....................: 97.66%
DBCC SHOWCONTIG scanning 'TRK_PRUINS_H_Comments' table...
Table: 'TRK_PRUINS_H_Comments' (1164283653); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 12
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 1.7
- Scan Density [Best Count:Actual Count].......: 28.57% [2:7]
- Extent Scan Fragmentation ...................: 85.71%
- Avg. Bytes Free per Page.....................: 1928.3
- Avg. Page Density (full).....................: 76.18%
DBCC SHOWCONTIG scanning 'TRK_ASI_H_Detail' table...
Table: 'TRK_ASI_H_Detail' (1263043981); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 37
- Extents Scanned..............................: 8
- Extent Switches..............................: 7
- Avg. Pages per Extent........................: 4.6
- Scan Density [Best Count:Actual Count].......: 62.50% [5:8]
- Logical Scan Fragmentation ..................: 78.38%
- Extent Scan Fragmentation ...................: 12.50%
- Avg. Bytes Free per Page.....................: 514.6
- Avg. Page Density (full).....................: 93.64%
DBCC SHOWCONTIG scanning 'TRK_PBC_L_Changes' table...
Table: 'TRK_PBC_L_Changes' (1299952153); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 4
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.3
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 25.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 1508.0
- Avg. Page Density (full).....................: 81.37%
DBCC SHOWCONTIG scanning 'TRK_PRI_L_Operation' table...
Table: 'TRK_PRI_L_Operation' (1321524237); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 6242.2
- Avg. Page Density (full).....................: 22.88%
DBCC SHOWCONTIG scanning 'TRK_PRI_L_OwnerInfo' table...
Table: 'TRK_PRI_L_OwnerInfo' (1353524351); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 3584.8
- Avg. Page Density (full).....................: 55.71%
DBCC SHOWCONTIG scanning 'TRK_PLAZ_H_Comments' table...
Table: 'TRK_PLAZ_H_Comments' (1372948363); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7901.0
- Avg. Page Density (full).....................: 2.38%
DBCC SHOWCONTIG scanning 'TRK_PBC_L_Deletes' table...
Table: 'TRK_PBC_L_Deletes' (1379952438); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6355.0
- Avg. Page Density (full).....................: 21.49%
DBCC SHOWCONTIG scanning 'TRK_PRUINS_L_Changes' table...
Table: 'TRK_PRUINS_L_Changes' (1404284508); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 97189
- Extents Scanned..............................: 12193
- Extent Switches..............................: 12260
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.09% [12149:12261]
- Logical Scan Fragmentation ..................: 2.92%
- Extent Scan Fragmentation ...................: 69.64%
- Avg. Bytes Free per Page.....................: 690.9
- Avg. Page Density (full).....................: 91.46%
DBCC SHOWCONTIG scanning 'TRK_PBT_L_Operation' table...
Table: 'TRK_PBT_L_Operation' (1425856592); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 1.2
- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 2542.4
- Avg. Page Density (full).....................: 68.59%
DBCC SHOWCONTIG scanning 'TRK_PBT_L_OwnerInfo' table...
Table: 'TRK_PBT_L_OwnerInfo' (1457856706); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1630
- Extents Scanned..............................: 206
- Extent Switches..............................: 205
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.03% [204:206]
- Extent Scan Fragmentation ...................: 46.60%
- Avg. Bytes Free per Page.....................: 3678.6
- Avg. Page Density (full).....................: 54.55%
DBCC SHOWCONTIG scanning 'TRK_PRUINS_L_Deletes' table...
Table: 'TRK_PRUINS_L_Deletes' (1484284793); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 621
- Extents Scanned..............................: 78
- Extent Switches..............................: 77
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [78:78]
- Logical Scan Fragmentation ..................: 0.16%
- Extent Scan Fragmentation ...................: 42.31%
- Avg. Bytes Free per Page.....................: 564.9
- Avg. Page Density (full).....................: 93.02%
DBCC SHOWCONTIG scanning 'TRK_PBC_C_Comments' table...
Table: 'TRK_PBC_C_Comments' (1495428847); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7873.0
- Avg. Page Density (full).....................: 2.73%
DBCC SHOWCONTIG scanning 'TRK_PEG_L_Changes' table...
Table: 'TRK_PEG_L_Changes' (1508616863); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 12
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 1.2
- Scan Density [Best Count:Actual Count].......: 20.00% [2:10]
- Logical Scan Fragmentation ..................: 75.00%
- Extent Scan Fragmentation ...................: 80.00%
- Avg. Bytes Free per Page.....................: 773.2
- Avg. Page Density (full).....................: 90.45%
DBCC SHOWCONTIG scanning 'NYADPRUINS' table...
Table: 'NYADPRUINS' (1577525149); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 79
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 90.91% [10:11]
- Extent Scan Fragmentation ...................: 36.36%
- Avg. Bytes Free per Page.....................: 339.3
- Avg. Page Density (full).....................: 95.81%
DBCC SHOWCONTIG scanning 'TRK_PEG_L_Deletes' table...
Table: 'TRK_PEG_L_Deletes' (1588617148); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 11
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 50.00% [2:4]
- Logical Scan Fragmentation ..................: 81.82%
- Extent Scan Fragmentation ...................: 25.00%
- Avg. Bytes Free per Page.....................: 584.6
- Avg. Page Density (full).....................: 92.78%
DBCC SHOWCONTIG scanning 'TRK_PRIAC_C_Detail' table...
Table: 'TRK_PRIAC_C_Detail' (1594189175); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 4106
- Extents Scanned..............................: 517
- Extent Switches..............................: 517
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.23% [514:518]
- Logical Scan Fragmentation ..................: 99.98%
- Extent Scan Fragmentation ...................: 0.39%
- Avg. Bytes Free per Page.....................: 366.7
- Avg. Page Density (full).....................: 95.47%
DBCC SHOWCONTIG scanning 'TRK_PRUINS_C_Comments' table...
Table: 'TRK_PRUINS_C_Comments' (1599761202); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 381
- Extents Scanned..............................: 54
- Extent Switches..............................: 53
- Avg. Pages per Extent........................: 7.1
- Scan Density [Best Count:Actual Count].......: 88.89% [48:54]
- Extent Scan Fragmentation ...................: 98.15%
- Avg. Bytes Free per Page.....................: 221.1
- Avg. Page Density (full).....................: 97.27%
DBCC SHOWCONTIG scanning 'TRK_PLAZ_L_Changes' table...
Table: 'TRK_PLAZ_L_Changes' (1612949218); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1135
- Extents Scanned..............................: 144
- Extent Switches..............................: 146
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 96.60% [142:147]
- Logical Scan Fragmentation ..................: 94.19%
- Extent Scan Fragmentation ...................: 20.14%
- Avg. Bytes Free per Page.....................: 735.1
- Avg. Page Density (full).....................: 90.92%
DBCC SHOWCONTIG scanning 'TRK_PLNJ_L_Operation' table...
Table: 'TRK_PLNJ_L_Operation' (1634521302); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 15
- Extents Scanned..............................: 12
- Extent Switches..............................: 11
- Avg. Pages per Extent........................: 1.3
- Scan Density [Best Count:Actual Count].......: 16.67% [2:12]
- Extent Scan Fragmentation ...................: 91.67%
- Avg. Bytes Free per Page.....................: 935.6
- Avg. Page Density (full).....................: 88.44%
DBCC SHOWCONTIG scanning 'TRK_PLNJ_L_OwnerInfo' table...
Table: 'TRK_PLNJ_L_OwnerInfo' (1666521416); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 794
- Extents Scanned..............................: 101
- Extent Switches..............................: 100
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.01% [100:101]
- Extent Scan Fragmentation ...................: 63.37%
- Avg. Bytes Free per Page.....................: 3330.1
- Avg. Page Density (full).....................: 58.86%
DBCC SHOWCONTIG scanning 'TRK_PLAZ_L_Deletes' table...
Table: 'TRK_PLAZ_L_Deletes' (1692949503); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1328
- Extents Scanned..............................: 168
- Extent Switches..............................: 168
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.22% [166:169]
- Logical Scan Fragmentation ..................: 99.62%
- Extent Scan Fragmentation ...................: 1.19%
- Avg. Bytes Free per Page.....................: 541.0
- Avg. Page Density (full).....................: 93.32%
DBCC SHOWCONTIG scanning 'TRK_ASI_C_Detail' table...
Table: 'TRK_ASI_C_Detail' (1698521530); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 463
- Extents Scanned..............................: 61
- Extent Switches..............................: 60
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 95.08% [58:61]
- Logical Scan Fragmentation ..................: 0.22%
- Extent Scan Fragmentation ...................: 4.92%
- Avg. Bytes Free per Page.....................: 421.2
- Avg. Page Density (full).....................: 94.80%
DBCC SHOWCONTIG scanning 'TRK_PEG_C_Comments' table...
Table: 'TRK_PEG_C_Comments' (1704093557); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8033.0
- Avg. Page Density (full).....................: 0.75%
DBCC SHOWCONTIG scanning 'TRK_PLAZ_C_Comments' table...
Table: 'TRK_PLAZ_C_Comments' (1808425912); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 16
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 1.6
- Scan Density [Best Count:Actual Count].......: 20.00% [2:10]
- Extent Scan Fragmentation ...................: 90.00%
- Avg. Bytes Free per Page.....................: 1738.3
- Avg. Page Density (full).....................: 78.52%
DBCC SHOWCONTIG scanning 'TRK_PRUINS_H_Detail' table...
Table: 'TRK_PRUINS_H_Detail' (1839762057); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 613130
- Extents Scanned..............................: 76986
- Extent Switches..............................: 85665
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 89.47% [76642:85666]
- Logical Scan Fragmentation ..................: 52.35%
- Extent Scan Fragmentation ...................: 13.80%
- Avg. Bytes Free per Page.....................: 2583.8
- Avg. Page Density (full).....................: 68.08%
DBCC SHOWCONTIG scanning 'TRK_PEG_H_Detail' table...
Table: 'TRK_PEG_H_Detail' (1944094412); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 13
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 3.3
- Scan Density [Best Count:Actual Count].......: 50.00% [2:4]
- Logical Scan Fragmentation ..................: 84.62%
- Extent Scan Fragmentation ...................: 25.00%
- Avg. Bytes Free per Page.....................: 1005.8
- Avg. Page Density (full).....................: 87.57%
DBCC SHOWCONTIG scanning 'TRK_PRI_L_Changes' table...
Table: 'TRK_PRI_L_Changes' (1981002584); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 11
- Extents Scanned..............................: 6
- Extent Switches..............................: 7
- Avg. Pages per Extent........................: 1.8
- Scan Density [Best Count:Actual Count].......: 25.00% [2:8]
- Logical Scan Fragmentation ..................: 63.64%
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 1186.7
- Avg. Page Density (full).....................: 85.34%
DBCC SHOWCONTIG scanning 'TRK_PLAZ_H_Detail' table...
Table: 'TRK_PLAZ_H_Detail' (2048426767); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1437
- Extents Scanned..............................: 182
- Extent Switches..............................: 182
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.36% [180:183]
- Logical Scan Fragmentation ..................: 99.79%
- Extent Scan Fragmentation ...................: 1.10%
- Avg. Bytes Free per Page.....................: 450.0
- Avg. Page Density (full).....................: 94.44%
DBCC SHOWCONTIG scanning 'TRK_PRI_L_Deletes' table...
Table: 'TRK_PRI_L_Deletes' (2061002869); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7341.0
- Avg. Page Density (full).....................: 9.30%
DBCC SHOWCONTIG scanning 'TRK_PBT_L_Changes' table...
Table: 'TRK_PBT_L_Changes' (2085334939); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 614
- Extents Scanned..............................: 80
- Extent Switches..............................: 81
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 93.90% [77:82]
- Logical Scan Fragmentation ..................: 99.35%
- Extent Scan Fragmentation ...................: 56.25%
- Avg. Bytes Free per Page.....................: 780.8
- Avg. Page Density (full).....................: 90.35%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-03-29 : 04:34:05

Don't mean to be picky but someone ought to be casting their magic wand over this:


[StatusIndicator] [nvarchar](1) NULL DEFAULT (''),

Nvarchar? (1) variable length byte? Default to empty string not NULL?

I'm not mad about dates that are Nvarchar(8) and default to '00000000' either

Some of these indexes (i.e. of the ones that have large number of pages only) don't look very "rebuilt" to me, is that possible?


Table: 'TRK_PRIAC_L_OwnerInfo' (95755844); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 3072
- Extents Scanned..............................: 386
- Extent Switches..............................: 385
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.48% [384:386]
- Extent Scan Fragmentation ...................: 35.49%
- Avg. Bytes Free per Page.....................: 3449.9
- Avg. Page Density (full).....................: 57.38%

Table: 'TRK_PRUINS_C_Detail' (127755958); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 47455
- Extents Scanned..............................: 5959
- Extent Switches..............................: 6787
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 87.39% [5932:6788]
- Logical Scan Fragmentation ..................: 99.99%
- Extent Scan Fragmentation ...................: 9.33%
- Avg. Bytes Free per Page.....................: 440.3
- Avg. Page Density (full).....................: 94.56%

Table: 'TRK_PRUINS_H_Detail' (1839762057); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 613130
- Extents Scanned..............................: 76986
- Extent Switches..............................: 85665
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 89.47% [76642:85666]
- Logical Scan Fragmentation ..................: 52.35%
- Extent Scan Fragmentation ...................: 13.80%
- Avg. Bytes Free per Page.....................: 2583.8
- Avg. Page Density (full).....................: 68.08%

and there are several, smaller but still significant number of pages, indexes that have 4K free per page ...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-29 : 12:29:04

Gotta love the nvarchar with default ''

Brett, did you manually rebuild all of the indexes?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources