Indexes and Concurrency
- Most SQL Server indexes are balanced tree (B-Tree) structures, with the exception of PRIMARY XML indexes and columnstore indexes.
- Clustered indexes organise the data in a table in the logical order of the clustering keys.
- A covering index can improve performance by containing all the data necessary to satisfy the query without requiring additional data access. Although clustered indexes are always covering indexes, they often are not the most efficient.
- The query optimiser requires indexes and statistics to create optimal execution plans.
- SQL Server automatically maintains statistics on your behalf; however, sometimes manually created or updated statistics can be beneficial.
- Although indexes can improve SELECT performance, having too many indexes to maintain can result in decreased performance of INSERT, UPDATE, and DELETE statements.
- An XML index is a specialised index that has one row for each node in the XML data.
- A spatial index is a geometric index that uses grids to improve spatial searching.
- A clustered index logically stores the rows in the table in the order of the key. The maximum is up to 16 index keys and/or 900 bytes.
- Filtered indexes were introduced to support sparse columns and do not provide any benefit with a column with many duplicate values. When querying for a small subset of values in a column, filtered indexes can provide better performance but Filtered indexes do not support XML queries.
- sys.dm_db_index_physical_stats DMV will provide information about index fragmentation.
- The sys.dm_db_index_operational_stats provides information about how SQL Server is using the indexes
- sys.dm_db_missing_index_details will provide information about indexes that optimizer would like to have present
- sys.dm_db_index_usage_stats will give high-level usage of indexes
- UPDATE STATISTICS command on the table specifying the WITH FULLSCAN option is used to recalculate statistics.
- UPDATE STATISTICS command on the table specifying thenWITH NORECOMPUTE option turns off automatic statistics updating
- The default transaction isolation level is read committed.
- sys.dm_tran_session_transactions displays the current executing transactions by session.
- sys.dm_tran_locks displays all locks currently held by transactions.
- sys.dm_os_waiting_tasks displays the tasks that are waiting and the time that session has been waiting.
- sys.dm_os_wait_stats displays the cumulative wait times for each SQL Server wait type.
- The blocking_session_id column of sys.dm_os_waiting_tasks enables you to determine where blocking is occurring.
- The blocking_session_id column of .sys.dm_exec_requests enables you to determine where blocking is occurring.
- The Processess Pane of Activity Monitor enables you to determine where blocking is occurring.
- The blocked_process column of the system_health Extended Events Session enables you to determine where blocking is occurring.
Useful External Related Links