What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

SQL Server includes two DMVs – sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats – that are extremely useful for monitoring which indexes are used as well as how and when they are used.  Both DMVs report similar statistics on information such as the number of scans, seeks, and updates to different indexes.  These DMVs are documented in Books Online…

6

Random Prefetching

In my last post, I explained the importance of asynchronous I/O  and described how SQL Server uses sequential read ahead to boost the performance of scans.  In this post, I’ll discuss how SQL Server uses random prefetching.  Let’s begin with a simple example of a query plan that performs many random I/Os.  As in my…

1