Transcript Ch10

SQL Server 2005
Implementation and
Maintenance
Chapter 10: Maintaining and
Automating SQL Server
Maintaining Indexes
• Page splits cause database
fragmentation
• Query
DM_DB_INDEX_PHYSICAL_STATS to
find fragmentation
– USE AdventureWorks;
SELECT INDEX_ID,
AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(db_id(),Object_ID(N'Sales.SalesOrderDet
ail'), NULL, NULL, 'DETAILED')
• find the fragmentation on the
Sales.SalesOrderDetail table in the
Adventure• Works database
© Wiley Inc. 2006. All Rights Reserved.
Reorganizing Indexes
• If database fragmentation is less than
10%, no action is required
• 20 – 30% requires you to reorganize
indexes
• Use ALTER INDEX REORGANIZE
– USE AdventureWorks
ALTER
INDEX PK_ProductPhoto_ProductPhotoID
ON Production.ProductPhoto
REORGANIZE
reorganize the PK_Product_Product-PhotoID index on the
Production.ProductPhoto table
© Wiley Inc. 2006. All Rights Reserved.
Rebuild Indexes
• More than 30% fragmentation
requires you to rebuild indexes
• There are two methods
– CREATE INDEX WITH DROP
EXISTING
– ALTER INDEX REBUILD
© Wiley Inc. 2006. All Rights Reserved.
Maintaining Statistics
• SQL Server formulates an execution
plan to run queries
– This is a map of available tables and
indexes
• Statistics are used to create an
execution plan
• Statistics are updated automatically by
default
• If you have changed this for an index
run UPDATE STATISTICS to bring it up
to date
© Wiley Inc. 2006. All Rights Reserved.
Using DBCC CHECKDB
•
•
Checks allocation, logical, and structural integrity of objects
It has several options
– NOINDEX
• intensive checks of nonclustered indexes should not be performed
– REPAIR_REBUILD
– REPAIR_ALLOW_DATA_LOSS
– ALL_ERRORMSGS
• If this is not used, then only the first 200 errors display.
– NO_INFOMSGS
• This suppresses all informational messages.
– TABLOCK
• TABLOCK
• causes DBCC CHECKDB to obtain locks instead of using a
snapshot, which makes it run faster.
– ESTIMATE_ONLY
• Displays the estimated amount of tempdb space needed to run
DBCC CHECKDB with all the other specified options. The actual
database check is not performed.
– PHYSICAL_ONLY
• Using this option causes DBCC CHECKDB to run much faster than
performing a full check, which makes it well suited for frequent use
on production systems.
– DATA_PURITY
• This causes DBCC CHECKDB to check the database for column
values that are not valid or are out of range.
© Wiley Inc. 2006. All Rights Reserved.
Shrinking Data Files
• When users add data, the data
files may grow in size
• When data is removed, the files
are not automatically reduced in
size
• Files can be shrunk manually or
on a scheduled basis
© Wiley Inc. 2006. All Rights Reserved.
Automation Basics
• SQL Agent performs automation tasks
• The Agent has three subcomponents
– Jobs
– Alerts
– Operators
• Make sure the Agent is set to start
automatically
• Have it log on with a domain account
© Wiley Inc. 2006. All Rights Reserved.
Database Mail
• Database Mail allows SQL Server
to send SMTP email
• It uses Service Broker so it runs
asynchronously
• For the SQL Agent to send mail
the MSDB database must be a
mailhost
© Wiley Inc. 2006. All Rights Reserved.
Operators
• An operator is an object that
contains contact information for a
DBA
– Name
– Email address
– Pager address
– Net send address
– Hours available
© Wiley Inc. 2006. All Rights Reserved.
Jobs
• A job is a series of tasks that can
be scheduled
• They have three main parts
– Steps
• The action(s) to perform
– Schedules
• When to perform the action(s)
– Notifications
• Whom to notify on completion
© Wiley Inc. 2006. All Rights Reserved.
Standard Alerts
• Alerts are fired when an event
occurs
• They can be configured to notify
operators and/or run a job
• Standard alerts are based on
built-in error messages or severity
levels
© Wiley Inc. 2006. All Rights Reserved.
Custom Alerts
• The built-in error messages do not
cover every circumstance
• You can create custom error messages
to based alerts on
– They must start at 50,001
– They are fired using the RAISERROR()
command
– They accept parameters
• %ls and %s for strings
• %ld and %d for numbers
© Wiley Inc. 2006. All Rights Reserved.
Performance Alerts
• These are based on Windows
performance counters
– I.e. Processor: % Processor Time
• When the counter reaches the
specified threshold, the alert fires
© Wiley Inc. 2006. All Rights Reserved.
WMI Alerts
• These are based on Windows
Management Instrumentation
• This allows you to fire alerts under
special circumstances:
– When a DDL statement is run
• ALTER LOGIN
• CREATE TABLE
© Wiley Inc. 2006. All Rights Reserved.
Maintenance Plans
• Maintenance needs to be run
regularly
• You can create jobs manually to
do this, but that is tedious
• It is best to use the Maintenance
Plan Wizard
– This will create a Maintenance Plan
to automate necessary maintenance
© Wiley Inc. 2006. All Rights Reserved.
Copying Databases
• The easiest way to copy a database is
the Copy Database Wizard
• There are several reasons to copy a
database
– Moving databases to an upgraded server
– Create a standby server for emergencies
– Copy a database from development to
production
© Wiley Inc. 2006. All Rights Reserved.