THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » SQL Server » Dynamic Management Objects   (RSS)
  • 2008: Rebuilding a Heap

    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 ...
    Posted to Louis Davidson (Weblog) by drsql on February 26, 2008
  • sys.dm_db_index_operational_stats

      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 ...
    Posted to Louis Davidson (Weblog) by drsql on August 26, 2007
  • sys.dm_exec_query_optimizer_info

    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: ) Type: View Data: Accumulating ...
    Posted to Louis Davidson (Weblog) by drsql on August 6, 2007
  • sys.dm_io_virtual_file_stats

    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 ...
    Posted to Louis Davidson (Weblog) by drsql on July 27, 2007
  • sys.dm_db_index_usage_stats

    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 ...
    Posted to Louis Davidson (Weblog) by drsql on July 22, 2007
  • sys.dm_db_file_space_usage

    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 ...
    Posted to Louis Davidson (Weblog) by drsql on July 14, 2007
  • sys.dm_db_partition_stats

    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 ...
    Posted to Louis Davidson (Weblog) by drsql on July 11, 2007
  • Blogging the DMV's

    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 ...
    Posted to Louis Davidson (Weblog) by drsql on July 10, 2007
  • Automatic indexing is something to tell you what indexes aren't used

    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.  ...
    Posted to Louis Davidson (Weblog) by drsql on October 12, 2006
Privacy Statement