SQL Server 2000 Defragmenting index problem. 1

[peterh3244]

Background

Recently index corruption has been occuring on one of our datebase tables.
Basically when trying to execute a select statement which orders by date only entries show that were entered on the day of corruption. Without the order by clause all data appears to be returned, but obviously not in the order we want it. To fix the problem we have to delete all that days ‘inserts’ and then re-create the indexes on the table. Finally the the data for that day is re-inserted into the table. Everything is fine for about three months and then the problem occurs again.

Investigation has established that the disk that the MDF resides on is 89% defragmented and the attempt at defraging it were unsuccessful. The LOG resides on another disk and again was fragmented at 50%, defragmentation of this disk was unsuccessfull.

During the investigation we created a SQL job that was supposed to alert us of table indexes that need defraging. Once defraged, the job was run again. This time not only indexes that were fragmented were re-reported BUT other indexes that were reported as OK were now being reported at fragmentation levels of above 30%.

As a number of unexpected results occurred during the index investigation I have documented it with any Questions we have.

Any answers to the question would be gratefully received as would any theories on why what has happened as happended

15/02/2011

1) The defrag index program was modified to create the frag table permanently to allow analysis of any voyager fragmented indexes.
2) The index defrag program was run (see Appendix A).
3) The table YesterdaysDiaries was reported as having indexes that fragmented over thirty percent,
4) The table YesterdaysDiaries was defraged using the command given.
5) The defrag index program was run again and although the database was not in use, not only was the table YesterdaysDiaries reported as being in need of defragmentation (which means the defrag had no affect maybe!) but other indexes of tables were reported as needing to be defraged. 4 tables in all.
6) The disks holding the database and log files required were defragmented.
7) There was no problem defragmenting the disk holding the log file.
8) However there was a problem defragmenting the disk holding the MDF file.
9) Therefore the database was shrunk. Using the SHRINK command. Four gigabytes was removed from the database.
10) Still the disk holding the MDF file could not be defraged.
11) MDF file was then copied to a scratch drive (and then deleted) from the disk to be defraged.
12) The disk was then defragmented successfully
13) MDF file then was copied back to the disk. Note the dragmentor still reported that MDF file was fragmented in 7 places However defragmentration was still a success!
14) The defrag program for indexes was again run
15) This time there were about 300 or more indexes returned as being defragmented.

16/2/2011

1. Correct all the defrag issues using recommeded command
2. UPDATE STATISTICS APPLIED for all tables using sp_updatestats
3. Re-run the defrag job.
4. Obsevered that although some table indexes which where reported as fragmented there were a less of these than before. Now 239 indexs out 994 are fragmented (24%). 24% of indexes are still fragmented
6. Backup database

Questions to answer

1. Does creating and dropping a new table (frag) cause fragmentation of the indexes. Would this be proved by modifying the program to create the frag table in a completely different database?
2. Why did the defraging command have no effect on YesterdaysDiaries?
3. Did the defragmentation of the disk cause 300 odd indexes to be fragmented?
4. OR was this fragmentation caused by the running of the SHRINK command.
5. Was the update statistics responsible for the reduction in the number of the indexes reported as fragmented?


Appendix A


CREATE PROCEDURE sp_Defrag

AS

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @NAME VARCHAR(512)
DECLARE @BUF VARCHAR(8000)
declare @msg1 varchar(255)
declare @msg2 varchar(255)
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fraglist]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE fraglist
END
-- Create the table
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)
-- Open the cursor
OPEN tables
select @@servername
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO FRAGLIST
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM FRAGLIST
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
set @Buf = @@servername + ' The Database is Fragmented.'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @name =[name] FROM [dbo].[sysobjects]
where id = RTRIM(@objectid)
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
set @msg1 = @execstr + ' -- (' + @NAME + ') ' + ' fragmented by ' + RTRIM(CONVERT(varchar(15),@frag)) + '%'
IF LEN(@BUF + CHAR(13) +CHAR(10) + ' ') >= 8000
BEGIN
set @msg2 = 'Database Status on' + @@Servername
EXEC master..xp_sendmail @recipients = '',
@copy_recipients = '*********',
@subject = @msg2,
@message = @BUF
set @buf = @@servername + ' The Voyager Database is Fragmented.' + CHAR(13) + CHAR(10) + ' ' + @msg1
END
ELSE
BEGIN
set @buf = @buf + CHAR(13) + CHAR(10) + ' ' + @MSG1
END
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
EXEC master..xp_sendmail @recipients = '****************',
@copy_recipients = '*****************',
@subject = @msg2,
@message = @BUF
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes


