Re: Row size and DBCC SHOWCONTIG
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 09/27/04
- Next message: Paul fpvt2: "Re: Customized IIf procedure in SQL Server"
- Previous message: Patrick: "Restoring DTS structures file"
- In reply to: Amy: "Re: Row size and DBCC SHOWCONTIG"
- Next in thread: Amy: "Re: Row size and DBCC SHOWCONTIG"
- Reply: Amy: "Re: Row size and DBCC SHOWCONTIG"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 27 Sep 2004 17:01:29 -0400
OK I guess that this behavior is noted but not officially documented. See
this link:
http://www.nigelrivett.net/AlterTableProblems.html
Personally I would have thought the CI rebuild would have fixed it I guess
some of the rows can still be wacked. Short of creating a new table and
copying all the data in I don't think there is a solid solution.
--
Andrew J. Kelly SQL MVP
"Amy" <XXXNOSPAMXXX___l.a@usa.com> wrote in message
news:uzLTpLGpEHA.2304@TK2MSFTNGP14.phx.gbl...
> 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Paul fpvt2: "Re: Customized IIf procedure in SQL Server"
- Previous message: Patrick: "Restoring DTS structures file"
- In reply to: Amy: "Re: Row size and DBCC SHOWCONTIG"
- Next in thread: Amy: "Re: Row size and DBCC SHOWCONTIG"
- Reply: Amy: "Re: Row size and DBCC SHOWCONTIG"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
