Recalculate All Indexes

Bit of a noob question -

I have several identical databases which contain several indexes.  Is there any way to set up a job on the DBs to auto recalculate all indexes without having to specifically script them all?
ccleebeltPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Indexes are normally updated every time you insert a row.  They wouldn't be much good if they weren't.
0
PortletPaulfreelancerCommented:
Yes.

Several of the DBA's participating here at EE recommend the following:

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.
Ola Hallengren
http://ola.hallengren.com/
THis conference video is Ola explaining his approach: recommended video

also refer to
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28163719.html#a39265474


{+edit}
Also Brent Ozar Unlimited has a script that helps assess your index health that may be of use:
http://www.brentozar.com/blitzindex/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sachitjainCommented:
Looks like you want something like this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [IndexRebuild]
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON ;

	-- Insert statements for procedure here
	DECLARE Curs CURSOR FORWARD_ONLY READ_ONLY
	FOR SELECT  'ALTER INDEX [' + si.name + '] ON ' + sch.name + '.' + t.name
		+ ' REBUILD WITH(MAXDOP = 24, PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = ON, ONLINE = ON)
		' AS IndexSqlScript,
		sch.name AS SchemaName,
		t.name AS TableName,
		si.name AS IndexName
	FROM    sys.tables t
		INNER JOIN sys.schemas sch ON t.[schema_id] = sch.[schema_id]
		CROSS APPLY MASTER..sys_PhysicalIndexStatistics_Wrapper(DB_ID(), object_id, NULL, NULL, NULL) s
		INNER JOIN sys.Indexes si ON si.[object_id] = t.[object_id] AND si.[index_id] = s.[IndexID]
	WHERE   AverageFragmentation > 1
		AND FragmentCount > 10
		AND si.type_desc <> 'HEAP'

	OPEN Curs

	DECLARE @IndexSqlScript NVARCHAR(MAX)
	DECLARE @SchemaName NVARCHAR(128)
	DECLARE @TableName NVARCHAR(128)
	DECLARE @IndexName NVARCHAR(128)
	
	FETCH NEXT FROM Curs INTO @IndexSqlScript, @SchemaName,
	@TableName, @IndexName

	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		PRINT @IndexSqlScript
		BEGIN TRY
			EXEC sp_executesql @IndexSqlScript
		END TRY
		BEGIN CATCH
			DECLARE @ErrorMessage NVARCHAR(4000)
				,@ErrorNumber INT
				,@ErrorSeverity INT
				,@ErrorState INT
				,@ErrorLine INT
				,@ErrorProcedure NVARCHAR(200) ;
		
			SELECT  @ErrorNumber = ERROR_NUMBER()
				,@ErrorSeverity = ERROR_SEVERITY()
				,@ErrorState = ERROR_STATE()
				,@ErrorLine = ERROR_LINE()
				,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;

			SELECT  @ErrorMessage = N'Error ' + CONVERT(NVARCHAR, @ErrorNumber) + ', 
				Level ' + CONVERT(NVARCHAR, @ErrorSeverity) + ',  
				State ' + CONVERT(NVARCHAR, @ErrorState) + ',  
				Procedure' + CONVERT(NVARCHAR, @ErrorProcedure) + ',  
				Line ' + CONVERT(NVARCHAR, @ErrorLine) + ',  
				Message: ' + ERROR_MESSAGE() ;
			
			PRINT @ErrorMessage
		END CATCH
		FETCH NEXT FROM Curs INTO @IndexSqlScript,
		@SchemaName, @TableName, @IndexName
	END
	
	CLOSE Curs
	DEALLOCATE Curs
END

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ccleebeltPresidentAuthor Commented:
Thanks for the answers.  Looking into both and when I come up for air I'll assign points.
0
PortletPaulfreelancerCommented:
Thanks ccleebelt. Which one did you choose? (Ola Hallengren's or BlizIndex) - just interested. Cheers, Paul
0
ccleebeltPresidentAuthor Commented:
I installed them both...  

From what I can tell, the Blitz will show me what is going on and where I can improve, whereas Ola's will actually go an recalc everything without having to define anything, right?

Please correct me if I am wrong.  I am a bit new to this.

Thanks.
0
PortletPaulfreelancerCommented:
I don't have a need to use either  (I am not a DBA but an Architect) - which is why I was interested.

I have read up on both, and both have been recommended by DBA's whose opinions I can trust - perhaps more favoring Ola's - but that maybe because the scope of hos solution is broader.

Not sure I would characterize the difference as you outline (comparing just the Indexing piece) as I understand it Ola's also provides a table of information you can report from regarding your indexes. Did you look at that video I suggested? If you do intend to use that solution I would seriously suggest you do. There are also videos for Blitzindex.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.