Defrag and Index

I am by no means an SQL expert, I need to figure out if doing a nightly
reindex defrags my indexes. From the data below it seems pretty obvious it
doesn't, but I am unsure.
I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
mess. Is this correct?
Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
than enthusiastic about doing an online defrag.
Do I need to do an DBCC DBREINDEX
So to recap...
1) Does night reindex, defrag?
2) Is the index below a disaster?
3) What is the best way to get this defragged? Keeping the index online
is preferred, but not necessarily required.
========================================================
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 948
- Extents Scanned.......................: 204
- Extent Switches.......................: 203
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
- Extent Scan Fragmentation ...............: 99.51%
- Avg. Bytes Free per Page................: 889.5
- Avg. Page Density (full)................: 89.01%
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
LEAF level scan performed.
- Pages Scanned........................: 292
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 810.7
- Avg. Page Density (full)................: 89.98%
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul,
There are not to many pages, so I will not worry much. If you really want to
defrag that table, then you have to add a clustered index, or dump the data,
recreate the table and import the data.
AMB
"Paul Bergson [MVP-DS]" wrote:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious it
> doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
> than enthusiastic about doing an online defrag.
> Do I need to do an DBCC DBREINDEX
> So to recap...
> 1) Does night reindex, defrag?
> 2) Is the index below a disaster?
> 3) What is the best way to get this defragged? Keeping the index online
> is preferred, but not necessarily required.
> ========================================================> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> TABLE level scan performed.
> - Pages Scanned........................: 948
> - Extents Scanned.......................: 204
> - Extent Switches.......................: 203
> - Avg. Pages per Extent..................: 4.6
> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> - Extent Scan Fragmentation ...............: 99.51%
> - Avg. Bytes Free per Page................: 889.5
> - Avg. Page Density (full)................: 89.01%
> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> LEAF level scan performed.
> - Pages Scanned........................: 292
> - Extents Scanned.......................: 37
> - Extent Switches.......................: 36
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 810.7
> - Avg. Page Density (full)................: 89.98%
>
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||So the line
Extent Scan Fragmentation ...............: 99.51%
Is not bad?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> Paul,
> There are not to many pages, so I will not worry much. If you really want
> to
> defrag that table, then you have to add a clustered index, or dump the
> data,
> recreate the table and import the data.
>
> AMB
> "Paul Bergson [MVP-DS]" wrote:
>> I am by no means an SQL expert, I need to figure out if doing a nightly
>> reindex defrags my indexes. From the data below it seems pretty obvious
>> it
>> doesn't, but I am unsure.
>> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
>> a
>> mess. Is this correct?
>> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
>> less
>> than enthusiastic about doing an online defrag.
>> Do I need to do an DBCC DBREINDEX
>> So to recap...
>> 1) Does night reindex, defrag?
>> 2) Is the index below a disaster?
>> 3) What is the best way to get this defragged? Keeping the index
>> online
>> is preferred, but not necessarily required.
>> ========================================================>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
>> TABLE level scan performed.
>> - Pages Scanned........................: 948
>> - Extents Scanned.......................: 204
>> - Extent Switches.......................: 203
>> - Avg. Pages per Extent..................: 4.6
>> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
>> - Extent Scan Fragmentation ...............: 99.51%
>> - Avg. Bytes Free per Page................: 889.5
>> - Avg. Page Density (full)................: 89.01%
>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
>> LEAF level scan performed.
>> - Pages Scanned........................: 292
>> - Extents Scanned.......................: 37
>> - Extent Switches.......................: 36
>> - Avg. Pages per Extent..................: 7.9
>> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 810.7
>> - Avg. Page Density (full)................: 89.98%
>>
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Paul,
That number is not relevant to heaps (tables without clustered index) and is
meaningless when the index spans multiple files.
DBCC SHOWCONTIG
http://msdn2.microsoft.com/en-US/library/aa258803(SQL.80).aspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
AMB
"Paul Bergson [MVP-DS]" wrote:
> So the line
> Extent Scan Fragmentation ...............: 99.51%
> Is not bad?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> > Paul,
> >
> > There are not to many pages, so I will not worry much. If you really want
> > to
> > defrag that table, then you have to add a clustered index, or dump the
> > data,
> > recreate the table and import the data.
> >
> >
> > AMB
> >
> > "Paul Bergson [MVP-DS]" wrote:
> >
> >> I am by no means an SQL expert, I need to figure out if doing a nightly
> >> reindex defrags my indexes. From the data below it seems pretty obvious
> >> it
> >> doesn't, but I am unsure.
> >>
> >> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
> >> a
> >> mess. Is this correct?
> >>
> >> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> >> less
> >> than enthusiastic about doing an online defrag.
> >>
> >> Do I need to do an DBCC DBREINDEX
> >>
> >> So to recap...
> >> 1) Does night reindex, defrag?
> >> 2) Is the index below a disaster?
> >> 3) What is the best way to get this defragged? Keeping the index
> >> online
> >> is preferred, but not necessarily required.
> >>
> >> ========================================================> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> >>
> >> TABLE level scan performed.
> >>
> >> - Pages Scanned........................: 948
> >>
> >> - Extents Scanned.......................: 204
> >>
> >> - Extent Switches.......................: 203
> >>
> >> - Avg. Pages per Extent..................: 4.6
> >>
> >> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> >>
> >> - Extent Scan Fragmentation ...............: 99.51%
> >>
> >> - Avg. Bytes Free per Page................: 889.5
> >>
> >> - Avg. Page Density (full)................: 89.01%
> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> >>
> >> LEAF level scan performed.
> >>
> >> - Pages Scanned........................: 292
> >>
> >> - Extents Scanned.......................: 37
> >>
> >> - Extent Switches.......................: 36
> >>
> >> - Avg. Pages per Extent..................: 7.9
> >>
> >> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> >>
> >> - Logical Scan Fragmentation ..............: 0.00%
> >>
> >> - Extent Scan Fragmentation ...............: 0.00%
> >>
> >> - Avg. Bytes Free per Page................: 810.7
> >>
> >> - Avg. Page Density (full)................: 89.98%
> >>
> >>
> >> --
> >> Paul Bergson
> >> MVP - Directory Services
> >> MCT, MCSE, MCSA, Security+, BS CSci
> >> 2003, 2000 (Early Achiever), NT
> >>
> >> http://www.pbbergs.com
> >>
> >> Please no e-mails, any questions should be posted in the NewsGroup
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
>
>|||Paul Bergson [MVP-DS] (pbergson@.allete_nospam.com) writes:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious
> it doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
No, because that's not an index, but a heap, a table without a
clustered index. Heaps are quite prone to fragmentation, and your
table is not in the best shape with 4.6 pages per extent. But as
Alejandro pointed out the table is not that big, and it is not likely
to be a major problem.
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> less than enthusiastic about doing an online defrag.
Neither INDEXDEFRAG or DBREINDEX works on heap. You can build a clustered
index on the table and then drop it. Or you can just build a clustered index
and have it that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

