| Author |
Topic |
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
 Posted - 2004-09-24 : 16:42:10
|
I am developing a process to do DBCC INDEXDEFRAG...I have the process in a while loop, NO CURSOR to run the DBCC INDEXDEFRAG with table name and index id.Questions: 1) How can I pass the Table ID and Index ID rather than names 2) How can I run this process so that I can monitor the sysprocesses and cancel the process if it take longer than x time... I'll log this and do a DBCC REINDEX on a maintenance window... 3) Am I pulling the records with the proper WHERE clause? Do these fields exist in the INFORMATION_SCHEMA tables 4) Suggestion and comments about the code written so far! Please advise best practice suggestions.USE BUFORD_DEVSet NoCount On;Declare @sSql as nvarchar(1000), @TableName as VARCHAR(80), @IndexName as VARCHAR(100), @iCntr as int--Drop Table #ReindexTablesSelect o.Name as TableName, i.Name as IndexName Into #ReindexTablesFrom SysObjects o Inner Join SysIndexes i On o.id = i.id Where o.xtype = 'U' and indid <> 0 and dpages > 0 and left(o.Name, 3) Select @iCntr = Count(*) From #ReindexTables--DBCC SHOWCONTIG (Employees)While Exists(Select * from #ReindexTables)Begin Select Top 1 @TableName = TableName, @IndexName = IndexName from #ReindexTables Print Cast(@iCntr as varchar(3)) + ' ' + @TableName + ': ' + @IndexName Set @sSql = 'DBCC IndexDefrag(BUFORD_DEV, ''' + @TableName + ''', ''' + @IndexName + ''') WITH NO_INFOMSGS' Exec (@sSql) iF @@Error <> 0 Print @sSql Set @sSql = 'Delete from #ReindexTables Where TableName = ''' + @TableName + ''' and IndexName = ''' + @IndexName + '''' --Print @sSql Exec sp_executesql @sSql Set @iCntr = @iCntr - 1 End--Drop Table #ReindexTablesSurf On Dude! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 16:44:43
|
| BTW, using a WHILE loop isn't much better than using a cursor. Set-based solutions should be used. For this type of task and a lot of DBA type scripts, looping is required though.I mentioned this as I've recently seen people saying I got rid of cursors by changing them to WHILE loops. Well that's not really much better. Tara |
 |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-09-24 : 17:04:55
|
| Cool...Did'nt Catch the WHERE Indexes_ID > @ID on Temp table Identity at first...What does this piece do 'WHERE indid > 0 AND indid < 255 AND 'SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'Surf On Dude! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 17:08:28
|
| That entire select query gets the indexes to be defragmented. So it excludes statistics and hypothetical indexes which is what that WHERE clause is doing. Both of those are stored in sysindexes so we want to exclude them. It doesn't hurt if you defragment them, it just isn't necessary so don't bother.Tara |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 17:21:12
|
| I wouldn't have a monitoring job that sees how long the job is taking. You should test the job out during a maintenance window to make sure that it isn't impacting much, which it shouldn't as DBCC INDEXDEFRAG isn't intrusive like DBCC DBREINDEX. Then schedule the job once you are happy with results and monitor the job history over time.Tara |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-09-24 : 17:27:23
|
| Thank you!!! Hey are'nt you from my home town San Diego?Surf On Dude! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 17:28:15
|
| Yep, born and raised. Living in Mission Valley currently.Tara |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-09-24 : 17:32:50
|
| Yea my dad still lives in PB...I'm stuck up here in Beautiful LA where we never trust air we can't see!Wish I could have gotten my start back home after college... pretty entrenched with the kids and all.Surf On Dude! |
|
|
|
|
DBCC IndexDefrag