Just the other day I was asked by a Business Line Manager about the usage patterns of tables in a database that I never seen before. I was lucky that the version of the database was 2005 and Dynamic Management Views existed that stored the requirement information.
Today, I am going to review a simple SELECT query that will retrieve the information that we want.
The first requirement is to list the schema names that the user tables belong to. We can obtain this information by looking at the sys.schemas system view.
The second requirement is to enumerate all the user tables to show their names and creation dates. We can obtain this information by querying the sys.objects system view. We will want to filter the returned data set for only user tables (‘U’). Ordering the results by table name is nice feature.
The third requirement is to display the row counts for all clustered indexes (id=1) or heaps (id=0). This count is equal to the number of records in the table. This information can be retrieved from the sys.indexes system view.
The fourth requirement is to display the number of updates, look ups, seeks, and scans that occurred against these base indexes. Date time stamps associated with these actions will tell us table usage patterns. The sys.dm_db_index_usage_stats Dynamic Management View (DMV) will supply us these numbers.
The TSQL below will return the information that we want.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
-- Which database to use. USE [PUBS_DW] GO -- Counters are cleared every time SQL Service is re-started SELECT -- Table, index, row cnt, creation date E.name as [schema_name], O.Name as [table_name], O.CrDate as [create_date], I.Rows as [rows_in_table], I.Name as [index_name], -- How many? S.user_updates, S.user_lookups, S.user_seeks, S.user_scans, -- When was it last S.last_user_update, S.last_user_lookup, S.last_user_seek, S.last_user_scan FROM sys.schemas E INNER JOIN sys.sysobjects O ON E.schema_id = O.uid INNER JOIN sys.sysindexes I ON O.id = I.id LEFT JOIN sys.dm_db_index_usage_stats S ON S.index_id = I.indid and S.object_id = O.id WHERE -- User tables O.type = 'U' AND -- Heap = 0, Clustered = 1, > 1 = All others I.IndId < 2 -- Current database AND S.database_id = DB_ID() ORDER BY O.Name |
The following output was generated on a new data warehouse [PUBS_DW] that I created. I will be reviewing that schema in the near future to demonstrate data warehousing design patterns.
In a nutshell, the TSQL statement above will tell you enough information to determine if a table is ACTIVE or INACTIVE. Also, you can determine if the table is being updated and/or queried.
Just remember, the counters for this DMV are reset every time the SQL Server Service is re-started. Therefore, this query will return no rows after a reboot of the Windows Server OS.
