Nice script. http://www.sqldbpros.com/sql-server-rebuild-indexes-the-fastest-way
I’ve only used it once. YMMV. Don’t blame me if it turns your cat into a packet of oreos. Brad McGehee (MVP, Works for Redgate, general all round SQL Server Guru) has some good tips at Rebuilding Indexes There’s also gold at http://benchmarkitconsulting.com/colin-stasiuk/2009/02/11/update-statistics-before-or-after-an-index-rebuild/ regarding index rebuilding.
The comments are pure geekery. Before rebuilding the indexes, you’ll probably want to put the database into single user mode. This’ll speed things up
- -- take to single user mode
- ALTER DATABASE DBNAME SET SINGLE_USER WITH NO_WAIT
- -- do your magic reindexing here
- -- let everyone back in
- ALTER DATABASE DBNAMESET MULTI_USER WITH NO_WAIT
You might also want to kill all the connections that aren’t you. I tend to do this before putting it in single user mode.
- use master
- go
- declare @command varchar(100)
- declare @spid int
- DECLARE active_users CURSOR LOCAL FOR
- SELECT spid FROM master.dbo.sysprocesses
- WHERE (dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE (name = 'DBNAME')))
- AND spid <> @@spid
- OPEN active_users FETCH NEXT FROM active_users INTO @spid
- WHILE (@@fetch_status = 0) BEGIN
- SET @command = 'kill ' + cast(@spid as varchar)
- print @command
- exec(@command)
- FETCH NEXT FROM active_users INTO @spid
- END
- DEALLOCATE active_users
- GO
