May 15, 2009 at 3:42 am
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
May 18, 2009 at 8:50 am
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