SQL Server: Database Indexes Interview Questions and Answers (Day-4)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Server: Script to find Heap Table without having an Index

SQL Server: Script to find Index Average Fragmentation in Percentage

SQL Server: Script to find Missing Indexes

SQL Server: Script to find usage of Database Indexes

SQL Server: Script to find Index Operational Stats

What is your best practice to perform large-scale Index operations?

Make sure the transaction log has been backed up and truncated before running large-scale index operations online, and that the log has sufficient space to store the projected index and user transactions.

Consider setting the SORT_IN_TEMPDB option to ON for the index operation. This separates the index transactions from the concurrent user transactions. The index transactions will be stored in the tempdb transaction log, and the concurrent user transactions will be stored in the transaction log of the user database.

This allows for the transaction log of the user database to be truncated during the index operation if it is required.

What is Online Indexing?

The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations.

DBAs can create, rebuild, or drop indexes online. The index operations on the underlying table can be performed concurrently with update or query operations.

The Online indexing feature is very helpful for environments that run 24 hours a day, seven days a week. The Online Indexing feature is available only in the Enterprise Editions.

Can we create index on table variables?

Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. The primary key will represent a clustered index, while the unique constraint a non clustered index.

What are your best practices to create an Index?

• Understand the characteristics of the database (OLTP / OLAP).
• Keep indexes lean.
• Understand the characteristics of the most frequently used columns and queries.
• Choose the right index type.
• Create the clustered index on every table.
• Keep the length of the index key short for clustered indexes.
• Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns.
• Consider using filtered indexes on columns that have well-defined subsets.
• Be sure to eliminate duplicate indexes, that is, multiple indexes created on the same set of columns.
• Consider the order of the columns if the index will contain multiple columns.
• Determine the Fill Factor.

How more indexes makes your INSERT slower?

The number of indexes on a table is the most dominant factor for insert performance. The more indexes a table has, the slower the execution becomes. The insert statement is the only operation that cannot directly benefit from indexing because it has no where clause.

Adding a new row to a table involves several steps. First, the database must find a place to store the row.

If there are indexes on the table, the database must make sure the new entry is also found via these indexes. For this reason it has to add the new entry to each and every index on that table. The number of indexes is therefore a multiplier for the cost of an insert statement.

Moreover, adding an entry to an index is much more expensive than inserting one into a heap structure because the database has to keep the index order and tree balance. That means the new entry cannot be written to any block—it belongs to a specific leaf node.

Once the correct leaf node has been identified, the database confirms that there is enough free space left in this node. If not, the database splits the leaf node and distributes the entries between the old and a new node.

Generally, when you should perform your index tunning exercise?

Index tuning isn’t something you do once a year. It’s something that you need to do iteratively– that means every month. Over time, data sizes change, user activity changes, and the SQL Server optimizer changes. Each of these things mean that indexes that are best for an application will also change. As you tune indexes, your query plans will change and you’re very likely to see more opportunities to add, drop, and combine indexes emerge. Because of this, you want to do a few changes every month.

Has default Index created on foreign key columns?

When a foreign key constraint is defined for a table, an index is not created by default by SQL Server.

However, it’s not uncommon for developers and database administrators to add them manually.

Indexes on foreign keys can provide performance benefits for table joins involving the primary and foreign key, and they can impact performance when the primary key value is updated, or if the row is deleted.

In which columns, you should consider your indexes?

Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses. Without an index, each of these operations will require a table scan of your table, potentially hurting performance.

When index needs change and you want to test how removing an index will affect performance, you have two options – you can disable or drop the index. Which is better?

DISABLING AN INDEX is better than to drop an Index.

When we Disable an Index, The metadata about the index is retained, as are the usage statistics in sys.dm_db_index_usage_stats.

The index is not usable by the query optimizer.

Disabling a nonclustered index will deallocate the index pages – the space is freed in the database.

Disabling a clustered index has additional effects. The data in the table still exists, but will be inaccessible for anything other than a drop or rebuild operation.

T-SQL Script to DISABLE an INDEX?

How you can re-enable the disable index?

If you want to re-enable the index, you need to rebuild it.

As a DBA, share your checklists and best practice for performance tuning of Indexes.

• Periodically, run the Database Engine Tuning Advisor against current Profiler traces to identify potentially missing indexes.
• Remove indexes that are never used.
• Don’t accidentally create redundant indexes.
• As a rule of thumb, every table should have at least a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases — such as an identity column, or some other column where the value is increasing — and is unique. In many cases, the primary key is the ideal column for a clustered index.
• Since you can only create one clustered index per table, take extra time to carefully consider how it will be used. Consider the type of queries that will be used against the table, and make an educated guess as to which query (the most common one run against the table, perhaps) is the most critical, and if this query will benefit most from having a clustered index.
• If a column in a table is not at least 95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, you generally don’t want to add non-clustered indexes to columns that aren’t at least 95% unique.
• Keep the “width” of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance.
• If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type).
• Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
• Don’t automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by queries run against the table.
• When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once the needed record is found, SQL Server doesn’t have to look any further.
• If you perform regular joins between two or more tables in your queries, performance will be optimized if each of the joined columns have appropriate indexes.
• Don’t automatically accept the default value of 100 for the fill factor for your indexes. It may or may not best meet your needs. A high fill factor is good for seldom changed data, but highly modified data needs a lower fill factor to reduce page splitting.
• Don’t over index your OLTP tables, as every index you add increases the time it takes to perform INSERTS, UPDATES, and DELETES. There is a fine line between having the ideal number of indexes (for SELECTs) and the ideal number to minimize the overhead that occurs with indexes during data modifications.