SQL Server – Indexed Columns Not Working for Basic Queries

indexsql server

Disclaimer: I'm not a DBA. I have picked up a few things from this board in the past that I'm building from.

I have a table of google analytics session start times. I have an index on each column. I want to filter for all sessions that were started between two dates. Screenshot below shows the query, and the index.

The query runs quickly but I do not believe it's using the index based on the Execution plan which both says that there's a missing index and shows a table scan rather than an index scan:

Why?

Is it because of something about the way I'm searching through the datetime? If instead of looking between dates, I set it equal to a date, the execution plan shows it using the index:

But it's not just this table or datetime. Here's a different table with an index on a varchar column:

And a simple query on this one also tells me I'm missing the index:

I'm stumped.

Best Answer

If you want SQL Server to use that index for that specific query, you need to include the session_id column. Otherwise, for each row it finds it will have to go do a key lookup into the base table. It will choose to do this for small result sets, but once you get above a certain number of rows ("the tipping point"), SQL Server thinks it's more efficient to just scan the whole base table.

You can use the INCLUDE clause of the CREATE INDEX syntax to accomplish this:

CREATE NONCLUSTERED INDEX IX_Start_Time
ON GoogleAnalytics.SessionStartTimes (session_start_time)
INCLUDE (session_id);

Related Solutions

SQL Server Execution Plan – Missing Index Not Displaying

Well just while your Tuning Advisor displays a missing index, this won't mean, that your current running query would benefit from this index. The missing index will just be displayed if the current plan would take a benefit from an index.

You can also take a look at one of these DMV's, they will display missing indices for the current db.

SELECT *
FROM sys.dm_db_missing_index_details

If you want additional information, you can also use this query from me:

SELECT  id.index_handle, DB_NAME(id.database_id), id.object_id, id.statement AS object_name, 
        gs.avg_user_impact, gs.avg_total_user_cost,
        id.equality_columns, id.inequality_columns, id.included_columns,
        gs.unique_compiles, gs.user_seeks, gs.last_user_seek, gs.last_system_seek, 
        gs.user_scans, gs.last_user_scan, gs.system_scans
FROM sys.dm_db_missing_index_details as id
INNER JOIN sys.dm_db_missing_index_groups as g
        ON id.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats as gs
        ON g.index_group_handle = gs.group_handle
ORDER BY gs.avg_user_impact DESC

Edit based on your last update

Well it's quite easy to determine why you won't get a suggestion.

First of all, why you'll get an proposed index for this query:

SELECT SomeVarchar20 FROM SomeTable WHERE Age=30

If you create the index, as suggested below, you'll completely serve the index out of the index.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SomeTable] ([Age])
INCLUDE ([SomeVarchar20])

The SQL Server will be able to search for a data page using the Age, as defined in your index. In addition, due to the fact, that you only select the column SomeVarchar20, it is proposed to be added to the leaf level pages of your index (defined by the INCLUDE). This will help the SQL Server to find the right pages based on the Age in the B-Tree architecture. After he found the last row, which holds a pointer to the HEAP or the Clustered Index where all other properties lies, he also gets to the included columns on the leaf level pages. Due to the fact, that he found everything to display in the index itself, he won't go further to your HEAP or your Clustered Index. This will drastically improve your read (!) performance.

The fact, why you won't get a suggestion to add an index to this column is quite clear to me:

SELECT SomeVarchar FROM SomeTable WHERE Name='Adam'

You say that your Column Name is an varchar(512) this means one value can be 512 Bytes long. In fact a SQL Server page can hold round about 8060 bytes. This means you may store up to 16 names (maybe more if you don't use the 512 bytes all the way) per page. This won't improve performance drastically, as SQL Server might even need to iterate (seek) over many many pages. In fact, he needs to maintain all those pages additionally on each insert/update/delete.

If you just use SomeVarchar, which may be a varchar(512) too, it would drastically raise the leaf level pages, which will make the index even inefficient. This is the reason why your SQL Server might not provide a missing index hint for your SELECT.

Hopefully this will clear up all other things in your mind. Otherwise let me know.

SQL Server execution plan is using PK instead of index

None of your indexes are good for this query

IX_Company_Id works only for the WHERE
IX_Project_02 has not overlap with any part of the query because the leading columns do not match a JOIN or WHERE

This should be better because it matches the JOIN and the WHERE although iIt does rely on a matching (Company_Id, Unit_Id) index on Unit

CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
    [Company_Id] , --can swap these 2 to see what happens if no index on Unit
    [Unit_Id] ,

    [UTE_Id] ,

    ProcessID

)
INCLUDE
    CalculateSequencialProject,
    CreateDate,
    OpeningDate,
    ForecastClosingDate,
    RealClosingDate,
    ProjectStatus

Best Answer

Related Solutions

SQL Server Execution Plan – Missing Index Not Displaying

SQL Server execution plan is using PK instead of index

Related Question