July 24, 2013 at 7:35 am
Hi,
I got very nice assistance last time i had an issue on this site. Can anyone assist me? I would like to know, is there a script i can run the re indexes all the tables in a sql database? Reasons:
I have clients running sql databases.Some of them start to complain about speed. The servers is fine, i cjhecked them out. Databases from 1.0 - 18 GB in size. After i re-index each table one by one, it seems a lot better. It takes forever though. Is there a script that can assist me?
Thank You!
July 24, 2013 at 7:43 am
July 24, 2013 at 8:11 am
Use this..
exec sp_msforeachtable @command1="print'?' dbcc dbreindex ('?')"
Taken from
http://www.sqlservercentral.com/scripts/Indexing/31908/">
http://www.sqlservercentral.com/scripts/Indexing/31908/
Or
You can use following to reindex all tables in all databases..
http://www.sqlservercentral.com/scripts/Indexing/30724/
July 24, 2013 at 8:22 am
Those links don't work for me. The code you posted will do the job, but it'll rebuild every index in the database regardless of how fragmented it is. This is a waste of resources and will significantly increase the size of your transaction log backups.
John
July 24, 2013 at 8:32 am
John Mitchell-245523 (7/24/2013)
Those links don't work for me. The code you posted will do the job, but it'll rebuild every index in the database regardless of how fragmented it is. This is a waste of resources and will significantly increase the size of your transaction log backups.John
Don't bother with those links. The first one is the exact code that was posted and the second is simply a cursor that does the same thing for all databases.
I second what John says. You shouldn't just blindly reindex everything.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2013 at 9:07 am
This will show you all of your index's which are fragmented between 5% and 40% - you can easily change this to execute the command rather than just print. and also change this to do rebuild on everything over 40%
--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 over 40% fragmented
BEGIN TRANSACTION
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 is over 40% 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
July 24, 2013 at 11:02 am
Very interesting thank you. Now, may I ask? I want to test this. I will test and mark the speed of the app connecting to the dbase. How do I fragment a table? To test? My transactions is slow on some servers, if I can find a remedy it will be Christmas.
July 25, 2013 at 2:35 am
Run the command on your fragmented database- it will not execute anything, only print the command. You can run this on a copy of live if need be, it will not effect anything
July 25, 2013 at 8:45 am
If you want to fragment your indexes, you'll need to make lots of modifications - inserts, updates and deletes. Try to choose operations that change data in index key columns, or that add rows to, or remove rows from, the middle of the index.
John
July 25, 2013 at 6:41 pm
Make a backup of your fragmeted DB before running reindex. So every time you will need to test your script, run a restore and then run your script
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply