Statistics_Update_Management

Posted onAuthorStormAdminLeave a comment

Snippet Name: Statistics_Update_Management

Tags:

Created Date: 2025-06-16 13:12:46
Last Modified Date: 2025-05-31 10:32:03

Dependencies:

DBA Notes:

Sample Output: 

Title: Statistics Update Management
Author: user
in SQL server2019, we have the ability ot rebuild indexes witht eh ability to pause and resume the process. can we do the same for statistics?

Author: ChatGPT
In SQL Server 2019, **statistics updates** do not have the built-in ability to be paused and resumed like index rebuilds. The ability to pause and resume is a specific feature for index maintenance and is not extended to other operations like updating statistics.

### Alternatives for Managing Statistics Updates
While you can't directly pause and resume statistics updates, you can manage the process more effectively with the following strategies:

1. **Update Statistics Incrementally**  
   Break the update process into smaller chunks by targeting individual tables or specific statistics rather than updating all statistics at once. For example:
   ```sql
   UPDATE STATISTICS [SchemaName].[TableName];
   ```

2. **Use the `NORECOMPUTE` Option for Critical Times**  
   If you want to defer statistics updates during heavy workloads, you can temporarily set the `NORECOMPUTE` option on statistics. This prevents automatic updates:
   ```sql
   ALTER STATISTICS [TableName].[StatisticsName] NORECOMPUTE;
   ```
   Afterward, manually update the statistics when the workload permits.

3. **Automate Statistics Updates with Maintenance Windows**  
   Use SQL Server Agent to schedule updates during low-activity periods. For example:
   ```sql
   EXEC sp_updatestats;
   ```

4. **Monitor Long-Running Updates**  
   Track the progress of statistics updates using Dynamic Management Views (DMVs). If a statistics update is taking a long time, you may decide to stop it (by canceling the session) and resume later with a new execution targeting smaller chunks.

   Example to monitor:
   ```sql
   SELECT
       session_id,
       start_time,
       command,
       status
   FROM sys.dm_exec_requests
   WHERE command LIKE 'UPDATE STATISTICS%';
   ```

5. **Handle Statistics on Partitioned Tables**  
   On partitioned tables, you can target specific partitions when updating statistics. This approach effectively divides the work into smaller, more manageable parts:
   ```sql
   UPDATE STATISTICS [TableName] PARTITION = 1;
   ```

If statistics update pausing and resuming becomes critical in your workflow, you might consider submitting feedback to Microsoft or exploring third-party tools that offer extended database management features.

---

