SQL2008R2 Traversal of all table update statistics and Index rebuilds

Source: Internet
Author: User

----------------------------------------------

"2. The following is the update STATISTICS"

 DECLAREUpdatestatisticstablesCURSORRead_Only for      SELECTSst.name, Schema_name (sst.schema_id) fromSys.tables SSTWHERESst. TYPE= 'U'    DECLARE @name   VARCHAR( the),            @schema VARCHAR( +)         OPENUpdatestatisticstablesFETCH NEXT  fromUpdatestatisticstables into @name,@schema          while(@ @FETCH_STATUS <> -1 )      BEGIN          IF(@ @FETCH_STATUS <> -2 )            BEGIN                    DECLARE @sql NVARCHAR(1024x768)            SET @sql='UPDATE STATISTICS' + Quotename(@schema)                               +                               '.' + Quotename(@name)                      EXECsp_executesql@sql            END               FETCH NEXT  fromUpdatestatisticstables into @name,@schema      END         CLOSEUpdatestatisticstablesdeallocateUpdatestatisticstablesGO    

UPDATE STATISTICS Tblcompany--  table name use   tblcompany;--database name EXEC sp_updatestats-- Update all table statistics

-------------------------------------------------

"1. The following are index rebuilds"

Excerpt from: http://www.cnblogs.com/shijun/archive/2012/04/21/2461737.html

The project upgrade database was upgraded from SQL2000 to 2008R2, and today the database tables were scanned for fragmentation, and some table fragments were found to be large, so they decided to rebuild the index, and the online help was the best teacher, and the relevant script excerpt was checked.

Reference sys.dm_db_index_physical_stats

SELECT    object_name(object_id) asObjectName,object_id  asObjectid, index_id asIndexID, Partition_number asPartitionnum, Avg_fragmentation_in_percent asfra fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,'LIMITED')WHEREAvg_fragmentation_in_percent> 10.0  andindex_id> 0;

Rebuilding or re-organizing an index using sys.dm_db_index_physical_stats in a script ( from online help )

SETNOCOUNT on;DECLARE @objectid int;DECLARE @indexid int;DECLARE @partitioncount bigint;DECLARE @schemaname nvarchar( the); DECLARE @objectname nvarchar( the); DECLARE @indexname nvarchar( the); DECLARE @partitionnum bigint;DECLARE @partitions bigint;DECLARE @frag float;DECLARE @command nvarchar(4000); --conditionally Select tables and indexes from the Sys.dm_db_index_physical_stats function--and Convert object and index IDs to names.SELECT    object_id  asObjectid, index_id asIndexID, Partition_number asPartitionnum, Avg_fragmentation_in_percent asFrag into#work_to_do fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,'LIMITED')WHEREAvg_fragmentation_in_percent> 10.0  andindex_id> 0; --Declare the cursor for the list of partitions to be processed.DECLAREPartitionsCURSOR  for SELECT *  from#work_to_do;--Open the cursor.OPENpartitions;--Loop through the partitions. while(1=1)    BEGIN; FETCH NEXT            fromPartitions into @objectid,@indexid,@partitionnum,@frag; IF @ @FETCH_STATUS < 0  Break; SELECT @objectname = QUOTENAME(O.name),@schemaname = QUOTENAME(s.name) fromSys.objects asoJOINSys.schemas asS ons.schema_id=o.schema_idWHEREO.object_id = @objectid; SELECT @indexname = QUOTENAME(name) fromsys.indexesWHERE  object_id = @objectid  andindex_id= @indexid; SELECT @partitioncount = Count(*)         fromsys.partitionsWHERE object_id = @objectid  andindex_id= @indexid; --arbitrary decision point at which to switch between reorganizing and rebuilding.        IF @frag < 30.0            SET @command =N'ALTER INDEX' + @indexname +N' on' + @schemaname +N'.' + @objectname +N'REORGANIZE'; IF @frag >= 30.0            SET @command =N'ALTER INDEX' + @indexname +N' on' + @schemaname +N'.' + @objectname +N'REBUILD'; IF @partitioncount > 1            SET @command = @command +N'partition=' + CAST(@partitionnum  as nvarchar(Ten)); EXEC(@command); PRINTN'Executed:' + @command; END; --Close and deallocate the cursor.CLOSEpartitions;deallocatepartitions;--Drop the temporary table.DROP TABLE#work_to_do;GO

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.