How to Update Statistics (stats) of All the Databases or Single Database in SQL Server - SQL Server DBA Tutorial

In this video you will learn how to update Statistics of All databases in SQL Server using SQL Server Management studio as well as using T-SQL Script. It shows different options of updating the statistics such as Index statistics, column statistics and statistics of single database using SQL Server Management Studio as well as using T-SQL Script.

Script used in the video
Script:
a) Update statistics of All User Databases

use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers]
WITH FULLSCAN
GO
use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers_2]
WITH FULLSCAN
GO
use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers_3]
WITH FULLSCAN
GO
use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers_4]
WITH FULLSCAN
GO
use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers_5]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[performance_counter_report_group_items]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[purge_info_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[snapshot_timetable_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[snapshots_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[source_info_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[supported_collector_types_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[wait_categories]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[wait_types]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[database_transaction_stats]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[metadata_table_blockers]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[storedprocedure_table_references]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[storedprocedure_usage_stats]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[table_usage_stats]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[active_sessions_and_requests]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[disk_usage]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[distinct_queries]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[distinct_query_to_handle]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[io_virtual_file_stats]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[log_usage]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[notable_query_plan]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[notable_query_text]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_latch_stats]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_memory_clerks]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_memory_nodes]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_process_memory]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_schedulers]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_wait_stats]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[performance_counter_instances]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[performance_counter_values]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[query_stats]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sql_process_and_system_memory]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_batch_manifests_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_cpu_memory_configurations_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_dac_collected_execution_statistics_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_smo_properties_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_volumes_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[trace_data]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[trace_info]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[computers_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[cpu_utilization_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[dacs_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[databases_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[datafiles_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[filegroups_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[logfiles_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[smo_servers_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[space_utilization_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[volumes_internal]
WITH FULLSCAN
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_staging].[consistent_batch_manifests_internal]
WITH FULLSCAN
GO
use [Reportserver]
GO
UPDATE STATISTICS [dbo].[ConfigurationInfo]
WITH FULLSCAN
GO
use [Reportserver]
GO
UPDATE STATISTICS [dbo].[DataSets]
WITH FULLSCAN
GO
use [Reportserver]
GO
UPDATE STATISTICS [dbo].[DataSource]
WITH FULLSCAN
GO
use [Reportserver]
GO
UPDATE STATISTICS [dbo].[Employee]
WITH FULLSCAN

2- Update Index Statistics of All User Databases

use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers]
WITH FULLSCAN,INDEX
GO
use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers_2]
WITH FULLSCAN,INDEX
GO
use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers_3]
WITH FULLSCAN,INDEX
GO
use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers_4]
WITH FULLSCAN,INDEX
GO
use [SalesOrders]
GO
UPDATE STATISTICS [dbo].[Customers_5]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[performance_counter_report_group_items]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[purge_info_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[snapshot_timetable_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[snapshots_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[source_info_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[supported_collector_types_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[wait_categories]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [core].[wait_types]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[database_transaction_stats]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[metadata_table_blockers]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[storedprocedure_table_references]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[storedprocedure_usage_stats]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [custom_snapshots].[table_usage_stats]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[active_sessions_and_requests]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[disk_usage]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[distinct_queries]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[distinct_query_to_handle]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[io_virtual_file_stats]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[log_usage]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[notable_query_plan]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[notable_query_text]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_latch_stats]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_memory_clerks]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_memory_nodes]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_process_memory]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_schedulers]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[os_wait_stats]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[performance_counter_instances]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[performance_counter_values]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[query_stats]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sql_process_and_system_memory]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_batch_manifests_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_cpu_memory_configurations_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_dac_collected_execution_statistics_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_smo_properties_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[sysutility_ucp_volumes_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[trace_data]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [snapshots].[trace_info]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[computers_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[cpu_utilization_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[dacs_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[databases_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[datafiles_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[filegroups_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[logfiles_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[smo_servers_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[space_utilization_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_core].[volumes_internal]
WITH FULLSCAN,INDEX
GO
use [PerformanceDWH]
GO
UPDATE STATISTICS [sysutility_ucp_staging].[consistent_batch_manifests_internal]
WITH FULLSCAN,INDEX
GO
use [Reportserver]
GO
UPDATE STATISTICS [dbo].[ConfigurationInfo]
WITH FULLSCAN,INDEX
GO
use [Reportserver]
GO
UPDATE STATISTICS [dbo].[DataSets]
WITH FULLSCAN,INDEX
GO
use [Reportserver]
GO
UPDATE STATISTICS [dbo].[DataSource]
WITH FULLSCAN,INDEX
GO
use [Reportserver]
GO
UPDATE STATISTICS [dbo].[Employee]
WITH FULLSCAN,INDEX



How to Update Statistics (stats) of All the Databases or Single Database in SQL Server

No comments:

Post a Comment