S SQL Server database maintenance and rebuild index

Source: Internet
Author: User

Step one: See if maintenance is required to see if scan density/scan density is 100%

    1. DECLARE @table_id int
    2. SET @table_id=object_id (' table name ')
    3. DBCC SHOWCONTIG (@table_id)

Step two: Refactor SQL Server database table indexes

    1. DBCC DBREINDEX (' table name ', PK_ index name, 100)

Redo the first step, such as finding the scan density/scan density or less than 100% to refactor all indexes of the table, does not necessarily reach 100%.

    1. DBCC DBREINDEX (' table name ', ' ', 100 ')

Rebuilding indexes on all tables in a database

Use My_database; DECLARE @name varchar (100)

DECLARE authors_cursor cursor for Select [name] from sysobjects where xtype= ' u ' ORDER by ID

OPEN Authors_cursor

FETCH NEXT from Authors_cursor to @name

While @ @FETCH_STATUS = 0 BEGIN

DBCC Dbreindex (@name, ", 90)

FETCH NEXT from Authors_cursor to @name END

Deallocate authors_cursor

Reference:

DBCC showcontig (Transact-SQL) http://msdn.microsoft.com/zh-cn/library/ms175008 (v=sql.90). aspx

DBCC dbreindex (Transact-SQL) http://msdn.microsoft.com/zh-cn/library/ms181671 (v=sql.90). aspx

[S]sql Server database Maintenance and rebuild index

Related Article

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.