query to find space usage of each column in a table

  • We a table of abnormal size and need to find usage by each column in the table.

    Below is the structure of the table:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T12]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[T12]

    GO

    CREATE TABLE [dbo].[T12] (

    [C1] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [C2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C3] [int] NOT NULL ,

    [C4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [C6] [int] NOT NULL ,

    [C7] [int] NOT NULL ,

    [C8] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [C536870912] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870913] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870914] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870915] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870916] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870917] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870918] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870919] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870920] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870921] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870922] [varchar] (130) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870923] [int] NULL ,

    [C536870924] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870925] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870926] [int] NULL ,

    [C536870927] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870928] [int] NULL ,

    [C536870929] [int] NULL ,

    [C536870930] [int] NULL ,

    [C536870931] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870933] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870934] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870935] [int] NULL ,

    [C536870936] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870937] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870938] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870940] [int] NULL ,

    [C536870944] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870945] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870946] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870947] [int] NULL ,

    [C900400000] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C901200000] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C901200001] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536871049] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C901200003] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C901200002] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870948] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870950] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870951] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870954] [int] NULL ,

    [C536870955] [int] NULL ,

    [C536870958] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870959] [int] NULL ,

    [C536870960] [int] NULL ,

    [C536870961] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870962] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870963] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870964] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870965] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870966] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870967] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870968] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870971] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870972] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [C536870973] [int] NULL ,

    [C536870974] [int] NULL ,

    [C536870975] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    CREATE UNIQUE CLUSTERED INDEX [IT12] ON [dbo].[T12]([C1]) WITH FILLFACTOR = 90 ON [PRIMARY]

    GO

    CREATE INDEX [I12_536870912_1] ON [dbo].[T12]([C536870912]) WITH FILLFACTOR = 90 ON [PRIMARY]

    GO

    /****** The index created by the following statement is for internal use only. ******/

    /****** It is not a real index but exists as statistics only. ******/

    if (@@microsoftversion > 0x07000000 )

    EXEC ('CREATE STATISTICS [hind_292_3] ON [dbo].[T12] ([C3]) ')

    GO

    /****** The index created by the following statement is for internal use only. ******/

    /****** It is not a real index but exists as statistics only. ******/

    if (@@microsoftversion > 0x07000000 )

    EXEC ('CREATE STATISTICS [hind_292_35] ON [dbo].[T12] ([C536870940]) ')

    GO

    =====================================================

    I tried to find total number of characters in each column and based on its data type tried to calculate space usage. But in the table most of the column is having NULL value and not able to find the number of characters in each table. Not sure whether all the values in those column are NULL>

    Any query to find the abnormal space usage of the table?

    BR,

    Parthi

  • Try this first... check Books Online for how to read the output...

    DBCC SHOWCONTIG (T12) WITH TABLERESULTS, ALL_INDEXES

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Then, try this on a couple of the VARCHAR(255) columns...

    SELECT *

    FROM T12

    WHERE DATALENGTH(columnname)-LEN(columnname) <> 0

    If you get any returns, it may be because data is being stored with trailing spaces and you just can't see them.... I reduced a 54 GByte table down to 8 GBytes using this method to find offending columns. It took a little longer to find the root cause. Turned out to be a trigger that was storing information produced as CHAR().

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    Thank you for your help..

    We already checked fragementation and there are no index and data fragementation as beow,

    DBCC SHOWCONTIG scanning 'T12' table...

    Table: 'T12' (1781581385); index ID: 1, database ID: 7

    TABLE level scan performed.

    - Pages Scanned................................: 160587

    - Extents Scanned..............................: 20149

    - Extent Switches..............................: 20150

    - Avg. Pages per Extent........................: 8.0

    - Scan Density [Best Count:Actual Count].......: 99.62% [20074:20151]

    - Logical Scan Fragmentation ..................: 0.00%

    - Extent Scan Fragmentation ...................: 1.86%

    - Avg. Bytes Free per Page.....................: 391.8

    - Avg. Page Density (full).....................: 95.16%

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    But the space usage of data is around 33GB for just 2 million records as follows,

    name rows reserved data index_size unused

    ==================================================================

    T122074897 34200264 KB 34111032 KB 84696 KB 4536 KB

    In the above table nearly 35 columns are having most of NULL values.

    we checked for the traling spaces in varchar(255) columns and there are no result set for the below query,

    SELECT *

    FROM T12

    WHERE DATALENGTH(columnname)-LEN(clolumnname) <> 0

    for the columns

    C536870921

    C536870944

    C536870945

    C536870922

    Can you please provide more details on your table space usage problem.

    BR,

    Parthi

  • What's in the TEXT column?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi,

    We do encounter a problem similar to this and it looks like the database has doubled (from 16GB to 32GB) right after the upgrade from SQLS 2000 to 2005.

    We do suspect the upgrade program is the root cause of this but, quit difficult to prove.

    Any idea about it ?

    Thanks.

  • Why not just do this?:

    Select

    SUM(Cast(DATALENGTH(C1) as BigInt)) as [C1]

    , SUM(Cast(DATALENGTH(C2) as BigInt)) as [C2]

    , SUM(Cast(DATALENGTH(C3) as BigInt)) as [C3]

    , SUM(Cast(DATALENGTH(C4) as BigInt)) as [C4]

    , SUM(Cast(DATALENGTH(C5) as BigInt)) as [C5]

    , SUM(Cast(DATALENGTH(C6) as BigInt)) as [C6]

    , SUM(Cast(DATALENGTH(C7) as BigInt)) as [C7]

    , SUM(Cast(DATALENGTH(C8) as BigInt)) as [C8]

    , SUM(Cast(DATALENGTH(C536870912) as BigInt)) as [C536870912]

    , SUM(Cast(DATALENGTH(C536870913) as BigInt)) as [C536870913]

    , SUM(Cast(DATALENGTH(C536870914) as BigInt)) as [C536870914]

    , SUM(Cast(DATALENGTH(C536870915) as BigInt)) as [C536870915]

    , SUM(Cast(DATALENGTH(C536870916) as BigInt)) as [C536870916]

    , SUM(Cast(DATALENGTH(C536870917) as BigInt)) as [C536870917]

    , SUM(Cast(DATALENGTH(C536870918) as BigInt)) as [C536870918]

    , SUM(Cast(DATALENGTH(C536870919) as BigInt)) as [C536870919]

    , SUM(Cast(DATALENGTH(C536870920) as BigInt)) as [C536870920]

    , SUM(Cast(DATALENGTH(C536870921) as BigInt)) as [C536870921]

    , SUM(Cast(DATALENGTH(C536870922) as BigInt)) as [C536870922]

    , SUM(Cast(DATALENGTH(C536870923) as BigInt)) as [C536870923]

    , SUM(Cast(DATALENGTH(C536870924) as BigInt)) as [C536870924]

    , SUM(Cast(DATALENGTH(C536870925) as BigInt)) as [C536870925]

    , SUM(Cast(DATALENGTH(C536870926) as BigInt)) as [C536870926]

    , SUM(Cast(DATALENGTH(C536870927) as BigInt)) as [C536870927]

    , SUM(Cast(DATALENGTH(C536870928) as BigInt)) as [C536870928]

    , SUM(Cast(DATALENGTH(C536870929) as BigInt)) as [C536870929]

    , SUM(Cast(DATALENGTH(C536870930) as BigInt)) as [C536870930]

    , SUM(Cast(DATALENGTH(C536870931) as BigInt)) as [C536870931]

    , SUM(Cast(DATALENGTH(C536870933) as BigInt)) as [C536870933]

    , SUM(Cast(DATALENGTH(C536870934) as BigInt)) as [C536870934]

    , SUM(Cast(DATALENGTH(C536870935) as BigInt)) as [C536870935]

    , SUM(Cast(DATALENGTH(C536870936) as BigInt)) as [C536870936]

    , SUM(Cast(DATALENGTH(C536870937) as BigInt)) as [C536870937]

    , SUM(Cast(DATALENGTH(C536870938) as BigInt)) as [C536870938]

    , SUM(Cast(DATALENGTH(C536870940) as BigInt)) as [C536870940]

    , SUM(Cast(DATALENGTH(C536870944) as BigInt)) as [C536870944]

    , SUM(Cast(DATALENGTH(C536870945) as BigInt)) as [C536870945]

    , SUM(Cast(DATALENGTH(C536870946) as BigInt)) as [C536870946]

    , SUM(Cast(DATALENGTH(C536870947) as BigInt)) as [C536870947]

    , SUM(Cast(DATALENGTH(C900400000) as BigInt)) as [C900400000]

    , SUM(Cast(DATALENGTH(C901200000) as BigInt)) as [C901200000]

    , SUM(Cast(DATALENGTH(C901200001) as BigInt)) as [C901200001]

    , SUM(Cast(DATALENGTH(C536871049) as BigInt)) as [C536871049]

    , SUM(Cast(DATALENGTH(C901200003) as BigInt)) as [C901200003]

    , SUM(Cast(DATALENGTH(C901200002) as BigInt)) as [C901200002]

    , SUM(Cast(DATALENGTH(C536870948) as BigInt)) as [C536870948]

    , SUM(Cast(DATALENGTH(C536870950) as BigInt)) as [C536870950]

    , SUM(Cast(DATALENGTH(C536870951) as BigInt)) as [C536870951]

    , SUM(Cast(DATALENGTH(C536870954) as BigInt)) as [C536870954]

    , SUM(Cast(DATALENGTH(C536870955) as BigInt)) as [C536870955]

    , SUM(Cast(DATALENGTH(C536870958) as BigInt)) as [C536870958]

    , SUM(Cast(DATALENGTH(C536870959) as BigInt)) as [C536870959]

    , SUM(Cast(DATALENGTH(C536870960) as BigInt)) as [C536870960]

    , SUM(Cast(DATALENGTH(C536870961) as BigInt)) as [C536870961]

    , SUM(Cast(DATALENGTH(C536870962) as BigInt)) as [C536870962]

    , SUM(Cast(DATALENGTH(C536870963) as BigInt)) as [C536870963]

    , SUM(Cast(DATALENGTH(C536870964) as BigInt)) as [C536870964]

    , SUM(Cast(DATALENGTH(C536870965) as BigInt)) as [C536870965]

    , SUM(Cast(DATALENGTH(C536870966) as BigInt)) as [C536870966]

    , SUM(Cast(DATALENGTH(C536870967) as BigInt)) as [C536870967]

    , SUM(Cast(DATALENGTH(C536870968) as BigInt)) as [C536870968]

    , SUM(Cast(DATALENGTH(C536870971) as BigInt)) as [C536870971]

    , SUM(Cast(DATALENGTH(C536870972) as BigInt)) as [C536870972]

    , SUM(Cast(DATALENGTH(C536870973) as BigInt)) as [C536870973]

    , SUM(Cast(DATALENGTH(C536870974) as BigInt)) as [C536870974]

    , SUM(Cast(DATALENGTH(C536870975) as BigInt)) as [C536870975]

    From T12

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I had these problems once with a coupld of tables.

    I divided the data_space with the number of rows and got an absurdly high value.

    I tried to find out why but I failed for quite some time. I believe it had to do with VARCHAR columns being converted into enourmous CHARs and then back again but it's nothing I can prove or could show.

    The solution was to copy all the data into a new table, drop the old one and rename the new one.

    Not a beautiful solution and nothing I would recomend most of the times but it brought down my table size from 220+ GB to roughly over 16 GB.

    And no, I didn't get that space from acidentally dropping indexes, thanks for thinking about it, I had all indexes and stats recreated. 🙂

    Best Regards,

    Hanslindgren

    P.S I am still curous where all this space was consumed from and how this could have been solved in another way...

  • Parthipan: How did this work out for you?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Lemme ask again... what's in the TEXT column?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the post - had trouble finding trailing spaces on an nchar field.

    SELECT count(Address5)

    FROM dbo.Location

    WHERE DATALENGTH(Address5)-LEN(Address5) > 2

Viewing 11 posts - 1 through 10 (of 10 total)

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