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
 SQL Server Administration (2000)
 Fragmentation - best practice

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-06-08 : 11:23:14
promise not to laugh!

I have just been made aware of DBCC SHOWCONTIG and got some alarming results when I ran it. On one of my most used tables I got the following result doing DBCC SHOWCONTIG (tablename):

DBCC SHOWCONTIG scanning 'tablename' table...
Table: 'tablename' (1096443030); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 842
- Extents Scanned..............................: 113
- Extent Switches..............................: 839
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 12.62% [106:840]
- Logical Scan Fragmentation ..................: 51.54%
- Extent Scan Fragmentation ...................: 96.46%
- Avg. Bytes Free per Page.....................: 2641.6
- Avg. Page Density (full).....................: 67.36%

"Something tells me" that this is not the way it's supposed to look and I need some advice. What are "target figures" to aim for and what do I need to do to get those numbers in:

1. tables with alot of inserts/updates
2. tables with only inserts

And what are other things to consider in regard to this...?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

nr
SQLTeam MVY

12543 Posts
Posted - 2004-06-08 : 14:42:33
http://www.sql-server-performance.com/dbcc_commands.asp

You should probably reindex or defrag if it's causing a problem.

To keep fragmentation low keep the number of indexes small. Try to insert in the order of the clustered index (or don't have one?). If updating try not to increase the size of the row, try not to update indexes fields and clustered ones even more so.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-06-08 : 14:49:28
Do you have schedukled maintenance?



Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-06-08 : 15:41:26
On the subject of reindexing or defragging, MS suggests that neither makes much of a difference except in a large-scale environment:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Here is what they used as their large-scale environment:

Microsoft partnered with Hitachi Data Systems to build the large-scale Storage Area Network (SAN) environment using a Hitachi Freedom Storage Lightning 9900 Series Lightning 9960 system for storage of the data. The database size for the testing done on this system was approximately 1 terabyte (TB). Data was spread across 64 physical spindles using RAID 1+0. The spindles used for data were exposed through eight Logical Unit Numbers (LUNs), and the database configuration consisted of one file group containing eight data files. tempdb and the database log were created on a separate set of spindles isolated from the data, with tempdb spread across 48 spindles and the log across 8 spindles. To quickly back up and restore images of the fragmented database, two Hitachi ShadowImage software copies of the data/log were maintained in the SAN, and the Lightning 9960 system was used to resynchronize a ShadowImage software copy of the data with the production copy. On this larger system, tests were repeated at two of the three fragmentation levels tested due to the amount of storage needed to maintain a copy for each level (approximately 1.4 TB).

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-06-08 : 16:09:53
quote:
Originally posted by tduggan

On the subject of reindexing or defragging, MS suggests that neither makes much of a difference except in a large-scale environment:


Good link....

And you mean which utility to ise, right?

Not that maintenance should be done..it does make a difference...

I found it funny that their "small" scale environment was 32 GB of data with a quad box and 4GB of RAM....

Add that link to your favorites...



Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-06-08 : 16:14:38
According to their tests, DBCC DBREINDEX and DBCC INDEXDEFRAG didn't make much of a difference and that DBCC INDEXDEFRAG often had the same results as the heavier DBREINDEX. It only made a difference in the large scale environments.

Due to that article, I'm reconsidering running DBCC DBREINDEX as often as we do and putting in DBCC INDEXDEFRAG instead, maybe only running DBREINDEX once per month and INDEXDEFRAG once a week. I haven't made the changes yet, but it's something that we are discussing.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-06-08 : 16:21:30
don't forget

quote:

Updating statistics is a side effect of DBCC DBREINDEX, which is not the case with DBCC INDEXDEFRAG. You can increase the effectiveness of DBCC INDEXDEFRAG by running UPDATE STATISTICS afterwards.





Brett

8-)
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2004-06-08 : 16:55:56
All the talk in this discussion has been focused on internal database fragmentation. I have found that external fragmentation can sometimes be what is causing performance issues.

If your filesystem gets 90% filled or more, NTFS starts having a hard time and begins fragmenting the heck out of your datafiles. This is especially true if you have unrestricted file growth turned on and you have had to shrink datafiles before.

