Re: Row size and DBCC SHOWCONTIG
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 09/26/04
- Next message: Andrew J. Kelly: "Re: Filegroups and BLOBs"
- Previous message: a: "Re: Foreign key indexes - to delete?"
- 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: Sun, 26 Sep 2004 19:20:16 -0400
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: Andrew J. Kelly: "Re: Filegroups and BLOBs"
- Previous message: a: "Re: Foreign key indexes - to delete?"
- 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
|
