choosing indexes on multiple fk table

• newbie_dev

  SSCommitted

  Points: 1916

  More actions

  May 15, 2009 at 3:42 am

  #204805

  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

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  May 18, 2009 at 8:50 am

  #996394

  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