April 20, 2008 at 8:01 pm
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
April 20, 2008 at 9:40 pm
Try this first... check Books Online for how to read the output...
DBCC SHOWCONTIG (T12) WITH TABLERESULTS, ALL_INDEXES
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2008 at 9:47 pm
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
Change is inevitable... Change for the better is not.
April 20, 2008 at 11:48 pm
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
April 21, 2008 at 5:49 am
What's in the TEXT column?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2008 at 1:08 pm
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.
April 23, 2008 at 3:09 pm
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]
April 28, 2008 at 8:18 am
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...
April 28, 2008 at 8:43 am
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]
April 28, 2008 at 5:09 pm
Lemme ask again... what's in the TEXT column?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2008 at 12:25 pm
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