Where's the Beef?

Last Post 24 Sep 2008 12:39 PM by SwePeso. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
timmer26
New Member
New Member

--
01 Jul 2008 06:32 PM
Is it just me or did Andrew Kelly not get his due? We were promised a stored procedure for rebuilding only indexes with X% fragmentation (we've all seen various flavors of this online) yet all we were presented with (in both print and online) were the parameter declarations.

Andrew, can you deliver us the rest of the stored procedure code here or are we to go search out the info on Solid Quality Mentors' website?

srinivasma
New Member
New Member

--
02 Jul 2008 02:44 AM
I commented out the line

--AND a.[object_id] NOT IN (SELECT ISNULL(OBJECT_ID(p.[ParsedValue]),1) FROM [dbo].[fn_split_inline_cte](@ExcludedTables,N',') AS p)


since I didnot get this function.

When I run this SP , more than once with default parameters , I am getting the same output
where some of the tables have fragmention more than 50% .

Repeated execution of this SP , provides almost same result from select * from #FragLevels .
So I assume this is not removing fragementation OR I am missing some thing ?

Thanks

M A Srinivas

mjswartd2l
New Member
New Member

--
04 Jul 2008 10:34 AM
Hey Andrew,

Hope you don't mind but I quoted your split sproc here:
http://dbwhisperer.blogspot.com/200...n-sql.html

Michael Swart
timmer26
New Member
New Member

--
24 Sep 2008 12:25 PM
Thanks again Andrew for the great code. I'd been writing and re-writing similar code for years trying to come up with something universal and always had exceptions. There was one thing I had to change in your code, but only because of poor programming from the vendor of one of the databases I administer (that NEVER happens, does it?)

This particular fix involved adding brackets ([]) around the index name in both the REORGANIZE and REBUILD statements (see below) because there were indexes that included periods in the names. I have a hard time not faulting Microsoft for allowing this, but best practices should prevent this travesty as well.

CODE WAS:
SET @Rebuild = N'ALTER INDEX ' + @IndexName + N' ON ' + @FullName + N' REBUILD' ;

CHANGED TO:
SET @Rebuild = N'ALTER INDEX [' + @IndexName + N'] ON ' + @FullName + N' REBUILD' ;

CODE WAS:
SET @Rebuild = N'ALTER INDEX ' + @IndexName + N' ON ' + @FullName + N' REORGANIZE' ;

CHANGED TO:
SET @Rebuild = N'ALTER INDEX [' + @IndexName + N'] ON ' + @FullName + N' REORGANIZE' ;

The final thing I did was to create this as a stored procedure in master. I then created a SQL Agent job that called the stored procedure from within a cursor that looped through all user databases I wanted it to. You see, I have a table in a database that exists on each of my instances that stores exceptions for automated processes, by joining that table to the sys.sysdatabases view as a basis for this cursor I can determine which databases I want this process to run against.

Furthermore, I added functionality to record a start and end time for each database's index organization process which is posted back to a table in my instance that can be used to track how much time each database is requiring for the index maintenance.

Runs like clockwork Andrew, thanks again!
SwePeso
New Member
New Member

--
24 Sep 2008 12:39 PM


Acceptable Use Policy
---