WHAT SQL TABLES AND INDEXES ARE TAKING UP SPACE IN MEMORY?

Ever have a SQL Server run low on memory? Unless you’re buying “big iron” for every server or never consolidate SQL instances, chances are you have. Ever wonder “What’s taking up all the memory?”, Probably. Luckily, with DMV’s introduced in SQL 2005, we can get a snapshot of the tables and indexes that are occupying memory inside the SQL Buffer Pool.

As we all know, indexes are a blessing for reading data from SQL, but not much thought is given to overhead associated with indexes. Not only is there more work to be done when an insert, update or delete on the base table (or clustered index), the newly created index will also start residing in memory as it is used in queries. Also, an index can become less useful over time as the columns in the index become less unique; resulting in index scans rather than index seeks. While this may cause query performance to suffer; in the context in memory, this causes more 8KB index pages to be read into memory, consequently bloating your Buffer Pool.

To view the tables and indexes that are taking up residence in you SQL Server memory, the following query can be ran from the master database. The query makes use of the sp_msforeachdb stored procedure to get the names of the objects in memory from each database . Below are some notes on the result set returned by the query:

·         This query will only return tables and indexes in memory from User databases. Master, Tempdb, MSDB, and Model are omitted.

·         Indexes with an ID of 1 are the clustered index for the table. Indexes with an ID of 0 are tables that do not have a clustered index and are referred to as Heaps. All others are non-clustered indexes.

·         Not only will you be able to see the size of each object in memory by megabytes, I’ve also included some usage statistics on those indexes. These statistics will give you an idea on how your index is being used and how effective it is.

·         Documentation on the system tables and DMV’s used in the query can be found below

o   sys.dm_db_index_usage_stats

o   sys.indexes

o   sys.partitions

o   sys.dm_os_buffer_descriptors

o   sys.allocation_units

·         This query does not return information on other objects in memory such as the ones that live in the Procedure Cache or those governed by the Lock Manager.

Query Below:

IF OBJECT_ID('tempdb..#Objects') IS NOT NULL
  DROP TABLE #Objects

 CREATE TABLE #Objects

    (
      ObjectName SYSNAME ,
      ObjectID INT ,
      IndexName SYSNAME ,
      IndexID INT ,
      BufferSize INT ,
      BufferCount INT ,
      DBName SYSNAME
    )
 GO

 EXEC sp_msforeachdb 'use ?;

 IF DB_ID(''?'') > 4
 insert into #Objects(ObjectName,
                      ObjectID,
                      IndexName,
                      IndexID,
                      BufferSize,
                      BufferCount,
                       DBName)
 SELECT OBJECT_NAME(P.Object_ID),
        P.Object_ID, 
        Coalesce(I.Name,''**Heap**''),   
        P.Index_ID,
        COUNT(*)/128, 
        COUNT(*),
        db_name()    
   FROM sys.allocation_units AS AU INNER JOIN sys.dm_os_buffer_descriptors AS OSB    
    ON AU.allocation_unit_id = OSB.allocation_unit_id    
                                     INNER JOIN sys.partitions AS P    
    ON AU.container_id = P.hobt_id 
                                    INNER JOIN sys.indexes AS I
    ON P.index_id = I.index_id
   AND P.object_id = I.object_id     
 WHERE OSB.database_id = DB_ID()    
 GROUP BY P.object_id, P.index_id,Coalesce(I.name,''**Heap**'')'
 GO

SELECT  #Objects.DBName ,

        #Objects.ObjectName AS TableName ,
        #Objects.IndexID,       
        #Objects.IndexName ,       
        #Objects.BufferSize ,
        #Objects.BufferCount ,
        IUS.user_seeks ,
        IUS.user_scans ,
        IUS.user_lookups ,
        IUS.user_updates ,
        IUS.last_user_seek ,
        IUS.last_user_scan ,
        IUS.last_user_lookup ,
        IUS.last_user_update ,
        IUS.system_seeks ,
        IUS.system_scans ,
        IUS.system_lookups ,
        IUS.system_updates ,
        IUS.last_system_seek ,
        IUS.last_system_lookup ,
        IUS.last_system_update ,
        IUS.last_system_scan
FROM    #Objects INNER JOIN sys.dm_db_index_usage_stats IUS
     ON #Objects.DBName = DB_NAME(IUS.database_id)
    AND #Objects.objectid = IUS.OBJECT_ID
    AND #Objects.indexid = IUS.index_id
ORDER BY 5 DESC

GO