SQL Server Index Defragmentation (Updated)

Question

SQL Server Index Defragmentation (Updated)

GregoryF

SSCertifiable

Points: 6296
More actions
September 20, 2012 at 11:50 pm

#262031

Comments posted to this topic are about the item SQL Server Index Defragmentation (Updated)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Brett Phipps Default port Points: 1483 More actions · Answer 1

It says it supports SQL 2005 or greater but we don't have a DBMonitor database. After searching it looks like this is a 3rd party tool from Devart?

Perhaps this should be mentioned and a discussion of the pros/cons of installing this tool on your database should be included instead of just a teaser of Defrags all indexes.... with no mention of the need to install this.

GregoryF SSCertifiable Points: 6296 More actions · Answer 2

DBMonitor is just the name of our administrative database, it does not belong to a third party tool. You can put the SP in any databasse (master, msdb, etc)

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Brett Phipps Default port Points: 1483 More actions · Answer 3

AAAH.....Sorry I was confused

Updated it to use our Utilities db and modified name to match our naming standards....

It's so big going through it to make sure I understand which parameters to run is taking some time.

gsc_dba SSCertifiable Points: 5407 More actions · Answer 4

Nice script

Just a heads up - found a missing QUOTENAME here: (around @DBName)

(only applies if you have underscores and/or hyphens and/or dashes in your database names)

--Get rid of the brackets :<-->: [IndexName] --> IndexName

set @IndexNameExists = substring(@IndexName, 2, len(@IndexName) - 2)

set @SQLCmdExist = N'select @IndexExists = name from ' + @DBName + '.sys.indexes where name = ''' + @IndexNameExists + ''''

set @PDExist = N'@IndexExists varchar(128) output'

exec sp_executesql @SQLCmdExist, @PDExist, @IndexExists output

Changed to:

set @SQLCmdExist = N'select @IndexExists = name from ' + QUOTENAME(@DBName) + '.sys.indexes where name = ''' + @IndexNameExists + ''''

and now works without any errors on my servers.

Thanks

gsc_dba

GregoryF SSCertifiable Points: 6296 More actions · Answer 5

I think you may have just solved my riddle of why it fails on SharePoint DB's that contain GUID's in their names. Thanks!

Just to confirm, I ran it against a test server (with the ISGUID functionality commented out) , and like you said, it now handles _'s and -'s. I've been spending months trying to figure this out, thanks again!

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

rw_ebox SSC Eights! Points: 804 More actions · Answer 6

A nice script!

Thank gcs_dba. You fix my problem too.

facemann Right there with Babe Points: 797 More actions · Answer 7

Interesting idea. Why is this better than using the "Reorganize Index Task" in the Maintenance Plan?

terry.home Ten Centuries Points: 1004 More actions · Answer 8

I concur with the previous question... I regularly run reorg_re-index on all tables and trying to work out what exactly this script is doing. The only clue is in the title as far as I can tell.

Perhaps it would be worth explaining in a little more detail why this is needed and what it does differently. How regular it is suggested to be run.

T

Jesper-315656 SSC Rookie Points: 31 More actions · Answer 9

Is there a specific reason why the indexname must be larger than 2 characters for the script to work?

I have a system where I can't change indexes as they are created (and maintained) by a client program (NAVISION) and are named $1, $2, etc...

I can see that your script conditions on the size, but why? Is it safe just to lower the requirement to 1?

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

akljfhnlaflkj

SSC Guru

Points: 76202

May 9, 2016 at 1:30 pm

#1877603

Thanks for the script.

SQL Server Index Defragmentation (Updated)

Cookies on SQLServerCentral