database became slow after the script

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

General SQL Server Forums

New to SQL Server Programming

database became slow after the script

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-09-26 : 02:48:37

Dear All,
i've run the index rebuild script shown below. after the script, the the queries has taken long time. is there anything i need to do after rebuilding the indexes with the below script?

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

-- SQL 2000 command
--DBCC DBREINDEX(@Table,' ',@fillfactor)

-- SQL 2005 command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

please help me in this

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-26 : 08:49:36

Why are you using Cursor for it? Use TARA'S script to rebuild index as it finds the fragmentation level and do rebuild or reorganize. Also did you update stats after running this script. You shouldn't update stats as it will take care of that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources