Geek Answers Handbook

Choosing Optimal Indexes for a SQL Server Table

I have a SQL Server table with the following structure:

CREATE TABLE [dbo].[Log]( [LogID] [bigint] IDENTITY(1,1) NOT NULL, [A] [int] NOT NULL, [B] [int] NOT NULL, [C] [int] NOT NULL, [D] [int] NOT NULL, [E] [int] NOT NULL, [Flag1] [bit] NOT NULL, [Flag2] [bit] NOT NULL, [Flag3] [bit] NOT NULL, [Counter] [int] NOT NULL, [Start] [datetime] NOT NULL, [End] [datetime] NOT NULL)

The table is used to record actions. Columns A Flag1 E represent foreign keys, Flag1 Flag3 indicate specific log states, and Start and End columns indicate the beginning and end of the action.

On average, this table is updated every ~ 30 seconds, and the update is ~ 50 inserts / updates.

The user can make a request from the user interface and filter the data in any column and all combinations of columns and column types.

What would be the best way to optimize data retrieval for this table:

  • Create one "main" index that will contain all of these columns.
  • Identify some of the most commonly used filter combinations, for example. [ A,D,E ], [ A, Start, End ], etc. and create indexes for them
  • Something else...
+4
source share
5 answers

I doubt anyone can do anything other than guessing - you need to record the use of the table and see from this use which combinations of columns are requested.

  • Create one "main" index that will contain all of these columns.

This is definitely not a good idea - if you have an index on (A, B, C, D, E) and you restrict your query to B and D, this index is completely useless. This is only useful.

  • if you often query all five columns
  • using combinations like (A, B), (A, B, C), (A, B, C, D) often

In any other case, it is waste - do not use it.

  • Identify some of the most commonly used filter combinations, for example. [A, D, E], [A, Start, End], etc. And create indexes for them

Yes, this is really the only way to succeed promises. You need to look at what requests are really happening, and then set up for them.

+9
source

Log tables are rarely indexed because indexing slows down the INSERT, UPDATE, and DELETE statements.

I would recommend either:

  • Loading records into a table (temporary or actual, indexed) before filtering
  • using indexed view

Basically - if speed / performance is very important, index entries in another form of the table so that the log is not affected.

+2
source

You cannot use internal keys in any index combiner unless a foreign key is specified. Let's say you have an index on (A,B,C,D) :

  • WHERE A=@a AND B=@b AND C=@c AND D=@d will make full use of the index
  • WHERE A=@a can use the index to filter the range of lines for scanning. The same goes for WHERE A=@a AND B=@b , WHERE A=@a AND C=@c , etc. Any combination that has the leftmost column ( A ) in it can use an index.
  • WHERe B=@b cannot use the index. Nor WHERE C=@c , WHERE D=@d and any other combination that is misisng A In other words, if column A not in the query constraints, the index is not applicable.

These are the most basic rules. Add to this that JOIN conditions may or may not be considered the same as WHERE clauses. And for great results, uncoated indices may suffer from a tipping point . And indexes can satisfy not only search terms, but can also help with ORDER BY clauses. The actual indexes to create depend on your query template, I / O capabilities, downloading updates, and last but not least, data size management overhead (the effect of file size and backups). The engine will give you hints about which indexes can be used for queries (the function of missing indexes ), but the engine will in no way balance the benefits of the index with the cost of one additional index (I / O, performance update, data size). There are Pointer Design Guides that are pretty good, but of course you need to read them. Ultimately, choosing the right indices depends on many factors and considerations that impose a corn response.

+2
source

One approach is to let SQL Server tell you about optimal usage. Run the trace for several minutes while the table is under “typical” use, and then run the Database Engine Tuning Advisor

+1
source

I would put the index on start (datetime) and all this, based on the assumption that several queries to the log will be initial, and most of them will start from the starting point.

0
source

More articles: