70-432 : Index usage (sys.dm_db_index_usage_stats)

This blog article is aimed at people preparing for 70-432 exam.

This DMV is particularly useful and represents one area where SQLServer appears to be ahead of te Oracle database engine (there is no easy to see which indexes have not been used in the last day / week / month ...)

Starting with the msdn documentation for dm_db_index_usage_stats:

sys.dm_db_index_usage_stats (Transact-SQL)

Returns counts of different types of index operations and the time each type of operation was last performed.

Column name	Data type	Description
database_id	smallint	ID of the database on which the table or view is defined.
object_id	int	ID of the table or view on which the index is defined
index_id	int	ID of the index.
user_seeks	bigint	Number of seeks by user queries.
user_scans	bigint	Number of scans by user queries.
user_lookups	bigint	Number of bookmark lookups by user queries.
user_updates	bigint	Number of updates by user queries.
last_user_seek	datetime	Time of last user seek
last_user_scan	datetime	Time of last user scan.
last_user_lookup	datetime	Time of last user lookup.
last_user_update	datetime	Time of last user update.
system_seeks	bigint	Number of seeks by system queries.
system_scans	bigint	Number of scans by system queries.
system_lookups	bigint	Number of lookups by system queries.
system_updates	bigint	Number of updates by system queries.
last_system_seek	datetime	Time of last system seek.
last_system_scan	datetime	Time of last system scan.
last_system_lookup	datetime	Time of last system lookup.
last_system_update	datetime	Time of last system update.

Remarks

Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.

http://msdn.microsoft.com/en-us/library/ms188755.aspx

