Louis Davidson : Dynamic Management Objects

2008: Rebuilding a Heap

drsql — Wed, 27 Feb 2008 04:10:24 GMT

In 2005, rebuilding a table that was a heap (no clustered index) wasn't easy. You could copy it to a different table, or you could add a clustered index and then drop it.

In 2008, this is a far easier thing to do. They have added to the ALTER TABLE command a method to rebuild the table, which is the same as rebuilding the clustered index for a clustered table, but for a HEAP, it is the only way to go.

In the following code sample, I create a heap, load it will "little data", then expand every row to much larger values. The result is a little bit of fragmentation, but more than that, tons of forwarding pointers (when a row won't fit on the same heap page, it gets moved to a different page, but the pointer to the row does not change in the indexes.) Rebuilding the heap is now really simple:

create table heapDemo
(
value varchar(1000)
)
GO
set nocount on
insert into heapDemo
select 'hi'
go 10000
--Expand the values to 500 times the size they were
update heapDemo
set value = replicate('hi',500)

Now, check the stats of the table (using the index stats dmv, no less)

select index_type_desc, fragment_count, page_count, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,'DETAILED')
where object_id = object_id('heapDemo')

This returns:

index_type_desc fragment_count page_count forwarded_record_count
--------------------- -------------------- -------------------- ----------------------
HEAP 6 1443 9961

Now, you can rebuild the heap with the command:

alter table heapDemo rebuild

Check the values now:

select index_type_desc, fragment_count, page_count, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,'DETAILED')
where object_id = object_id('heapDemo')

This shows no that the forwarded_record_count is 0, which is the idea situation for your heap.

index_type_desc fragment_count page_count forwarded_record_count
--------------------- -------------------- -------------------- ----------------------
HEAP 3 1440 0

Nice new addition!

sys.dm_db_index_operational_stats

drsql — Sun, 26 Aug 2007 20:12:00 GMT

This object provides very useful stats on how many times an index has been used, locked, waited on, etc. An index in this context can mean several things: a clustered index, heap, index, or a partition of either of these. The grain of the function is down to the partition level, so if you are working with a table that is partitioned into five parts, it will return 5 rows (In the previous section, sys.dm_db_index_usage_stats would have seen the object as only a single row)

This object will give you a deep feel for how indexes are being used, and just exactly how much the index is costing you. It does this in part by telling you how often the index is modified at the Leaf level or non-leaf level, as well as how often users waited on blocks associated with the object, which can mean SQL Server locks, or even hardware or I/O latches.

Type: function

Parameters: database_id , object_id, index_id, partition_number (each parameter can be NULL or DEFAULT if you want to return all rows)

Data: accumulating refreshed when server is restarted or (perhaps obviously) when the index is dropped and recreated. Statistics live on when the index is rebuild, reorganized, and even when it is disabled and rebuilt.

Columns:

