SQL Server: How to gather suggestion or info to create indexes for a table

That's not really how it works. You cannot look at a table and identify what columns need indexes. It is all related to the types of queries you are doing on that table. The purpose of an index is to speed up finding rows to be returned. Since filtering is generally done by your where or group by clauses then those are the columns you generally put indexes on. For example if your app needs to query for all product titles that contain certain values then the title column should probably be indexed. Using the query profiler tool while you run common queries will help you to quickly identify which columns to index. If a column isn't indexed but is used in a where/group then the performance is slower generally but the query profiler will show you that.

Note that you should not make all columns indexed because indexing slows down non-query operations and can negatively impact performance. Index on the most commonly needed columns used in your filters based upon profiling your app while it is running. In some cases you might find that it is better to normalize your table some more to eliminate the need for an index. For example if you're storing an address and you store the state/country as a string value in the table but you will need to filter based upon those values then it might be better to move the state/country information to a separate table and use a foreign key to reference it. This eliminates the need for you to index yourself and would potentially reduce the table size. But it complicates other things so it should always be done on a case by case basis. There is no hard and fast rules.

Again, profile your common queries and create indice where the queries can benefit from them.

As for single for composite key, it depends upon your queries as well. If, for example, you have a table that requires 2 columns to properly filter on something then a composite key may be beneficial there (especially if you need uniqueness) but otherwise simple keys are usually sufficient.

Hi @T.Zacks ,

How a DBA knows on which fields he/she should create indexes?

The best candidate data type for the SQL Server index is the integer column due to its small size. On the other hand, columns with text, ntext, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot participate in the index key.
The second point is the location of the column in the query. For instance, the columns used in the WHERE clause, the JOIN prediction, LIKE and the ORDER BY clause are the best candidate columns to be indexed.

How a DBA knows that he need to create composite index or multiple indexes for each fields ?

For composite indexes, please refer to:https://dataschool.com/sql-optimization/multicolumn-indexes/

How a DBA knows that he need create a cluster or non-cluster index ?

Clustered indexes define the way records are physically sorted in a database table. A clustered index is very similar to the table of contents of a book. In the table of contents, you can see how the book has been physically sorted. Either the topics are sorted chapter wise according to their relevance or they can be sorted alphabetically.
A non-clustered index is an index that doesn’t physically sort the database records. Rather, a non-clustered index is stored at a separate location from the actual database table. It is the non-clustered index which is actually similar to an index of a book. A book index is stored at a separate location, while the actual content of the book is separately located.

In addition, the following links about SQL Server index best practices
https://www.sqlshack.com/top-five-considerations-for-sql-server-index-design/
https://blog.quest.com/11-sql-server-index-best-practices-for-improved-performance-tuning/

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Hi @T.Zacks

As Niko and Cool said... this is not how it works...

If you have the table structure, the only index you can recommend is the PrimaryKey (or an equivalent)... all other indexes are results of the workload.

You need to know which queries are running on those table(s), then you can tune the indexes on that table according to the running SELECT queries.

If there is already a workload running on those tables... you might be able to use the QueryStore and/or some DMVs to find out more information on the usage of that table. Eventually, you find some hints for a missing index (or unused indexes), but if you just have "the documentation" or just the structure on paper... you won't be able to create a correct index!

As others have said, indexes are to support queries. Without having sample queries it is not possible to pre-define indexes.

Indexes have overhead on inserts/updates. So you want to limit the number of indexes created on a table to only useful indexes. There is also not real point in adding non-clustered indexes to tables less than 10,000 rows. The overhead is normally not worth the tiny performance increase.

You an use "missing indexes" after the system has been running for a while. HOWEVER, you should not blindly create indexes based on missing indexes. It will report overlapping and duplicate indexes. It also almost always recommends a full covering index, which generally means your clustered index is incorrect and should be changed.

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions?view=sql-server-ver16