Post reply

How to find memory usage by index in sql server?

  • October 4, 2015 at 2:58 am

    #301186

    hi all,

    i want to create a lot of index for my database for performance.

    but i need find memory usage by indexes

    How to find memory usage by index in sql server?

  • October 5, 2015 at 10:36 pm

    #1831590

    Hi Grasshopper,

    you may use the query below to check which index/es consumes most buffer pool memory.

    USE DB_Name;

    GO

    ;WITH src AS

    (

    SELECT

    [Object] = o.name,

    [Type] = o.type_desc,

    [Index] = COALESCE(i.name, ''),

    [Index_Type] = i.type_desc,

    p.[object_id],

    p.index_id,

    au.allocation_unit_id

    FROM

    sys.partitions AS p

    INNER JOIN

    sys.allocation_units AS au

    ON p.hobt_id = au.container_id

    INNER JOIN

    sys.objects AS o

    ON p.[object_id] = o.[object_id]

    INNER JOIN

    sys.indexes AS i

    ON o.[object_id] = i.[object_id]

    AND p.index_id = i.index_id

    WHERE

    au.[type] IN (1,2,3)

    AND o.is_ms_shipped = 0

    )

    SELECT

    src.[Object],

    src.[Type],

    src.[Index],

    src.Index_Type,

    buffer_pages = COUNT_BIG(b.page_id),

    buffer_mb = COUNT_BIG(b.page_id) / 128

    FROM

    src

    INNER JOIN

    sys.dm_os_buffer_descriptors AS b

    ON src.allocation_unit_id = b.allocation_unit_id

    WHERE

    b.database_id = DB_ID()

    GROUP BY

    src.[Object],

    src.[Type],

    src.[Index],

    src.Index_Type

    ORDER BY

    buffer_pages DESC;

    Best Regards,

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply