Updating Statistics with Ola Hallengren’s Script

I am a HUGE fan of updating statistics as part of regular maintenance.  In fact, if you don’t know if you have a step or job that updates out of statistics on a regular basis, go check now!  This post will still be here when you get back 😊 At any rate, for a long […]

Do you need to update statistics after an upgrade?

This post originally went live on May 11, 2018, but modifications were made on May 14, 2018 after some additional internal discussions with Microsoft.  Changes made on May 14, 2018 are in blue.   There are a variety of methods we use for helping customers upgrade to a new SQL Server version, and one question we […]

SQLskills SQL101: Updating SQL Server Statistics Part II – Scheduled Updates

In last week’s post I discussed the basics of how automatic updates to statistics occur in SQL Server.  This week I want to talk about scheduled (aka manual) updates, because as you might remember, we really want to control when statistics are updated. In terms of updating statistics you have multiple options, including: Update Statistics […]

SQLskills SQL101: Updating SQL Server Statistics Part I – Automatic Updates

One of my favorite topics in SQL Server is statistics, and in my next two posts I want to cover how they are updated: either by SQL Server or by you. We’ll start with updates by SQL Server, and these happen automatically. In order for automatic updates of statistics to occur, the AUTO UPDATE STATISTICS […]

SQL Server 2016 Upgrade Testing with the New Cardinality Estimator: Context Matters

This week I’ve been working with SQL Server 2016 and it’s been a lot of fun – this is truly a great release from the SQL Server team.  Yesterday I was working on some upgrade testing with regard to the new Cardinality Estimator (CE), introduced in SQL Server 2104 and well explained in this whitepaper […]

Further Testing with Automatic Updates to Statistics

A new question on an older post, Understanding When Statistics Will Automatically Update, came in this week.  If you look in the comments, you see that Rajendra asks: …I want know, whether schema change of a table (adding new / (deleting an existing) column) also qualifies for UPDATE STATS? It’s an interesting question that I’d […]

Statistics Starters Presentation: Scripts, the Database, and Some Answers

This past Tuesday I presented “Statistics Starters” for the PASS DBA Fundamentals Virtual Chapter.  You can read the abstract here, and as you may have guessed from the title, it was a 200 level session on statistics appropriate for anyone who knew of statistics in SQL Server, but wasn’t exactly sure how they were created, […]

SQL Server Maintenance Plans and Parallelism – Index Rebuilds

In my previous post, SQL Server Maintenance Plans and Parallelism – CHECKDB, we looked at the degree of parallelism used when CHECKDB is run.  It ultimately depends on SQL Server Edition and the max degree of parallelism setting for the instance, which is not the case for index rebuilds (today’s topic, as you probably surmised!). […]

Understanding When Statistics Will Automatically Update

During the PASS Summit in November I presented a session on Demystifying Database Statistics (if you attended Summit but missed it, you can stream it from here). During the session I went through a demo that showed the automatic update of a statistic. The database had the Auto Update Statistics option enabled, and I had […]

New Statistics DMF in SQL Server 2008R2 SP2

There is a wonderful new DMF that is available in Service Pack 2 for SQL 2008R2: sys.dm_db_stats_properties.  There are many other DMFs and DMVs that include “stats” in their name – sys.dm_db_index_physical_stats, dm_exec_query_stats and sys.dm_os_wait_stats to name a few.But how many of those are about the statistics that describe data distribution in a column?  Up […]