defaults

Can someone give me a simple example of how you use the
defaults in sql server 2000? I cant figure it outSee following exmaple:
(while creating table)
CREATE TABLE multinulls (
a int NULL,
b char(5) default 'hello'
)
you can also create default as an object and bind it to table's column
Ex:
CREATE DEFAULT abc_const AS 'abc'
GO
sp_bindefault abc_const, 'test_defaults.char1'
GO
-Vishal
"bill" <bkelleman@.pcdata1.com> wrote in message
news:02a901c377be$aada4a30$a101280a@.phx.gbl...
> Can someone give me a simple example of how you use the
> defaults in sql server 2000? I cant figure it out|||What exactly do you mean? How to insert default values or How to set it up?
Setup.
For example Open EM, Create a table two columns
Column 1 name it Log_Date datetime with a default of getdate()
Column 2 name it test_data varchar(20)
Open the table and insert something on the test_data column click on the
exclamation point to execute
or simply use query analyzer
BEGIN TRANSACTION
CREATE TABLE dbo.test0001
(
log_date datetime NULL,
test_data varchar(20) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.TEST001 ADD CONSTRAINT
DF_Table1_log_date DEFAULT getdate() FOR log_date
GO
COMMIT
INSERT INTO TEST0001(TEST_DATA)
SELECT 'TEST1'
SELECT * FROM TEST0001
If you create a new default on a column to an existing table with data then
you need to do something like
insert into test0001 default values
select * from test0001
Hope this helps
Yovan Fernandez
"bill" <bkelleman@.pcdata1.com> wrote in message
news:02a901c377be$aada4a30$a101280a@.phx.gbl...
> Can someone give me a simple example of how you use the
> defaults in sql server 2000? I cant figure it out