November 11, 2013 at 4:09 am
Hi,
i am paying more attention to execution plans right now, and after execution a few queries, SSMS suggested that:
/*
Missing Index Details from SQLQuery5.sql - ...................
The Query Processor estimates that implementing the following index could improve the query cost by 52.1092%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TInscricoesAlunosUniCurr] ([alunos_id],[apagado])
GO
*/
and i have created something like
CREATE NONCLUSTERED INDEX IX_TInscricoesAlunosUniCurr_alunos_id_apagado ON [dbo].[TInscricoesAlunosUniCurr] ([alunos_id],[apagado])
my question is, it's good to follow the advice and create every NONCLUSTERED INDEX suggested ? And, is there a rule of thumb about how many these type indexes per table ?
Also, i have seen this after the declaration of a table
CREATE UNIQUE INDEX idx_user_end_id ON dbo.Sessions(username, endtime, id);
what is the difference between CREATE UNIQUE INDEX and CREATE NONCLUSTERED INDEX ?
November 11, 2013 at 4:15 am
a20213 (11/11/2013)
it's good to follow the advice and create every NONCLUSTERED INDEX suggested ?
No!
Also, i have seen this after the declaration of a table
CREATE UNIQUE INDEX idx_user_end_id ON dbo.Sessions(username, endtime, id);
what is the difference between CREATE UNIQUE INDEX and CREATE NONCLUSTERED INDEX ?
The first creates a unique nonclustered index, key values only allowed once. The second creates a non-unique nonclustered index, where key values can occur multiple times.
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
November 11, 2013 at 4:21 am
No!
So, what should i do when i see this kind of advices, should i just ignore it ?
November 11, 2013 at 4:36 am
a20213 (11/11/2013)
No!
So, what should i do when i see this kind of advices, should i just ignore it ?
No, it's useful as a place to start when you're tuning queries. Emphasis 'start', you need to do a lot more analysis and testing before implementing any such suggestions, otherwise you're likely to end up with a massively over-indexed database.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply