SQL Server-刪除統計值Job

Posted by: bart30508 | in SQL Server | 10 months ago |

發現有一個Database, 老是會發生其某張Table的JOINL SQL Server Optimzer老是選取不好的執行計畫, Index也已最佳化, 果斷想到是統計值問題.

但是更新統計值問題後, 問題仍然存在.

原來是重複統計值問題!

以下建立一個Job, 定期清理由系統自行建立的重複統計值

--找尋是否有同一個欄位名稱的統計值重複,如果有建立刪除語法
DECLARE @EXEC NVARCHAR(2000)

DECLARE MYCURSOR CURSOR
FOR
WITH autostats (
    object_id
    ,stats_id
    ,name
    ,column_id
    )
AS (
    SELECT sys.stats.object_id
        ,sys.stats.stats_id
        ,sys.stats.name
        ,sys.stats_columns.column_id
    FROM sys.stats
    INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
        AND sys.stats.stats_id = sys.stats_columns.stats_id
    WHERE sys.stats.auto_created = 1
        AND sys.stats_columns.stats_column_id = 1
    )
SELECT 'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id) + '].[' + OBJECT_NAME(sys.stats.object_id) + '].[' + autostats.name + ']'
FROM sys.stats
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
    AND sys.stats.stats_id = sys.stats_columns.stats_id
INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id
    AND sys.stats_columns.column_id = autostats.column_id
INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id
    AND sys.stats_columns.column_id = sys.columns.column_id
WHERE sys.stats.auto_created = 0
    AND sys.stats_columns.stats_column_id = 1
    AND sys.stats_columns.stats_id != autostats.stats_id
    AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0;

OPEN MYCURSOR

FETCH NEXT
FROM MYCURSOR
INTO @EXEC

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT (@EXEC)

    EXEC (@EXEC)

    FETCH NEXT
    FROM MYCURSOR
    INTO @EXEC
END

CLOSE MYCURSOR

DEALLOCATE MYCURSOR

Reference: https://dotblogs.com.tw/stanley14/2018/01/14/statistics_overlapping

Currently unrated
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (146)

Feeds

RSS / Atom

SQL Server-刪除統計值Job

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.