1 / 8

SQL Administration Tasks: Updating Distribution Statistics, Rebuilding Indexes, and Maintaining Full-Text Indexes

This article discusses the essential SQL administration tasks for keeping databases running smoothly, including updating distribution statistics, rebuilding indexes, and maintaining full-text indexes. It also covers alerts and how to create a maintenance plan.

randyi
Download Presentation

SQL Administration Tasks: Updating Distribution Statistics, Rebuilding Indexes, and Maintaining Full-Text Indexes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 3. SQL Administration tasks • Objectives • Keep the databases long time running • Contents • Update distribution statistics • Rebuild indexes • Maintain full-text indexes • Alerts • Maintenance Plan • Practicals • Rebuild and update database indexes • Making Alerts • Making Maintenance Plan • Summary

  2. Updating Distribution Statistics • verify that the autocreate and autoupdate statistics options are enabled (the default) for a database, • right-click the database in SQL Server Enterprise Manager, click Properties, and then click the Options tab. Verify that both of the check boxes are selected. • SQL query analyzer To manually update statistics on a table: • SQL query analyzer To manually force create statistics on a table: $ man man $ man –k tcp $ man passwd $ man 5 passwd Update statistics northwind.dbo.customers Create statistics stats1 on northwind.dbo.customers (customerid) with fullscan, norecompute

  3. Maintaining Indexes • After a while, the database’s become fragmentated, this is normally defragmented automatically. • Indexes control the order and placement of data stored in datapages of a table. • Databases can become slow and start consume memory and cpu cycles. • This makes the need for rebuilding indexes in databases regulary. • Database Maintenance Plan Wizard in SQL Server Enterprise Manager. • You can rebuild indexes using the Transact-SQL Drop index dbo.customers.northwind_customers_id Create index northwind_customers_id On northwind.dbo.customers (customerid)

  4. Maintaining Full-Text Indexes • Full-text indexes are indexes of all character data in one or more tables in a database. • use the Full-Text Indexing Wizard in SQL Server Enterprise Manager to • enable full-text indexing for a database, one or more tables, and specified columns within the tables. • use the enterprice manager -> Database -> Full text catalog (right click) to • New • Rebuild • Re populate • Remove Double click or right click on any full text catalogue to change its properties/scheduling and so on • The indexes are stored in the file system • Full text indexes must be populated with data from databases/tables

  5. Configuring event Alerts • Let the system speak to you! • SQL Server Event Alerts • Error number. A unique number for each error. • Message string. Diagnostic information regarding the cause of the error, including the object name • Severity. Low numbers indicate information messages and high numbers indicate serious errors. • State code. Used by Microsoft support engineers to find the source code location for the error. • Procedure name. The stored procedure name if the error occurred in a stored procedure. • Line number. The line number of a statement in a stored procedure that caused the error. • Performance Conditions. SQL Server 2000 provides objects and counters that are used by Windows 2000 System Monitor • Configuring Alerts • Using the Create Alert Wizard • Using SQL Server Enterprise Manager Directly Management -> SQL Server Agent -> Alert (right click to make new or click on event) • Responses • one or more operators can be notified using e-mail, pager, or NET SEND. • A custom notification message can be added • A specified • job can also be executed

  6. New Alert from enterprice manager

  7. Creating a Database Maintenance Plan • Using the Database Maintenance Plan Wizard The Database Maintenance Plan Wizard allows you to configure the following tasks to execute automatically according to specified schedules. • Rebuilding indexes using a specified fill factor • Shrinking a database to a specified size • Updating distribution statistics • Performing DBCC consistency checks • Backing up database and transaction log files • Setting up log shipping • To start the Database Maintenance Plan Wizard, from the Tools menu: 1. click Database Maintenance Planner, or in the console tree, right-click Database Maintenance Plans, in the Management container 2. then click New Maintenance Plan to display the Welcome To The Database Maintenance Plan Wizard page.

  8. Viewing and Modifying Database Maintenance Plans • After the database maintenance plan has been created, you can view and modify it in one of two ways. • Management container in the instance • expand the SQL Server Agent container, and then click the Jobs container. • Using the Sqlmaint Utility • command-prompt utility can also be used to create and execute a database maintenance plan. • Microsoft recomen using the wizard

More Related