Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As per suggestion index must be most selective to improve the performance.
As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows
The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index.

Example with good selectivity:

A table having 100K records and one of its indexed column has 88K distinct values, then the selectivity of this index is 88K / 100K = 0.88.

Now I come to the point.
I have one table having 180K records.
Fields that are being frequently used in search criteria are:

Search record using Name of user.
Field Type :-> Not null , nvarchar(32).
Unique records are 627

Search record using Active_date.
Field Type :-> DateTime ,Null.
Unique records are 85627

Search records using Current_stateField Type :-> Not Null , nvarchar(32).
Unique records are only 2 that are "Pending" and "Closed".

Currently all above fields are indexed.
In terms of selectivity, case (1) and (3) are not the most selectives. What should I do with them in order to improve performance?

2 Answers
2

One thing that does jump out at me is that it's very likely you can improve performance on the table by normalizing it! The presence of so many duplicated (so few unique) values in the columns you listed suggests that perhaps many others in the table are not normalized, as well. I'm suggesting making the Name column an int (or even smallint) with a foreign key to a Names table, and the Current_state column bit (or alternately a tinyint) with a foreign key to a WhateverStates table. You would have to, of course, change your data access code to deal with this indirection, but that is nothing more than the basic job relational database developers have always had to do.

Normalizing will reduce the number of bytes per row, increasing the number of rows per page, reducing the number of pages that have to be read to satisfy any particular query, helping performance across the board! Right now the columns given require likely close to 34 bytes each. After the change I suggest, those columns will only require 11 bytes each. Of course, I haven't seen your whole table--your rows may be so big that it doesn't matter.

What columns and datatypes are in the clustered index (if there is one)? This can radically affect the size of the nonclustered indexes, again affecting performance in exactly the way I described (rows per page).

When you do query based on non-selective columns such as Current_state, what other columns are always or almost always included? It may be okay for you to have a nonselective column in an index if the index also contains a more selective column (or that in conjunction with the less selective column is more highly selective). If on the other hand you generally query often for rows based on the single column Current_state = 'Pending', then you can add a filtered index:

CREATE INDEX IX_YourTable_Pending ON dbo.YourTable (ClusteredColumnsInOrder)
WHERE Current_State = 'Pending'; --SQL 2008 and up only

This technique could help you even when you also include other columns: you would want to put those in the index instead of the ClusteredColumnsInOrder columns I suggested (which was just a tricky way to not put any additional columns into the nonclustered index, since--remember, now--nonclustered indexes always have all the columns of the clustered index implicitly included). Or, if you only pull a very few other columns, you can make your nonclustered index cover the query by adding INCLUDE (AdditionalColumn1, AdditionalColumn2) so that the query engine doesn't have to go back to the clustered index to satisfy the query.

You haven't provided very much information such as full table schema, sample data, and sample queries, and without those it's going to be pretty hard to give you very specific advice about what to do.

One thing I can say, though, is that indiscriminately throwing indexes at the table may not improve things much and could in fact hurt performance of your system overall.

If the hints I have given you here don't seem to help much, then I recommend that you do come back with some of the additional info I mentioned so that we can do a better job of assisting you.

If you dont know what fields they will be searching on, you should not create indexes for all the fields. They can still search them.

Nothing says the query will not perform well. The reason for the guideline is that this is generally what SQL Server will do when looking at your statistics to determine if it should even use the index you created.

If you are always querying these fields, it is a good idea to do a covering index on the additional fields (Name, Current_State) so SQL Server does not have to then lookup the records that match the date, it already has the addition data it needs. (with name, it might not be a good idea)

Ultimately, you should run a few queries (with various indexes) and review the statistics to see which performs the best according to your terms.

Hello Steve , Here use case are pre define . That queries is being fired on thoes above particular fields frequently So i was looking for creating index on those fields.
–
Sagar MakhesanaJan 5 '14 at 17:54