Posts Tagged ‘Dynamic Management View’

SQL Server – How to get last access/update time for a table

11.03.2014 2 comments

Modify date and create date for a table can be retrieved from sys.tables catalog view. When any structural changes are made the modify date is updated. It can be queried as follows:

USE [SqlAndMe]

SELECT    [TableName] = name,
FROM    sys.tables
WHERE    name = 'TransactionHistoryArchive'



sys.tables only shows modify date for structural changes. If we need to check when was the tables last updated or accessed, we can use dynamic management view sys.dm_db_index_usage_stats. This DMV returns counts of different types of index operations and last time the operation was performed.

It can be used as follows:

USE [SqlAndMe]

SELECT    [TableName] = OBJECT_NAME(object_id),
last_user_update, last_user_seek, last_user_scan, last_user_lookup
FROM    sys.dm_db_index_usage_stats
WHERE    database_id = DB_ID('SqlAndMe')
AND        OBJECT_NAME(object_id) = 'TransactionHistoryArchive'



last_user_update – provides time of last user update

last_user_* – provides time of last scan/seek/lookup

It is important to note that sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted.


Hope This Helps!


SQL Server – Server-related dynamic management views in SQL Server 2008 SP1 and “Denali”

29.07.2011 1 comment

There are three new server-related DMVs introduced in SQL Server 2008 R2 Service Pack 1 and SQL Server Codename "Denali".


1. sys.dm_server_services:

– For SQL Server 2008 R2 SP1, it contains information about SQL Server and SQL Server Agent services for the current instance.

– For "Denali", it returns information about SQL Server, Full-Text Search and SQL Server Agent services for the current instance. Let’s use below query on both:

SELECT servicename          AS [Name],

       startup_type_desc    AS [Type],

       status_desc          AS [Status],

       process_id           AS [ProcessID],

       service_account      AS [Startup Account]

FROM   sys.dm_server_services

Result Set for SQL Server 2008 R2 SP1:

Name                           Type       Status         ProcessID   Startup Account

————————–     ——-    ——         ———   —————

SQL Server (MSSQLSERVER)       Automatic  Running        2492        .sqladmin

SQL Server Agent (MSSQLSERVER) Manual     Stopped        NULL        .sqladmin


(2 row(s) affected)

Result Set for "Denali":

Name                       Type    Status   ProcessID  Startup Account

————————– ——  ——   ———  —————

SQL Server (DENALI)        Manual  Running  5176       .sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .sqladmin


(3 row(s) affected)

Ah!, for "Denali" it’s returning two entries for SQL Server Agent, and not returning the Full-Text Search service, (it is installed), a possible "Denali" bug (Anyone else got the same issue?).


2. sys.dm_server_registry:

– For both SQL Server 2008 R2 SP1 and "Denali" – this view returns configuration and installation information, such as host machine or network configuration for the current instance, i.e. information we used to fetch using xp_instance_regred for an instance, Let’s execute below query on both:

SELECT registry_key, value_name, value_data

FROM   sys.dm_server_registry

WHERE  value_name = N'CurrentVersion'

Result Set for SQL Server 2008 R2 SP1:

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLMSoftware…MSSQLServerCurrentVersion   CurrentVersion       10.50.2500.0


(1 row(s) affected)

Result Set for "Denali":

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLMSoftware…MSSQLServerCurrentVersion    CurrentVersion       11.0.1440.19


(1 row(s) affected)


3. sys.dm_server_memory_dumps:

This view returns a list of memory dump files generated by the SQL Server Database Engine. It returns below information:

i) filename – File name and physical path of the memory dump file,

ii) creation_time – date and time the memory dump file creation,

iii) size_in_bytes – memory dump file size in bytes.


Hope This Helps!


Using SQL DMVs – sys.dm_db_file_space_usage

06.06.2011 No comments

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.

sys.dm_db_file_space_usage has following columns:

1. database_id – will always be 2, database id of tempdb.

2. unallocated_extent_page_count – total number of free extents in the database file

3. version_store_reserved_page_count – pages allocated for version store

4. user_object_reserved_page_count – pages allocated for user objects, these are the permanent object you create explicitly in tempdb, system tables, temporary tables, table variables…

5. internal_object_reserved_page_count – pages allocated for internal objects

for details about other columns returned refer BOL.


Let’s see it in action:

— © 2011 – Vishal (


SELECT      *

FROM        sys.dm_db_file_space_usage


SELECT      *

INTO        #TempTable2

FROM        AdventureWorks2008R2.HumanResources.Employee


SELECT      *

FROM        sys.dm_db_file_space_usage

Result Set:

database_id file_id unallocated_extent_page_count version_store_reserved_page_count user_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count

———– ——- —————————– ——————————— ——————————- ———————————– ———————–

2           1       134832                        0                                 64                              0                                   136


(1 row(s) affected)


(290 row(s) affected)


database_id file_id unallocated_extent_page_count version_store_reserved_page_count user_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count

———– ——- —————————– ——————————— ——————————- ———————————– ———————–

2           1       134824                        0                                 72                              0                                   136


(1 row(s) affected)

The user_object_reserved_page_count changed to 72 from 64 after creating a temporary table.

And you can also see that the free extents available decreased from 134832 to 134824.


If you need to check currently available free space in tempdb, you can use below query:

SELECT      (unallocated_extent_page_count * 1.0) / 128

            AS [FreeSpcae (MB)]

FROM        sys.dm_db_file_space_usage

Result Set:

FreeSpcae (MB)




(1 row(s) affected)

Hope This Helps!


Recently Executed Queries – Using DMVs/DMFs

06.05.2011 No comments

To find the queries that were executed recently using the sys.dm_exec_query_stats DMV.


SELECT sql_handle, creation_time, last_execution_time
FROM   sys.dm_exec_query_stats
WHERE  last_execution_time > '2011-05-06 06:45:00.000'


The above statement will return the performance statistics for cached query plans. sys.dm_exec_query_stats contains the sql handle of the statement which was executed. To retrieve the actual query text, you need to use a DMF – sys.dm_exec_sql_text as follows:




This functions takes the sql/plan hadle as input and returns the sql text. CROSS APPLY can be utilized add query text to the output:


SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > '2011-05-06 19:15:00.000'
ORDER BY      STATS.last_execution_time DESC




Hope This Helps!