database_id, object_id, index_id -identifies the index in question
partition_number - 1 for non-partitioned tables. For partitioned tables will be the 1 – based number of the partition.
leaf_insert_count – the number of times a new row was added to the leaf of the index
leaf_delete_count – the number of times a row was deleted from the leaf
leaf_update_count – the number of changes to index keys for this index
leaf_ghost_count – the number of ghost records that have been marked as deleted but not cleaned up by SQL Server (http://www.sql-server-performance.com/tips/glossary_p1.aspx)
nonleaf_insert_count, nonleaf_delete_count, nonleaf_update_count – Same as the leaf page values, except for the b-tree index pages
leaf_allocation_count, nonleaf_allocation_count – The number of page allocated to the leaf and non-leaf pages of the index, respectively.
leaf_page_merge_count, nonleaf_page_merge_count – The number of pages in the index that have been merged into a single page.
range_scan_count - number of times the index has been used in a range scan operator
singleton_lookup_count – number of times the index has been used to fetch a single row
forwarded_fetch_count – for a heap, shows the number of forward pointers have been used to resolve a query (http://sqlblog.com/blogs/hugo_kornelis/archive/2006/11/03/The-table-scan-from-hell.aspx)
lob_fetch_in_pages, lob_fetch_in_bytes – Quantifies the number of large object (varchar(max), varbinary(max), text, etc) have been retrieved using this index
lob_orphan_create_count, lob_orphan_insert_count – total number of large object pages orphaned by bulk operations
row_overflow_fetch_in_pages , row_overflow_fetch_in_bytes – Quantifies the amount of data retrieved from row overflow pages for rows greater than one page in size
column_value_push_off_row_count, column_value_pull_in_row_count – Number of pages of large object or row overflow pages that have been moved by an in-row data operation
row_lock_count – number of row locks that have been requested against this index
row_lock_wait_count – number of times a process has waited on a row lock against this index
row_lock_wait_in_ms – amount of time spent waiting on a row lock against this index
page_lock_count, page_lock_wait_count, page_lock_wait_in_ms – same as row_lock values at the page grain
index_lock_promotion_attempt_count, index_lock_promotion_count – number of times the lock grain for an operation using this index was attempted or granted to be escalated (like from row to page)
page_latch_wait_count , page_latch_wait_in_ms – number of waits and time waited on the physical page of the object to have the latch removed
page_io_latch_wait_count, page_io_latch_wait_in_ms - number of waits and time waiting on and I/O operation on a physical page of the object to be completed
This object is related to the sys.dm_db_index_usage_stats in that they both provide information about how the index is used. This object, however, gives more detailed information about the inner workings of how the index is used at a physical level, whereas the usage stats is more of a feel for how it is used by the optimizer to satisfy the needs of certain queries. Use usage stats if you want counts of each usage, as each usage counts as 1. The operational stats object may have multiple values set for each type of activity recorded. (http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx)

Examples:

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

Note: This is part of an ongoing project to write a book about all of the dynamic management views for Red-Gate. It will be freely distributable once complete as an ebook. Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book. Also, I will begin maintaining the following web page once the book is closer to completion: http://drsql.org/dmvbook.aspx.

sys.dm_exec_query_optimizer_info

drsql — Tue, 07 Aug 2007 00:40:55 GMT

Information about how queries have been optimized since the server has been restarted. Note that counters are only affected when there is some sort of optimization event, not on every query. (reference: http://sqlserver-qa.net/blogs/perftune/archive/2007/05/11/get-statistics-on-query-plan-optimizer-execution.aspx )

Type: View

Data: Accumulating reset at restart

Columns:

counter - Name of the query
occurrence – Number of times the counter was recorded to
value – May or may not have some value, but is typically an average of the values that were recorded when the counter was written to (for a complete list of counters, check the books online documentation on sys.dm_exec_query_optimizer_info (1))

Example:

As an example, on a low use server, I restarted the server and executed:

select counter,occurrence, value
from sys.dm_exec_query_optimizer_info

You will get values like:

counter           occurrence    value
----------------- ------------- --------------------
optimizations     1             1
elapsed time      1             0.0235776097702821
final cost        1             4.1957E-05

This tells you the number of statements that have been optimized and a plan created (the same value is for all three of the displayed counters), the average elapsed time spent compiling in the value column for the elapsed time row, and finally, the average final cost in the final cost row. Using these counters and some of the others you will discover will help you to determine how often statements ae being compiled.

Execute it again, it will give you the same values (assuming you are the only user, of course.) Now, execute the same query, this time making it uppercase, forcing another complication, since plans must match in every way:

SELECT COUNTER, OCCURRENCE, VALUE
FROM SYS.DM_EXEC_QUERY_OPTIMIZER_INFO

You will see that the occurrences have increased, and there may be some difference in the average time it took:

counter           occurrence    value
----------------- ------------- --------------------
optimizations     2             1
elapsed time      2             0.020978588737036
final cost        1             4.1957E-05

---------------------------------------

sys.dm_io_virtual_file_stats

drsql — Sat, 28 Jul 2007 01:26:00 GMT

Excellent dmv that shows, for each file that SQL Server uses for the databases, stats on how frequently the file has been used by the database. This is one of the primary dynamic management views I use almost daily (well, not usually when I am on vacation, but even sometimes then!) The file can be any file used in the database, including the log or full text files.

Type: Function

Parameters:

database_id – key of the database, retrieved from sys.databases
file_id - key of a file in a database. Can be retrieved from sys.database_files if you are working in the context of a database, or sys.master_files will give you all files in all databases

Columns:

database_id, file_id – same as the parameter descriptions
sample_ms – the number of milliseconds that have passed since the values for sys.dm_io_virtual_file_stats were reset the only way to reset the values is to restart the server.
num_of_reads – number of individual read operations that were issued to the file. Note that this is physical reads, not logical reads. Logical reads would not be registered.
num_of_bytes_read – the number of bytes that were read, as opposed to the number of reads. The size of a read is not a constant value that can be calculated by the number of reads.
Io_stall_read_ms – total time user processes waited for IO. Note that this number can be much greater than the sample_ms. If 10 processes are trying to use the file simultaneously, but the disk is only able to server 1, then you might get 9 seconds waiting over a 10 second time period.
num_of_writes , num_of_bytes_written, io_stall_write_ms - the same as the read values, except for writes.
io_stall – sum of io_stall_write_ms and io_stall_read_ms
size_on_disk_bytes – the size of the file in bytes
file_handle – the Windows file handle of the file (Books Online)

Example:

For all databases, get vital stats on how busy the file has been, since the last restart:

select db_name(mf.database_id) as databaseName, mf.physical_name,
       num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes,
       num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes
from sys.dm_io_virtual_file_stats(null,null) as divfs
         join sys.master_files as mf
              on mf.database_id = divfs.database_id
                 and mf.file_id = divfs.file_id

sys.dm_db_index_usage_stats

drsql — Mon, 23 Jul 2007 02:45:12 GMT

This object gives statistics on how an index has been used to resolve queries. Most importantly it tells you the number of times a query was used to find a single row (user_seeks), a range of values, or to resolve a non-unique query (user_scans ), if it has been used to resolve a bookmark lookup (user_lookups) and how many changes to the index (user_updates. Note that sys.dm_db_index_operational_stats will give the details of how it has been modified.)

It returns all indexes (including heaps and the clustered index) from the entire server for each index that has been used, though you will usually only want to use it for one database since you will have to look up the name of the index in sys.indexes. There will not be a row in sys.dm_db_index_usage_stats unless the index has been used since creation or since SQL Server has been restarted.

Type: view

Data: accumulating, refreshed when server is restarted or (perhaps obviously) when the index is dropped and recreated. Statistics live on when the index is rebuild, reorganized, and even when it is disabled and rebuilt.

Columns:

database_id – primary key of the database
object_id - the object_id of the table the index belongs to
index_id - the primary key of the index
user_seeks – the number of times the index has been used in a user query in a seek operation (one specific row)
user_scans – the number of times the index has been used by scanning the leaf pages of the index for data
user_lookups – for clustered indexes only, this is the number of times the index has been used in a "bookmark lookup" to fetch the full row. This is because non-clustered indexes use the clustered indexes key as the pointer to the base row.
user_updates – The number of times the index has been modified due to a change in the table's data.
last_user_seek – The date and time of the last user seek operation
last_user_scan – The date and time of the last user scan operation
last_user_lookup – The date and time of the last user lookup operation
last_user_update – The date and time of the last user update operation
system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update - Same as the user query columns, but records when the index is used for a system operation, such as automatic statistics operations.

This is one of the most interesting views that I often use in performance tuning. It gives you something that no previous version of SQL Server did (at least in an easy to discover manner for the "average" dba, that I know of): The ability to tell when indexes are NOT being used. It is easy to see when an index is being used by a query by simply looking at the plan. But now, using this dynamic management view, you can see over time what indexes are used, not used, and probably more importantly, updated many many times and never being used.

Examples:

Create table, do a few queries in tempdb. Table and full code will be in final book.

--returns all indexes for a database and their stats.
--Rows with no usage since the last restart will be null

select object_schema_name(indexes.object_id) + '.' + object_name(indexes.object_id) as objectName,
         indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc,
         ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates
from sys.indexes
               left outer join sys.dm_db_index_usage_stats ddius
                        on indexes.object_id = ddius.object_id
                             and indexes.index_id = ddius.index_id
                             and ddius.database_id = db_id()
order by ddius.user_seeks + ddius.user_scans + ddius.user_lookups desc

sys.dm_db_file_space_usage

drsql — Sat, 14 Jul 2007 19:35:20 GMT

Give space usage of objects in tempdb (most likely this will be extended to more than just tempdb in a future edition of SQL Server.) Can be used to see how and why space is being used in Tempdb, on a file by file basis.

Type: View

Data: temporal, reflects the current state of the file usage

Columns:

database_id – identifies the database (relates to sys.databases) (NOTE: only includes tempdb in 2005)
file_id – the file identifier (relates to sys.database_files)
unallocated_extent_page_count – Total number of pages that are located on unallocated extents (8 contiguous 8K pages) that are reserved in the file but not currently allocated to objects. Note that unused pages on extents that have any active data on them will not be reflected in the total.
version_store_reserved_page_count – Number of pages that are reserved to support snapshot isolation transactions.
user_object_reserved_page_count - Number of pages reserved to user tables
internal_object_reserved_page_count - Number of pages reserved to internal objects, such as work tables that SQL Server creates to hold intermediate results
mixed_extent_page_count – Number of extents that have pages of multiple types (user objects, version store, or internal objects, Index Allocation Map (IAM) pages, etc.)

Example:

The following query will show the number of pages allocated to each file in your tempdb, and how much space is allocated to the various purposes, or unallocated.

select mf.physical_name, mf.size as entire_file_page_count,
          dfsu.unallocated_extent_page_count,
          dfsu.user_object_reserved_page_count,
          dfsu.internal_object_reserved_page_count,
          dfsu.mixed_extent_page_count
from sys.dm_db_file_space_usage dfsu
          join sys.master_files as mf
                   on mf.database_id = dfsu.database_id
                         and mf.file_id = dfsu.file_id

sys.dm_db_partition_stats

drsql — Thu, 12 Jul 2007 01:41:00 GMT

For the current database, gives you space oriented statistics for each partition of indexes (even if you only have one partion), like row count, page counts, etc. Resembles the sysindexes in previous versions of SQL Server, with more information.

Type: view

Data: accumulating, refreshed at server restart

Scope: Reset on server restart (or object/partition drop and recreate)

Columns:

partition_id - numeric identifier of the partition (relates to sys.partitons)
object_id - the object_id of the object that the partition belongs to
index_id - identifies the index on the table. 0 = Heap
partition_number - 1 for non-partitioned tables. For partitioned tables will be the 1 – based number of the partition.
in_row_data_page_count - the number of pages being used for data of the object. Can be the leaf pages of an index or the data pages of a clustered table or heap
in_row_used_page_count - Includes all pages in use for the object, including non-leaf index and index allocation map pages.
in_row_reserved_page_count - Includes all pages in use plus any pages reserved for the object, even if the pages are not currently in use.
lob_used_page_count - Count of pages in use for the object to store out-of-row values such as varchar(max), varbinary(max), etc.
lob_reserved_page_count – Count of out of the row pages including any that are reserved but not in use.
row_overflow_used_page_count - Count of pages that are in use for storing overflow data for rows that are larger than will fit on a single ~8K page
row_overflow_reserved_page_count - Count of overlow pages that includes any pages that are reserved but not in use
used_page_count - Total number of pages in use in the partiton for any reason
reserved_page_count - Total number of pages in use or reserved in the partiton for any reason
row_count – The number of rows in the table

Examples:

Get rowcount of tables. Note that I grouped on the object_id, because for a partitioned table, you need to add all of the rows in all partitions.

select object_name(dm_db_partition_stats.object_id),
           sum(dm_db_partition_stats.row_count) as row_count
from sys.dm_db_partition_stats
              join sys.indexes
                     on indexes.object_id = dm_db_partition_stats.object_id
                          and indexes.index_id = dm_db_partition_stats.index_id
where indexes.type_desc in ('CLUSTERED','HEAP')
group by dm_db_partition_stats.object_id

Other planned examples include a sample partition to show the rowcounts/usage, and summations of some of the other values to give a meaningful data other than rowcount.

SQLBlog Tags: SQL Server, Dynamic Management Objects, T-SQL

Blogging the DMV's

drsql — Tue, 10 Jul 2007 04:14:00 GMT

Well, I am working on a project that is going to be a book on dynamic management views (and functions, but DMF is an interesting acronym, and objects, as in DMO, has a well known other meaning. What I will post will be one object in the following format:

Name of object

Type: Function or View

Parameters: If it is a function

Scope: Really denotes how long the data's life is for. Many of the dynamic management functions are used to record some event, like an index being use for a query. There is not usually (well, I haven't finished cataloging them, but so far there is never) any way to reset the values without rebooting the server. There are tools to use to mitigate the issue, (like Tom Davidson's DMVStats tool that Kalen mentions here,) but the first time you look at the data in these objects it will be a bit confusing if you don't know how long the data has been gathered.

Columns: A listing for all of the columns that are output by the DMV, including a description

Example Usage: A query or two regarding possible uses

Then finally, there will be a section of the book that has a list of ways you can use the DMV's to diagnose common things about your server.

The book will be very reasonably priced (or not priced) and you will hear more about that over the next few weeks. What I am asking of you (and you over there to the left) is for your comments/likes, dislikes, and especially query ideas. I will include your query in the book (or coalesce it with others) and everyone who comments constructively will be mentioned in the acknowledgements of the book. I will also include any links you might have to DMV related queries in the Bibliography (which already has 16 items in it from just getting started.)

I will be posting these to my http://sqlblog.com/blogs/louis_davidson/default.aspx site only, since comments there can be entered without a passport. I will make mention of it on my http://drsql.spaces.live.com blog and provide more loose commentary about the writing process.

Automatic indexing is cool...plus something to tell you what indexes aren't used

drsql — Thu, 12 Oct 2006 22:28:27 GMT

Now, I will admit up front that I haven't technically tried out all of their bits and pieces of the following tool, but over the past week I have made a lot of use of some of the indexing suggestions by "hacking" out the code that generates the index create statements from the sys.dm_db_missing_index_group_stats dynamic management view. Let me just say upfront: "Wow!" I have seen some very wild index suggestions come from it, and today I took a few of the examples and applied them. We saw a 30-50% improvement over the test without the indexes.

The blog was: Fun for the day - Automated Auto-Indexing! and was posted back in June! I have been dying to try it out, but I am generally not one for building elaborate testing scenarios, so it has been a blog entry just circling waiting to see the light.

Well, over the last few days we have really started hammering on my companies product, preparing to ship it and one of the tools I used was their auto-indexing query that you can download from that blog entry. It uses 3 dynamic management views that are awfully cool: sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details to build a set of actual create table statements (ok, a few other tables are involved, but still)

I am not promising you it will be the answer to all of your indexing problems, especially since it only considers what indexes it would have liked to have had to answer a query. It doesn't have a clue what your read/write ratio is. I hope to try out the full blown automatic index generation package with SQL Agent jobs to automatically tune my data warehouse and operation data stores in the future. Any place with only reads (when not loading, of course) should be almost a no brainer...

So you add all of these indexes to improve performance, but as you know, indexes improve performance, but they also hurt write performance. In this blog:

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

The Microsoft team with the only name longer than the name of my book (Microsoft SQL Server Development Customer Advisory Team) gives lots of interesting queries to assist with performance tuning. For example, this query:

--- rarely used indexes appear first

declare @dbid int

select @dbid = db_id()
select objectname = object_name(s.object_id)
       , s.object_id
       , indexname = i.name
       , i.index_id
       , user_seeks
       , user_scans
       , user_lookups
       , user_updates
from sys.dm_db_index_usage_stats s
           join sys.indexes i
               on i.object_id = s.object_id
                    and i.index_id = s.index_id
where database_id = @dbid
and objectproperty(s.object_id , 'IsUserTable') = 1
order by (user_seeks + user_scans + user_lookups + user_updates) asc

Lists how often indexes are used, from least to most. Obviously these are tools that we need to consider using when we tune a new system, but we will certainly want to really watch stuff like this during early days when a system hits production to look at trimming back indexes that are purely for performance that are never being used for such. Stats like this are useless unless you have a totally full load and have gone through real life situations, unlike the previous, “what might have helped” queries from the automatic indexing stuff.

SQL Server keeps getting better and better, and these sorts of thing are some of the primary evidence. If you haven't started to look at the dynamic management views, you should. If only to google them and check out what the blogs are saying about them.

Crossposted to http://drsql.spaces.live.com