Now the following is a good blog post with an example of how this works in practice and also when you should be checking index usage stats. Not only does the following provide a nice simple of example of usage of the DMV sys.dm_db_index_usage_stats but it also makes a good point about focusing your attention and efforts on defragmentating the key indexes with high usage stats (it is too easy to get side tracked by problems where you can see something is clearly wrong, but you aren't necessarily tackling the key performance problems) :

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’.

If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a lot more disk space than it could do and it may be worth compacting (with a rebuild or a defrag) to get that disk space back. However, usually there’s not much point spending resources to remove any kind of fragmentation when an index isn’t being used. This is especially true of those people who rebuild all indexes every night or every week.

...

If you're interested in whether an index is being used, you can filter the output. Let's focus in on a particular table - AdventureWorks.Person.Address.

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

You'll probably see nothing in the output, unless you've been playing around with that table. Let's force the clustered index on that table to be used, and look at the DMV output again.

SELECT * FROM AdventureWorks.Person.Address;

GO

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

Now there's a single row, showing a scan on the clustered index. Let's do something else.

SELECT StateProvinceID FROM AdventureWorks.Person.Address

WHERE StateProvinceID > 4 AND StateProvinceId <>

GO

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

And there's another row, showing a seek in one of the table's non-clustered indexes.

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/20/how-can-you-tell-if-an-index-is-being-used.aspx

70-432 : Locks and Latchs (sys.dm_db_index_operational_stats)

This blog article is aimed at people preparing for 70-432 exam.

The MCTS 70-432 exams introduces some of the dynamic management views (DMVs). I like the ideas of DMVs, as a seasoned Oracle DBA I know many of the key Oracle Instance and Database views well. Once of the advantages of having a SQL view (as opposed to a GUI tool) is that he DMV query can be scheduled to run periodically and log key columns over time (very important for a DBA).

Starting with the msdn documentation for dm_db_index_operational_stats we can that it can capture leaf DML operations plus lock and latch waits:

You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots.

Use the following columns to identify areas of contention.

To analyze a common access pattern to the table or index partition, use these columns:

• leaf_insert_count
• leaf_delete_count
• leaf_update_count
• leaf_ghost_count
• range_scan_count
• singleton_lookup_count

To identify latching and locking contention, use these columns:

• page_latch_wait_count and page_latch_wait_in_ms
  These columns indicate whether there is latch contention on the index or heap, and the significance of the contention.
• row_lock_count and page_lock_count
  These columns indicate how many times the Database Engine tried to acquire row and page locks.
• row_lock_wait_in_ms and page_lock_wait_in_ms
  These columns indicate whether there is lock contention on the index or heap, and the significance of the contention.

To analyze statistics of physical I/Os on an index or heap partition

• page_io_latch_wait_count and page_io_latch_wait_in_ms
  These columns indicate whether physical I/Os were issued to bring the index or heap pages into memory and how many I/Os were issued.

Column Remarks

The values in lob_orphan_create_count and lob_orphan_insert_count should always be equal.

The value in the columns lob_fetch_in_pages and lob_fetch_in_bytes can be greater than zero for nonclustered indexes that contain one or more LOB columns as included columns. For more information, see Index with Included Columns. Similarly, the value in the columns row_overflow_fetch_in_pages and row_overflow_fetch_in_bytes can be greater than 0 for nonclustered indexes if the index contains columns that can be pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB.

How the Counters Are Reset

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats.

The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.

http://msdn.microsoft.com/en-us/library/ms174281(SQL.90).aspx

Next what is the difference between a lock and a latch? A lock (aka "enqueue") is a request for exclusive or shared owner of some data - a row,page or extent (a set of eight contiguous pages makes up an extent). Now for Oracle a latch is lock on an internal data structure in the SGA:

What is the difference between locks, latches, enqueues and semaphores?

A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.

Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.

Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:

• semmns = sum of the "processes" parameter for each instance (see init.ora for each instance)
• semmni = number of instances running simultaneously;
• semmsl = semmns

http://www.orafaq.com/wiki/Oracle_database_Internals_FAQ#What_is_the_difference_between_locks.2C_latches.2C_enqueues_and_semaphores.3F

while for SQLServer a latch is often described as a "lightweight lock" and again is about protect internal database engine structures like buffers:

Tips for Using SQL Server Performance Monitor Counters

By : Brad McGehee Aug 24, 2005

A latch is in essence a "lightweight lock". From a technical perspective, a latch is a lightweight, short-term synchronization object (for those who like technical jargon). A latch acts like a lock, in that its purpose is to prevent data from changing unexpectedly. For example, when a row of data is being moved from the buffer to the SQL Server storage engine, a latch is used by SQL Server during this move (which is very quick indeed) to prevent the data in the row from being changed during this very short time period. This not only applies to rows of data, but to index information as well, as it is retrieved by SQL Server.

Just like a lock, a latch can prevent SQL Server from accessing rows in a database, which can hurt performance. Because of this, you want to minimize latch time.

SQL Server provides three different ways to measure latch activity. They include:

• Average Latch Wait Time (ms): The wait time (in milliseconds) for latch requests that have to wait. Note here that this is a measurement for only those latches whose requests had to wait. In many cases, there is no wait. So keep in mind that this figure only applies for those latches that had to wait, not all latches.
• Latch Waits/sec: This is the number of latch requests that could not be granted immediately. In other words, these are the amount of latches, in a one second period, that had to wait. So these are the latches measured by Average Latch Wait Time (ms).
• Total Latch Wait Time (ms): This is the total latch wait time (in milliseconds) for latch requests in the last second. In essence, this is the two above numbers multiplied appropriately for the most recent second.

When reading these figures, be sure you have read the scale on Performance Monitor correctly. The scale can change from counter to counter, and this is can be confusing if you don't compare apples to apples.

Based on my experience, the Average Latch Wait Time (ms) counter will remain fairly constant over time, while you may see huge fluctuations in the other two counters, depending on what SQL Server is doing.

http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p3.aspx

To finish the following query looks useful, a good example of how to use the sys.dm_db_index_operational_stats view:

Tables where the most latch contention is occurring

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

http://sqlblog.com/blogs/louis_davidson/archive/2007/08/26/sys-dm-db-index-operational-stats.aspx

Databases: UK Oracle User Group SANs and HBAs

At the recent UK Oracle User Groups, there was lots of discussion around SAN performance and HBAs (Host Bus Adapters). 

Perhaps I've not concidered SAN performance enough and I've never heard of HBAs. So this post gives a quick overview:

In computer hardware, a host controller, host adapter, or host bus adapter (HBA) connects a host system (the computer) to other network and storage devices. The terms are primarily used to refer to devices for connecting SCSI, Fibre Channel and eSATA devices, but devices for connecting to IDE,Ethernet, FireWire, USB and other systems may also be called host adapters. Recently, the advent ofiSCSI has brought about Ethernet HBAs, which are different from Ethernet NICs in that they include hardware iSCSI-dedicated TCP Offload Engines.

http://en.wikipedia.org/wiki/Host_Bus_Adapter

I found that Emulex are partnering Oracle, providing Virtual HBA technology:

"Emulex is working closely with Oracle to deliver industry-first capabilities for enterprise-class data centers, such as data integrity, support for Virtual HBA technology and 8Gb/s Fibre Channel SANs," Mike Smith, executive vice president, worldwide marketing, Emulex Corp. "Leveraging Emulex 8Gb/s HBAs, customers can utilize twice the performance of today's products, providing the I/O scalability necessary to support increased numbers of applications running on Oracle VM-based virtualized servers, which helps reduce management, procurement, power and cooling costs."

http://www.oracle.com/technologies/virtualization/partners.html

Lastly the following (SQL Server) blog post, is about tuning your HBA. This post gives a flavour of what DBA and System Admins should be concidering when working with HBAs:

Tuning your SAN: Too much HBA Queue Depth?

Modifying the “HBA Queue Depth” is a performance tuning tip for servers that are connected to Storage Area Networks (SAN’s).  A Host Bus Adapter (HBA) is the storage equivalent of a network card and the Queue Depth parameter controls how much data is allowed to be “in flight” on the storage network from that card.

http://sqlblogcasts.com/blogs/christian/archive/2009/01/12/tuning-your-san-too-much-hba-queue-depth.aspx