Post reply

Cluster Index.. Insert update operations Vs Performance in MS SQL 2005

  • May 29, 2009 at 8:38 am

    #155758

    Hi Experts, Its little urgent.

    I am facing some issues in database performance in MS SQL 2005

    I have 1 TB database and having perf problem.

    application is using only 1 table and performing Insert update operation on this table and complaining performance problem

    I have cluster index setup on this table and records are 160 K . just thinking how cluster index perform insert update operations for new records.. does its take long time to do this operations.

    Please let me know your suggestions

    Thanks

    ichbinraj

  • May 29, 2009 at 8:46 am

    #1002089

    It depends.

    What is the clustered index key? How wide is it?

    Where are your inserts in the clustered key, beginning, middle, end?

    Are your updates, updating the clustered key?

    What is the fill factor of the index?

    How many rows are you inserting/updating in a batch?

    Performance will be worse on a wide clustered index with inserts anywhere but the end or updates to the clustered index key as the data will have to be re-arranged on disk and page splits will occur.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 29, 2009 at 8:49 am

    #1002090

    give us the query and the columns on which the indexes have been defined ...

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • May 29, 2009 at 9:11 am

    #1002112

    hi my information about the Table

    What is the clustered index key? its created on 5 columns

    Where are your inserts in the clustered key, beginning, middle, end? its dynamic process so they can pick up from any where

    Are your updates, updating the clustered key? yes

    What is the fill factor of the index? 80

    Thanks

    Ichbinraj

  • May 29, 2009 at 9:23 am

    #1002126

    Sounds like you may not have the best clustered index key. I'd recommend reading this blog post by Kimberly Tripp as it does a good job defining what makes a good clustered index.

    Based on the answers you provided, yup, the clustered index is going to slow down the inserts and updates you are doing. I would guess that you are getting a lot of page splits which will slow down everything as well.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 29, 2009 at 9:42 am

    #1002139

    one quick question

    IF my application updating existing record that mean

    some 123 record does it going to slowdown the performance ?

    If I am trying to insert a new records number 11124 what is the process ? if have 150 K record numbers ?

    Thanks

    Ichbinraj

  • May 29, 2009 at 10:22 am

    #1002160

    Not sure I understand what you're asking.

    When a row is updated, the changes have to be made to all of the indexes on the table, likewise when rows are inserted.

    If that's not what you're asking, can you please explain your question a bit clearer?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 29, 2009 at 10:26 am

    #1002165

    If you're having performance problems with a particular query, please post table definition, all index definitions, the query and the execution plan, saved as a .sqlplan file, zipped and attached to your post.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 29, 2009 at 11:22 am

    #1002190

    Hi ,

    Here its my query : SELECT * FROM pep.ZSDI38_DATAITEMS WHERE SID = '80168005' and ITEM_CUSTOM1 in (1000180023,1000180024);

    and Index information

    ZSDI38_DATAITEMS~0clustered, unique, primary key, stats no recompute located on PRIMARYMANDT, SID, ITEM_CUSTOM1, ITEM_CUSTOM2, ITEMNO

    E Plan : I can't find attachment link in this form.

    Thanks

    ichbinraj

  • May 29, 2009 at 11:57 am

    #1002197

    And the table structure?

    The attachment button is right beneath the text box where you type your post. In the section titled "Post Options"

    I can tell you right now that not only is your cluster a poor choice, but it's useless for that query. The leading column of the cluster is Mandt. The query doesn't filter by that at all. Hence the only way that query can run, assuming there are no nonclustered indexes on the table, is by scanning the cluster, ie a full table scan.

    You need to do two things to fix it.

    1) Remove the SELECT * and explicitly reference just the columns that you need back from that query.

    2) Add a nonclustered index on SID, Item_Custom1. Providing the number of rows returned is small, that should be enough. If SQL doesn't use the index, you'll need to make it covering by adding the columns in the select clause as INCLUDE columns, though not a good idea if lots and lots of columns are returned.

    One other thing. Why is auto_update stats disabled on that clustered index? Unless you manually update stats, that could be causing you a whol lot of problems.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 29, 2009 at 12:21 pm

    #1002207

    Hi ,

    Application fetching all the fields and updating other tables in application.

    So I have to select all fields on .

    As per your suggestion . I have to create non cluster index on SID and ITEM_custom1 ... right ?

    I am attaching execution plan .. remove .txt in the file extension

    Thanks

    ichbinraj

  • May 29, 2009 at 12:31 pm

    #1002210

    ichbinraj (5/29/2009)

    As per your suggestion . I have to create non cluster index on SID and ITEM_custom1 ... right ?

    Yup. It should help.

    About the auto_update state?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 29, 2009 at 12:55 pm

    #1002219

    Hi did you check the execution plan ?

    about stats update I am doing it manually.

    if i change any thing on cluster index .. means removing cluster index from sid and item _custom1 .. and creating non cluster index on these will help the performance ? or just creating non cluster index on cluster index is fine ?

    Can you please modify this following script including creating non cluster index

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [pep].[ZSDI38_DATAITEMS](

    [MANDT] [nvarchar](3) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

    [SID] [nvarchar](20) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

    [ITEM_CUSTOM1] [nvarchar](50) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

    [ITEM_CUSTOM2] [nvarchar](50) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

    [ITEMNO] [nvarchar](20) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

    [ITEMDESC] [nvarchar](80) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

    CONSTRAINT [ZSDI38_DATAITEMS~0] PRIMARY KEY CLUSTERED

    (

    [MANDT] ASC,

    [SID] ASC,

    [ITEM_CUSTOM1] ASC,

    [ITEM_CUSTOM2] ASC,

    [ITEMNO] ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    Thanks

    ichbinraj

  • May 29, 2009 at 1:03 pm

    #1002224

    ichbinraj (5/29/2009)

    Hi did you check the execution plan ?

    Yes, I did

    if i change any thing on cluster index .. means removing cluster index from sid and item _custom1 .. and creating non cluster index on these will help the performance ? or just creating non cluster index on cluster index is fine ?

    I said add a nonclustered index. I didn't say change the clustered index.

    Yes, the cluster is a bad one. Without a lot more info on the queries and the data I can't suggest you a better cluster. Take a look at the blog post that Jack posted and see if you can find a better candidate for the clustered index. It doesn't have to be the primary key.

    For now, just add a nonclustered index.

    Can you please modify this following script including creating non cluster index

    Why don't you try it yourself? I suggest starting with the Books Online entry for CREATE INDEX

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 29, 2009 at 1:04 pm

    #1002225

    ichbinraj (5/29/2009)

    application is using only 1 table and performing Insert update operation on this table and complaining performance problem

    ichbinraj (5/29/2009)

    Hi ,

    Application fetching all the fields and updating other tables in application.

    So I have to select all fields on .

    Thanks

    ichbinraj

    Am I missing something or is it lost in the translation????

    -- You can't be late until you show up.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply