Re: Row size and DBCC SHOWCONTIG

From: Amy (XXXNOSPAMXXX___l.a_at_usa.com)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 09:23:51 +0200

Hi andrew,

Thanks again for your reply.
My original table has >3M rows.
I have changed the data types drastically - from a total row size of about
500 Bytes to less than 200 Bytes.
There were NUMERICSs that i've changed to SMALL/TINY INTs, DATETIMEs to
SMALLDATETIMEs etc.
Judging by the time it took to complete i can say it did alter the physical
structure. It took several minutes.
I also see a decrease in the DB and table size (although it's far from
linear to the the data type size decrease).
Execution plans for queries run against the altered table DO correctly show
the new estimated row size.
Since I had to drop all dependent objects before issuing the ALTER COLUMN,
the clustered index was dropped and afterwards recreated.
That too did not update the new row size.
I provided the script just to demonstrate the issue. It's the same with
large tables.
This is so annoying.

Any more ideas who to fix this? I've ran out...

Thanks again,

Amy

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:%237PTn9BpEHA.3712@TK2MSFTNGP10.phx.gbl...
> Amy,
>
> Sorry but I didn't even see the script the first time<g>. I should have
> scrolled down but thought that was all there was. I don't know how
accurate
> that test is due to the fact you are only talking about 1 row. That can
> have several implications and makes showcontig pretty much useless. But
in
> any case SQL Server most likely only changed the meta data and didn't
> actually touch the row. Some times an alter column does not actually need
> to change the physical data on the page directly after an alter. It
changes
> the meta data and will address the actual data later.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Amy" <XXXNOSPAMXXX___l.a@usa.com> wrote in message
> news:e04o7yApEHA.1460@TK2MSFTNGP12.phx.gbl...
> > Hi Andrew,
> >
> > Thanks for your reply.
> >
> > Yes, I have a clustered index on my real table.
> > The script i provided did not create any constraints or indexes only
> because
> > you need to remove dependent objects before you can alter the columns
and
> i
> > wanted to keep it short and simple.
> > I have checked it before posting.
> > It behaves the same way with or without a clustered index.
> >
> > Amy
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> > news:OUG9nPApEHA.3988@tk2msftngp13.phx.gbl...
> > > Do you have a clustered index on that table? If it is a heap you can
> > > reindex all you want and it won't affect the rows themselves.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Amy" <l.a@usa.com> wrote in message
> > > news:eo8zdX%23oEHA.3640@TK2MSFTNGP10.phx.gbl...
> > > > Hi All,
> > > >
> > > > I am having a hard time understanding when does SQL Server update
> > metadata
> > > > in sysindexes.
> > > >
> > > > I investigated a given schema and found out that data types were
> abused
> > > and
> > > > I want to change it.
> > > > After I execute "ALTER TABLE ALTER COLUMN" to change the datatypes,
> > > > DBCC SHOWCONTIG (and other features) seem to still think that the
row
> is
> > > > much longer.
> > > > I've tried rebuilding indexes, updating usage and statistics but
it's
> > like
> > > > the original values refuse to be updated.
> > > >
> > > > What am I missing?
> > > >
> > > > Here is a simple reproduction script:
> > > >
> > > > USE TEMPDB
> > > > GO
> > > >
> > > > CREATE TABLE T1 (Col1 CHAR(100), col2 CHAR(100), col3 CHAR(100))
> > > > GO
> > > >
> > > > INSERT T1 VALUES ('aaaa','bbbb','cccc')
> > > > GO
> > > >
> > > > SELECT minlen,xmaxlen FROM sysindexes WHERE id = OBJECT_ID('T1')
> > > > GO
> > > >
> > > > -- Look at min/max/avg Record size
> > > > DBCC SHOWCONTIG (T1) WITH TABLERESULTS
> > > > GO
> > > >
> > > > ALTER TABLE T1 ALTER COLUMN col1 CHAR(10)
> > > > ALTER TABLE T1 ALTER COLUMN col2 CHAR(10)
> > > > ALTER TABLE T1 ALTER COLUMN col3 CHAR(10)
> > > > GO
> > > >
> > > > SELECT minlen,xmaxlen FROM sysindexes WHERE id = OBJECT_ID('T1')
> > > > GO
> > > >
> > > > -- Look at min/max/avg Record size
> > > > DBCC SHOWCONTIG (T1) WITH TABLERESULTS
> > > > GO
> > > >
> > > > -- Try to update metadata
> > > > EXEC sp_updatestats
> > > > EXEC sp_spaceused 'T1', 'true'
> > > > DBCC UPDATEUSAGE ('TempDB','T1')
> > > > GO
> > > >
> > > > SELECT minlen,xmaxlen FROM sysindexes WHERE id = OBJECT_ID('T1')
> > > > GO
> > > >
> > > > -- Look at min/max/avg Record size
> > > > DBCC SHOWCONTIG (T1) WITH TABLERESULTS
> > > > GO
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Performance degradation after changing data type
    ... changes were made with 4 successive ALTER TABLE ALTER COLUMN statements ... clustered index, it might help (and be faster than creating a clustered ... I also think that using ALTER TABLE ALTER COLUMN to revert to REAL will ... So chances are you have lots of fragmentation due to the ...
    (microsoft.public.sqlserver.server)
  • Re: Row size and DBCC SHOWCONTIG
    ... Andrew J. Kelly SQL MVP ... > Since I had to drop all dependent objects before issuing the ALTER COLUMN, ... >> Amy, ... >> Sorry but I didn't even see the script the first time. ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance degradation after changing data type
    ... I've put a clustered index on here. ... >changes were made with 4 successive ALTER TABLE ALTER ... fragmentation due to the ... That will defrag ...
    (microsoft.public.sqlserver.server)
  • Re: Update One Based on ANother Table
    ... On Thu, 13 Oct 2005 15:50:35 GMT, Mike wrote: ... >The table I am working was imported and the data types were nvarchar but I ... ALTER TABLE MyTable ... ALTER COLUMN MyColumn new_datatype ...
    (comp.databases.ms-sqlserver)
  • Re: Adding Not Null with Default column to large Table
    ... Perhaps you could re-index the clustered index for the table ... The ALTER may be going slowly because your ... > 2) Running multiple updates to the table ...
    (microsoft.public.sqlserver.server)
Loading