A free tool from the sysinternals people will quickly tell you how fragmented your datafiles are. go to www.sysinternals.com and download their 'CONTIG' tool.

You can generate a quick report on external datafile fragmentation by using the following command (assuming your datafiles are in C:\Program Files\Microsoft SQL Server\MSSQL\Data\) This report can be generated with the databases online.

contig -a -v "C:\Program Files\Microsoft SQL Server\MSSQL\Data\*"


Contig can also be used to defragment files, but the database needs to be offline before you do this.


-ec
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-06-08 : 18:43:14
Ok, I've read the articles and even though the MS whitepaper confused me a little I have always thought that a fragmented disk/index performs worse than a defragmented disk/index so I'll set up some maintenance-scripts to do this for me. I found a script for doing DBCC REINDEX on the entire database (will do this manually ever now and then) and do a DBCC INDEXDEFRAG once a week or something. Thought about updating statistics aswell, after the defrag if noone objects to that. Anybody got a nice quick way of defragging every index in the database? It would be nice to do use a dynamic script...I have a hard time querying the systables myself...

As you might understand I'm a developer doing a DBA's job and luckily for me the db is only about 400MB but it's growing and I really don't want things to get out of hand. Any other maintenance-stuff i need to worry about besides backup?? (derrick and the other DBA's will probably kill me for asking a question like this )
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-06-08 : 18:46:51
INDEXDEFRAG:


CREATE PROC isp_DBCC_INDEXDEFRAG
(@dbName SYSNAME)
AS
SET NOCOUNT ON

DECLARE @objID INT
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)

SET @objID = 0

SET @SQL = ''
SET @SQL = @SQL + 'SELECT i.id, i.name '
SET @SQL = @SQL + 'INTO ##Indexes '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i ' 
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'

EXEC sp_executesql @statement = @SQL

WHILE @objID < (SELECT MAX(id) FROM ##Indexes)
BEGIN

	SELECT TOP 1 @objID = id, @idxName = name
	FROM ##Indexes
	WHERE id > @objID
	ORDER BY id

	SET @SQL = 'DBCC INDEXDEFRAG(' + @dbName + ', ' + CONVERT(VARCHAR(50), @objID) + ', ' + @idxName + ') WITH NO_INFOMSGS'

	EXEC sp_executesql @statement = @SQL

END

DROP TABLE ##Indexes

RETURN 0




GO




And DBREINDEX:



CREATE    PROC isp_DBCC_DBREINDEX
(@dbName SYSNAME)
AS
SET NOCOUNT ON

DECLARE @objID INT
DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)

SET @objID = 0

SET @SQL = ''
SET @SQL = @SQL + 'SELECT i.id, i.name, o.name AS ObjectName '
SET @SQL = @SQL + 'INTO ##Indexes '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i ' 
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'

EXEC sp_executesql @statement = @SQL

WHILE @objID < (SELECT MAX(id) FROM ##Indexes)
BEGIN

	SELECT TOP 1 @objID = id, @idxName = name, @objName = ObjectName
	FROM ##Indexes
	WHERE id > @objID
	ORDER BY id

	SET @SQL = 'DBCC DBREINDEX(''' + @dbName + '.dbo.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'

	EXEC sp_executesql @statement = @SQL

END

DROP TABLE ##Indexes

RETURN 0





GO




Just pass each stored proc the database name.

Tara
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-06-08 : 18:59:48
Thanx tara
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2004-07-21 : 08:57:48
Tara

Can you explain to me the logic behind which this procedure determins which index will be defragmented? I initially thought it was looking only at primary keys, then only at clustered indexes, but none of these seems to be the case. It does seem to look only at one index per table.

What is the purpose of the (i.status & (64 | 8388608)) <= 0 test?

-------
Moo. :)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-07-21 : 12:09:02
It should be looking at all indexes regardless if they need to be fragmented or not. The WHERE statement just makes sure that they are in fact indexes and not statistics or hypothetical indexes.

Tara
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2004-07-22 : 04:14:14
quote:
Originally posted by tduggan

It should be looking at all indexes regardless if they need to be fragmented or not. The WHERE statement just makes sure that they are in fact indexes and not statistics or hypothetical indexes.

Tara


I think that the SELECT TOP 1 in the index defrag code is the problem when I am using it. The dynamic SELECT returns a set like 


46115405	PK_OP_INDEX
46115405	IX_OP_INDEX1
46115405	IX_OP_INDEX2


But when that goes through the loop, only the first index will ever get evaluated and put through the defrag.

I'm not sure if that's what you intended, or if it is a result of my hacking around with the code to include table names for loggin purposes, or if it's a bugette.

-------
Moo. :)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-07-22 : 12:28:31
You're right. I need to look at this further. I'll probably change the loop so that it grabs the TOP 1 each time, but after processing it, it deletes it, then does a TOP 1 again. So even if the ids are the same, it'll get it.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-07-22 : 13:12:26
Ok, how about this for DBREINDEX:



ALTER      PROC isp_DBCC_DBREINDEX
(@dbName SYSNAME)
AS
SET NOCOUNT ON

DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RowCnt INT

CREATE TABLE ##Indexes
(
	Indexes_ID INT IDENTITY(1, 1) NOT NULL, 
	IndexName SYSNAME NOT NULL, 
	ObjectName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName) '
SET @SQL = @SQL + 'SELECT i.name, o.name '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i ' 
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0 AND 1=0'

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
FROM ##Indexes
ORDER BY Indexes_ID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

	SET @SQL = 'DBCC DBREINDEX(''' + 'GT' + '.dbo.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'

	EXEC sp_executesql @statement = @SQL

	SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
	FROM ##Indexes
	WHERE Indexes_ID > @ID
	ORDER BY Indexes_ID
	
	SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Indexes

RETURN 0







GO



If that works for you, I'll change the INDEXDEFRAG one too.

Tara
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2004-07-23 : 03:35:58
Yep, definitely seems to look better (> 1 index per table). I'm slightly confused as to how it manages to step through but I imagine I will get there in the end.

Modified slightly to change debug info -


ALTER  PROC isp_DBCC_DBREINDEX
(@dbName SYSNAME)
AS
SET NOCOUNT ON


--declare @dbname sysname 
--set @dbname = 'testdb'

DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RowCnt INT

CREATE TABLE ##Indexes
(
	Indexes_ID INT IDENTITY(1, 1) NOT NULL, 
	IndexName SYSNAME NOT NULL, 
	ObjectName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName) '
SET @SQL = @SQL + 'SELECT i.name, o.name '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i ' 
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0 --AND 1=0'

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
FROM ##Indexes
ORDER BY Indexes_ID



SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

	SET @SQL = 'DBCC DBREINDEX(''' + @dbname + '.dbo.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'

	EXEC sp_executesql @statement = @SQL
	--print @sql

	SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
	FROM ##Indexes
	WHERE Indexes_ID > @ID
	ORDER BY Indexes_ID
	
	SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Indexes

RETURN 0







GO



and for defrags


ALTER  PROC isp_DBCC_INDEXDEFRAG
(@dbName SYSNAME)
AS
SET NOCOUNT ON


--declare @dbname sysname 
--set @dbname = 'testdb'

DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RowCnt INT

CREATE TABLE ##Indexes
(
	Indexes_ID INT IDENTITY(1, 1) NOT NULL, 
	IndexName SYSNAME NOT NULL, 
	ObjectName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName) '
SET @SQL = @SQL + 'SELECT i.name, o.name '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i ' 
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0 --AND 1=0'

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
FROM ##Indexes
ORDER BY Indexes_ID



SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

	SET @SQL = 'DBCC INDEXDEFRAG(' + @dbname + ',' + @objName + ', ' + @idxName + ') WITH NO_INFOMSGS'
	EXEC sp_executesql @statement = @SQL
--	print @sql

	SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
	FROM ##Indexes
	WHERE Indexes_ID > @ID
	ORDER BY Indexes_ID
	
	SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Indexes

RETURN 0


GO




-------
Moo. :)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site