SQL Server Performance

Index Fragmentation issue. Fragmentation remains as it is even after Re-building the indexes.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by bsethi24, Dec 29, 2010.

  1. bsethi24 New Member

    I have a table by name tbl_trPurchaseClear. Structure of this table is given below.
    It stores all the records related with Purchase deals & have 304885 rows. Records in this table grow by approx. 100 Rows per day.
    Due to performance issue, I Re-build all the indexes, Re-Organize All the indexes and Update the statistics of this table.
    After doing all these 3 things when I clicked on Tables - tbl_trPurchaseClear - Indexes - PK_tbl_trPurchaseClear – Properties then MS-SQL shows me below details.
    Index_Name PK_tbl_trPurchaseClear
    Index_ID 1
    Depth 3
    Pages 8999
    Rows 303948
    MinimumRecordSize 218
    MaximumRecordSize 535
    AverageRecordSize 234.243
    ForwardedRecords NULL
    AveragePageDensity 98.55804299
    IndexType CLUSTERED INDEX
    PartitionNumber 1
    GhostRows 0
    VersionGhostRows 0
    AverageFragmentation 76.18624292

    I have one question why MS-SQL shows AVERAGEFRAGMENTATION - 76.18624292?
    How can I reduce Fragmentation to improve Performance?
    I am worry about this because I read on Microsoft website that when you have Fragmentation more than 30-40%, Re-build the Index. I did the same but, of no result.
    Kindly guide me on the same as soon as possible.
    --<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--
    -- Table Structure --
    USE [PurchaseDB]
    GO
    /****** Object: Table [dbo].[tbl_trPurchaseClear] Script Date: 12/30/2010 13:43:25 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tbl_trPurchaseClear](
    [pkey] [int] NOT NULL,
    [Purchase_no] [int] NOT NULL,
    [Purchase_dt] [datetime] NOT NULL,
    [PurchaseVendor_Pkey] [int] NULL,
    [PurchaseBorker_Pkey] [int] NULL,
    [amt] [decimal](18, 2) NOT NULL,
    [servicetax] [decimal](18, 0) NULL CONSTRAINT [DF__tbl_smstr__servi__76D9C1AC] DEFAULT ((0)),
    [pyt_mode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Pay_pyt_mode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Remarks] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [taken_by] [int] NULL,
    [status_flag] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [trPurchase_pkey] [int] NULL,
    [InsertedBy] [uniqueidentifier] NULL,
    [InsertedDate] [datetime] NULL,
    [UpdatedBy] [uniqueidentifier] NULL,
    [UpdatedDate] [datetime] NULL,
    [Purchase_Unit] [int] NOT NULL,
    [ChequeDDNo] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Bank] [int] NULL,
    [ChequeDDDate] [datetime] NULL,
    [CreditCardType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CreditCardNo] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CreditCardValidTill] [datetime] NULL,
    [ApprovalCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [IsPurchase] [bit] NULL CONSTRAINT [DF__tbl_smstr__IsRec__77CDE5E5] DEFAULT ((0)),
    [RemarksCancel] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CardMachine_Pkey] [int] NULL,
    [Purchase_type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CardMachineServiceTax] [decimal](18, 0) NULL CONSTRAINT [DF_tbl_trPurchaseClear_CardMachineServiceTax] DEFAULT ((0)),
    [CancelBy_Pkey] [int] NULL,
    [BatchId] [int] NULL,
    [CancelDate] [datetime] NULL,
    [Contract_Pkey] [int] NULL,
    [FinFromYear] [datetime] NULL,
    [FinToYear] [datetime] NULL,
    [OpenPType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [PK_tbl_trPurchaseClear] PRIMARY KEY CLUSTERED
    (
    [Purchase_Unit] ASC,
    [pkey] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
    CONSTRAINT [UN_tbl_trPurchaseClear_BusinessUnit_Pkey_Purchase_no] UNIQUE NONCLUSTERED
    (
    [Purchase_Unit] ASC,
    [Purchase_no] ASC,
    [Purchase_type] ASC,
    [FinFromYear] ASC,
    [FinToYear] ASC,
    [OpenPType] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    -- Table Structure --
    --<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--
    Thanks & Regards,
    B.S.
    bsethi24, Dec 29, 2010
    #2

  2. Luis Martin Moderator

    You can try drop and create cluster index.
    Just a thought.
    Luis Martin, Dec 30, 2010
    #3

  3. satya Moderator

    What is the service pack level on SQL Server?
    Run the following TSQL and produce results here:
    SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    indexstats.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
    INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
    AND i.index_id = indexstats.index_id
    WHERE indexstats.avg_fragmentation_in_percent > 20How big is the index?
    use sys.dm_db_index_physical_stats DMV to get such information,
    Run the following TSQL and produce results here:
    satya, Jan 4, 2011
    #4

  4. RamJaddu Member

    Is it fragemtation checks done right after rebuild index? If that is the case then you might need to look into physical hdd disk framentations.
    Other wise there might be large amounts of updates / deletes happening on table (ie primary key column).
    Do you have any overnight archiving SQL jobs scheduled?
    RamJaddu, Jan 17, 2011
    #5

Share This Page