Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
fabianus76
Posting Yak Master
191 Posts |
Posted - 2006-08-16 : 13:14:57
|
Hello guys, Two things:1) Could somebody explain me how to reindex all tables in my db?2) How do I know when I should reindex my db?Thank you very much for any help!Regards,Fabianmy favorit hoster is ASPnix : www.aspnix.com ! |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-16 : 13:20:48
|
1. using DBCC DBREINDEX or DBCC INDEXDEFRAG or drop index create index or alter index statements2. well run DBCC SHOWCONTIG to see how much fragmentation you have in your indexes. I defragment my indexes when the fragmentation reaches 10-20% based on the table. some people have a scheduled job that rebuilds the indexes every day in low traffic hours. SQL server 2005 has also improved greatly on live index rebuild.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
fabianus76
Posting Yak Master
191 Posts |
Posted - 2006-08-16 : 15:14:12
|
Thank you very much Spirit1. That's very useful for me. Could you help me to interpreter the report for one of my tables: DBCC SHOWCONTIG scanning 'cs_UserProfile' table...Table: 'cs_UserProfile' (2114822596); index ID: 1, database ID: 18TABLE level scan performed.- Pages Scanned................................: 27- Extents Scanned..............................: 6- Extent Switches..............................: 19- Avg. Pages per Extent........................: 4.5- Scan Density [Best Count:Actual Count].......: 20.00% [4:20]- Logical Scan Fragmentation ..................: 96.30%- Extent Scan Fragmentation ...................: 83.33%- Avg. Bytes Free per Page.....................: 5165.3- Avg. Page Density (full).....................: 36.18%The percentage you're talking about, is it the Logical Scan Fragmentation?Thank you very much for your help!Regards,Fabianmy favorit hoster is ASPnix : www.aspnix.com ! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-16 : 15:14:59
|
http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspxTara Kizer |
|
|
fabianus76
Posting Yak Master
191 Posts |
Posted - 2006-08-17 : 08:39:49
|
Here is a nice way to reindex all tables : USE DatabaseName --Enter the name of the database you want to reindex DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursorI found this here : http://www.sql-server-performance.com/rebuilding_indexes.aspRegards,Fabianmy favorit hoster is ASPnix : www.aspnix.com ! |
|
|
|
|
|