CODEWITHSUNDEEP
sqlsql-serverperformanceindexing
Dominik
Dominik

Reputation: 13

Sometimes bad Performance and Missing Index hint - Why?

Sometimes (not always) I have a performance problem with a with. The SQL behind is not great but usually (when it works normally) the performance is enough.

Now I noticed that sometimes the performance is really bad (it takes round about 10 times longer). So I checked the SQL with the execution plan.

In cases I have performance problems I get the message

Missing Index (Impact 59.007): Create Nonclustered Index ....

So I checked the index of the named table when I have the problems and when not. In both cases I have the same indexes.

So now my questions:

EDIT: Create Table Statement:

CREATE TABLE [dbo].[t1] (
  [c1] [int] IDENTITY(1,1) NOT NULL,
  [c2] [nvarchar](50) NULL,
  [c3] [nvarchar](50) NULL,
  [c4] [nvarchar](50) NULL,
  [c5] [date] NULL,
  [c6] [date] NULL,
  [c7] [nvarchar](50) NULL,
  [c8] [int] NULL,
  [c9] [int] NULL,
  [c10] [nvarchar](50) NULL,
  [c11] [decimal](15, 2) NULL,
  [c12] [int] NULL,
  [c13] [int] NULL,
  [c14] [nvarchar](50) NULL
) ON [PRIMARY]

5 separate índexes (4 non clustered, 1 clusted).

Upvotes: 1

Views: 483

Answers (1)

dbajtr
dbajtr

Reputation: 2044

Why do I get sometimes the missing index message and sometimes not when my indexes of the named table do not change?

This solely depends on the query that is being run, If the SQL Engine thinks that an index would have helped the plan that it chose to complete that query then it will tell you.

NOTE: THESE ARE NOT ALWAYS THE BEST ONES!

The engine doesn't generate the "best" execution plan, its gets one that is "good enough" otherwise it may spend more time getting the best one than actually completing the query. So when it suggest an index its suggesting it for the good enough plan than it generated. Look at the query and choose the indexes based on that. If you're starting out with index management look at the joins and where clauses first, play about on a test server and see which is best.

Do you have any ideas why I have these performance problems (and different execution plans) just sometimes?

Kinda explained this in my first answer, are you running the same query when you get the different execution plans? Each different query will generate a differnet execution plan, even adding an extra white space at the end of a query will generate a new one.

Upvotes: 1

Related Questions