locked
Table script RRS feed

  • Question

  • Does someone have the script to get top 10 tables in sql sever with max memory?


    Friday, August 5, 2016 10:16 AM

Answers

  • Hi saravana, Max Memory?

    I suppose you are talking about space used:

     
    SELECT   
    
    
         t.NAME AS TableName,  
         i.name as indexName,  
         p.[Rows],  
         sum(a.total_pages) as TotalPages,   
         sum(a.used_pages) as UsedPages,   
         sum(a.data_pages) as DataPages,  
         (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,   
         (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,   
         (sum(a.data_pages) * 8) / 1024 as DataSpaceMB  
     FROM   
         sys.tables t  
     INNER JOIN        
         sys.indexes i ON t.OBJECT_ID = i.object_id  
     INNER JOIN   
         sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  
     INNER JOIN   
         sys.allocation_units a ON p.partition_id = a.container_id  
     WHERE   
         t.NAME NOT LIKE 'dt%' AND  
         i.OBJECT_ID > 255 AND     
         i.index_id <= 1  
     GROUP BY   
         t.NAME, i.object_id, i.index_id, i.name, p.[Rows]  
     ORDER BY   
         Rows desc 

    • Marked as answer by saravana raj Friday, August 5, 2016 10:50 AM
    Friday, August 5, 2016 10:31 AM

All replies

  • Hi saravana, Max Memory?

    I suppose you are talking about space used:

     
    SELECT   
    
    
         t.NAME AS TableName,  
         i.name as indexName,  
         p.[Rows],  
         sum(a.total_pages) as TotalPages,   
         sum(a.used_pages) as UsedPages,   
         sum(a.data_pages) as DataPages,  
         (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,   
         (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,   
         (sum(a.data_pages) * 8) / 1024 as DataSpaceMB  
     FROM   
         sys.tables t  
     INNER JOIN        
         sys.indexes i ON t.OBJECT_ID = i.object_id  
     INNER JOIN   
         sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  
     INNER JOIN   
         sys.allocation_units a ON p.partition_id = a.container_id  
     WHERE   
         t.NAME NOT LIKE 'dt%' AND  
         i.OBJECT_ID > 255 AND     
         i.index_id <= 1  
     GROUP BY   
         t.NAME, i.object_id, i.index_id, i.name, p.[Rows]  
     ORDER BY   
         Rows desc 

    • Marked as answer by saravana raj Friday, August 5, 2016 10:50 AM
    Friday, August 5, 2016 10:31 AM
  • Can you define what you mean by "max memory"? Are you talking about size of the tables (disk)? Or how much memory each table is currently allocating in the buffer pool?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Sunday, August 7, 2016 1:00 PM