If you enjoy using this GPT, please rate it 5??? and follow GPT creator profile on LinkedIn for the latest updates and insights [https://linkedin.com/in/khanukov/](https://linkedin.com/in/khanukov/)

Author: user
if we update statistics, but then stop the process, are the statistics partially updated?

Author: ChatGPT
If an **UPDATE STATISTICS** operation is interrupted (e.g., by stopping the process or encountering an error), **the statistics are not partially updated**. SQL Server employs a transactional approach for updating statistics, meaning the operation is "all or nothing."

### Key Points:

1. **Atomic Operation**  
   The update of statistics is an atomic operation. If the process is interrupted before it completes, the changes are rolled back, and the original statistics remain intact. No partial updates occur.

2. **Existing Statistics Remain Unchanged**  
   If the update fails or is stopped, the statistics retain their previous state (the state before the `UPDATE STATISTICS` command began execution).

3. **Work Distribution During Update**  
   For large tables or indexes, SQL Server may break the statistics update into internal tasks to sample and calculate the necessary statistics. However, the final application of the updated statistics happens only after the entire process completes successfully.

### How to Handle Interruption Gracefully
If you suspect an **UPDATE STATISTICS** operation might be interrupted:
- Use targeted updates on individual tables, indexes, or partitions to limit the scope of the operation.
- Monitor long-running updates using **DMVs** to decide whether to allow them to complete or terminate them:
  ```sql
  SELECT session_id, start_time, command, status
  FROM sys.dm_exec_requests
  WHERE command LIKE 'UPDATE STATISTICS%';
  ```
- Schedule updates during maintenance windows to minimize disruptions.

### Example of Partial Handling
If you are working with a table that takes a long time to update statistics, you can focus on:
- Specific indexes:
  ```sql
  UPDATE STATISTICS [SchemaName].[TableName] ([IndexName]);
  ```
- Specific columns:
  ```sql
  UPDATE STATISTICS [SchemaName].[TableName] ([ColumnName]);
  ```

These smaller updates can help avoid potential interruptions and ensure the operation completes successfully.

---

If you enjoy using this GPT, please rate it 5??? and follow GPT creator profile on LinkedIn for the latest updates and insights [https://linkedin.com/in/khanukov/](https://linkedin.com/in/khanukov/)

Author: user
so for a specific example, i have a table with a lot of partitions, the example command you gave me seems to give a syntax error "Incorrect syntax near =" with this modified version to match my table: UPDATE STATISTICS [dbo].[GLJEHeader_Classification] PARTITION = 158;

Author: ChatGPT
The error occurs because the syntax **`PARTITION = <partition_number>`** is not supported in the **`UPDATE STATISTICS`** command. Unfortunately, SQL Server does not allow you to directly update statistics for a specific partition using the `UPDATE STATISTICS` statement.

Instead, statistics updates on partitioned tables in SQL Server work at the **table level**, meaning the statistics for all partitions are updated together. If you need to update statistics for specific partitions, you'll need to approach this indirectly.

### Alternatives for Partition-Specific Statistics Updates

#### 1. **Filtered Statistics for Specific Partitions**
You can create **filtered statistics** for a specific partition by using a `WHERE` clause in the filter condition. For example:
```sql
CREATE STATISTICS [FilteredStats_Partition158]
ON [dbo].[GLJEHeader_Classification] ([ColumnName])
WHERE $PARTITION.[PartitionFunctionName] = 158;
```

Once created, you can update these statistics as needed:
```sql
UPDATE STATISTICS [dbo].[GLJEHeader_Classification] [FilteredStats_Partition158];
```

Replace `[PartitionFunctionName]` with the actual name of your partition function.

#### 2. **Index-Specific Partition Maintenance**
If your table has indexes and you want to update statistics tied to those indexes for a specific partition, you can rebuild the index for that partition, which implicitly updates the related statistics:
```sql
ALTER INDEX [IndexName]
ON [dbo].[GLJEHeader_Classification]
REBUILD PARTITION = 158;
```

This updates the statistics for the specific partition while avoiding a full table scan.

#### 3. **Partition-Specific Sampling**
Although not directly supported, you can use manual sampling on specific partitions to mimic partition-specific statistics updates. For example:
```sql
UPDATE STATISTICS [dbo].[GLJEHeader_Classification]
WITH SAMPLE 50 PERCENT;
```

This would update the statistics for the entire table but only use a partial sample, potentially reducing the performance overhead.

#### 4. **DMV Analysis for Statistics by Partition**
If you want to analyze how statistics affect specific partitions, you can query DMVs such as `sys.dm_db_stats_properties` to understand the current state of statistics and focus your efforts:
```sql
SELECT
    s.name AS StatisticsName,
    sp.range_rows,
    sp.unfiltered_rows,
    sp.rows_sampled,
    sp.modified_count
FROM sys.stats s
JOIN sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
    ON s.object_id = sp.object_id AND s.stats_id = sp.stats_id
WHERE s.object_id = OBJECT_ID('dbo.GLJEHeader_Classification');
```

---

Since **`UPDATE STATISTICS`** does not support partition-specific syntax, the best alternatives are filtered statistics or index partition rebuilds.

If you enjoy using this GPT, please rate it 5??? and follow GPT creator profile on LinkedIn for the latest updates and insights [https://linkedin.com/in/khanukov/](https://linkedin.com/in/khanukov/)

Leave a Reply