SQL Server Index - A very large table with a where clause regarding a very small range of values ​​- Do I need an index for the where clause?

Question

I am creating a database with one table for a special scenario for which I need to implement a solution. After a short time, the table will contain several hundred million rows, but each row will be quite compact. Even when there are a lot of rows, I need to insert, update and choose the speed to be beautiful and fast, so I need to choose the best indexes for the job.

My table looks like this:

create table dbo.Domain ( Name varchar(255) not null, MetricType smallint not null, -- very small range of values, maybe 10-20 at most Priority smallint not null, -- extremely small range of values, generally 1-4 DateToProcess datetime not null, DateProcessed datetime null, primary key(Name, MetricType) ); 

The selection request will look like this:

 select Name from Domain where MetricType = @metricType and DateProcessed is null and DateToProcess < GETUTCDATE() order by Priority desc, DateToProcess asc 

The first type of update will look like this:

 merge into Domain as target using @myTablePrm as source on source.Name = target.Name and source.MetricType = target.MetricType when matched then update set DateToProcess = source.DateToProcess, Priority = source.Priority, DateProcessed = case -- set to null if DateToProcess is in the future when DateToProcess < DateProcessed then DateProcessed else null end when not matched then insert (Name, MetricType, Priority, DateToProcess) values (source.Name, source.MetricType, source.Priority, source.DateToProcess); 

The second type of update will look like this:

 update Domain set DateProcessed = source.DateProcessed from @myTablePrm source where Name = source.Name and MetricType = @metricType 

Are they the best indexes for optimal insertion, updating and speed selection?

 -- for the order by clause in the select query create index IX_Domain_PriorityQueue on Domain(Priority desc, DateToProcess asc) where DateProcessed is null; -- for the where clause in the select query create index IX_Domain_MetricType on Domain(MetricType asc); 

Answer 1

remarks:

• Your updates should use PK
• Why not use tinyint (range 0-255) to make the strings even narrower?
• Do you need time? Can you use smalledatetime?

Ideas:

• There is no index in the SELECT query to cover it. You need one ( DateToProcess, MetricType, Priority DESC) INCLUDE (Name) WHERE DateProcessed IS NULL `: you need to experiment with the column order of the columns to get the best

• You can expand this index to have filtered indexes for MetricType (storing the DatePcessed IS NULL filter). I would do it after another, when I have millions of lines to test with

Answer 2

I suspect that your best performance would be the lack of indexes on Priority and MetricType . Power is probably too low for indexes to do much good.

An index on DateToProcess almost certainly help, since there is more power in this column, and it is used in the WHERE and ORDER BY . I will start with this first.

Does the index in DateProcessed for discussion. It depends on what percentage of NULL values ​​you expect from this column. It is best, as usual, to check the query plan with some real data.

Answer 3

In the table schema section, you noted that "MetricType" is one of the two main keys, so you need to index it along with the "Name" column. As for the fields "Priority" and "DateToProcess", since they will be present in the where clause, this may not prevent them from being indexed, but I do not recommend that the where clause in your "DateProcessed" index is null, indexing only the dataset is not is a good idea, delete it and index all of these two columns.