Index Definition: Which Columns and Performance Impact?

Question

I know how to use indexes (clustured and non clustured) But when should I use indexes without clustering in my table. What scripts should be there to make my index without congestion. I moved on to msdn guidelines, but still a little confused.

Should I only create unique columns like NC or where there are other columns, also like NC.

If I overload the table with NC indices, will it also reduce my performance?

Should I use a non-C composite index for columns that are foreign keys.

I know that the primary key must be Clustured, the unique keys must be NC, but as for foreign keys.

Answer 1

You can have only one clustered index for each table. It does not have to be the primary key, but in most cases it will.

Other than that - it really depends on the queries and the tipping point for which indexes will be used . But defining indexes also means that it will affect DML - inserts, updates, and deletions will have little success.

Should I use composite non-clustered indexes (indexes) for columns that are foreign keys?

It does not matter what a column is, it is a use that is important for the optimizer to determine which index, clustered or otherwise used.

Answer 2

a clustered index defines your physical table structure (to a certain extent) - for example. it determines in what order the data is ordered. Think of a phone book that is "grouped" by (LastName, FirstName) - at least in most countries.

You get only one clustered index per table - so choose it wisely! According to the Queen of Indexing Gospel , Kimberly Tripp , the clustering key should be narrow, stable (never changing), unique (yes!), And ideally everything grows.

It should be narrow, as the clustering key will be added to each record of each nonclustered index. After all, the clustering key is the value used to finally find the actual data.

It must be stable, because constantly updating a plurality of index values ​​is a costly undertaking — all the more so since the clustering key must be updated in all nonclustered indexes.

It must be unique, because again - this is ultimately the value used to determine the evidence. If you select a column that is not unique, SQL Server will "identify" your clustering key by adding a 4-byte value to it - not very good.

And ideally, the clustering key should constantly increase, since this leads to the least fragmentation of the page and index and, therefore, is best suited for performance.

The ideal candidate for the clustering key would be the INT ID (or BIGINT) - it ideally fulfills all these requirements.

As for non-clustered indexes - use and choose them wisely! There is only one general rule that I can give you: all columns that are part of a foreign key (referring to another table) must be in the index - SQL Server will not (contrary to popular belief and many myths) put such an index in its place automatically - never was, never does.

Other than that - you need to look at your system, see what queries you have - all the columns that appear in the WHERE or SORT clause are potential candidates for indexing, but too many indexes are not very good either ....

Answer 3

Yes, you can overload your tables with too many indexes. In general, each additional index evaluates the execution time in terms of maintaining the index. Tables that are heavily updated tend to have fewer indexes.

Another broad rule (from Richard Campbell, from RunAs Radio and DotNetRocks) is that several broad indexes will work better than more narrow indexes. A wide index will cover a wider range of queries, and there is less for the query optimizer for research. Remember that the query optimizer has a limited time to run.

Explore the SQL Server profiler. There are tools there (they were autonomous, but they changed, and I have not used them recently). They can analyze workloads and make indexing recommendations. This will be a better choice than indexes chosen intuitively.

Answer 4

If you have queries that reference columns that are not listed in your index, the SQL server engine will have to search the table to get columns not included in the table from the actual table.

If you frequently perform these queries, you should create non-clustered indexes that "span" the query, including all columns referenced in the index. This should include any non-unique columns.

Adding indexes to a table always reduces write performance, since the index must be updated every time the table is updated.

Answer 5

What areas are you searching for? Search? Etc. Determine which fields you use when executing your queries (WHERE clause) and they can be good candidates.

For example, think of a library. The book catalog has a clustered index for ISBN and a small index, for example, year of publication, etc.

And what helped me is what Bart Duncan published a long time ago. He deserves it.

The article was entitled "Do you use SQL code that is not in the DMV?" Take a look and run this query:

SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC 

This is not the final solution for you, but it will help you identify some indexes. And a link to the article: http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx . By default, when you create a PK in SQL Server, by default it is a clustered index, it does not have to be, but it usually matters.

Answer 6

If you should or do not want the clustered indexes to depend on your workload (usually the number and type of SELECT statements that fall into your table usually prevail)

The clustered index will force the storage order of the rows to match the values ​​of the clustered index. (For this reason, there can only be one clustered index in a table, because rows are stored on disk only once). This makes sense if most of your queries always require a group of related strings.

Example: suppose you store CustomerOrders, and often want to know the number of CustomerOrders (regardless of customer) for a given period of time. In this case, it may be useful to create a clusterd index with OrderDate as the first column. If, on the other hand, you often look for all CustomerOrders with the same CustomerId, it makes sense to put CustomerId as the first column in your cluster index.

The disadvantage of clustered indexes is not the clustered index itself, but secondary indexes: secondary indexes are not clustered themselves (by definition, since rows can only be stored once and stored in the order of the clustered index), and their index entries point to index entries of the clustered index. Therefore, to retrieve a row through a secondary index, 2 read operations are required: one from the secondary index, and then one from the cluster index that it points to.