SA0195 : Duplicate statistics must be removed

Introduction

Automatically created statistics in SQL Server can lead to redundant data management and increased resource usage, potentially affecting performance.

Description

In SQL Server, statistics provide essential data distribution information to the query optimizer, improving query plan selection. However, when statistics are automatically created on columns that later have an index or manual statistics applied, these autogenerated statistics can be redundant.

For example:

-- Example of a table with duplicate statistics
CREATE TABLE Users (ID INT, Name VARCHAR(50));

-- Automatically created statistics
SELECT * FROM Users WHERE ID = 10;

-- Index creation which may duplicate statistics
CREATE INDEX idx_ID ON Users(ID);

This situation is problematic because SQL Server continues to update and maintain the automatically created statistics even when they offer no additional value over the index statistics, leading to unnecessary resource consumption.

Resource consumption is increased due to redundant statistics management and updates.
Query performance can be negatively impacted by outdated or unnecessary statistics.

How to fix

This guide provides a method to resolve issues related to redundant automatically created statistics in SQL Server, as identified by the SQL Enlight analysis rule sa0195 .

Follow these steps to address the issue:

1.Identify the automatically created statistics that are deemed redundant. You can use the DMVs like sys.stats to find the statistics associated with specific tables and fields.

2.Evaluate whether these statistics are redundant by checking if indexes or manually created statistics exist for the same columns.

3.Backup relevant database objects and statistics to ensure no unintended data loss occurs during modification.

4.Drop the redundant automatically created statistics using the DROP STATISTICS command. Use the following syntax:

For example:

-- Drop automatically created statistics that are duplicates
DROP STATISTICS schema_name.table_name.statistics_name;

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Analysis Rules