Transcript lesson26

Maintaining and Automating
SQL Server
Lesson 26
Skills Matrix
Skill Matrix
Automating Administrative Activities
• Maintaining an efficient operation means you
continually—as in all the time—watch for new
bottlenecks.
• Reducing a bottleneck simply reveals a new
bottleneck, so your job is to balance all bottlenecks
equally so no one interferes more than another.
• Balancing bottlenecks requires a proactive program of
regular maintenance. Maintenance functions are best
performed during off-hours or minimal-use hours so as
to cause the least interference to productivity.
• Automating these functions allows you to sleep well at
night (the SWAN principle).
Maintenance Plans
Maintaining Indexes
• You learned you need indexes on most SQL
Server tables to speed up access to the data.
• Without these indexes, SQL Server would
need to perform table scans, reading every
record in the table, to find any amount of
data.
• To keep your indexes running at peak
efficiency, you must perform periodic
maintenance on them.
Maintaining Indexes
• The primary issue to watch for in an index is
page splitting.
• A page split is caused when a page of data
fills to 100 percent and more data must be
added to it.
• When a new page created is out of order
leads to fragmentation.
Reorganizing and Rebuilding Indexes
• If the amount of fragmentation on your index
is less than 10 percent, you really don’t need
to do anything.
• When it’s from 10 percent to 30 percent,
you should reorganize your index; and
anything higher requires a rebuild.
• To reorganize an index, use the ALTER INDEX
REORGANIZE statement.
Maintaining Statistics
• Whenever you perform a query on your data, SQL
Server’s Query Optimizer creates a map of which
tables and indexes to use to execute the query as
fast as possible.
• This map is called an execution plan.
• To choose the fastest index for the query, SQL
Server must know how much of the data in the
index is applicable to the query being run.
• To get this information, SQL Server reads the index
statistics, which tell the database engine what the
index holds.
Maintaining Statistics
• The problem is that indexes change when the data
in the underlying table changes. When the index
changes, the statistics become out of date.
• This isn’t a problem if you created the index with
the default value of STATISTICS_NORECOMPUTE
OFF, because SQL Server will automatically update
statistics for you.
• But if your server is low on resources and you had
to turn this value on, then you will need to update
your statistics manually.
Maintaining Databases
• Database files are volatile.
• They are constantly being changed and
updated, so just like any other volatile file on
a system, they can become corrupt.
• Consequently, it is important to perform
periodic maintenance on database files.
DBCC CHECKDB
• You can use DBCC CHECKDB to check the
allocation, logical, and structural integrity of
objects in the database.
• This is necessary because databases are in
a constant state of flux; in other words, data
are always being inserted, updated, and
deleted.
• So, it stands to reason that occasionally
something is not going to be written to disk
correctly and will need to be repaired.
Shrink Databases
• When users enter new data into a database, SQL
Server may have to expand the size of the data and log
files on disk to accommodate the new data.
• When this happens, the size of the physical file on disk
increases, so the hard drive has less space available
for other files.
• When a user deletes data, though, the size of the
physical file is not reduced; the file remains the same
size.
• This condition can result in wasted system resources
and possibly cause problems for other processes that
need disk space.
Shrink Databases
• Using SQL Server Management Studio, you can shrink
the entire database or just a single file at a time.
• SQL Server also provides the DBCC SHRINKFILE and
DBCC SHRINKDATABASE utilities to enable you to
script these functions or include them in a job for
scheduled processing.
• DBCC SHRINKDATABASE reduces the size of the data
and log files in the specified database, although the
database cannot be made smaller than the minimum
size specified when the database was originally
created.
Understanding Automation Basics
• You can automate nearly any administrative
task you can think of through SQL Server.
That may sound like an exaggeration, but
look at the features you can automate:
– Any Transact-SQL code
– Scripting languages such as VBScript or
Jscript using PowerShell
– Operating system commands
– Replication tasks
Understanding Automation Basics
• This functionality is powerful, so before you
start to use automation, you need to know
how it works.
• At the heart of SQL Server’s automation
capability is the SQL Server Agent service
(also referred to as “the agent”).
Understanding Automation Basics
• Automation and replication are the sole
functions of the service, which uses three
subcomponents to accomplish its
automation tasks:
– Alerts
– Operators
– Jobs
Configuring Database Mail
• You can use Database Mail to send e-mail for the SQL
Server services using the standard Simple Mail Transfer
Protocol (SMTP).
• It is actually a separate process that runs in the
background, so if a problem occurs, SQL Server is
unaffected.
• Database Mail is also scalable because it uses the
Service Broker queue, which allows the request to be
handled asynchronously and even saves the request if
the server goes down before it can be handled.
Operators
• When an alert is fired, it can be sent to a
user.
• Users who need to receive these messages
are known in SQL Server as operators.
• Operators are used to configure who
receives alerts and when they’re available to
receive these messages.
Operators
• You need to configure several settings to enable
SQL Server to contact the appropriate people when
problems occur.
• An operator is the object used in SQL Server to
configure all these settings.
• Net Send messages are messages sent from a
source machine to a destination machine that pop
up on the user’s screen.
Operators
Jobs
• A job is a series of tasks that can be
automated to run whenever you need them
to run.
• Some of these tasks need to be
accomplished in succession.
Jobs
Creating Alerts
• An alert is fired when an event (usually a
problem) occurs on SQL Server; some
examples are a full transaction log or
incorrect syntax in a query.
• These alerts can then be sent to an operator
so that they can be addressed.
• Alerts are based on one of three features: an
error number, an error severity level, or a
performance counter.
Types of Alerts
• Standard Errors
• Custom Errors
• Creating Performance Alerts
• WMI Alerts
Alerts
Maintenance Plan Wizard
• You need to perform many tasks to keep your
databases running at peak performance at all
times.
• Such tasks as index reorganizations, database file
size reductions, and database and transaction log
backups all need to happen on a regular basis to
keep your server running smoothly.
• To avoid all the labor of creating multiple jobs for
multiple databases, use the Maintenance Plan
Wizard.
Summary
• This lesson covered a lot of ground, but what you
learned here will save you a lot of time and effort in
server administration and reporting.
• First you learned about required maintenance—that
you need to perform many tasks on your server to
keep it running smoothly and efficiently.
• You must back up databases and transaction logs,
reorganize index and data pages inside the
database files, check for database integrity
regularly, and keep statistics up to date.
Summary
• You also learned that automation includes three
main components: operators, jobs, and alerts.
• Operators are the individuals who are notified
when a problem needs attention; they can be
notified via e-mail, pager, or Net Send messages.
• Jobs are series of tasks and schedules that can be
automated to activate at any time; they can include
Transact-SQL code, command executive code, or
scripting language code.
Summary
• You investigated mail support.
• To configure mail support, you learned you
first need create a mailhost database and
add a profile and an account.
• When these are in place, you can start
sending e-mail.
• If you want to send mail to operators, you
need to make MSDB a mailhost database.
Summary
• You learned how to create operators and configure
them to receive e-mail, pager, or Net Send
messages.
• You can also configure them to be available only at
certain times of the day by setting their availability.
• In addition, you learned how to create jobs.
• You can configure jobs to run any type of code at
any time, and you can configure them to inform an
operator when they complete, when they succeed,
or when they fail.
Summary
• Next you set alerts, which are used to notify an
operator when an error has occurred.
• Not all errors fire an event, though—only those that
are written to the Windows Event log and have an
alert configured will fire an alert that notifies
someone.
• You learned how to create alerts that are based on
the standard error messages that come with SQL
Server, as well as how to create your own custom
error messages that can be used for any purpose.
Summary
• You then learned how to create and use
performance alerts to stop problems before
they start.
• You also learned how to create WMI alerts
so you can be notified when server events
occur, such as CREATE TABLE or other DDL
statements.
Summary
• After learning the importance of periodic
maintenance and how to schedule jobs to
run automatically, you learned how to tie all
of that together by using the Maintenance
Plan Wizard to automate these processes for
you.
Summary for Certification Examination
• Know how to find and fix index fragmentation. When users
insert data in a table, SQL Server must make room for the
new data in the table and any associated indexes.
• When an index page gets too full, SQL Server moves half of
the data to a new page to make room for more data on the
existing page.
• This is called a page split; when it occurs, the pages are no
longer contiguous, and a condition called fragmentation
occurs. You can use sys.DM_DB_ INDEX_PHYSICAL_STATS
to find the amount of fragmentation on your indexes. If your
index is less than 30 percent fragmented, you can
reorganize it; anything higher than 30 percent requires a
rebuild to bring the index back into shape.
Summary for Certification Examination
• Know how to update statistics. SQL Server uses
statistics to figure out which index, if any, you can use
to speed up a SELECT query.
• If you created an index and told SQL Server not to
automatically recompute statistics for you, then you
will need to update them yourself using the UPDATE
STATISTICS command.
• Know how to repair databases. Because databases are
just files on the hard disk, they can become corrupted
on occasion.
• You need to know how to use DBCC CHECKDB to find
and fix imperfections in your databases.
Summary for Certification Examination
• Understand Database Mail. Database Mail is
used for sending e-mail from SQL Server.
• This is especially useful for sending
notifications for alerts and errors. Make sure
you know how to set up a mailhost database
and configure the SQL Server Agent to use a
profile to send mail.
Summary for Certification Examination
• Understand jobs, operators, and alerts. SQL Server
automation is built on three primary features.
• Jobs are a series of steps that can be run on a set
schedule.
• Operators are people or groups of people to be
notified by e-mail, pager, or Net Send message
when something happens on the server.
• Alerts are fired when something happens on the
system, such as an error.
• Alerts can notify an operator and automatically run
a job when fired.
Summary for Certification Examination
• Know the Maintenance Plan Wizard and Copy Database
Wizard.
• You can automate all the maintenance tasks discussed
in this lesson by using the Maintenance Plan Wizard.
• Just run the wizard, and it will create all the appropriate
jobs for you and then run them on a set schedule.
• The Copy Database Wizard is just as handy.
• When a developer needs to copy a database from
development to production, for instance, or you need to
copy a database from a satellite office to the home
office, you can use this wizard to simplify the process.