Results 1 to 5 of 5

Thread: Fragmentation

  1. #1
    Join Date
    Jul 2002
    Location
    Lima - Perú
    Posts
    30

    Red face Unanswered: Fragmentation

    Hi :
    In SQL 7.0 SP 2

    I have this table :

    CREATE TABLE [dbo].[DFFCENCO] (
    [FCYPkCCCF] [int] NOT NULL ,
    [FCYCCCN1F] [char] (2) NULL ,
    [FCYCCCN2F] [char] (2) NULL ,
    [FCYCCCN3F] [char] (2) NULL ,
    [FCYCCCN4F] [char] (2) NULL ,
    [FCYCCCN5F] [char] (2) NULL ,
    [FCYDscCCF] [varchar] (80) NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[DFFCENCO] WITH NOCHECK ADD
    CONSTRAINT [PK_DFFCENCO] PRIMARY KEY CLUSTERED
    (
    [FCYPkCCCF]
    ) WITH FILLFACTOR = 100 ON [PRIMARY]
    GO

    CREATE INDEX [DFFCENC1] ON [dbo].[DFFCENCO]([FCYCCCN1F], [FCYCCCN2F], [FCYCCCN3F], [FCYCCCN4F], [FCYCCCN5F]) ON [PRIMARY]

    And the DBCC SHOWCONTIG give me that :

    DBCC SHOWCONTIG scanning 'DFFCENCO' table...
    Table: 'DFFCENCO' (1632724869); index ID: 1, database ID: 37
    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]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 50.00%
    - Avg. Bytes Free per Page.....................: 176.0
    - Avg. Page Density (full).....................: 97.83%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    I'm traying defragment this table but i cant do it until now, please, Why ? What is the solution ?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Fragmentation

    What do you mean you can't defrag the table? What command are you using?

    Also, this table appears so small (only 2 extents) it's possible that the scan density is the best that it's going to get.

    Regards,

    Hugh Scott

    Originally posted by lvigo
    Hi :
    In SQL 7.0 SP 2

    I have this table :

    CREATE TABLE [dbo].[DFFCENCO] (
    [FCYPkCCCF] [int] NOT NULL ,
    [FCYCCCN1F] [char] (2) NULL ,
    [FCYCCCN2F] [char] (2) NULL ,
    [FCYCCCN3F] [char] (2) NULL ,
    [FCYCCCN4F] [char] (2) NULL ,
    [FCYCCCN5F] [char] (2) NULL ,
    [FCYDscCCF] [varchar] (80) NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[DFFCENCO] WITH NOCHECK ADD
    CONSTRAINT [PK_DFFCENCO] PRIMARY KEY CLUSTERED
    (
    [FCYPkCCCF]
    ) WITH FILLFACTOR = 100 ON [PRIMARY]
    GO

    CREATE INDEX [DFFCENC1] ON [dbo].[DFFCENCO]([FCYCCCN1F], [FCYCCCN2F], [FCYCCCN3F], [FCYCCCN4F], [FCYCCCN5F]) ON [PRIMARY]

    And the DBCC SHOWCONTIG give me that :

    DBCC SHOWCONTIG scanning 'DFFCENCO' table...
    Table: 'DFFCENCO' (1632724869); index ID: 1, database ID: 37
    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]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 50.00%
    - Avg. Bytes Free per Page.....................: 176.0
    - Avg. Page Density (full).....................: 97.83%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    I'm traying defragment this table but i cant do it until now, please, Why ? What is the solution ?

  3. #3
    Join Date
    Jul 2002
    Location
    Lima - Perú
    Posts
    30

    Question Re: Fragmentation

    Hi Hugh :

    I'm using DBCC DBREINDEX and DROP a CREATE INDEX or ALTER TABLE DROP and ADD CONSTRAINT. But the results is like i show previously. Maybe that is because of the small size of the table ??? is normal that the percentage of scan density in this case be 50% or less ??

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Fragmentation

    Given the size of the table, I think that you will not be able to do any better than 50%. In fact, I have heard said (but I have not independently verified) that on tables that are very small, there is no point to putting an index as it will simply slow down operations. This line of reasoning states that if you put an index on a very small table, then SQL must first read through the index to find the pointer when it could be faster to go right to the data. This only works for small tables. What's small? I dunno.

    Again, I have not independently verified this information.

    I think in your case, until your table starts growing, you will not get better than 50%.

    Regards,

    Hugh Scott

    Originally posted by lvigo
    Hi Hugh :

    I'm using DBCC DBREINDEX and DROP a CREATE INDEX or ALTER TABLE DROP and ADD CONSTRAINT. But the results is like i show previously. Maybe that is because of the small size of the table ??? is normal that the percentage of scan density in this case be 50% or less ??

  5. #5
    Join Date
    Jul 2002
    Location
    Lima - Perú
    Posts
    30

    Re: Fragmentation

    Thank's Hugh, please help me with another question :

    I have an account "XX" that exists in two servers with the same configuration, in both is a simple user. this account have assigned permissions on all the objects of the MSDB so that it can create jobs. The problem is in which in one of the servers when the user executes job of his property, it does not visualize the execution status, whereas in the other if, How Can I solve this so that in both it can see the execution status ???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •