How can SQL Server 2005 help me evaluate and manage indexes?

(1) How can I find out whether my indexes are useful?How are they used?

(2) Do I have any tables or indexes that are not used (or rarely)

(3) What is the cost of index maintenance vs. its benefit?

(4) Do I have hot spots & index contention?

(5) Could I benefit from more (or less) indexes?

Answer:

SQL Server 2005 Dynamic Management Views (DMVs) are important insofar as they expose changing server state information that typically spans many sessions, many transactions, and many requests.DMVs provide a level of transparency that was not available in SQL Server 2000 and can be used for diagnostics, memory and process tuning, and monitoring.The SQL Server engine tracks detailed resource history in DMVs that can be queriable with SELECT statements but are not persisted to disk.Thus DMVs reflect activity since the last SQL Server recycle.

Since indexes provide an alternative to a table scan, and because DMVs expose index usage counters, we can compare the cost vs. the benefit of indexes.This comparison will include the maintenance cost of keeping the index up to date, versus the benefit of reads e.g. when an index can be used in lieu of a table scan.Keep in mind that an update or delete operation involves both a read which is first required to determine whether a row qualifies for the update operation, and a write if a row is found to qualify.In an insert operation, only writes will be performed on all indexes.Consequently, in an insert-intensive workload, writes will exceed reads.In an update-intensive (updates and deletes) workload, read and write counts are generally close assuming there are not a lot of ‘records not found’.In read-intensive workloads, read counts will exceed write counts.Referential constraints such as foreign keys require other read activity (for inserts, updates, and deletes) to ensure referential integrity is maintained.

(1)How can I find out whether my indexes are useful?How are they used?

First, we will determine whether indexes are ‘useful’.DDL is used to create objects (such as indexes) and update the catalog.Creating the index does not constitute ‘use’ of the index, and thus the index will not be reflected in the index DMVs until the index is actually used.When an index is used by a Select, Insert, Update, or Delete, its use is captured by sys.dm_db_index_usage_stats.If you have run a representative workload, all useful indexes will have been recorded in sys.dm_db_index_usage_stats.Thus, any index not found in sys.dm_db_index_usage_stats is unused by the workload (since the last re-cycle of SQL Server).Unused indexes can be found as follows:

(2) Do I have any tables or indexes that are not used (or rarely used)?

Rarely used indexes will appear in sys.dm_db_index_usage_stats just like heavily used indexes.To find rarely used indexes, you look at columns such as user_seeks, user_scans, user_lookups, and user_updates.

If a table is heavily updated and also has indexes that are rarely used, the cost of maintaining the indexes could exceed the benefits.To compare the cost and benefit, you can use the table valued function sys.dm_db_index_operational_stats as follows:

The difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats is as follows.Sys.dm_db_index_usage_stats counts each access as 1, whereas sys.dm_db_index_operational_stats counts depending on the operation, pages or rows.

(4) Do I have hot spots & index contention?

Index contention (e.g. waits for locks) can be seen in sys.dm_db_index_operational_stats.Columns such as row_lock_count, row_lock_wait_count, row_lock_wait_in_ms, page_lock_count, page_lock_wait_count, page_lock_wait_in_ms, page_latch_wait_count, page_latch_wait_in_ms, pageio_latch_wait_count, pageio_latch_wait_in_ms detail lock and latch contention in terms of waits.You can determine the average blocking and lock waits by comparing waits to counts as follows:

from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s,sys.indexes i

whereobjectproperty(s.object_id,‘IsUserTable’)= 1

and i.object_id= s.object_id

and i.index_id = s.index_id

orderby row_lock_wait_count desc

The following report shows blocks in the [Order Details] table, index OrdersOrder_Details.While blocks occur less than 2 percent of the time, when they do occur,the average block time is 15.7 seconds.

It would be important to track this down using the SQL Profiler Blocked Process Report.You can set the Blocked Process Threshold to 15 using sp_configure ‘Blocked Process Threshold’,15.Afterwards, you can run a trace to capture blocks over 15 seconds.

The Profiler trace will include the blocked and blocking process.The advantage of tracing for long blocks is the blocked and blocking details can be saved in the trace file and can be analyzed long after the block disappears.Historically, you can see the common causes of blocks.In this case the blocked process is the stored procedure NewCustOrder.The blocking process is the stored procedure UpdCustOrderShippedDate.

The caveat with Profiler Trace of Blocked Process Report is that in the case of stored procedures, you cannot see the actual statement within the stored procedure that is blocked.You can capture the actual blocked statement of a stored procedure in realtime (as it is occuring) using the following:

Remembering that indexes involve both a maintenance cost and a read benefit, the overall index cost benefit can be determined by comparing reads and writes.Reading an index allows us to avoid table scans however they do require maintenance to be kept up-to-date.While it is easy to identify the fringe cases where indexes are not used, and the rarely used cases, in the final analysis, index cost benefit is somewhat subjective.The reason is the number of reads and writes are highly dependent on the workload and frequency.In addition, qualitative factors beyond the number of reads and writes can include a highly important monthly management report or quarterly VP report in which the maintenance cost is of secondary concern.

Writes of all indexes are performed for inserts, but there are no associated reads (unless there are referential constraints).Besides select statements, reads are performed for updates and deletes, writes are performed if rows qualify.OLTP workloads have lots of small transactions, frequently combining select, insert, update and delete operations.Data Warehouse activity is typically separated into batch windows having a high concentation of write activity, followed by an on-line window of read activity.

SQL Statement

Read

Write

Select

Yes

No

Insert

No

Yes, all indexes

Update

Yes

Yes, if row qualifies

Delete

Yes

Yes, if row qualifies

In general, you want to keep indexes to a funtional minimum in a high transaction OLTP environment due to high transaction throughput combined with the cost of index maintenance and potential for blocking.In contrast, you pay for index maintenance once during the batch window when updates occur for a data warehouse.Thus, data warehouses tend to have more indexes to benefit its read-intensive on-line users.

In conclusion, an important new feature of SQL Server 2005 includes Dynamic Management Views (DMVs).DMVs provide a level of transparency that was not available in SQL Server 2000 and can be used for diagnostics, memory and process tuning, and monitoring. DMVs can be useful in answering practical questions such as index usage, cost benefit of indexes, and index hot spots.Finally, DMVs are queriable with SELECT statements but are not persisted to disk.Thus they reflect changing server state information since the last SQL Server recycle.

I too see indexes on completely useless fields but the problem is that sometimes indexes may seem usefull but in reality they might be unused. This is very hard to capture in SQL Server 2000. The new DMV’s make this a very easy job.

Would you like to know more about what the inner workings of your MS SQL Server 2005 box?

There are a 50+ reports. Some of them address basic questions such as slowest queries, missing indexes, and most expensive queries (Note: while this information is basic, it is still NOT available in the other software packages on the market, who are claiming to “monitor”, “optimize” and “tune” MS SQL database deployments.)The software also provides variety of other information via its UI such as hardware configuration, NUMA node presence, scheduling details, low level session information, database objects storage and usage details, etc.

SQL Minds, out of North Carolina, is claiming many things, but not admitting their relaltionship to a STOCK SCAM called Vision Technology Corp. Plamen Slavov (Product Manager) is a realtor from Florida, not an SQL guy, or even an IT guy.

Ivan Ivanov, CEO of SQL Minds is also the CTO and board member of Vision Technology Corp (VSTC), and is also the CEO of Data Consulting Group (SQL Minds one and only testimonial and the "beta" customer announced by VSTC.. meaning they are beta’ing their own stuff) THIS IS A SCAM. Read all about it here