Defragmentation and reindexing

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 2005 Forums

Transact-SQL (2005)

Defragmentation and reindexing

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2007-12-27 : 14:57:33

Hi experts,
For defragmenation and reindexing they are using the below cursor, and now they have asked me to remove the cursor and schedule the job accordingly to do the same functionality, so what will be the other way we can do without cursor? can you plase let me know the solution?

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

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT database_name FROM dbadmin.dbo.tdbstatus WHERE status='y'
AND database_name NOT IN ('master','model','msdb','tempdb')
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

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

Your help will be appreciated.

-Thanks N Regards,
Kanthi.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-27 : 15:01:38

You either have to use a cursor or a loop to be able to process an index one at a time, so their request to remove the cursor is absurd.

Here's how I handle defragmentation:
http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx

The problem with your script is that it is reindexing regardless if it is fragmented or not. This means you are causing performance issues for a lengthier period of time than is necessary. You need to utilize sys.dm_db_index_physical_stats to determine what to reindex and not just do it blindly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2007-12-27 : 16:15:37

Tara Kizer, Thank you very much for your quick reply.

-Thanks N Regards,
Kanthi.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources