Which tables are heavily used with MSSQL

  • Microsoft SQL Server keeps track of all usage, live through Dynamic Management Views [DMV] such as sys.dm_db_index_usage_stats.
  • These Dynamic Management Views are live, meaning that they reflect the the current usage, up to now.
  • These Dynamic Management Views accumulate statistics from the the time the SQL Server engine is started.

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Tables and index usage

  1. USE sql911;
  2. go
  3.  
  4. SELECT CAST(OBJECT_NAME(usg.OBJECT_ID) AS VARCHAR(30)) AS 'Table',
  5.        CAST(idx.name AS VARCHAR(30)) AS 'Index',
  6.        usg.user_seeks + usg.user_scans + usg.user_lookups AS 'Reads',
  7.        usg.user_updates AS 'Writes'
  8. FROM sys.dm_db_index_usage_stats AS usg
  9. INNER join sys.indexes AS idx
  10. ON usg.OBJECT_ID = idx.OBJECT_ID
  11. and idx.index_id = usg.index_id
  12. WHERE OBJECTPROPERTY(usg.OBJECT_ID,'isusertable') = 1
  13. ORDER BY 'Table';
  14. go
Table                          Index                          Reads                Writes
------------------------------ ------------------------------ -------------------- --------------------
DEPARTMENT                     NULL                           3                    0
EMP_WORK                       NULL                           2                    0
Emps                           NULL                           3                    0
Nums                           PK__Numbers__07F6335A          3                    0
ORG                            PK__ORG__0425A276              1                    0
projects                       PK__PROJECT__7D78A4E7          3                    0
sales                          NULL                           3                    0
sales02                        PK_sales_02                    3                    0
trans02                        NULL                           9                    0

(9 row(s) affected)
  • These statistics are reset to 0 when MSSQL Server is started.
  • Do not make critical decisions until you have all the stats needed.
  • Do not drop any index, just because the stats from sys.dm_db_index_usage_stats say that there was no usage.
  • A lot of operations, therefore usage, are done at month-end and year-end!
  • To get accurate picture, run these queries over time.

Why such low numbers?

  1. The Microsoft SQL Server was just started.
  2. This is a development SQL Server, and not a production server.