find index fragmentation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

find index fragmentation

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-07 : 23:48:39

in 2005 i use sys.dm_db_index_physical_stats to find fragmentation of indexes...

in 2000 is there a way to return the fragmentation ( as a value) of an index to be stored into a variable ...

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-08 : 00:34:25

DBCC INDEXDEFRAG will give u same information in SQL 2000. These DMVs are new features of SQL 2005

Madhu

Kristen
Test

22859 Posts

Posted - 2007-01-08 : 03:02:01

"in 2000 is there a way to return the fragmentation"

Have a look at DBCC SHOWCONTIG

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-08 : 17:57:37

okay ive rebuilt the index but its not changing in 2000 do i need to update stats?

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-08 : 18:35:48

to rebuild an index this is what im doing

USE blabla
DBCC SHOWCONTIG('Edeposit')
DBCC DBREINDEX('Edeposit')
DBCC SHOWCONTIG('Edeposit')

to see a before and after, and the scan density is still 75% why isnt it changing

propanecan
Yak Posting Veteran

60 Posts

Posted - 2007-01-08 : 19:01:07

How many pages used on that table? Mind posting the entire results set?

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-08 : 19:17:04

- Pages Scanned................................: 41
- Extents Scanned..............................: 8
- Extent Switches..............................: 7
- Avg. Pages per Extent........................: 5.1
- Scan Density [Best Count:Actual Count].......: 75.00% [6:8]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 1392.2
- Avg. Page Density (full).....................: 82.80%

propanecan
Yak Posting Veteran

60 Posts

Posted - 2007-01-08 : 19:37:39

I may not always get a very high scan density on tables with less than 1000 pages.

How many indexes for this table? Clustered/non-clustered?

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-08 : 19:47:42

also just on another note, is it normal when reindexing itll got from 33% > 50%

then when u reindex again itll go from 50% > 33%

and will keep doing this exact swap in values each time

Kristen
Test

22859 Posts

Posted - 2007-01-08 : 19:57:37

The fragmentation is fairly meaningless with very few pages, as propanecan said.

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-08 : 20:02:13

how many pages do you guys beging to consider working with

Kristen
Test

22859 Posts

Posted - 2007-01-08 : 20:20:33

propanecan said 1000

If you are not sure if your Reindex is working re-try on your largest table.

We use Reindex for up to 10,000 pages, and Index Defrag for files bigger than that.

For the example you have shown, 41 pages, I shouldn't think SQL Server performance is going to be effected one way or the other - the whole table will be loaded into memory in pretty much one bite of the file anyway!

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-08 : 20:27:51

thanx fellas helps alot

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-09 : 02:11:01

i don't think 1000 is the best number as most if not all our pages are less then that...

could that be an indiciation of poor design or just different?

Kristen
Test

22859 Posts

Posted - 2007-01-09 : 04:16:17

No, its just an indication that you have a small database (or at least none of the tables are "large", in which case optimisation isn't going to make much difference - doesn't mean you shouldn;t do it! just that its probably not worth getting clever about HOW you do it.

For example, assuming that you do NOT have thousands of tables, i.e. your whole database is small (less than 100MB for example), I suggest you just REINDEX every table without worrying whether it is fragmented or not.

For example, you could use the Maintenance Wizard and get that to do the optimisation

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-09 : 18:10:49

the strange thing is the database is huge its 130GIG datafiles

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-09 : 18:16:43

When your database is that large, you do want to run DBCC DBREINDEX instead of DBCC INDEXDEFRAG in SQL Server 2000. I'd suggest touching only a few indexes per night.

Tara Kizer

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-09 : 18:35:29

thank you i will give that a go... it doesnt take much longer then 10 minutes to run script

Kristen
Test

22859 Posts

Posted - 2007-01-10 : 04:47:44

"the strange thing is the database is huge its 130GIG datafiles"

Is that the physical size of the database files (MDF / LDF), or the actual "used" part of the database?

The size of a FULL backup file would be a good indication of the actual used size.

If the Used size is 130GB I can't see how your largest index would only have 41 pages

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-11 : 23:06:31

okay this is the script im using (next post) if anyone is interested in trying it...

it will rebuild indexes less then < 90% density, and will show you a before and after shot after its done.

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-11 : 23:06:55

CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL,
)

CREATE TABLE #fraglist1 (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL,
)
CREATE TABLE #statstable (
DatabaseName VARCHAR(40),
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
ScanDensity DECIMAL,
NewScanDensity DECIMAL,
LogicalFrag DECIMAL,
NewLogicalFrag DECIMAL,
CountPages INT
)

Create Table #temp
(
seqno int identity(1,1),
sqltext varchar(8000)
)

exec sp_msforeachdb'

Create Table #temp
(
seqno int identity(1,1),
sqltext varchar(8000)
)

DECLARE @sql nvarchar(4000), @i int, @maxrows int

if (''?'') <> ''tempdb'' and (''?'') <> ''msdb'' and (''?'') <> ''model'' and (''?'') <> ''master''
BEGIN
USE ?

Insert #temp
Select ''DBCC SHOWCONTIG (''+so.name+'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'' from
sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type =''U''
AND si.indid < 2
AND si.rows > 0
AND so.name NOT LIKE ''% %''

select @maxrows = count(seqno)
from #temp

set @i = 0

While @i < @maxrows
begin
Select @sql = sqltext from #temp where seqno = @i

INSERT INTO #fraglist
Exec(@sql)

set @i = @i + 1
end

insert into #statstable
select DatabaseName=''?'',ObjectName,ObjectId,IndexName,IndexID,ScanDensity,0,LogicalFrag,0,CountPages
from #fraglist
where scandensity < 90 --and CountPages > 1000

truncate table #temp

Insert #temp
Select ''DBCC DBREINDEX (''+ObjectName+'')'' from
#statstable,sysobjects where ObjectName = sysobjects.name

select @maxrows = count(seqno)
from #temp

set @i = 0

While @i < @maxrows
begin
select @sql = sqltext from #temp where seqno = @i

INSERT INTO #fraglist
Exec(@sql)

set @i = @i + 1
end

truncate table #temp

Insert #temp
Select ''DBCC SHOWCONTIG (''+so.name+'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'' from
sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type =''U''
AND si.indid < 2
AND si.rows > 0
AND so.name NOT LIKE ''% %'' --avoid poorly designed fields

select @maxrows = count(seqno)
from #temp

set @i = 0

While @i < @maxrows
begin
Select @sql = sqltext from #temp where seqno = @i

INSERT INTO #fraglist1
Exec(@sql)

set @i = @i + 1
end

END
'

update #statstable
set NewScanDensity = (select TOP 1 fl1.ScanDensity
from #fraglist1 fl1
where fl1.ObjectId = #statstable.ObjectId
and fl1.IndexId = #statstable.IndexId)

update #statstable
set NewLogicalFrag = (select TOP 1 fl1.LogicalFrag
from #fraglist1 fl1
where fl1.ObjectId = #statstable.ObjectId
and fl1.IndexId = #statstable.IndexId)

select * from #statstable
where scandensity <> newscandensity

drop table #fraglist
drop table #fraglist1
drop table #statstable
drop table #temp

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources