Speeding things up

[edgare]

Hi

whats the best way to reindex,defrag and rebuild the database to increase performance? I am using SQL 7.

 

[Joeybear23]

The index wizard is a wonderful tool.

SQL/SAP DBA

 

[edgare]

Would the Maintanance wizard fully rebuild and reindex the tables?? Or r u talkin abou the Index Tuning Wizard?

 

[Joeybear23]

Index tuning wizard.

SQL/SAP DBA

 

[edgare]

Would scripts like this help? Or its pointless just using tuning is a better way ? (which by the way didnt work on my database its named DATA_10 sorry not much knowledge in programming)

/*
Defrag or re-index all indecies
*/

DECLARE @TableName sysname
DECLARE @IndexName VARCHAR(100)
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT name from sysobjects where xtype='U'

OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid,name FROM sysindexes WHERE id = OBJECT_ID (@TableName)
and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid,@IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Maintenancing ' + (@TableName) + '.' + (@IndexName)
--IF @indid <> 255 DBCC INDEXDEFRAG (0, @TableName,
@indid)
IF @indid <> 255 DBCC DBREINDEX (@TableName, @IndexName)
FETCH NEXT FROM cur_indfetch INTO @indid,@IndexName
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch

 

[jsteph]

Be careful of the Index Tuning Wizard. I have to disagree to the sentiment that it's a wonderful tool. It may be a quick way to get some performance boost out of some of your queries, but it is a relatively rote tool.

SQL-7's Index Wizard just never seems to draw a line at diminishing returns.

That is to say, it always seems to take the 'easiest' choice for an index. What fields are in the WHERE clause? Ok, let's make an index with 'em all. That's something a trained monkey could do. Not that I expect magic, but when I was working with Oracle, that seemed to optimize with a little more thought, for instance, it would exclude fields where the uniqueness was very low and an index wouldn't really help much. But with MS, "if it's in the WHERE clause, it's in the index" seems to be the mantra. Or with Aggregate (Group By) queries, I've seen it just index all fields referenced!

So the gist is, if you used the index wizard on everything, you end up with an over-indexed database, and this can drastically slow things down if it's a highly transactional system, as well as increase the size and maintenance workload. To really get things tight, you need to analyze your data and look at the fields, know the data, approximate row counts,field uniqueness percentages, etc.

Just my opinion...
--Jim