Should I reorg or update stats after a rebuild of indexes

Question

I am doing a Index Rebuild every Sunday. Is it correct that this updates my stats as well ? I have noticed that as the week progreses - the quries get slower. What other actions should I preform on the remaining days ie Mon-Sat

Should I do a Index Reorg OR should I do a sp_updatestats ?

Thanks in Advance.

 

Answer 1

I usually perform a weekly index rebuild and follow it up with daily update statistics during non-business hours.

---

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @PradeepAdiga

Answer 2

I am doing a Index Rebuild every Sunday. Is it correct that this updates my stats as well ? I have noticed that as the week progreses - the quries get slower. What other actions should I preform on the remaining days ie Mon-Sat

Should I do a Index Reorg OR should I do a sp_updatestats ?

Thanks in Advance.

 

>>>I am doing a Index Rebuild every Sunday. Is it correct that this updates my stats as well ?

Yes the rebuild index will update the stats  automatically.

>>> I have noticed that as the week progreses - the quries get slower. What other actions should I preform on the remaining days ie Mon-Sat

It depends upon your transaction. I suggest you to check Mon-Sat how the index got fragmented and statistics are out of date then you can decide whether will go Index Reorg/rebuild or update stats.

For fragmentation:  sys.dm_db_index_physical_stats

statistics:

 

SELECTname index_name ,

STATS_DATE(OBJECT_ID, index_id)

St_date

 

FROM sys.indexes

WHERE OBJECT_ID = OBJECT_ID('programcosts_month'

)

 

>>> Should I do a Index Reorg OR should I do a sp_updatestats ?

The answer is "It depends" . What type of transaction,How big you DB ect...

---

Muthukkumaran Kaliyamoorthy SQL DBA

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Answer 3

Check the fragmentation level during weekdays. Depending on the fragmentation level you can decide to have reorg on weekdays.

Since it is performance issue, I also advice to check the missing indexes stats for your database at regular intervals.

 

Pls mark as answer, if this helps.

---

- Kerobin

Answer 4

The following post might help you understand better..

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/aecc91b8-d0b8-4567-9c01-155c5c28306c/

 

 

---

- Kerobin

Answer 5

An index rebuild updates index statistics, not column statistics, so you might want to consider a separate step to do this.

Reorganising indexes doesn't update statistics.

Also, a rebuild index will do an update statistics with fullscan.  If you run adhoc update statistics command, the default is to use a 'sample' of rows, which will probably be less accurate than the full scan, and overwrite those statistics.

 

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 6

we can plan to run the rebuild index on daily basis depending upon the fragmentaion check. U can loop through all the indexes in a database and check if they are fragmented say 5% or more then you can reindex them. this way you can keep all the indexes defragmented.

 

 

---

VEERESH V NASHI.

Answer 7

I would suggest reading Paul Randall's comments on statistics and reindexing.  Such as:

http://www.sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx

Or read all his blog posts on indexes.

RLF

Answer 8

we can plan to run the rebuild index on daily basis depending upon the fragmentaion check. U can loop through all the indexes in a database and check if they are fragmented say 5% or more then you can reindex them. this way you can keep all the indexes defragmented.

 

 

---

VEERESH V NASHI.

Nope no need to rebuild the index less than 5%. It will not impact that much you can reorganize them.

@Drdba123

See my post and read the conditions: index defragmentation script if you want use it.

---

Muthukkumaran Kaliyamoorthy SQL DBA

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Answer 9

Hi ,

To keep your database's access to indexes as fast as possible, you should defragment your indexes—just as you would occasionally defragment files. Which tables are good candidates for defragmenting? Here are a few guidelines:

• Tables with 100,000 rows or more
• Tables with fewer records, but the number of columns and data type matter
• Tables fragmented more than 5%

When you have determined which indexes are mostly fragmented, here are the guidelines for how to proceed:

• If an index is 5-30% fragmented, reorganize the index.followed by update statistics
• If an index is over 30% fragmented, rebuild the index.

even if an index is more than 30% fragmented, a rebuild may not help if the database contains small tables with small indexes. In that case, you may have to manually drop and rebuild the index.

I would recommend to create a custom script which will scan all the indexes in tables and will do re-organize if it is 5-30% and re-build if it is 30%