find size iof the index

  • Hello frineds, i use SQL S2205. Now can someone help me with a query that will show me the indexes on a database that has large sizes eg. more than atleast 500 MB.

  • any ideas friends?

  • courtesy of Mr. Barry Young

    /*

    vwTableInfo - Table Information View

    This view display space and storage information for every table in a

    SQL Server 2005 database.

    Columns are:

    Schema

    Name

    Ownermay be different from Schema)

    Columnscount of the max number of columns ever used)

    HasClusIdx1 if table has a clustered index, 0 otherwise

    RowCount

    IndexKBspace used by the table's indexes

    DataKBspace used by the table's data

    16-March-2008, RBarryYoung

    31-January-2009, Edited for better formatting

    */

    --CREATE VIEW vwTableInfo

    -- AS

    SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]

    , tbl.Name

    , Coalesce((Select pr.name

    From sys.database_principals pr

    Where pr.principal_id = tbl.principal_id)

    , SCHEMA_NAME(tbl.schema_id)) as [Owner]

    , tbl.max_column_id_used as [Columns]

    , CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]

    , Coalesce( (Select sum (spart.rows) from sys.partitions spart

    Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]

    , Coalesce( (Select Cast(v.low/1024.0 as float)

    * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    Where i.object_id = tbl.object_id )

    , 0.0) AS [IndexKB]

    , Coalesce( (Select Cast(v.low/1024.0 as float)

    * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    Where i.object_id = tbl.object_id)

    , 0.0) AS [DataKB]

    , tbl.create_date, tbl.modify_date

    FROM sys.tables AS tbl

    INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)

    INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')

    order by datakb desc

    ---------------------------------------------------------------------

  • Rather impatient aren't you... that said try this (Contributed by one unknown to me, prior to your asking the question. To this individual we both owe a hearty THANK YOU.

    Select obj.name,

    (Select Sum(((used * 8192.00)/1024))

    from sysindexes

    where id in (select objChild.id from sysobjects objChild where objChild.name = obj.name)

    and indid in(0,1)

    ) TableSizeKb,

    (Select Sum(((used * 8192.00)/1024)) from sysindexes where

    id in (select objChild.id from sysobjects objChild where objChild.name = obj.name)

    and indid not in (255,0,1)

    ) IndexSizeKb

    From sysobjects obj

    Where type = 'U'

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks bitbucket-25253 the output is what i want but just need to add one column that says table name.

    Name Tablesizekb Indexsizekb

    pe_lymph_detail_ 44856.0000000 3856.0000000

    ORT_Surg_Schedule_25728.00000006920.0000000

    but if one more column that says Table name then would be great. Please can you get me a script that will show table name also.

    I appreciate you work.

    Thanks

  • I owe bitbucket a favour so:

    the 'name' column is the name of the table.

    for clarity you could change the first line of the query to

    Select obj.name as 'table',

    barrys code also lists the table name.

    ---------------------------------------------------------------------

  • Use the code posted by george sibbald-364359 to this forum.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (11/10/2009)


    Use the code posted by george sibbald-364359 to this forum.

    you can call me just george sibbald. Despite that darned number suffix which I hate I am the only george sibbald on the forum. 🙂

    ---------------------------------------------------------------------

  • Anthony, this might be more what you originally asked for:

    Select o.name as 'table', i.indid,i.name as 'index',i.used * (8192.00/1024) as 'index size in KB' from sysindexes i

    inner join sysobjects o on i.id = o.id

    where i.indid not in (255,0)

    and o.type = 'U'

    --and i.used > 64000

    order by o.name,i.used desc

    the commented out line will limit it to indexes > 500MB (= 64000 pages)

    remember indid = 1 is the clustered index and therefore actually the size of the table.

    ---------------------------------------------------------------------

  • george sibbald-364359 (11/10/2009)


    you can call me just george sibbald. Despite that darned number suffix which I hate I am the only george sibbald on the forum. 🙂

    George,

    You can change that via the Control Panel

    It's registration details you want. This link might take you straight there: https://www.sqlservercentral.com/MyAccount

  • A 2005-specific version:

    ;WITH NonclusteredIndexes

    AS (

    SELECT [schema_name] = SCHEMA_NAME(SO.[schema_id]),

    [object_name] = OBJECT_NAME(PS.[object_id]),

    index_name = SI.name,

    used_MB = FLOOR(SUM(used_page_count) / 128.0)

    FROM sys.dm_db_partition_stats PS

    JOIN sys.objects SO

    ON SO.[object_id] = PS.[object_id]

    JOIN sys.indexes SI

    ON SI.[object_id] = PS.[object_id]

    AND SI.index_id = PS.index_id

    WHERE SI.type_desc = N'NONCLUSTERED'

    GROUP BY

    SCHEMA_NAME(SO.[schema_id]),

    OBJECT_NAME(PS.[object_id]),

    SI.name

    )

    SELECT *

    FROM NonclusteredIndexes

    WHERE used_MB >= 500

    ORDER BY

    used_MB DESC;

  • thanks for the tip Paul but unfortunately when I say I am the only george sibbald on the forum that is true, but the nickname george sibbald is taken, a I registered again when I changed jobs a few years back. Unfortunately the original unused account got to keep the name.

    I tried to get it changed via the webmaster, but no reply.

    Its a shame as I have 19 points on that old account 🙂

    ---------------------------------------------------------------------

  • Check your messages George! 😉

  • Hey Paul, I heard from steve, the wheels are in motion. Thanks a lot mate 😀

    ---------------------------------------------------------------------

  • yea, I got my name back!

    😀

    ---------------------------------------------------------------------

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

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