Many thanks Peter

 

[gmmastros]

I would suggest that you stop shrinking your database. This can lead to horrendous fragmentation issues.

File fragmentation is not the same thing as index fragmentation. Please do not confuse them.

To reduce file fragmentation, stop shrinking your database. You should also take a long hard look at your database. Check the auto-grow setting. Is it set to a really low value? If so, change it. I think it's better to set the database to auto-grow by size (not percent). Make the size something relatively large compared to the size of your database. Then, try not to let the database auto-grow. Auto-grow is a relatively slow process. You should monitor the size and free space in your database and manually grow the size instead of allowing it to auto-grow. By setting the size manually, you can cause it to happen when the database activity is low. This applies to the log file also.

Index fragmentation is caused by data. Think about a single page in a phone book. Suppose the page is full and the first name is "Smith, John" and the last name is "Smith, Zachary". Now suppose you want to add "Smith, Mary". The data should be added to this page in the book, but there is no room for it. In this situation, SQL Server would create a new page and move 1/2 the data from the original page to the new one (this is called a page split). Excessive page splits leads to fragmentation.

Please run this and post the output here:

Select   OrigFillFactor, Count(*) As IndexCount
From     sysindexes
Group By OrigFillFactor

Fill Factor is a setting that SQL Server uses when creating/recreating indexes. A Fill Factor of 0 is the same as 100%. This means that all available space will be used. If your indexes are getting fragmented, you should adjust the fill factor for the index. For example, if your fill factor is 0, set the fill factor to 90. If it's already 90, set it to 80. Keep adjusting the fill factor so that your indexes do not get overly fragmented.

What I mean is... if you reindex once a week, and some of your indexes are highly fragmented at the end of the week, then change the fill factor for those indexes.

When you change the fill factor, be aware that your database will get larger. Think of it this way. If you were to re-create the phone book with a fill factor of 90, this means that each page would have 10% of the rows available for new data. This means you have some wiggle room to add additional data, but it also means your phone book will require more pages, too.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[peterh3244]

Thanks for you response its really helpful, I have asked our support team a few question, and responsed to the one you asked,

1) IT Support have stated that the database is only shrunk on an odhoc basis when needed it is needed (once a quarter at most - for SQL replication). Basically this to enable the database to be sent down the line when out of sync.

2) We understand that file fragmention is different to index fragmentation as index fragmentation occurs within the MDF file. However are we right in thinking that file fragmentation will not help performance?

3) Our database auto grow is set to 10%, Our database is 35 gig (estimate). Should we set the auto grow to 35 meg. Or should we set this higher in your opinon.

4) As requested the output from

Select OrigFillFactor, Count(*) As IndexCount
From sysindexes
Group By OrigFillFactor

is

OrigFillFactor, IndexCount
0 , 879
90 , 3671


from your comments we should set the ones at 0 to 90 and those at 90 to 80?

Although our database will get larger (care disk space)

Lastly we are going to discuss the idea of manuaslly growing the database.


Lookling forward to your reply

 

[gmmastros]

1. That's good, but maybe it can be better. Personally, the only time I shrink a database is after I've done a huge delete, and I think the database will be considerably smaller. My rule of thumb is... if I expect the database to shrink by approximately 1/2, then I will manually shrink the DB.

2. File fragmentation is less important than index fragmentation. If you grow your database by a relatively large amount, you will have less file fragmentation. Personally, I doubt you need to worry about this.

3. Personally, I would set the auto grow to approximately 5 or maybe even 10 gigs. Essentially, you should grow your file to accommodate what you estimate the size to be in a relatively long time (1 year?).

4. This is a harder question to answer. Some index will naturally NOT get fragmented. This usually happens when you have an index based on an identity column, and you rarely (if ever) delete from the table. Other indexes will experience more fragmentation. It really does depend on the keys you are using for the indexes and the nature in which the data is changing. Do you have a lot of inserts, updates, and/or deletes? If so, you will probably want to have a lower fill factor.

I would suggest that you take a look at your existing indexes. Specifically, check to see what the fragmentation level is prior to re-indexing. If the fragmentation is high, then lower the fill factor. If the index is NOT fragmented, then don't bother changing the fill factor.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[peterh3244]

Thanks for your help, I will propose these changes at the meeting next week.

 

[LyleU]

George,

I have been lurking on Tek-Tips for some time and there is no end to the useful information that I have been able to glean. Thanks for a clear and concise explanation of fragmentation in SQL Server databases.


Lyle
----
"For every expert, there is an equal and opposite expert." - Arthur C. Clarke.