更新SQL Server实例所有数据库表统计信息-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

更新SQL Server实例所有数据库表统计信息

简介: # 引出问题 自从上次菜鸟为老鸟解决了《RDS SQL SERVER 解决中文乱码问题》问题,老鸟意犹未尽,决定再想个招来刁难刁难菜鸟:“我最近做T-SQL性能调优的时候,经常发现执行计划中的统计信息不准确,导致SQL Server查询性能低下,想个办法帮我一次性更新实例所有数据库下所有表统计信息吧?” # 分析问题 “要一次性更新实例级别所有数据库下所有表统计信息啊,这个还真的有点犯难”,

引出问题

自从上次菜鸟为老鸟解决了《RDS SQL SERVER 解决中文乱码问题》问题,老鸟意犹未尽,决定再想个招来刁难刁难菜鸟:“我最近做T-SQL性能调优的时候,经常发现执行计划中的统计信息不准确,导致SQL Server查询性能低下,想个办法帮我一次性更新实例所有数据库下所有表统计信息吧?”

分析问题

“要一次性更新实例级别所有数据库下所有表统计信息啊,这个还真的有点犯难”,菜鸟一边小声嘀咕,一边不停的问G哥,终于功夫不负有心人,发现了两个非常有意思的系统存储过程。这两个系统存储过程均为SQL Server未对外公开(Undocumented)的系统存储过程,但是对于DBA或者日常数据库管理人员,非常有用。今天我们就可以使用它们来快速简洁的解决掉老鸟的问题。
查询这两个系统存储过程,需要在sys.all_objects中查找:

USE master
GO
SELECT * 
FROM sys.all_objects WITH(NOLOCK)
WHERE name IN('sp_msforeachtable','sp_msforeachdb')

如下截图
01.png
简单的功能解释
sys.sp_MSforeachdb:SQL Server遍历该实例下所有的数据库,包含系统数据库。sys.sp_MSforeachtable:SQL Server遍历某一个数据库下所有的表对象。

解决问题

好了,有了对这两个系统存储过程粗略的认识,让我们来如何解决老鸟的问题。话不多说,直接代码伺候

USE master
GO

DECLARE
    @sql NVARCHAR(MAX)
;

SET
    @sql = N'
USE [?]
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'') 
BEGIN
    RAISERROR(N''----------------------------------------------------------------
Search on database: ?'', 10, 1) WITH NOWAIT
    EXEC SYS.SP_MSFOREACHTABLE N''
    UPDATE STATISTICS * WITH FULLSCAN
    RAISERROR(''''on table:*'''',10,1) with nowait''
    ,@replacechar =N''*''
    ,@whereand=N''and o.name NOT LIKE ''''#%''''''
END
'
;

EXEC SYS.SP_MSFOREACHDB @sql,@replacechar=N'?'

哇,相当牛X,总共仅仅24行代码解决了老鸟的所有问题,一次性,简单,快捷,简洁的更新了老鸟的表统计信息,这下老鸟不会再遇到统计信息未及时更新的问题了。
嘚瑟下执行结果输出,限于篇幅,省略掉了一些输出:

----------------------------------------------------------------
Search on database: ReportServer
on table:[dbo].[History]
on table:[dbo].[ConfigurationInfo]
on table:[dbo].[Catalog]
...
on table:[dbo].[ServerUpgradeHistory]
----------------------------------------------------------------
Search on database: ReportServerTempDB
on table:[dbo].[ExecutionCache]
on table:[dbo].[SnapshotData]
...
on table:[dbo].[SessionData]
----------------------------------------------------------------
Search on database: AdventureWorks2008R2
on table:[Production].[ProductInventory]
on table:[Sales].[SpecialOffer]
on table:[Person].[Address]
...
on table:[dbo].[ErrorLog]
----------------------------------------------------------------
...

写在最后

这段脚本很好很强大,威猛又持久,如果需要在产品环境使用,请选择在流量低谷时段执行,以免对你的生产线SQL Server数据库造成超预期的影响。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: