Improve SQL Server Performance with sp_UpdateStats2 Statistics Maintenance

Why another statistics maintenance procedure?

Updating statistics is an important aspects of SQL Server performance tuning. Poorly maintained statistics can lead to bad cardinality estimates, inefficient execution plans, and unpredictable query performance.

For years, Ola Hallengren’s Maintenance Solution has been the gold standard for index and statistics maintenance on SQL Server. Its robustness, logging, and configurability make it a must-have for most production environments.

However, during real-world projects – especially those involving ETL workloads and intermediate data processing – I repeatedly faced limitations when using IndexOptimize only for statistics maintenance:

• The procedure name is misleading when it is used solely to update statistics
• Filtering statistics at a schema / table / statistic name level is not granular enough
• Sampling strategies based on table size are difficult to express
• ETL developers often need targeted, intermediate statistics refreshes, not full database maintenance

This is why I developed sp_UpdateStats2, a procedure strongly inspired by Ola Hallengren’s design, but dedicated exclusively to statistics maintenance, with additional flexibility.

---

Design principles

sp_UpdateStats2 follows a few core principles:

1. Stay compatible with Ola Hallengren’s ecosystem
2. Be DBA-safe and production-ready
3. Offer fine-grained filtering
4. Adapt statistics recomputation to table size
5. Remain usable by ETL developers without reinventing the wheel

🔧 Strong recommendation Install Ola Hallengren Maintenance Solution in a dedicated DBA database (e.g. DBA) Then deploy sp_UpdateStats2 in the same database + create a role that allow to execute the sp_UpdateStats2 and read/write to the CommandLog table to benefit from:

• CommandLog integration
• Proven logging and error handling patterns
• A clean separation between application and maintenance code

---

What sp_UpdateStats2 adds compared to IndexOptimize

1. A procedure dedicated to statistics

Even if IndexOptimize supports statistics updates, its name is index-centric and it's target is database centric. sp_UpdateStats2 is explicitly designed for statistics only, making its intent clearer.

---

2. Advanced filtering by name (Include / Exclude)

You can filter statistics at three levels, using name patterns:

• Schemas
• Tables
• Statistics

Each level supports:

• @Include…
• @Exclude…

This allows very precise targeting, which is especially useful in ETL pipelines.

---

3. Adaptive statistics strategy based on table size

The procedure introduces:

• Two rowcount thresholds
• Two sampling percentages

This allows you to:

• Use FULLSCAN on small tables
• Use controlled sampling on large tables
• Avoid wasting resources on massive fact tables when not required

---

Typical use cases

• ETL pipelines requiring intermediate statistics refresh
• Post-load statistics update on specific tables
• Large databases where full stats maintenance is too expensive
• Shared DBA standards exposed safely to developers

---

Installation overview

1. Install Ola Hallengren Maintenance Solution in a DBA database
2. Deploy sp_UpdateStats2.sql from the repository

👉 Source code: https://github.com/aetperf/dbatoolsScripts/blob/main/DBA/Stats/sp_UpdateStats2.sql

---

Default behavior and parameter philosophy

sp_UpdateStats2 is designed to be safe by default. When no include or exclude filters are specified, the procedure processes all user schemas, tables, and statistics in the target database(s), excluding system objects.

The default thresholds and sampling parameters are intentionally conservative:

• Small tables are updated using FULLSCAN, ensuring maximum accuracy where the cost is negligible
• Large tables use sampled statistics to avoid excessive IO and blocking
• All defaults aim to strike a balance between statistics quality and operational safety on production systems

This means you can run the procedure with minimal parameters and still obtain predictable, production-ready behavior, while retaining the ability to fine-tune execution when needed.

---

Parameter summary table

Parameter	Default (effective)	Values / format	What it does
@Databases	Proc DB if NULL	Comma list; supports wildcards %, exclusions -, and keywords like USER_DATABASES, ALL_DATABASES, AVAILABILITY_GROUP_DATABASES	Selects target databases ([GitHub][2])
@UpdateStatistics	ALL	ALL, INDEX, COLUMNS	Selects which stats types are updated; NULL exits without updating ([GitHub][2])
@OnlyModifiedStatistics	N	Y/N	If Y, update only stats with modification_counter > 0 (requires sys.dm_db_stats_properties) ([GitHub][2])
@StatisticsModificationLevel	NULL	0–100	Update only when modified rows exceed a percentage (or a dynamic threshold); cannot be combined with @OnlyModifiedStatistics='Y' ([GitHub][2])
@StatisticsSample	NULL	0–100	Forces a fixed sampling percent (cannot be combined with @StatisticsResample='Y') ([GitHub][2])
@StatisticsResample	N	Y/N	Uses WITH RESAMPLE (or partition-level resample for incremental stats when enabled) ([GitHub][2])
@PartitionLevel	Y	Y/N	For incremental stats: with @StatisticsResample='Y', can resample only selected partitions ([GitHub][2])
@TimeLimit	NULL	seconds (>= 0)	Stops when total runtime reaches the limit ([GitHub][2])
@Delay	NULL	seconds (0–86399)	Wait between each UPDATE STATISTICS command ([GitHub][2])
@LogToTable	N	Y/N	Logs each command to dbo.CommandLog in the procedure’s database ([GitHub][2])
@Execute	Y	Y/N	N prints commands instead of running them (still can log if enabled) ([GitHub][2])
@IncludeSchemas / @ExcludeSchemas	NULL	Comma list of LIKE patterns	Filters schema names ([GitHub][2])
@IncludeTables / @ExcludeTables	NULL	Comma list of LIKE patterns; can be TablePattern or Schema.TablePattern	Filters tables/views by name (schema-qualified supported) ([GitHub][2])
@IncludeStats / @ExcludeStats	NULL	Comma list of LIKE patterns; can be StatPattern or Schema.Table.StatPattern	Filters statistics by name (fully qualified supported) ([GitHub][2])
@SamplePercentSmallTables	100	1–100	Adaptive sampling for small tables (100 = FULLSCAN) ([GitHub][2])
@SamplePercentBigTables	10	1–100	Adaptive sampling for big tables ([GitHub][2])
@SamplePercentVeryBigTables	1	1–100	Adaptive sampling for very big tables ([GitHub][2])
@ThresholdBigTables	1000000	rows (>= 0)	Rowcount threshold to switch to “big table” sampling ([GitHub][2])
@ThresholdVeryBigTables	100000000	rows (>= 0)	Rowcount threshold to switch to “very big table” sampling ([GitHub][2])

Notes on constraints:

• @StatisticsSample and @StatisticsResample='Y' cannot be combined. ([GitHub][2])
• @OnlyModifiedStatistics='Y' cannot be combined with @StatisticsModificationLevel. ([GitHub][2])

10 usage samples

All examples assume the procedure is installed in DBA.dbo (adjust to your DBA database name).

1) Standard “update all statistics” on one database

EXEC DBA.dbo.sp_UpdateStats2
    @Databases        = 'MyAppDB',
    @UpdateStatistics = 'ALL';

2) Column statistics only

EXEC DBA.dbo.sp_UpdateStats2
    @Databases        = 'MyAppDB',
    @UpdateStatistics = 'COLUMNS';

3) Index statistics only

EXEC DBA.dbo.sp_UpdateStats2
    @Databases        = 'MyAppDB',
    @UpdateStatistics = 'INDEX';

4) Target multiple databases with keywords, wildcards, and exclusions

EXEC DBA.dbo.sp_UpdateStats2
    @Databases        = 'USER_DATABASES,-%Archive%,-DBA',
    @UpdateStatistics = 'ALL';

5) Include schemas and exclude tables using lists and wildcards

EXEC DBA.dbo.sp_UpdateStats2
    @Databases        = 'MyAppDB',
    @UpdateStatistics = 'ALL',
    @IncludeSchemas   = 'stg%,ods%,dwh%',
    @ExcludeTables   = 'tmp%,wrk%,save%';

6) Include/exclude tables

EXEC DBA.dbo.sp_UpdateStats2
    @Databases        = 'MyAppDB',
    @UpdateStatistics = 'ALL',
    @IncludeTables    = 'Fact%,Dim%',
    @ExcludeTables    = 'FactStaging%,DimTemp%';

7) Filter specific statistics

EXEC DBA.dbo.sp_UpdateStats2
    @Databases        = 'MyAppDB',
    @UpdateStatistics = 'ALL',
    @IncludeStats     = '%Sales%',
    @ExcludeStats     = '%_WA_Sys_%';

8) Update only modified statistics (fast incremental runs)

EXEC DBA.dbo.sp_UpdateStats2
    @Databases             = 'MyAppDB',
    @UpdateStatistics      = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable            = 'Y';

9) Update statistics based on a modification percentage threshold

EXEC DBA.dbo.sp_UpdateStats2
    @Databases                   = 'MyAppDB',
    @UpdateStatistics            = 'ALL',
    @StatisticsModificationLevel = 10;  -- percent

10) Demonstrate sampling controls: fixed sample vs resample vs adaptive thresholds

Fixed sample for everything:

EXEC DBA.dbo.sp_UpdateStats2
    @Databases        = 'MyAppDB',
    @UpdateStatistics = 'ALL',
    @StatisticsSample = 20;

Resample using last sample:

EXEC DBA.dbo.sp_UpdateStats2
    @Databases          = 'MyAppDB',
    @UpdateStatistics   = 'ALL',
    @StatisticsResample = 'Y';

Adaptive sampling tuned by table size (and throttled):

EXEC DBA.dbo.sp_UpdateStats2
    @Databases                    = 'MyAppDB',
    @UpdateStatistics             = 'ALL',
    @ThresholdBigTables           = 2000000,
    @ThresholdVeryBigTables       = 50000000,
    @SamplePercentSmallTables     = 100,
    @SamplePercentBigTables       = 15,
    @SamplePercentVeryBigTables   = 2,
    @Delay                        = 1,
    @TimeLimit                    = 1800;

---

Why this matters in some projects

This combination of:

• safe defaults
• pattern-based filtering
• adaptive sampling

allows DBAs to expose sp_UpdateStats2 to ETL developers without sacrificing control or stability, while still relying on Ola Hallengren’s proven logging and execution framework.

TL;DR

sp_UpdateStats2 is a statistics-only maintenance procedure for SQL Server, built on the proven foundations of Ola Hallengren’s Maintenance Solution, but extended for some real-world DBA and ETL use cases.

• Dedicated to statistics maintenance (no index ambiguity)

• Supports include / exclude filtering on schemas, tables, and statistics

  • Comma-separated lists
  • SQL wildcards (%)

• Automatically adapts sampling strategy based on table size

• Integrates with CommandLog for logging and auditing

• Safe defaults, production-ready

• Ideal for ETL pipelines needing intermediate or targeted statistics updates

👉 Install it alongside Ola Hallengren’s solution in a dedicated DBA database and use it when you need precision, control, and robustness for statistics maintenance.

Final thoughts

sp_UpdateStats2 is not meant to replace Ola Hallengren’s Maintenance Solution. On the contrary, it extends it, keeping the same philosophy while addressing very practical field needs:

• Clear separation of concerns
• Better naming
• More flexibility
• ETL-friendly usage

If you already trust Ola Hallengren’s scripts (and you should), sp_UpdateStats2 fits naturally into your DBA toolbox.