September 6, 2013 at 3:10 am
Morning!
I have a script which reindex's\reorgs index's depending on their fragmentation. But, it doesnt take into account the schema name, so anything other than DBO and the script fails.I have no idea how to incorporate this into the script. Below is step 1 of the process, which reorganised indexs fragmented between 5 and 40%. Thanks in advance for the help.
CREATE procedure [dbo].[DefragIndexStep1]
as
--Create temp table for list of indexs
CREATE TABLE #IndexFrag(
database_id int,
object_ID int,
index_id int,
name ntext,
avg_fragmentation_In_Percent real )
--Fill the table with all the indexs and fragmentation level
insert into #IndexFrag (database_id, object_ID, index_id, name, avg_fragmentation_In_Percent)
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
--select * from #IndexFrag
--drop table #indexfrag
--Selecting all index's between 5% and 40% fragmented
declare @cnt int
declare @Result nvarchar(128)
declare @cmd nvarchar(500)
declare @tablename nvarchar(500)
declare FindFragment cursor for
SELECT name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')
--Cursor to go through each index which are between 5% and 40% fragmented and rebuild
open FindFragment
fetch next from FindFragment into @result
while @@fetch_status = 0
BEGIN
set @tablename = ( select OBJECT_NAME(object_id) FROM sys.indexes WHERE name = @Result)
set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REORGANIZE '
--print @cmd
EXEC sp_executeSQL @cmd
fetch next from FindFragment into @result
END
close FindFragment
deallocate FindFragment
drop table #IndexFrag
GO
September 6, 2013 at 3:24 am
Change the following line
set @tablename = ( select OBJECT_NAME(object_id) FROM sys.indexes WHERE name = @Result)
To
set @tablename = ( select s.name +'.'+ OBJECT_NAME(o.object_id) FROM sys.objects o
JOIN sys.schemas s
ON o.schema_id = s.schema_id
JOIN sys.indexes i
ON o.object_id = i.object_idWHERE name = @Result)
That should work. If you're looking for a much more advanced script check out Ola Hallengrens solution at http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
[font="Verdana"]Markus Bohse[/font]
September 6, 2013 at 3:40 am
Two places that need to change.
First, the cursor definition
declare FindFragment cursor for
SELECT object_id, name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')
because index names aren't required to be unique in a database. Leaving it to you to change the variables, fetch statements, etc.
Second.
select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i INNER JOIN sys.tables AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @ObjectID
That should do the job
p.s. Noticed you're not excluding small indexes. Any reason why not?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 3:45 am
GilaMonster (9/6/2013)
Two places that need to change.First, the cursor definition
declare FindFragment cursor for
SELECT object_id, name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')
because index names aren't required to be unique in a database. Leaving it to you to change the variables, fetch statements, etc.
Good point Gail, I missed that part.
[font="Verdana"]Markus Bohse[/font]
September 6, 2013 at 4:26 am
Second.
select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i INNER JOIN sys.tables AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @ObjectID
That should do the job
p.s. Noticed you're not excluding small indexes. Any reason why not?[/quote]
Cheers for that. Where should we be setting @objectID though? Should the last part be i.object_id = t.object_id ?
Also, what size would you same is classed as a small index? I will incorporate this into the script, I never even though so thanks.
September 6, 2013 at 4:27 am
Cheers for the fast response MarkusB
September 6, 2013 at 4:31 am
SQLSteve (9/6/2013)
Cheers for that. Where should we be setting @objectID though?
Look at the first changes, to the cursor
Should the last part be i.object_id = t.object_id ?
It is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 5:15 am
Appologies, I set the variable but the cursor doesnt work
(0 row(s) affected)
Msg 16924, Level 16, State 1, Line 50
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
tried altering it with no luck
September 6, 2013 at 5:23 am
Yes, that's why I said 'Leaving it to you to change the variables, fetch statements, etc.'
If you change the cursor definition, you'll have to change the fetch statements, add variables, etc. Normal, basic stuff.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 6:53 am
GilaMonster (9/6/2013)
Yes, that's why I said 'Leaving it to you to change the variables, fetch statements, etc.'If you change the cursor definition, you'll have to change the fetch statements, add variables, etc. Normal, basic stuff.
Normal basic stuff when you have been doing it for many years, I agree.
Ididnt realise i needed to give the fetch statement all the variables, clearly i do.
So we now have the fetch statement
fetch next from FindFragment into @objectID, @result
and the variable is declared
but now the script isnt doing anything, it runs through fine does nothing. Im sure its something basic again but I dont know what?
September 9, 2013 at 3:19 am
Updated script - the script runs fine but nothing seems to be happening? Any help would be appreciated. Cheers
CREATE TABLE #IndexFrag(
database_id int,
object_ID int,
index_id int,
name ntext,
avg_fragmentation_In_Percent real )
--Fill the table with all the indexs and fragmentation level
insert into #IndexFrag (database_id, object_ID, index_id, name, avg_fragmentation_In_Percent)
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
--select * from #IndexFrag
--drop table #indexfrag
--Selecting all index's over 40% fragmented
declare @cnt int
declare @Result nvarchar(128)
declare @cmd nvarchar(500)
declare @tablename nvarchar(500)
declare @objectID int
declare FindFragment cursor for
SELECT object_id, name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')
--Cursor to go through each index which are over 40% fragmented and rebuild
open FindFragment
fetch next from FindFragment into @objectID, @result
while @@fetch_status = 0
BEGIN
select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i
INNER JOIN sys.tables AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @objectID
set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REBUILD '
--print @cmd
EXEC sp_executeSQL @cmd
fetch next from FindFragment into @result
END
close FindFragment
deallocate FindFragment
drop table #IndexFrag
September 9, 2013 at 4:24 am
If you print the command rather than exec, what prints out?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2013 at 6:51 am
its coming back with a syntax error at the print @cmd line :/ which makes me think thats why the script isnt doing anything.
If I declare all the variables just before the set @cmd line it prints something.
September 9, 2013 at 7:11 am
And the error reads..... ?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2013 at 7:26 am
Apologies
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near '@cmd'.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply