0

This question already has an answer here:

So I have the following indexes on my table:

CREATE NONCLUSTERED INDEX [$5] ON [dbo].[Record Link]
(
    [Company] ASC,
    [Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

 CREATE NONCLUSTERED INDEX [$6] ON [dbo].[Record Link]
(
    [Notify] ASC,
    [Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [$7] ON [dbo].[Record Link]
(
    [Company] ASC,
    [Type] ASC,
    [Link ID] ASC,
    [Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [$8] ON [dbo].[Record Link]
(
    [Company] ASC,
    [Type] ASC,
    [Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [bain_test] ON [dbo].[Record Link]
(
    [Record ID] ASC,
    [Type] ASC,
    [Company] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [$4] ON [dbo].[Record Link]
(
    [Notify] ASC,
    [Company] ASC,
    [Link ID] ASC,
    [To User ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

The Problem is that some of the indexes only get used some times. As an example look at this query and the Execution plan, you can see that it is executed within the EXISTS but it is not executed for the rest of the query. Both where clauses are identical.

enter image description here

Here is that queries statistics time and IO:

*SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Record Link'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*

*(1 row affected)*

*SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.*
*(1 row affected)*

*Table 'Record Link'. Scan count 1, logical reads 1017129, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.*

*(1 row affected)*

*SQL Server Execution Times:
   CPU time = 1969 ms,  elapsed time = 1963 ms.*

I don't have the option of changing the query as it from an ERP system and I'm trying to optimize the speed.

And while we are here there are a few different variations for querying this table.

Here are another one that don't use the indexes at all.

With this one things are strange, so i first tried having an index that uses fields: Record ID, Company, this didn't work until I created a new index and made it: Company, Record ID.

I Did this yesterday and it was working, but this morning in the profiler its not using the index again.

SELECT  TOP (1) 
    "timestamp","Link ID",
    "Record ID",
    "URL1",
    "URL2",
    "URL3",
    "URL4",
    "Description",
    "Type",
    DATALENGTH("Note"),
    "Created",
    "User ID",
    "Company",
    "Notify",
    "To User ID" 
FROM 
    "Live".dbo."Record Link" WITH(UPDLOCK)  
WHERE 
    ("Record ID"=@0 AND "Company"=@1) 
ORDER BY 
    "Link ID" ASC 
OPTION(OPTIMIZE FOR UNKNOWN)

With this one, i really just cant figure it out, i thought the index with company,notify,User ID would work:

SELECT TOP (@0)
    "timestamp", "Link ID", "Record ID",
    "URL1", "URL2", "URL3", "URL4",
"Description", "Type", DATALENGTH("Note"), "Created", "User ID",
    "Company", "Notify", "To User ID"
FROM "Live_Replica".dbo."Record Link" WITH(READUNCOMMITTED)
WHERE ("Link ID">@1
  AND (("Company"=@2 OR "Company"=@3)
  AND "Notify"=@4
  AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5
    OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6)))
ORDER BY "Link ID" ASC
OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

I Have tried googling for this issues but cant seem to find an answer at all. All indexes are rebuilt every night and i don't shrink the database at all so that the indexes don't get fragmented.

marked as duplicate by Paul White sql-server Feb 6 at 11:30

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

As an example look at this query and the Execution plan, you can see that it is executed within the EXISTS but it is not executed for the rest of the query. Both where clauses are identical.

Both where clauses are identical, but the select lists are not.

There is NO covering index for your queries among those shown in your question, except for those with exists.

For exists query it's important only to know if such a record (with [Record ID] = ..., [Type] = ...,[Company] = ...) exists, so the server can only control your index [bain_test] that includes all these fields as key fiels.

For the second query you asked server for many other fields (see your SELECT list) that are not included in this index and in no other index, so if the server chose your index it had to do lookups to a base table, and since statistics cannot be used in your case (you used oprimize for unknown hint), server estimated the number of returned rows as too many and chosed to do a scan. Scan at least is continuos reading while lookups are random and they cost.

The same is for your third query. None of the indexes is covering, you select the fields that are not included in your indexes, and even if it seems that this query should return only one row, it's not a random row, your top has order by clause, so all the records should be read first and ten sorted.

I think you should examing your missimg indexes from

sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats,

sys.dm_db_missing_index_details

to decide what indexes you should create.

And also take a look on sys.dm_db_index_usage_stats to find out unused indexes.

Your indexes are very similar, and your conditions are similar too, at least all the queryis have Record ID AND Company. Why don't you think that your clustered index (on [Record ID] I suppose) is not optimal? It's already sorted on one of your conditions, it is very probably unique and it's always covering. It's very probably that your indexes are not used at all.

And please update your quastion with clustered index definition.

  • There is no order operation in the second query plan so the clustered index must be on [Link ID] I think. – Denis Rubashkin Jan 31 at 8:54
  • Hi sepupic, Thanks for the explanation, Its not optimal because the datatype is of varbinary. so its a long key in many cases, but not much I can do about that. I also read that using include just wastes space and they you should only use the fields in the where clause, this has work until now. It also works on all other queries. I can't use the include in the one case as there is an image field "Note". – BainAnator Jan 31 at 9:06
  • >>>Its not optimal because the datatype is of varbinary. so its a long key in many cases<<< Your clustered index key is ALWAYS presented in all your nonclustered indexes too – sepupic Jan 31 at 9:14
  • >>>I also read that using include just wastes space<<< Creating many useless indexes is not only waste of space, it's also a const of mantenance of those indexes on every DML operation – sepupic Jan 31 at 9:17
  • 1
    The only option to make query optimizer use the index is to replace OPTION(OPTIMIZE FOR UNKNOWN) with OPTION(RECOMPILE). Then, depending on statistics, the index could be used if it doesn't cause a huge number of key lookup operations. – Denis Rubashkin Jan 31 at 10:39

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