Member Login
  • Products
    • Qure Suite
    • Qure Analyzer
    • Qure Optimizer
  • Downloads
    • Qure Analyzer (Free)
    • Qure Optimizer (Free Trial)
  • Support
    • Contact Support
    • Frequently Asked Questions
    • Limitations and Known Issues
  • Resources
    • Qure Optimizer Resources
    • Qure Analyzer Resources
    • Whitepapers
    • Videos and Tutorials
    • eBooks
    • Attend an Online Demo
    • inTune eMagazine
    • SQL Server Tuning Blog
  • Learn More
    • About Workload Tuning
    • SQL Server Tuning
    • SQL Server Performance
    • SQL Server Performance Tuning
    • Tips
    • SQL Server Articles
  • Company
    • About DBSophic
    • Management
    • Partners
    • News
    • Success Stories
    • Customer Testimonials
    • Selected Customers
  • Contact Us
Subscribe to blog

SQL Server Tuning Blog

By SQL Server MVP Ami Levin

Subscribe to feed Viewing entries tagged Statistics

Index rebuilds and statistics updates

Posted by Ami Levin
Ami Levin
My name is Ami Levin, and I've been working with SQL Server for over 15 years. I
User is currently offline
on Wednesday, 25 April 2012
in SQL Server Tuning

An interesting discussion came up lately on the MVP private forums regarding index rebuilds, recompilation and statistics updates. Although the debate spanned too many aspects for me to cover here in this short post, I think that one important point should be emphasized as it might be making you do redundant work or even worse - unknowingly degrade the performance of your workload.

When you rebuild an index, a full scan of all index keys is performed. SQL Server uses this opportunity not only to rebuild the index, but also to update the related statistics. What is unique about this statistics update is the fact that it is equivalent to an update with the full-scan option. The default statistics sampling uses only a small fraction of the keys in order not to load your production server. A statistics based on a full scan of the data has the potential to be more accurate, therefore provide the query optimizer with better information, allowing it in turn to produce better performing execution plans for your workload queries. Also remember that a statistics update flushes all plans that use it from the procedure cache forcing a recompilation of the related queries.

...
Tags: Maintenance, Performance, Statistics
Hits: 1560 0 Comments Continue reading

Latest Posts

  • A Month of Joins - T-SQL Tuesday #37
    T-SQL Tuesday is now entering its fourth year! Sebastian Meine, who's hosting this month's T-SQL Tue
    Read More
  • Where did I leave my keys?
    "If you walk into a room full of DBAs or DB developers and you feel like having the same kind o
    Read More
  • Qure Analyzer V1.5 Released
    Just two months ago, we launched the beta version of Qure Analyzer V1.5. We would like to thank the
    Read More

Tag Cloud

SQL Graphics RTM common pitfalls Announcements iPad Maintenance MVP TSQL2sDay Performance SQL Server 2012 Qure Analyzer Articles Performance Statistics hardware installation data types Publications Presentations Statistics HA/DR SSDT

Blog Calendar

Loading ...
© 2011 DBSophic Ltd. All rights reserved. | Legal