Strange Clustered Index Update

  • You know, what's really wierd is that there is NO physical update operator in the ShowPlan, only the logical update operator.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I was also suspecting a CASCADE because of the indexes somewhere..

    What kills me is that everytime I recreate this situation "by hand", I don't get this behaviour. Only if I use the DDL from the production DB.. and that one is several 1000's lines. Anyway, here is the part for the table:

    CREATE TABLE [dbo].[ats_CampaignClients](

    [CCID] [bigint] IDENTITY(1,1) NOT NULL,

    [CampaignID] [int] NOT NULL,

    [ClientID] [bigint] NOT NULL,

    [Status] [int] NOT NULL CONSTRAINT [DF_ats_CampaignClients_Status] DEFAULT ((0)),

    [RandomOrder] [bigint] NOT NULL CONSTRAINT [DF_ats_CampaignClients_RandomOrder] DEFAULT ((100000)*rand()),

    [LocationID] [int] NULL,

    [Attempts] [int] NOT NULL CONSTRAINT [DF_ats_CampaignClients_Attempts] DEFAULT ((0)),

    [LastCallStatus] [int] NULL,

    [CreatedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CreatedOn] [datetime] NULL,

    [ModifiedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ModifiedOn] [datetime] NULL,

    CONSTRAINT [PK_ats_CampaignClients] PRIMARY KEY CLUSTERED

    (

    [CCID] ASC

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

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] WITH CHECK ADD CONSTRAINT [FK_ats_CampaignClients_ats_CallStatusReasonList] FOREIGN KEY([LastCallStatus])

    REFERENCES [dbo].[ats_CallStatusReasonList] ([ID])

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] CHECK CONSTRAINT [FK_ats_CampaignClients_ats_CallStatusReasonList]

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] WITH CHECK ADD CONSTRAINT [FK_ats_CampaignClients_ats_Campaign] FOREIGN KEY([CampaignID])

    REFERENCES [dbo].[ats_Campaign] ([ID])

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] CHECK CONSTRAINT [FK_ats_CampaignClients_ats_Campaign]

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] WITH CHECK ADD CONSTRAINT [FK_ats_CampaignClients_ats_CampaignLocation] FOREIGN KEY([LocationID])

    REFERENCES [dbo].[ats_CampaignLocation] ([LocationID])

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] CHECK CONSTRAINT [FK_ats_CampaignClients_ats_CampaignLocation]

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] WITH CHECK ADD CONSTRAINT [FK_ats_CampaignClients_ats_Client] FOREIGN KEY([ClientID])

    REFERENCES [dbo].[ats_Client] ([ID])

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] CHECK CONSTRAINT [FK_ats_CampaignClients_ats_Client]

    GO

    CREATE NONCLUSTERED INDEX [IX_GetByClient] ON [dbo].[ats_CampaignClients]

    (

    [CampaignID] ASC,

    [ClientID] ASC

    )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    GO

    CREATE CLUSTERED INDEX [IX_GetByRandom] ON [dbo].[ats_CampaignClients]

    (

    [RandomOrder] DESC,

    [Status] ASC,

    [CampaignID] ASC

    )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[ats_CampaignClients] ADD CONSTRAINT [PK_ats_CampaignClients] PRIMARY KEY NONCLUSTERED

    (

    [CCID] ASC

    )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

  • Does this script really run? It defines the primary key twice. Once as a clustered index and once as a non-clustered index. If you're able to run this, you might want to check the consistency of your database, which service pack you're on... You should get an error from this script.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Ouch, good spotting!

    I've this was a compilation from the creation script of the DB. It seems the table is initially created w/ a clustered PK , then that index is dropped, then somewhere lower these 3 indexes are created, and this time the PK index is not clustered. Ok, it's a bit messy. You know ... production level code 🙂

  • hmm interesting situation why the column should be the Primary Key column but on the other hand it is not clustered till it is primary key! ok ok everyday we saw new experience ...interesting!

    What is the purpose to create the Primary Key on that column and not Clustered Index, all of us knows that in the Primary Key column we store the unique values so the index on these columns is welcome for fast search!?

    :w00t:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • radu.poenaru (6/5/2008)


    Ouch, good spotting!

    I've this was a compilation from the creation script of the DB. It seems the table is initially created w/ a clustered PK , then that index is dropped, then somewhere lower these 3 indexes are created, and this time the PK index is not clustered. Ok, it's a bit messy. You know ... production level code 🙂

    So then the question still comes back, are there dependent objects elsewhere in the structure that are causing this. You can use Red Gate's dependency checking tool on a free trial. Try it on this system. Something is referencing this table and you need to identify it in order to understand why you're seeing the plan you are.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • oki, willdo that

  • Dugi (6/5/2008)


    hmm interesting situation why the column should be the Primary Key column but on the other hand it is not clustered till it is primary key! ok ok everyday we saw new experience ...interesting!

    What is the purpose to create the Primary Key on that column and not Clustered Index, all of us knows that in the Primary Key column we store the unique values so the index on these columns is welcome for fast search!?

    :w00t:

    Clustered indexes are the best solution for range searches, so you might gain a lot by haing your clustered key be on your primary range search column(s). Also - since it dictates the physical order - it might make sense to have a clustered index either put all of inserts "at the end" so that you can keep your page splits to a minimum, OR in reverse you're looking to spread the writes acorss the DB because you're looking to avoid some type of blocking.

    Making the Primary Key the clustered key is not an automatic decision in my mind. They both have their own very distinct purposes, so you should consider whether they might need to stay the same or not. You only get one of each after all.....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • That's true.. almost all of the select (well, the time-critical ones) are done on the [RandomOrder],[CampaignID],[Status] columns.. so it's prefferable to have the table clustered on these columns.

    The primary key is there ,well, for ID purposes mostly...

Viewing 9 posts - 16 through 23 (of 23 total)

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