2

I am trying to analyze queries on this table:

CREATE TABLE [dbo].[Values](
    [tid] [smallint] NOT NULL,
    [t] [datetime2](3) NOT NULL,
    [v] [real] NOT NULL,
 CONSTRAINT [PK_Values_Unique] PRIMARY KEY CLUSTERED 
(
    [t] ASC,
    [tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I am reviewing some Estimated Execution Plans using SQL Server Management Studio, first query.

SELECT *
FROM [dbo].[Values]
WHERE  [tid] = 1

Displaying Estimated Execution Plan for this query yields:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Values] ([tid])
INCLUDE ([timestamp],[v])

Which, I can understand, it wants to have a quick look up by tid, altough I don't fully why the INCLUDE is suggested.

Then I tried a second query (another tid), which is pretty the same with another tid:

SELECT *
FROM [dbo].[Values]
WHERE  [tid] = 1 or [tid] = 2

But now, SSMS, doesn't suggest the previous index.

Why does SSMS suggests an INDEX for the first query but not for the second now although they are pretty the same ?

Should adding this suggested INDEX be helpful for both types of queries?

EDIT: This table has over 2 * 10^9 rows.

  • How many distinct values does tid take? – Thomas Kejser Sep 14 '14 at 10:59
  • 8000 distinct values – Ofiris Sep 14 '14 at 12:28
  • And how large a % of them has the values 1 and 2? – Thomas Kejser Sep 14 '14 at 16:39
  • The values are equally distributed - so 0.025%. – Ofiris Sep 16 '14 at 6:34
4

Some context

The first things to observe is that your clustered index does not help with the lookup of the tid column, because t is the leading column in the index.

If you flipped the order of t and tid in the key, I would expect the index hint to go away and the query to run faster without adding any new indexes.

Specific Answer

The most likely reason that the second query plan does not suggest an index is that the new filter on tid picks more than around 30% of all the values in the table. When that is the case, SQL Server will generally prefer to table scan instead of seeking the index (because this is the better strategy). Hence, suggesting an index is no longer the right thing to do. Selecting two values instead of one may be exactly the tipping point of this 30% constant in the optimiser

To explore this effect, you can force the index hint to return by doing this:

SELECT *
FROM [dbo].[Values] WITH (FORCESEEK)
WHERE  [tid] = 1 or [tid] = 2

.. But please don't do this in production, only to explore the effects.

Why the include?

The include is there because if it wasn't, the execution would have to do this:

  1. Find the value you are looking for in the newly created index on tid
  2. Go to the main index (the primary key) to pick up the values of column v and t

The INCLUDE makes sure all the columns are contained in the index so the second step is not needed. Incidentally, this is one of the reasons why you should eschew SELECT *.

  • Any documentation about this 30% threshold?And if you have a covering index for all columns will SELECT * know to use it?Thanks. – Mihai Sep 14 '14 at 14:03
  • 1
    The 30% threshold is simply something you have to experiment with to find out yourself - that is how I found it. I don't think there is an "official" source for it. Joe Chang has pretty much reverse engineered all the magic constants in there, his blog is here: sqlblog.com/blogs/joe_chang – Thomas Kejser Sep 14 '14 at 16:38
  • 1
    And yes.. SELECT * will know how to use the covering index. However, if you don't need all the columns, don't select them all. This way, a smaller index can often be used instead of a covering one. – Thomas Kejser Sep 14 '14 at 16:38
  • +1 Thanks Thomas, do you think I should accept the suggestion ? most of the queries contains more than one tid in the where clause. – Ofiris Sep 16 '14 at 6:36
  • No, I don't think my answer is correct then. Unless your statisics are not up to date. To validate, please add the following to the question: Query plans for the two queries and the output of DBCC SHOWSTATISTICS WITH HISTOGRAM – Thomas Kejser Sep 16 '14 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.