choosing indexes on multiple fk table

  • Hi

    I'm trying to optimize a sp, that is very big and I don't want to put it here, and analyzing the estimated execution plan I have some index scans on the next table :

    CREATE TABLE [dbo].[Mt_Anag_Banche_Dati](

    [Anag_Banche_Dati_Id] [uniqueidentifier] NOT NULL,

    [Anag_Id] [uniqueidentifier] NOT NULL,

    [Banca_Dati_Id] [uniqueidentifier] NOT NULL,

    [Data_Richiesta] [datetime] NULL,

    [User_Id_Richiesta] [int] NULL,

    [Esito] [tinyint] NULL,

    [Valutazione] [varchar](50) COLLATE Latin1_General_CI_AS NULL,

    [User_Id_Last] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Mt_Anag_Banche_Dati_User_Id_Last] DEFAULT ('53A023AD-31FD-460C-923C-409A47840085'),

    [Datasys_Last] [datetime] NOT NULL CONSTRAINT [DF_Mt_Anag_Banche_Dati_Datasys_Last] DEFAULT (getdate()),

    [Data_Fine] [datetime] NULL,

    CONSTRAINT [PK_Mt_Anag_Banche_Dati] PRIMARY KEY NONCLUSTERED

    (

    [Anag_Banche_Dati_Id] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Mt_Anag_Banche_Dati] WITH CHECK

    ADD CONSTRAINT [FK_Mt_Anag_Banche_Dati_Mt_Anag] FOREIGN KEY([Anag_Id])

    REFERENCES [dbo].[Mt_Anag] ([Anag_Id])

    GO

    ALTER TABLE [dbo].[Mt_Anag_Banche_Dati] WITH CHECK

    ADD CONSTRAINT [FK_Mt_Anag_Banche_Dati_ref_User] FOREIGN KEY([User_Id_Last])

    REFERENCES [dbo].[Sst_User] ([User_Id])

    GO

    ALTER TABLE [dbo].[Mt_Anag_Banche_Dati] WITH NOCHECK

    ADD CONSTRAINT [FK_Mt_Anag_Banche_Dati_Tbmt_Banche_Dati] FOREIGN KEY([Banca_Dati_Id])

    REFERENCES [dbo].[Tbmt_Banche_Dati] ([Banca_Dati_Id])

    GO

    ALTER TABLE [dbo].[Mt_Anag_Banche_Dati] CHECK CONSTRAINT [FK_Mt_Anag_Banche_Dati_Tbmt_Banche_Dati]

    My sp uses the [Data_Richiesta] and [Data_Fine] for selecting some data from a table [Mt_Anag_Banche_Dati_Detail] wich has a fk to [Mt_Anag_Banche_Dati].

    I have a clustered index on [Anag_Banche_Dati_Id] wich is used in joins, and a nonclustered one on [Anag_Id]

    I think they are not in the "best combination" because all the fk are used often, also the [Anag_Banche_Dati_Id] column, in joins, and the two dates are used in this sp that takes more than 10 seconds.Any suggestion in choosing the indexes?

    I don't think that the clustered one is best chosen, neither the others.Should I group all the fk in one index?

    Thanks

  • It's going to be difficult to help tune a stored procedure or a table's indexes without also seeing the code for the stored procedure, and the execution plan.



    A.J.
    DBA with an attitude

Viewing 2 posts - 1 through 1 (of 1 total)

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