Previously, I introduced dynamic management views, which are useful tools for monitoring and troubleshooting SQL Server databases. As a follow-up here, I explore a few additional dynamic management views (DMVs) that database administrators can use to determine the effectiveness of current indexes, as well as to find their fragmentation levels. Additionally, I provide a DMV that should be used to retrieve the last statement executed by a given SQL Server process ID number (SPID).
DMVs for index effectiveness
As database administrators (DBAs) know, proper indexes can improve query performance and application scalability. Each additional index, however, adds overhead to the system, because SQL Server has to maintain these indexes as data is added, modified or removed from tables and views. Prior to implementing new indexes, you should examine database activity to ensure that you have only those indexes that improve performance of commonly executed queries. Note that SQL Server doesn't prevent you from having to build multiple indexes on the same column or set of columns. It also doesn't alert you that an index you're about to build will not optimize your queries.
Duplicate indexes do not benefit the system. So too, indexes that the SQL Server query optimizer cannot use to resolve queries provide no benefit. It is, therefore, crucial to know how frequently your indexes are used, along with their effect on query performance. Fortunately, SQL Server 2005 and 2008 include a DMV called sys.dm_db_index_usage_stats to measure the effectiveness of indexes. Like all DMVs, the contents of sys.dm_db_index_usage_stats are discarded when you restart your SQL Server instance. So if you want to collect index usage statistics over time, you should periodically copy the contents of this DMV to a user-defined table.
Each time an index is used for scan, this DMV increments the seek or lookup columns in SQL Server. The following query, for example, retrieves user tables and views with corresponding index usage statistics within the AdventureWorksDW sample database:
SELECT
object_name(a.object_id) AS table_name,
COALESCE(name, 'object with no clustered index') AS index_name,
type_desc AS index_type,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b
ON a.index_id = b.index_id
AND a.object_id = b.object_id
WHERE database_id = DB_ID('AdventureWorksDW')
AND a.object_id > 1000
Useful indexes will have the highest total in the user_seeks column. Take note of the column user_updates, which shows you the level of maintenance required for a given index. If you notice any indexes that are seldom used for user seeks, scans or lookups but are still frequently updated, the cost of maintaining them outweighs the benefits of keeping them.
Dynamic management functions and fragmented indexes
Data changes can cause your indexes to become fragmented, and high levels of fragmentation lessen the effectiveness of an index. As a result, SQL Server is forced to scan more index pages, and therefore queries are slower even when the index is used. To avoid the negative impact of fragmentation, a DBA can rebuild or defragment indexes. In previous versions of SQL Server, you had to use the DBCC SHOWCONTIG statement to obtain the index fragmentation level. This statement also had a WITH TABLERESULTS option to return results in an organized, table format.
As you can imagine, examining each index in a database with what can be thousands of tables is a monotonous task. Not to mention, performing this task manually is a poor use of a DBA's time. Instead, many DBAs have implemented an automated solution that populates a temporary table with the output of DBCC SHOWCONTIG. It then either rebuilds or defragments an index based on the index's fragmentation level.
This method, however, is outdated. Although DBCC SHOWCONTIG still exists in SQL Server 2005 and 2008, you should instead use the sys.dm_db_index_physical_stats dynamic management function (DMF). DBCC SHOWCONTIG does not support some of the new index features introduced with the latest versions of SQL Server and will probably soon be removed.
With sys.dm_db_index_physical_stats, you no longer have to create a temporary table to store results. Instead you have the latest fragmentation levels available in defined columns at any given time. The syntax of this DMF is as follows:
Sys.dm_db_index_physical_stats (
{database_id | NULL | DEFAULT | 0},
{object_id | NULL | DEFAULT | 0},
{index_id | NULL | 0 | -1 | DEFAULT},
{partition_number | NULL | 0 | DEFAULT},
{mode | DEFAULT | NULL} )
You can find the explanation of each parameter at SQL Server Books Online. Note that when specifying parameters for this DMF, you can use the system functions db_id() and object_id().
The following query returns fragmentation information for all indexes in all databases:
SELECT * FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL)
This next statement returns index fragmentation level for all indexes of a particular object:
SELECT * FROM sys.dm_db_index_physical_stats(6, 469576711, NULL, NULL, NULL)
You will receive results that look similar to the following:
| database_id |
object_id |
index_id |
partition_number |
| 6 |
469576711 |
1 |
1 |
| 6 |
469576711 |
1 |
1 |
| index_type_desc |
alloc_unit_type_desc |
index_depth |
| CLUSTERED INDEX |
IN_ROW_DATA |
3 |
| CLUSTERED INDEX |
LOB_DATA |
1 |
| index_level |
avg_fragmentation_in_percent |
fragment_count |
| 0 |
0.592592593 |
87 |
| 0 |
0 |
NULL |
| avg_fragment_size_in_pages |
page_count |
7.75862069 |
675 |
| NULL |
8396 |
| avg_page_space_used_in_percent |
record_count |
| NULL |
NULL |
| NULL |
NULL |
| ghost_record_count |
version_ghost_record_count |
| NULL |
NULL |
| NULL |
NULL |
| min_record_size_in_bytes |
max_record_size_in_bytes |
| NULL |
NULL |
| NULL |
NULL |
| avg_record_size_in_bytes |
forwarded_record_count |
| NULL |
NULL |
| NULL |
NULL |
Although the output is voluminous, this DMF allows you to retrieve only those columns in which you are interested. This is another upgrade from DBCC SHOWCONTIG, which does not allow you to retrieve a subset of columns.
Retrieving currently executing SQL statements
Many DBAs have used the DBCC INPUTBUFFER command with earlier versions of SQL Server to obtain the last SQL statement executed by a given connection. This statement, however, only returns the last 255 characters of the statement, which may not be the full statement. SQL Server 2005 and 2008 offer several alternatives to retrieve this information. The sys.dm_exec_sql_text and sys.dm_exec_requests DMVs could be partnered together, and the fn_get_sql() function, in conjunction with the same sys.dm_exec_requests DMV, are another option to obtain that last statement.
Here are several examples:
To find a statement executed by a specific session (53):
SELECT
SUBSTRING(b.text, (a.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(b.text)
ELSE a.statement_end_offset END
- a.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_requests a
CROSS APPLY fn_get_sql (a.sql_handle) b
WHERE a.session_id = 53
To get SQL statements submitted by all running or suspended sessions:
SELECT
a.session_id,
a.status,
a.start_time,
a.command,
SUBSTRING(b.text, (a.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(b.text)
ELSE a.statement_end_offset END
- a.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.status IN ('running', 'suspended')
You can find the full details of fn_get_sql, sys.dm_exec_requests and sys.dm_exec_sql_text at SQL Server Books Online. The above queries use statement_start_offset and statement_end_offset columns to retrieve only those SQL statements that are currently executing, even if they're enclosed within stored procedures or user-defined functions. If we had used DBCC INPUTBUFFER instead, we would obtain only the stored procedure or user-defined function name and execution parameters.
Baya Dewald is a database consultant who helps customers develop highly available and scalable applications with SQL Server and Analysis Services. Throughout his career. he has managed database administrator teams and databases of terabyte caliber. Baya's primary areas of expertise are performance tuning, replication and data warehousing. He can be reached at baya@bayasqlconsulting.com.
