Hi

Any of you guys created good scripts to optimize the database like reindexing and defragging the tables/indexes? Willing to share with the community?

Hi guys here is what i got so far adding these to ur database will speed things up. Dont forget the screen and mslock ... db also not just ur data database.

----Rebuilding an Index

CREATE PROC ind_rebuild
AS
DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName + ' table'
DBCC DBREINDEX (@TableName, ' ', 90)
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO


--Defragmenting an Index

CREATE PROC ind_defrag
AS
USE YOU_DATA_BASE
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '
+ rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG (pubs, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch

--Show table defragmentation
CREATE PROC AS SHOW_DEFRAG
AS
USE YOU_DATA_BASE
DECLARE @TableName sysname
DECLARE cur_showfragmentation CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_showfragmentation
FETCH NEXT FROM cur_showfragmentation INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Show fragmentation for the ' + @TableName + ' table'
DBCC SHOWCONTIG (@TableName)
FETCH NEXT FROM cur_showfragmentation INTO @TableName
END
CLOSE cur_showfragmentation
DEALLOCATE cur_showfragmentation

What version of SQL are you running these against? Have you tried them on any ES database or just Progression?

Thanks for posting them.

Its SQL 7 with Windows 2000 Pro but it should with with all versions of SQL ... 7-2000 and probably even yukon ...

Any baseline information available on these scripts actual effect on performance? We have two companies, one db running around 30 gb and the other db around 2 gb. Under Progression, performance on the 30 gb was acceptable but under ES, some transactions can take an extremly long time to run. We are going to be installing a new SQL server at the end of this week to hopefully resolve some of that but I'm sure there are some indexing or table management tasks that would also help.

Well I had a database about 2 gigs and it helped out alot 20% increase in performance. Also don’t forget to reindex the screens and msllock databases also ... after doing reindex on screens and msllock the speed at which Macola opens up menus tripled. Processing aging reports ... was also a little faster. 30 Gig is a big database i would recommend switching to raid and adding as many drives as you can in the raid array. There is also a company out there http://www.quickshift.com that claims to increase speeds significantly. I tried them and our speeds didn’t go up enough to pay the grand or so for the license fee. You can get 7 day trial and check it out for yourself.

p.s. back up first then do this also do it during after hours since 2 gig take about 10 min to reindex while 30 would take significantly longer.

That's a pretty significant increase in performance. Will have to look at this on the 30 gb db. Will also look at the quickshift app.

Database is already on a 5 drive raid 5 array on a dual channel controller. Channel one handles the OS on a raid 1 array. The problem is the server was purchased in 2000 for SQL 7, its a dual 600 w/ 4 gb memory and I believe a very small cache on the drive controller. Perfomance monitor shows the processors getting thrashed most of the time. We are replacing with a dual 3.66 w/ 5 gb and a top of the line array controller this Friday.

Thanks for the info.

We have a 130 gb database as well as a 28 gb database. We sell low dollar items and have a lot of transactions as you can imagine we have speed issues on a regular basis. I will utilize this script against my test company and let everyone know how it does.

jnelson wrote:
also try this ... best one so far ...

---

-- OBJECT NAME : isp_DBCC_DBREINDEX
--
-- AUTHOR : Tara Duggan
-- DATE : May 11, 2004
--
-- INPUTS : @dbName - name of the database
-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure runs DBCC DBREINDEX for each of the indexes in the database.
--
-- EXAMPLES (optional) : EXEC isp_DBCC_DBREINDEX @dbName = 'GT'
--
-- MODIFICATION HISTORY :

---

-- 12/22/2004 - Tara Duggan
-- If table has a clustered index, just rebuild that index; otherwise rebuild all.
--
-- 12/30/2004 - Tara Duggan
-- If table has a non-unique clustered index, just rebuild that index; otherwise rebuild all.

---

create PROC fastrebuild
(@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,
Status INT NOT NULL
)

-- non-unique clustered indexes
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], o.[name], i.status '
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 i.indid = 1 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND (i.status & 2) = 0'

EXEC sp_executesql @statement = @SQL

-- non-clustered indexes except for those tables that have clustered non-unique indexes; these will be rebuilt automatically
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], o.[name], i.status '
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 i.indid > 1 AND i.indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0 AND '
SET @SQL = @SQL + 'o.[name] NOT IN (SELECT ObjectName FROM ##Indexes)'

EXEC sp_executesql @statement = @SQL

-- unique clustered indexes
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], o.[name], i.status '
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 i.indid = 1 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND (i.status & 2) <> 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

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

I am a newbie with SQL. Why not just rebuild the files from within Macola? What is the difference between that and indexing with the scripts?

And since I don't know 'script' how can I tell these scripts are any good?
Do I have to do anything to them before I run them?

Thanks.